【SQL 基礎】游標（curosr）（二）如何調查子游標的增加

2020 年 3 月 26 日
筆記

概述

本文主要通過例子介紹如何調查子游標的增加。

關於游標的基礎，大家可以參考前一篇【游標（curosr）】。

如何調查子游標的增加

下面我們通過例子來介紹如何調查子游標的增加。

0 .準備測試數據

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                    20

1 .通過v$sqlarea 視圖查看執行後的狀況

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 查看不能產生了不同子游標的原因

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 的使用方法如下：

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

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_value

3.2 設定CURSORTRACE 為level 1。

SQL> alter system set events 'immediate trace name cursortrace level 577, address 903546294';System altered.

3.3 產生一個新的子游標。

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                    10

3.4 查看執行情況

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

SQL> alter system set events 'immediate trace name cursortrace level 2147483648, address 1';System altered.

3.6 輸出的CURSORTRACE例：

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）的值，來表明失敗的原因。如下面的值。）

Failed sharing : 4000000000000000★

方法3：cursordump

在11.2以後的環境中還可以通過cursordump來查看子游標產生的原因：

cursordump的取得方法：

alter system set events 'immediate trace name cursordump level 16'

4 .輸出的cursordump例：

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)。

其他

和游標相關的視圖：

V$OPEN_CURSOR  V$SESSION_CURSOR_CACHE  V$SYSTEM_CURSOR_CACHE  V$SQL_CURSOR  V$SQL_SHARED_CURSOR

和游標相關的初始化參數：

（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