[20201207]12c v$open_cursor视图.txt
--//12cv$open_cursor视图定义与11g有点不同,通过测试说明问题.注我使用18c，并不影响测试结果。
1.环境:
TTT@192.168.X.X:1521/orcl> select BANNER from v$version ;
BANNER
----------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
--//测试使用18c也是一样的。
2.测试:
--//session 1:
TTT@192.168.X.X:1521/orcl> @ spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------- ------------------ ------ ------- ---------- --------------------------------------------------
395 21260 32697 DEDICATED 28776 43 163433 alter system kill session '395,21260' immediate;
TTT@192.168.X.X:1521/orcl> select * from dept where deptno=10;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
--//执行5次以上.
TTT@192.168.X.X:1521/orcl> @ tpt/hash
HASH_VALUE SQL_ID CHILD_NUMBER HASH_HEX
---------- -------------------------- ------------ ------------------
911274289 4xamnunv51w9j 0 3650f131
--//session 2:
SYS@192.168.X.X:1521/orcl> select * from v$open_cursor where sid=395 and sql_id='4xamnunv51w9j'
2 @ prxx
==============================
SADDR : 00000000924558D0
SID : 395
USER_NAME : TTT
ADDRESS : 00000001064A04D8
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
HASH_VALUE : 911274289
SQL_ID : 4xamnunv51w9j
SQL_TEXT : select * from dept where deptno=10
LAST_SQL_ACTIVE_TIME : 2020-12-07 08:47:28
SQL_EXEC_ID :
CURSOR_TYPE : SESSION CURSOR CACHED
CHILD_ADDRESS : 00000000E5D4F420
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CON_ID : 3
PL/SQL procedure successfully completed.
--//注意看下划线,增加了CHILD_ADDRESS,CON_ID列。
SYS@192.168.X.X:1521/orcl> @ sharepool/shp4 4xamnunv51w9j 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
--------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- -------------------------- ----------
child handle address 00000000E5D4F420 00000001064A04D8 select * from dept where deptno=10 1 0 0 0000000076F94EE0 00000000BFBB9948 4072 16200 3219 23491 23491 911274289 4xamnunv51w9j 0
parent handle address 00000001064A04D8 00000001064A04D8 select * from dept where deptno=10 1 0 0 0000000100C653E0 00 4072 0 0 4072 4072 911274289 4xamnunv51w9j 65535
--//上面查询v$open_cursor的CHILD_ADDRESS :00000000E5D4F420,正好对上子游标的KGLHDADR=00000000E5D4F420。
SYS@192.168.X.X:1521/orcl> set desc linenum on
SYS@192.168.X.X:1521/orcl> @ desc v$open_cursor
Name Null? Type
------------------------------- -------- ----------------------------
1 SADDR RAW(8)
2 SID NUMBER
3 USER_NAME VARCHAR2(128)
4 ADDRESS RAW(8)
5 HASH_VALUE NUMBER
6 SQL_ID VARCHAR2(13)
7 SQL_TEXT VARCHAR2(60)
8 LAST_SQL_ACTIVE_TIME DATE
9 SQL_EXEC_ID NUMBER
10 CURSOR_TYPE VARCHAR2(64)
11 CHILD_ADDRESS RAW(8)
12 CON_ID NUMBER
--//可以发现12c比11g视图v$open_cursor增加2列.CHILD_ADDRESS,CON_ID.
--//也就是12c视图里面增加子光标handle address.
--//对比11g的情况：
SCOTT@book> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> set desc linenum on
SCOTT@book> @ desc v$open_cursor
Name Null? Type
------------------------------- -------- ----------------------------
1 SADDR RAW(8)
2 SID NUMBER
3 USER_NAME VARCHAR2(30)
4 ADDRESS RAW(8)
5 HASH_VALUE NUMBER
6 SQL_ID VARCHAR2(13)
7 SQL_TEXT VARCHAR2(60)
8 LAST_SQL_ACTIVE_TIME DATE
9 SQL_EXEC_ID NUMBER
10 CURSOR_TYPE VARCHAR2(64)
3.继续测试:
--//session 1:
TTT@192.168.X.X:1521/orcl> alter session set optimizer_index_caching=1;
Session altered.
select * from dept where deptno=10;
--//执行多次.
--//session 2:
SYS@192.168.X.X:1521/orcl> select * from v$open_cursor where sid=395 and sql_id='4xamnunv51w9j'
2 @ prxx
==============================
SADDR : 00000000924558D0
SID : 395
USER_NAME : TTT
ADDRESS : 00000001064A04D8
HASH_VALUE : 911274289
SQL_ID : 4xamnunv51w9j
SQL_TEXT : select * from dept where deptno=10
LAST_SQL_ACTIVE_TIME : 2020-12-07 08:47:28
SQL_EXEC_ID :
CURSOR_TYPE : SESSION CURSOR CACHED
CHILD_ADDRESS : 00000000E5D4F420
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CON_ID : 3
==============================
SADDR : 00000000924558D0
SID : 395
USER_NAME : TTT
ADDRESS : 00000001064A04D8
HASH_VALUE : 911274289
SQL_ID : 4xamnunv51w9j
SQL_TEXT : select * from dept where deptno=10
LAST_SQL_ACTIVE_TIME : 2020-12-07 08:53:03
SQL_EXEC_ID :
CURSOR_TYPE : SESSION CURSOR CACHED
CHILD_ADDRESS : 00000001031D80B8
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CON_ID : 3
PL/SQL procedure successfully completed.
SYS@192.168.X.X:1521/orcl> set linesize 300
SYS@192.168.X.X:1521/orcl> @ sharepool/shp4 4xamnunv51w9j 0
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
--------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- -------------------------- ----------
child handle address 00000000E5D4F420 00000001064A04D8 select * from dept where deptno=10 1 0 0 0000000076F94EE0 00000000BFBB9948 4072 16200 5035 25307 25307 911274289 4xamnunv51w9j 0
child handle address 00000001031D80B8 00000001064A04D8 select * from dept where deptno=10 1 0 0 00000001031D7EF8 00000000E5C9A580 4072 16200 5035 25307 25307 911274289 4xamnunv51w9j 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
parent handle address 00000001064A04D8 00000001064A04D8 select * from dept where deptno=10 1 0 0 0000000100C653E0 00 8144 0 0 8144 8144 911274289 4xamnunv51w9j 65535
--//子游标句柄地址能与前面的视图对上.正是通过这个子游标句柄地址,能很快定位语句的执行计划,减少latch 或者mutex的消耗.
SYS@192.168.X.X:1521/orcl> column VIEW_DEFINITION format a80
SYS@192.168.X.X:1521/orcl> select * from V$FIXED_VIEW_DEFINITION where view_name='GV$OPEN_CURSOR';
VIEW_NAME VIEW_DEFINITION CON_ID
--------------- -------------------------------------------------------------------------------- ----------
GV$OPEN_CURSOR select inst_id,kgllkuse, kgllksnm, user_name, kglhdpar, kglnahsh, 0
kgllksqlid, kglnaobj, kgllkest,
decode(kgllkexc, 0, to_number(NULL), kgllkexc), kgllkctp,
kgllkhdl, con_id from x$k
gllk where kglhdnsp = 0 and kglhdpar != kgllkhdl
--//12c v$open_cutsor增加CON_ID 字段外,还增加CHILD_ADDRESS字段.这样更加清晰指示执行计划使用那个子光标.
--//上官方网站.以下是11.1的参考：
https://docs.oracle.com/cd/B28359_01/server.111/b28320/dynviews_2081.htm#REFRN30166
V$OPEN_CURSOR
V$OPEN_CURSOR lists cursors that each user session currently has opened and parsed.
Column Datatype Description
-----------------------------------------------------------------------------------------------------------------------------------------------
SADDR RAW(4 | 8) Session address
SID NUMBER Session identifier
USER_NAME VARCHAR2(30) User that is logged in to the session
ADDRESS RAW(4 | 8) Used with HASH_VALUE to uniquely identify the SQL statement being executed in the session
HASH_VALUE NUMBER Used with ADDRESS to uniquely identify the SQL statement being executed in the session
SQL_ID VARCHAR2(13) SQL identifier of the SQL statement being executed in the session
SQL_TEXT VARCHAR2(60) First 60 characters of the SQL statement that is parsed into the open cursor
LAST_SQL_ACTIVE_TIME DATE Time when this cursor was last executed
SQL_EXEC_ID NUMBER If the open cursor is executing, then the SQL execution identifier for that execution (see V$SQL_MONITOR)
--------------------------------------------------------------------------------------------------------------------------------------------------
--//11.2还增加了cursor_type字段.
--//这样就可以很好避免我以前遇到的困惑.链接:
--//http://blog.itpub.net/267265/viewspace-2682615/=>[20200326]dbms_monitor跟踪与SQL语句分析.txt
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2739863/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
