11G R2 V$SQL_MONITOR檢視繫結變數
Read bind variable values of currently executing SQL
If you are running on Oracle 11.2+ and have the licenses for Oracle Diagnostics & Tuning Packs and when Oracle's SQL monitoring feature actually kicks in, then you can use V$SQL_MONITOR to view the bind variable values of currently executing SQL. Normally the bind variable values live in the private memory (PGA) of a process, but when SQL Monitoring kicks in for a statement in Oracle 11.2, it will copy the current bind variable values to SGA (V$SQL_MONITOR), so these will be readable by other users (like you, who's trying to troubleshoot a performance problem) as well.A little example, executed on Oracle 11.2.0.1 is here. I set up couple of bind variables and then run a query which is designed to take very long time to complete:
SQL> VAR ename VARCHAR2(100)
SQL> VAR deptno NUMBER
SQL>
SQL> EXEC :ename:= 'TANEL'
PL/SQL procedure successfully completed.
SQL> EXEC :deptno:= 1234567890;
PL/SQL procedure successfully completed.
SQL> ed
Wrote file afiedit.sql
1 SELECT
2 COUNT(*)
3 FROM
4 scott.emp e
5 , scott.dept d
6 , (SELECT 1 FROM dual CONNECT BY LEVEL <= 1000000) x
7 , (SELECT 1 FROM dual CONNECT BY LEVEL <= 1000000) y
8 WHERE
9 e.deptno = d.deptno
10 AND d.deptno < :deptno
11* AND e.ename != :ename
12 /
Once the SQL monitoring kicks in (in about 5 seconds of CPU + IO wait time usage by default), the bind variable values will appear in V$SQL_MONITOR.BIND_XML column for my session:
SQL> select xmltype(binds_xml) from v$sql_monitor where sid = 26 and status = 'EXECUTING';
XMLTYPE(BINDS_XML)
-----------------------------------------------------------------------------------------------------------
Here you go, in addition to current bind variable values I even see the bind variable data types (and their max lengths) which can be useful when troubleshooting some types or problems (like cursor sharing or implicit datatype conversion related problems).
Note that there's an undocumented parameter _sqlmon_binds_xml_format which controls whether the bind values are shown in human-readable format or a hex-dump in the BINDS_XML column:
SQL> @pd _sqlmon_binds_xml_format
Show all parameters and session values from x$ksppi/x$ksppcv...
NAME VALUE DESCRIPTION
---------------------------- ------------- -------------------------------------------------------
_sqlmon_binds_xml_format default format of column binds_xml in [G]V$SQL_MONITOR
SQL> @pvalid _sqlmon_binds_xml_format
Display valid values for multioption parameters matching "sqlmon"...
PAR# PARAMETER ORD VALUE DEFAULT
------ -------------------------------------------------- ---------- ------------------------------ -------
2227 _sqlmon_binds_xml_format 1 DEFAULT
_sqlmon_binds_xml_format 2 HEXDUMP
So, if you are not running on Oracle 11.2 or you don't have Diag+Tuning pack licenses or if the SQL Monitoring just doesn't kick in due to some bug, then what to do?
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/22034023/viewspace-708041/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 檢視繫結變數變數
- 透過v$sql_bind_capture 檢視繫結變數。SQLAPT變數
- 通過v$sql_bind_capture 檢視繫結變數。SQLAPT變數
- 檢視未繫結變數的sql變數SQL
- 11g新動態效能檢視V$SQL_MONITOR,V$SQL_PLAN_MONITORSQL
- Oracle 變數繫結與變數窺視合集Oracle變數
- oracle繫結變數窺視(zt)Oracle變數
- oracle 11g 新特性之動態繫結變數窺視(二)Oracle變數
- oracle 11g 新特性之動態繫結變數窺視(一)Oracle變數
- 繫結變數窺視測試案例變數
- 10g以後檢視未使用繫結變數的sql變數SQL
- 使用remove_constants工具檢視Oracle是否使用繫結變數REMOracle變數
- 【原創】Oracle 變數繫結與變數窺視合集Oracle變數
- 繫結變數變數
- v$sql_bind_capture 獲取繫結變數SQLAPT變數
- [20190506]檢視巢狀與繫結變數.txt巢狀變數
- 繫結變數之繫結變數窺探(Bind Peeking)變數
- Oracle 繫結變數Oracle變數
- v$sql_bind_capture 獲取繫結變數資訊SQLAPT變數
- oracle bind value peeking繫結變數窺視Oracle變數
- 繫結變數窺測變數
- PLSQL使用繫結變數SQL變數
- Oracle之繫結變數Oracle變數
- 關於繫結變數變數
- V$sql查詢未使用繫結變數的語句SQL變數
- 【優化】使用繫結變數 OR 不使用繫結變數,這不是問題!優化變數
- ORACLE 繫結變數用法總結Oracle變數
- 【最佳化】使用繫結變數 OR 不使用繫結變數,這不是問題!變數
- 繫結變數窺測的演變變數
- 繫結變數的測試變數
- Oracle 繫結變數窺探Oracle變數
- oracle 繫結變數(bind variable)Oracle變數
- 如何獲取繫結變數變數
- Oracle 繫結變數 詳解Oracle變數
- 關於繫結變數的SQL繫結什麼值變數SQL
- MySQL高階特性——繫結變數MySql變數
- SQL Developer中使用繫結變數SQLDeveloper變數
- 繫結變數之基本概念變數