概述
本文主要通过例子介绍如何调查子游标的增加。
关于游标的基础，大家可以参考前一篇【游标（curosr）】。
如何调查子游标的增加
下面我们通过例子来介绍如何调查子游标的增加。
0 .准备测试数据
代码语言：javascript复制SQL> conn scott/tiger
Connected.
SQL> var v1 varchar2(4);
SQL> begin
:v1 := 'WARD';
end;
/ 2 3 4 PL/SQL procedure successfully completed.SQL> select /* Cursor TEST */ * from emp where ENAME= :v1; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30SQL> var v1 varchar2(100);
SQL> begin
:v1 := 'SMITH';
end;
/ 2 3 4 PL/SQL procedure successfully completed.SQL> select /* Cursor TEST */ * from emp where ENAME= :v1; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 201 .通过v$sqlarea 视图查看执行后的状况
代码语言：javascript复制SQL> select sql_id,sql_text,version_count
from v$sqlarea
where sql_text like 'select /* Cursor TEST */ %'; 2 3 SQL_ID
-------------
SQL_TEXT
----------------------------------------------------
VERSION_COUNT
-------------
fawn2kwuxq1dq
select /* Cursor TEST */ * from emp where ENAME= :v1
2 ★VERSION_COUNT为2，产生了不同的子游标SQL> select SQL_ID,CHILD_NUMBER,EXECUTIONS from v$sql where sql_id='fawn2kwuxq1dq';SQL_ID CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
fawn2kwuxq1dq 0 1
fawn2kwuxq1dq 1 1我们可以看到相同的SQL文，产生了不同的子游标
方法1：v$sqlsharedcursor
2 .通过v$sqlsharedcursor 查看不能产生了不同子游标的原因
代码语言：javascript复制SQL> set linesize 500
SQL> select * from v$sql_shared_cursor where sql_id='fawn2kwuxq1dq';SQL_ID ADDRESS CHILD_ADDRESS CHILD_NUMBER U S O O S L F E B P I S T A B D L T B I I R L I O E M U T N F A P T D L D B P C S C P T M B M R O P M F L P L A F L R L H P B U REASON
------------- ---------------- ---------------- ------------ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - --------------------------------------------------------------------------------
fawn2kwuxq1dq 000000008B8644C8 000000008B864048 0 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N <ChildNode><ChildNumber>0</ChildNumber><ID>40</ID><reason>Bind mismatch(22)</rea
fawn2kwuxq1dq 000000008B8644C8 000000008A6415E0 1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N <ChildNode><ChildNumber>1</ChildNumber><ID>40</ID><reason>Bind mismatch(22)</rea通过v$sqlsharedcursor视图我们看到，子游标（CHILD_NUMBER：1）产生的原因是BINDLENGTHUPGRADEABLE，即： 新执行SQL的绑定变量的长度超过了以前执行时的变量定义长度。
（倒数第三的B列的值为Y，通过查询v$sqlsharedcursor视图定义，我们知道代表着BINDLENGTHUPGRADEABLE）。
方法2：CURSORTRACE
虽然对于本次问题我们很容易能够定义到原因，如果是10g以后的版本，对于某些情况或非常复杂的问题，我们可以使用CURSORTRACE 的功能来辅助调查。 CURSORTRACE 的使用方法如下：
代码语言：javascript复制10g以后的版本：<设定CURSORTRACE >
alter system set events 'immediate trace name cursortrace level <n>, address hash_value';<关闭CURSORTRACE>
alter system set events 'immediate trace name cursortrace level 2147483648, address 1';其中， <n>为跟踪的级别，通常可以设的值 ：(577=level 1, 578=level 2, 580=level 3)；
hash_value为SQL文的Hash值。※注意：CURSORTRACE 必须是在产生子游标的过程中有效，才能输出一些有用的信息。
下面我们接着上面的例子
3.1 定位SQL的HASH_VALUE
代码语言：javascript复制SQL> select sql_id, sql_text, hash_value from v$sqlarea where sql_text like 'select /* Cursor TEST */ %';SQL_ID
-------------
SQL_TEXT
-----------------------------------------------------
HASH_VALUE
----------
fawn2kwuxq1dq
select /* Cursor TEST */ * from emp where ENAME= :v1
903546294 ★hash_value3.2 设定CURSORTRACE 为level 1。
代码语言：javascript复制SQL> alter system set events 'immediate trace name cursortrace level 577, address 903546294';System altered.3.3 产生一个新的子游标。
代码语言：javascript复制SQL> var v1 varchar2(800);
SQL> begin
:v1 := 'MILLER';
end;
/ 2 3 4 PL/SQL procedure successfully completed.SQL> select /* Cursor TEST */ * from emp where ENAME= :v1; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7934 MILLER CLERK 7782 23-JAN-82 1300 103.4 查看执行情况
代码语言：javascript复制SQL> select SQL_ID,CHILD_NUMBER,EXECUTIONS from v$sql where sql_id='fawn2kwuxq1dq';SQL_ID CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
fawn2kwuxq1dq 0 1
fawn2kwuxq1dq 1 1
fawn2kwuxq1dq 2 1 ★产生了新的子游标3.5 关闭CURSORTRACE
代码语言：javascript复制SQL> alter system set events 'immediate trace name cursortrace level 2147483648, address 1';System altered.3.6 输出的CURSORTRACE例：
代码语言：javascript复制CUR#1 XSC 0x2b613f6f25d0 CHILD#-1 CI (nil) CTX (nil)
PARSING SQLTEXT=select /* Cursor TEST */ * from emp where ENAME= :v1
SQLHASH=35db05b6
Checking for already pinned child. fbcflg 108
No valid child pinned
Parent 0x8a7f5940(0x8a7ea7d0) ready for search
kksCheckCursor: next child is #1
kksCheckCursor: pinning child #1 in shared mode 0x8a7f5840 0x8a7dad48
Compilation environment difference Failed sharing : 0
SQL pgadep:0 pgapls:0 user
Calling for auth
Checking for already pinned child. fbcflg 102
Object is invalid
No valid child pinned
Parent 0x8a7f5940(0x8a7ea7d0) ready for search
kksCheckCursor: next child is #1
kksCheckCursor: pinning child #1 in shared mode 0x8a7f5840 0x8a7dad48
Compilation environment difference Failed sharing : 0
SQL pgadep:0 pgapls:0 user
Calling for auth
kksUnlockChild: releasing child
Failed sharing : 4000000000000000★查找失败的原因4000000000000000
kksSearchChildList: no suitable child found (hash_match=1)
SearchChildList failed ctx=(nil) xscflg=100432 ctxflg=0 flg=102
allocated new child slot bi=0x8a96c048 flg=1 cld=2 hsh=35db05b6
kkshinins insert child into incomplete list bi=0x8a96c048 cld=2 flg=25
Created new child obj #2 dsfl=2002011 8003c00 xsc=0x2b613f6f25d0 chd=0x8a660b20 clk=0x91eb2710 ★追加了一个新的子游标
Pin new child obj #2 dsfl=2002011 xsc=0x2b613f6f25d0 chd=0x8a660b20 clk=0x91eb2710 cpn=(nil)
kksLoadChild: reload 0 path 2
kksLoadChild: reload 0 path 3
Compilation environment difference Failed sharing : 0
Change in cursor environment
SQL pgadep:0 pgapls:0 user
Compilation environment difference Failed sharing : 0
Change in cursor environment
SQL pgadep:0 pgapls:0 user
Compilation environment difference Failed sharing : 0
Change in cursor environment
SQL pgadep:0 pgapls:0 user
Compilation environment difference Failed sharing : 0
Change in cursor environment
SQL pgadep:0 pgapls:0 user
Child creation successful xsc=0x2b613f6f25d0 chd=0x8a660b20 cpn=0x8bba3680 sta=0 bi=0x8a96c048 cld=2
Add new child to parent list pi=0x8abad150 psn=2 bi=0x8a96c048 cld=2 flg=35 ★
kkshindel remove child from incomplete list bi=0x8a96c048 cld=2 flg=30
kkshhcins insert child into hash table bi=0x8a96c048 cld=2 flg=38
Downgrading child pin to share
Cursor about get executed
Checking for already pinned child. fbcflg 1
Checking for already pinned child. fbcflg 1
Cursor#1 mapped
Cursor unmapped*** 2016-07-19 13:35:10.907
CUR#1 XSC 0x2b613f6f25d0 CHILD#2 CI 0x8a96b150 CTX (nil)
Cursor#1 mapped
Closing cursor根据上面的 输出我们可以看到查找失败的原因是4000000000000000，即：bind length upgradeable （当游标共享失败时，Oracle会更新位向量（bit vector）的值，来表明失败的原因。如下面的值。）
代码语言：javascript复制Failed sharing : 4000000000000000★方法3：cursordump
在11.2以后的环境中还可以通过cursordump来查看子游标产生的原因：
cursordump的取得方法：
代码语言：javascript复制alter system set events 'immediate trace name cursordump level 16'4 .输出的cursordump例：
代码语言：javascript复制Bucket: #=66998 Mutex=0x8e099ca0(0, 14, 0, 6)
LibraryHandle: Address=0x8a7ea7d0 Hash=35db05b6 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
ObjectName: Name=select /* Cursor TEST */ * from emp where ENAME= :v1
FullHashValue=a89535ce44458f11e572829735db05b6 Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=903546294 OwnerIdn=83
Statistics: InvalidationCount=0 ExecutionCount=3 LoadCount=4 ActiveLocks=0 TotalLockCount=3 TotalPinCount=1
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=3 Version=0 BucketInUse=2 HandleInUse=2 HandleReferenceCount=0
Concurrency: DependencyMutex=0x8a7ea880(0, 3, 0, 0) Mutex=0x8a7ea910(125, 91, 0, 6)
Flags=RON/PIN/TIM/PN0/DBN/[10012841]
WaitersLists:
Lock=0x8a7ea860[0x8a7ea860,0x8a7ea860]
Pin=0x8a7ea840[0x8a7ea840,0x8a7ea840]
LoadLock=0x8a7ea8b8[0x8a7ea8b8,0x8a7ea8b8]
Timestamp: Current=07-19-2016 13:33:05
HandleReference: Address=0x8a7ea9b0 Handle=(nil) Flags=[00]
LibraryObject: Address=0x8abad0b0 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
ChildTable: size='16'
Child: id='0' Table=0x8abadf60 Reference=0x8abad9a0 Handle=0x8a7ea350
Child: id='1' Table=0x8abadf60 Reference=0x8abadd38 Handle=0x8a7dad48
Child: id='2' Table=0x8abadf60 Reference=0x8a96c2e8 Handle=0x8a660b20
NamespaceDump:
Parent Cursor: sql_id=fawn2kwuxq1dq parent=0x8abad150 maxchild=3 plk=n ppn=n piflg=82 pflg=10008100 oct=03 psn=3 app(hash)=SQL*Plus(3669949024) act(hash)=(0) caller obj#=0 line#=0
CursorDiagnosticsNodes:
ChildNode: ChildNumber=1 ID=40 reason=Bind mismatch(22) size=4x4 bind_position=0 original_oacflg=3 original_oacmxl=2000 upgradeable_new_oacmxl=32 ★
ChildNode: ChildNumber=0 ID=40 reason=Bind mismatch(22) size=4x4 bind_position=0 original_oacflg=3 original_oacmxl=32 upgradeable_new_oacmxl=2000 ★可以看到上面ChildNumber=1和ChildNumber=0产生的原应都是由于Bind mismatch(22)。
其他
和游标相关的视图：
代码语言：javascript复制V$OPEN_CURSOR
V$SESSION_CURSOR_CACHE
V$SYSTEM_CURSOR_CACHE
V$SQL_CURSOR
V$SQL_SHARED_CURSOR和游标相关的初始化参数：
代码语言：javascript复制（11.2.0.4）
SQL> show parameter cursorNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_bind_capture_destination string memory disk
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 300
session_cached_cursors integer 50版权声明：本文为订阅号TeacherWhat原创文章，转载必须注明出处，作者保留一切相关权力！
