[ORACLE]查看SQL绑定变量具体值查看SQL绑定变量值

admin 运维技术 2023-02-26 1411浏览 0

如何查看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.