如何查看SQL的绑定变量的值
#查看SQL语句
SQL> SELECT A.SQL_TEXT,A.EXECUTIONS,A.MODULE FROM V$SQL A WHERE A.SQL_ID IN ('fqyhfw5h5rs5q'); SQL_TEXT EXECUTIONS MODULE ---------------------------------------------------------------------------------------------------- ---------- -------------------- SELECT * FROM "LEIN" "LEIN" WHERE "MANDT"=:A0 AND "LENUM"=:A1 FOR UPDATE 191679 SAPLL03T
#查看该SQL的绑定变量的值具体
SQL> col VALUE_STRING for A50 SQL> col NAME for A10 SQL> col SQL_ID for A15 SQL> SELECT A.SQL_ID,A.NAME,A.POSITION,A.DATATYPE_STRING,A.VALUE_STRING,LAST_CAPTURED FROM V$SQL_BIND_CAPTURE A WHERE A.SQL_ID IN ('fqyhfw5h5rs5q') ; SQL_ID NAME POSITION DATATYPE_STRING VALUE_STRING LAST_CAPTURED --------------------------------------- ---------- ---------- --------------------------------------------- -------------------------------------------------- --------------- fqyhfw5h5rs5q :A0 1 CHAR(32) 011 09-APR-20 fqyhfw5h5rs5q :A1 2 CHAR(128) 00950000000013160652 09-APR-20
#查看该SQL历史执行计划
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR(SQL_ID => 'fqyhfw5h5rs5q' )) ; PLAN_TABLE_OUTPUT ------------------------------------------------------------------------- SQL_ID fqyhfw5h5rs5q -------------------- SELECT * FROM "LEIN" "LEIN" WHERE "MANDT"=:A0 AND "LENUM"=:A1 FOR UPDATE Plan hash value: 976062693 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 (100)| | | 1 | FOR UPDATE | | | | | | | 2 | TABLE ACCESS BY INDEX ROWID| LEIN | 1 | 134 | 1 (0)| 00:00:01 | | 3 | INDEX UNIQUE SCAN | LEIN~0 | 1 | | 0 (0)| | --------------------------------------------------------------------------------------- 15 rows selected.
#查询DBA_HIST_SQLBIND
SELECT SNAP_ID，SQL_ID,NAME,POSITION,DATATYPE_STRING，VALUE_STRING,LAST_CAPTURED FROM DBA_HIST_SQLBIND WHERE SQL_ID='fqyhfw5h5rs5q'; SQL> SELECT SQL_ID,NAME,POSITION,DATATYPE_STRING,VALUE_STRING,COUNT(1) FROM DBA_HIST_SQLBIND WHERE SQL_ID='fqyhfw5h5rs5q' AND SNAP_ID BETWEEN 82059 AND 82110 GROUP BY SQL_ID,NAME,POSITION,DATATYPE_STRING，VALUE_STRING; SQL_ID NAME POSITION DATATYPE_STRING VALUE_STRING COUNT(1) --------------- ---------- ---------- --------------- -------------------------------------------------- ---------- fqyhfw5h5rs5q :A1 2 CHAR(128) 00950000000013160652 1 fqyhfw5h5rs5q :A1 2 CHAR(128) 00950000000013104952 1 fqyhfw5h5rs5q :A0 1 CHAR(32) 011 15 fqyhfw5h5rs5q :A1 2 CHAR(128) 00950000000013166613 1 fqyhfw5h5rs5q :A1 2 CHAR(128) 00950000000013037748 1 fqyhfw5h5rs5q :A1 2 CHAR(128) 00950000000013164602 1 fqyhfw5h5rs5q :A1 2 CHAR(128) 00950000000013105764 1 fqyhfw5h5rs5q :A1 2 CHAR(128) 00950000000013166287 1 fqyhfw5h5rs5q :A1 2 CHAR(128) 00950000000013165011 1 fqyhfw5h5rs5q :A1 2 CHAR(128) 00950000000013165266 1 fqyhfw5h5rs5q :A1 2 CHAR(128) 00950000000013154547 1 fqyhfw5h5rs5q :A1 2 CHAR(128) 00950000000013161927 1 fqyhfw5h5rs5q :A1 2 CHAR(128) 00950000000013149054 1 fqyhfw5h5rs5q :A1 2 CHAR(128) 00950000000013156540 1 fqyhfw5h5rs5q :A1 2 CHAR(128) 00950000000013115472 1 fqyhfw5h5rs5q :A1 2 CHAR(128) 00950000000013166044 1 16 rows selected. SQL> SELECT SQL_ID,NAME,DATATYPE_STRING,VALUE_STRING,COUNT(1) FROM DBA_HIST_SQLBIND WHERE SQL_ID='fqyhfw5h5rs5q' AND SNAP_ID BETWEEN 82059 AND 82110 GROUP BY SQL_ID,NAME,DATATYPE_STRING，VALUE_STRING; 2 SQL_ID NAME DATATYPE_STRING VALUE_STRING COUNT(1) --------------- ---------- --------------- -------------------------------------------------- ---------- fqyhfw5h5rs5q :A1 CHAR(128) 00950000000013156540 1 fqyhfw5h5rs5q :A1 CHAR(128) 00950000000013037748 1 fqyhfw5h5rs5q :A1 CHAR(128) 00950000000013105764 1 fqyhfw5h5rs5q :A1 CHAR(128) 00950000000013160652 1 fqyhfw5h5rs5q :A1 CHAR(128) 00950000000013161927 1 fqyhfw5h5rs5q :A1 CHAR(128) 00950000000013166044 1 fqyhfw5h5rs5q :A1 CHAR(128) 00950000000013164602 1 fqyhfw5h5rs5q :A1 CHAR(128) 00950000000013104952 1 fqyhfw5h5rs5q :A1 CHAR(128) 00950000000013166613 1 fqyhfw5h5rs5q :A1 CHAR(128) 00950000000013149054 1 fqyhfw5h5rs5q :A1 CHAR(128) 00950000000013165011 1 fqyhfw5h5rs5q :A1 CHAR(128) 00950000000013166287 1 fqyhfw5h5rs5q :A0 CHAR(32) 011 15 fqyhfw5h5rs5q :A1 CHAR(128) 00950000000013115472 1 fqyhfw5h5rs5q :A1 CHAR(128) 00950000000013165266 1 fqyhfw5h5rs5q :A1 CHAR(128) 00950000000013154547 1 16 rows selected.
