【SQL 基础】游标（curosr）（二）如何调查子游标的增加

SQLplusDB

发布于 2020-03-26 10:20:38

5390

发布于 2020-03-26 10:20:38

文章被收录于专栏：Oracle数据库技术

概述

本文主要通过例子介绍如何调查子游标的增加。

关于游标的基础，大家可以参考前一篇【游标（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

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2016-07-30，如有侵权请联系 cloudcommunity@tencent.com 删除

css