[20201207]12c v$open_cursor视图.txt

来源：这里教程网时间：2026-03-03 16:17:08 作者：

[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

编辑推荐：

返回列表

[20201207]12c v$open_cursor视图.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推