第一部分 游标与对象之间的关系
(本实验以游标与表的之间的关系进行实验)。
alter">alibank@rac3>alter session set events '10046 trace name context forever, level 12';
Session altered.
select">alibank@rac3>select count(*) from xcy_test_libmutex where a=1;
COUNT(*)
----------
0
alter">alibank@rac3>alter session set events '10046 trace name context off';
Session altered.
该session 产生的dump 文件是。rac3_ora_12705.trc
sqlplus">oracle@rac3:/home/oracle>sqlplus "/ as sysdba "
SQL*Plus: Release 11.2.0.1.0 Production on Thu Aug 12 10:36:29 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
alter">sys@rac3>alter session set events 'immediate trace name library_cache level 12';
Session altered.
该dump 产生的文件是 rac3_ora_13186.trc
分析dump 文件。
Bucket: #=112546 Mutex=8bfc7210(0, 478, 0, 6)
LibraryHandle: Address=8e7dc168 Hash=1b6bb7a2 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
ObjectName: Name=ALIBANK.XCY_TEST_LIBMUTEX
FullHashValue=00f4874b8a77eb299738b5d61b6bb7a2 Namespace=TABLE/PROCEDURE(01) Type=TABLE(02) Identifier=115776 OwnerIdn=93
Statistics: InvalidationCount=0 ExecutionCount=0 LoadCount=1 ActiveLocks=0 TotalLockCount=2 TotalPinCount=2
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=0 KeepHandle=0 BucketInUse=2 HandleInUse=2
Concurrency: DependencyMutex=8e7dc218(0, 2, 0, 0) Mutex=8e7dc290(223, 44, 0, 6)
Flags=PIN/TIM/[00002801]
WaitersLists:
Lock=8e7dc1f8[8e7dc1f8,8e7dc1f8]
Pin=8e7dc208[8e7dc1d8,8e7dc1d8]
Timestamp: Current=08-12-2010 10:21:01
HandleReference: Address=8e7dc330 Handle=7cd85808 Flags=OWN[200]
LockInstance: id='LB00f4874b8a77eb29' GlobalEnqueue=0 ReleaseCount=0
PinInstance: id='NB00f4874b8a77eb29' GlobalEnqueue=0
ReferenceList:
Reference: Address=81bd48a8 Handle=4f670510 Flags=DEP[01]
Timestamp=08-12-2010 10:21:01 InvalidatedFrom=0
Reference: Address=81bde750 Handle=39cdb448 Flags=DEP[01]
Timestamp=08-12-2010 10:21:01 InvalidatedFrom=0
LibraryObject: Address=81bdd058 HeapMask=0000-0701-0701 Flags=EXS/LOC[0004] Flags2=[0000] PublicFlags=[0000]
DataBlocks:
Block: #='0' name=KGLH0^1b6bb7a2 pins=0 Change=NONE
Heap=39741848 Pointer=81bdd140 Extent=81bdcfd8 Flags=I/-/-/A/-/-
FreedLocation=0 Alloc=0.710938 Size=4.000000 LoadTime=4550974530
Block: #='8' name=KGLS^1b6bb7a2 pins=0 Change=NONE
Heap=81bdd358 Pointer=31d737d8 Extent=31d72a18 Flags=I/-/-/A/-/-
FreedLocation=0 Alloc=0.843750 Size=4.000000 LoadTime=4550974540
Block: #='9' name=KGLS^1b6bb7a2 pins=0 Change=NONE
Heap=81bdd470 Pointer=31d64848 Extent=31d63a18 Flags=I/-/-/A/-/-
FreedLocation=0 Alloc=0.429688 Size=4.000000 LoadTime=4550974550
Block: #='10' name=KGLS^1b6bb7a2 pins=0 Change=NONE
Heap=81bdd4f8 Pointer=31d60928 Extent=31d5fa18 Flags=I/-/-/A/-/-
FreedLocation=0 Alloc=0.210938 Size=4.000000 LoadTime=4550974550
Bucket: #=97758 Mutex=8bf7f370(0, 222, 0, 6)
LibraryHandle: Address=66bcd480 Hash=cebf7dde LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
ObjectName: Name=SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1") FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("XCY_TEST_LIBMUTEX") FULL("XCY_TEST_LIBMUTEX") NO_PARALLEL_INDEX("XCY_TEST_LIBMUTEX") */ :"SYS_B_2" AS C1, CASE WHEN "XCY_TEST_LIBMUTEX"."A"=:"SYS_B_3" THEN :"SYS_B_4" ELSE :"SYS_B_5" END AS C2 FROM "XCY_TEST_LIBMUTEX" "XCY_TEST_LIBMUTEX")
FullHashValue=728498d5b9686fef4b9145dfcebf7dde Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=3468656094 OwnerIdn=93
Statistics: InvalidationCount=0 ExecutionCount=1 LoadCount=2 ActiveLocks=0 TotalLockCount=1 TotalPinCount=1
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=1 KeepHandle=1 BucketInUse=0 HandleInUse=0
Concurrency: DependencyMutex=66bcd530(0, 0, 0, 0) Mutex=66bcd5a8(223, 20, 0, 6)
Flags=RON/PIN/TIM/PN0/DBN/[10012841]
WaitersLists:
Lock=66bcd510[66bcd510,66bcd510]
Pin=66bcd520[66bcd4f0,66bcd4f0]
Timestamp: Current=08-12-2010 10:32:07
LibraryObject: Address=81bd5058 HeapMask=0000-0001-0001 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
DataBlocks:
Block: #='0' name=PCUR^cebf7dde pins=0 Change=NONE
Heap=7c904de8 Pointer=81bd5140 Extent=81bd4fd8 Flags=I/-/P/A/-/-
FreedLocation=0 Alloc=1.515625 Size=4.000000 LoadTime=4550974550
ChildTable: size='16'
Child: id='0' Table=81bd5f08 Reference=81bd59a8 Handle=4f670510
NamespaceDump:
Parent Cursor: sql_id=4r4a5vz7byzfy parent=0x181bd5140 maxchild=1 plk=n ppn=n
Bucket: #=32264 Mutex=87e49080(0, 472, 0, 6)
LibraryHandle: Address=8e1f9c90 Hash=27467e08 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
ObjectName: Name=select count(*) from xcy_test_libmutex where a=1
FullHashValue=aee1f0a0b0fa17fa7ae8811c27467e08 Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=658931208 OwnerIdn=93
Statistics: InvalidationCount=0 ExecutionCount=1 LoadCount=2 ActiveLocks=0 TotalLockCount=1 TotalPinCount=1
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=1 KeepHandle=1 BucketInUse=0 HandleInUse=0
Concurrency: DependencyMutex=8e1f9d40(0, 0, 0, 0) Mutex=8e1f9db8(223, 20, 0, 6)
Flags=RON/PIN/TIM/PN0/DBN/[10012841]
WaitersLists:
Lock=8e1f9d20[8e1f9d20,8e1f9d20]
Pin=8e1f9d30[8e1f9d00,8e1f9d00]
Timestamp: Current=08-12-2010 10:32:07
LibraryObject: Address=81bdf058 HeapMask=0000-0001-0001 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
DataBlocks:
Block: #='0' name=PCUR^27467e08 pins=0 Change=NONE
Heap=4fbebc48 Pointer=81bdf140 Extent=81bdefd8 Flags=I/-/P/A/-/-
FreedLocation=0 Alloc=1.515625 Size=4.000000 LoadTime=4550974520
ChildTable: size='16'
Child: id='0' Table=81bdff08 Reference=81bdf9a8 Handle=39cdb448
NamespaceDump:
Parent Cursor: sql_id=7pu413hmnczh8 parent=0x181bdf140 maxchild=1 plk=n ppn=n
再次执行:
alter">alibank@rac3>alter session set events '10046 trace name context forever, level 12';
Session altered.
select">alibank@rac3>select count(*) from xcy_test_libmutex where a=2;
COUNT(*)
----------
0
alter">alibank@rac3>alter session set events '10046 trace name context off';
Session altered.
产生的dump 文件是rac3_ora_14191.trc。
alter">sys@rac3>alter session set events 'immediate trace name library_cache level 12';
产生的dump 文件是:rac3_ora_14170.trc
分析dump 文件。
Bucket: #=112546 Mutex=8bfc7210(0, 482, 0, 6)
LibraryHandle: Address=8e7dc168 Hash=1b6bb7a2 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
ObjectName: Name=ALIBANK.XCY_TEST_LIBMUTEX
FullHashValue=00f4874b8a77eb299738b5d61b6bb7a2 Namespace=TABLE/PROCEDURE(01) Type=TABLE(02) Identifier=115776 OwnerIdn=93
Statistics: InvalidationCount=0 ExecutionCount=0 LoadCount=1 ActiveLocks=0 TotalLockCount=4 TotalPinCount=4
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=0 KeepHandle=0 BucketInUse=4 HandleInUse=4
Concurrency: DependencyMutex=8e7dc218(0, 4, 0, 0) Mutex=8e7dc290(223, 69, 0, 6)
Flags=PIN/TIM/[00002801]
WaitersLists:
Lock=8e7dc1f8[8e7dc1f8,8e7dc1f8]
Pin=8e7dc208[8e7dc1d8,8e7dc1d8]
Timestamp: Current=08-12-2010 10:21:01
HandleReference: Address=8e7dc330 Handle=7cd85808 Flags=OWN[200]
LockInstance: id='LB00f4874b8a77eb29' GlobalEnqueue=0 ReleaseCount=0
PinInstance: id='NB00f4874b8a77eb29' GlobalEnqueue=0
ReferenceList:
Reference: Address=81cd0fa8 Handle=4f25fb70 Flags=DEP[01]
Timestamp=08-12-2010 10:21:01 InvalidatedFrom=0
Reference: Address=81bd48a8 Handle=4f670510 Flags=DEP[01]
Timestamp=08-12-2010 10:21:01 InvalidatedFrom=0
Reference: Address=81bde750 Handle=39cdb448 Flags=DEP[01]
Timestamp=08-12-2010 10:21:01 InvalidatedFrom=0
LibraryObject: Address=81bdd058 HeapMask=0000-0701-0701 Flags=EXS/LOC[0004] Flags2=[0000] PublicFlags=[0000]
DataBlocks:
Block: #='0' name=KGLH0^1b6bb7a2 pins=0 Change=NONE
Heap=39741848 Pointer=81bdd140 Extent=81bdcfd8 Flags=I/-/-/A/-/-
FreedLocation=0 Alloc=0.710938 Size=4.000000 LoadTime=4550974530
Block: #='8' name=KGLS^1b6bb7a2 pins=0 Change=NONE
Heap=81bdd358 Pointer=31d737d8 Extent=31d72a18 Flags=I/-/-/A/-/-
FreedLocation=0 Alloc=0.843750 Size=4.000000 LoadTime=4550974540
Block: #='9' name=KGLS^1b6bb7a2 pins=0 Change=NONE
Heap=81bdd470 Pointer=31d64848 Extent=31d63a18 Flags=I/-/-/A/-/-
FreedLocation=0 Alloc=0.429688 Size=4.000000 LoadTime=4550974550
Block: #='10' name=KGLS^1b6bb7a2 pins=0 Change=NONE
Heap=81bdd4f8 Pointer=31d60928 Extent=31d5fa18 Flags=I/-/-/A/-/-
FreedLocation=0 Alloc=0.210938 Size=4.000000 LoadTime=4550974550
对比一下dump Bucket: #=112546 Mutex=8bfc7210 的被再次refrence 的信息:
[ 1 ]
[ 2 ]
------------------------------------------------------------------------------------------------------------------------
1 * Bucket: #=112546 Mutex=8bfc7210(0, 482, 0, 6)
* Bucket: #=112546 Mutex=8bfc7210(0, 478, 0, 6)
=======
2 LibraryHandle: Address=8e7dc168 Hash=1b6bb7a2 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
3 ObjectName: Name=ALIBANK.XCY_TEST_LIBMUTEX
4 FullHashValue=00f4874b8a77eb299738b5d61b6bb7a2 Namespace=TABLE/PROCEDURE(01) Type=TABLE(02) Identifier=115776 OwnerIdn=93
=======
5 * Statistics: InvalidationCount=0 ExecutionCount=0 LoadCount=1 ActiveLocks=0 TotalLockCount=4 TotalPinCount=4
* Statistics: InvalidationCount=0 ExecutionCount=0 LoadCount=1 ActiveLocks=0 TotalLockCount=2 TotalPinCount=2
6 * Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=0 KeepHandle=0 BucketInUse=4 HandleInUse=4
* Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=0 KeepHandle=0 BucketInUse=2 HandleInUse=2
7 * Concurrency: DependencyMutex=8e7dc218(0, 4, 0, 0) Mutex=8e7dc290(223, 69, 0, 6)
* Concurrency: DependencyMutex=8e7dc218(0, 2, 0, 0) Mutex=8e7dc290(223, 44, 0, 6)
=======
8 Flags=PIN/TIM/[00002801]
9 WaitersLists:
10 Lock=8e7dc1f8[8e7dc1f8,8e7dc1f8]
11 Pin=8e7dc208[8e7dc1d8,8e7dc1d8]
12 Timestamp: Current=08-12-2010 10:21:01
13 HandleReference: Address=8e7dc330 Handle=7cd85808 Flags=OWN[200]
14 LockInstance: id='LB00f4874b8a77eb29' GlobalEnqueue=0 ReleaseCount=0
15 PinInstance: id='NB00f4874b8a77eb29' GlobalEnqueue=0
16 ReferenceList:
=======
17 18 =======
19 Reference: Address=81bd48a8 Handle=4f670510 Flags=DEP[01]
20 Timestamp=08-12-2010 10:21:01 InvalidatedFrom=0
21 Reference: Address=81bde750 Handle=39cdb448 Flags=DEP[01]
22 Timestamp=08-12-2010 10:21:01 InvalidatedFrom=0
23 LibraryObject: Address=81bdd058 HeapMask=0000-0701-0701 Flags=EXS/LOC[0004] Flags2=[0000] PublicFlags=[0000]
=======
24 * DataBlocks:
* DataBlocks:
=======
25 Block: #='0' name=KGLH0^1b6bb7a2 pins=0 Change=NONE
26 Heap=39741848 Pointer=81bdd140 Extent=81bdcfd8 Flags=I/-/-/A/-/-
27 FreedLocation=0 Alloc=0.710938 Size=4.000000 LoadTime=4550974530
28 Block: #='8' name=KGLS^1b6bb7a2 pins=0 Change=NONE
29 Heap=81bdd358 Pointer=31d737d8 Extent=31d72a18 Flags=I/-/-/A/-/-
30 FreedLocation=0 Alloc=0.843750 Size=4.000000 LoadTime=4550974540
31 Block: #='9' name=KGLS^1b6bb7a2 pins=0 Change=NONE
32 Heap=81bdd470 Pointer=31d64848 Extent=31d63a18 Flags=I/-/-/A/-/-
33 FreedLocation=0 Alloc=0.429688 Size=4.000000 LoadTime=4550974550
34 Block: #='10' name=KGLS^1b6bb7a2 pins=0 Change=NONE
35 Heap=81bdd4f8 Pointer=31d60928 Extent=31d5fa18 Flags=I/-/-/A/-/-
36 FreedLocation=0 Alloc=0.210938 Size=4.000000 LoadTime=4550974550
------------------------------------------------------------------------------------------------------------------------
现比较与分析一下差异:
1 * Bucket: #=112546 Mutex=8bfc7210(0, 482, 0, 6)
* Bucket: #=112546 Mutex=8bfc7210(0, 478, 0, 6)
这个(0, 482, 0, 6) 其中482的该Mutex 的大小,后面比前面大多4个(单位),应该是用于新Reference 的游标。
17 18
5 * Statistics: InvalidationCount=0 ExecutionCount=0 LoadCount=1 ActiveLocks=0 TotalLockCount=4 TotalPinCount=4
* Statistics: InvalidationCount=0 ExecutionCount=0 LoadCount=1 ActiveLocks=0 TotalLockCount=2 TotalPinCount=2
多refrence 一次,TotalLockCount +2 TotalPinCount +2 , 为什么呢?
再来分析以下,上面第二次10046 事件生成的trace 文件。
PARSING IN CURSOR #3 len=522 dep=1 uid=93 oct=3 lid=93 tim=1281581180150239 hv=3468656094 ad='166bcd480' sqlid='4r4a5vz7byzfy'
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1") FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("XCY_TEST_LIBMUTEX") FULL("XCY_TEST_LIBMUTEX") NO_PARALLEL_INDEX("XCY_TEST_LIBMUTEX") */ :"SYS_B_2" AS C1, CASE WHEN "XCY_TEST_LIBMUTEX"."A"=:"SYS_B_3" THEN :"SYS_B_4" ELSE :"SYS_B_5" END AS C2 FROM "XCY_TEST_LIBMUTEX" "XCY_TEST_LIBMUTEX") SAMPLESUB
END OF STMT
PARSE #3:c=0,e=181,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1918266520,tim=1281581180150236
BINDS #3:
Bind#0
oacdty=02 mxl=22(01) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0100 frm=00 csi=00 siz=24 off=0
kxsbbbfp=2b5e0dd8fff8 bln=22 avl=01 flg=09
value=0
Bind#1
oacdty=02 mxl=22(01) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0100 frm=00 csi=00 siz=24 off=0
kxsbbbfp=2b5e0dd8ffd8 bln=22 avl=01 flg=09
value=0
Bind#2
oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0100 frm=00 csi=00 siz=24 off=0
kxsbbbfp=2b5e0dd8ffb8 bln=22 avl=02 flg=09
value=1
Bind#3
oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0100 frm=00 csi=00 siz=24 off=0
kxsbbbfp=2b5e0dd8ff98 bln=22 avl=02 flg=09
value=2
Bind#4
oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0100 frm=00 csi=00 siz=24 off=0
kxsbbbfp=2b5e0dd8ff78 bln=22 avl=02 flg=09
value=1
Bind#5
oacdty=02 mxl=22(01) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0100 frm=00 csi=00 siz=24 off=0
kxsbbbfp=2b5e0dd8ff58 bln=22 avl=01 flg=09
value=0
EXEC #3:c=1000,e=276,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1918266520,tim=1281581180150569
WAIT #3: nam='Disk file operations I/O' ela= 9 FileOperation=2 fileno=14 filetype=2 obj#=-1 tim=1281581180150629
FETCH #3:c=0,e=335,p=0,cr=7,cu=0,mis=0,r=1,dep=1,og=1,plh=1918266520,tim=1281581180150923
STAT #3 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=7 pr=0 pw=0 time=0 us)'
STAT #3 id=2 cnt=927 pid=1 pos=1 obj=115776 op='TABLE ACCESS FULL XCY_TEST_LIBMUTEX (cr=7 pr=0 pw=0 time=926 us cost=3 size=5317 card=409)'
CLOSE #3:c=0,e=10,dep=1,type=0,tim=1281581180151036
=====================
PARSING IN CURSOR #1 len=50 dep=0 uid=93 oct=3 lid=93 tim=1281581180151661 hv=393329838 ad='17c88c430' sqlid='6u5xbvwbr3g5f'
select count(*) from xcy_test_libmutex where a=2
END OF STMT
PARSE #1:c=4000,e=3620,p=0,cr=8,cu=0,mis=1,r=0,dep=0,og=1,plh=1918266520,tim=1281581180151659
EXEC #1:c=0,e=20,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1918266520,tim=1281581180151724
WAIT #1: nam='SQL*Net message to client' ela= 5 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1281581180151763
FETCH #1:c=0,e=62,p=0,cr=7,cu=0,mis=0,r=1,dep=0,og=1,plh=1918266520,tim=1281581180151852
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=7 pr=0 pw=0 time=0 us)'
STAT #1 id=2 cnt=0 pid=1 pos=1 obj=115776 op='TABLE ACCESS FULL XCY_TEST_LIBMUTEX (cr=7 pr=0 pw=0 time=0 us cost=3 size=13 card=1)'
WAIT #1: nam='SQL*Net message from client' ela= 164 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1281581180152077
FETCH #1:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=1918266520,tim=1281581180152103
WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1281581180152122
WAIT #1: nam='SQL*Net message from client' ela= 505 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1281581180152639
=====================
可以看到,总共parse 与XCY_TEST_LIBMUTEX 的表相关的sql 两个,所以lock 了两次xcy_test_libmutex, pib 两次。
第二次解析为什么会执行 上面的CURSOR #3 , 在library cache 里面是否有相关记录,我们来比较一下:
[ 1 ]
[ 2 ]
------------------------------------------------------------------------------------------------------------------------
1 * Bucket: #=97758 Mutex=8bf7f370(0, 222, 0, 6)
* Bucket: #=97758 Mutex=8bf7f370(0, 225, 0, 6)
=======
2 LibraryHandle: Address=66bcd480 Hash=cebf7dde LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
3 ObjectName: Name=SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1") FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("XCY_TEST_LIBMUTEX") FULL("XCY_TEST_LIBMUTEX") NO_PARALLEL_INDEX("XCY_TEST_LIBMUTEX") */ :"SYS_B_2" AS C1, CASE WHEN "XCY_TEST_LIBMUTEX"."A"=:"SYS_B_3" THEN :"SYS_B_4" ELSE :"SYS_B_5" END AS C2 FROM "XCY_TEST_LIBMUTEX" "XCY_TEST_LIBMUTEX")
4
5 FullHashValue=728498d5b9686fef4b9145dfcebf7dde Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=3468656094 OwnerIdn=93
=======
6 * Statistics: InvalidationCount=0 ExecutionCount=1 LoadCount=2 ActiveLocks=0 TotalLockCount=1 TotalPinCount=1
* Statistics: InvalidationCount=0 ExecutionCount=2 LoadCount=2 ActiveLocks=0 TotalLockCount=2 TotalPinCount=1
7 * Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=1 KeepHandle=1 BucketInUse=0 HandleInUse=0
* Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=1 KeepHandle=1 BucketInUse=1 HandleInUse=1
8 * Concurrency: DependencyMutex=66bcd530(0, 0, 0, 0) Mutex=66bcd5a8(223, 20, 0, 6)
* Concurrency: DependencyMutex=66bcd530(0, 1, 0, 0) Mutex=66bcd5a8(223, 26, 0, 6)
=======
9 Flags=RON/PIN/TIM/PN0/DBN/[10012841]
10 WaitersLists:
11 Lock=66bcd510[66bcd510,66bcd510]
12 Pin=66bcd520[66bcd4f0,66bcd4f0]
13 Timestamp: Current=08-12-2010 10:32:07
14 LibraryObject: Address=81bd5058 HeapMask=0000-0001-0001 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
15 DataBlocks:
16 Block: #='0' name=PCUR^cebf7dde pins=0 Change=NONE
17 Heap=7c904de8 Pointer=81bd5140 Extent=81bd4fd8 Flags=I/-/P/A/-/-
18 FreedLocation=0 Alloc=1.515625 Size=4.000000 LoadTime=4550974550
19 ChildTable: size='16'
20 Child: id='0' Table=81bd5f08 Reference=81bd59a8 Handle=4f670510
=======
21 * NamespaceDump:
* NamespaceDump:
22 * Parent Cursor: sql_id=4r4a5vz7byzfy parent=0x181bd5140 maxchild=1 plk=n ppn=n
* Parent Cursor: sql_id=4r4a5vz7byzfy parent=0x181bd5140 maxchild=1 plk=n ppn=n
------------------------------------------------------------------------------------------------------------------------
发现,该游标的确直接被执行了。excution 的次数+1 , 另外 上面推测的Mutex=8bf7f370(0, 222, 0, 6)的第一个数字是大小,这个是错误的。
没有发现该cursor 多记录任何信息。
6 * Statistics: InvalidationCount=0 ExecutionCount=1 LoadCount=2 ActiveLocks=0 TotalLockCount=1 TotalPinCount=1
* Statistics: InvalidationCount=0 ExecutionCount=2 LoadCount=2 ActiveLocks=0 TotalLockCount=2 TotalPinCount=1
继续测试, 看真正完全的软解析对library cache 是否有影响。
select">alibank@rac3>select count(*) from xcy_test_libmutex where a=2;
COUNT(*)
----------
0
然后执行dump
alter">sys@rac3>alter session set events 'immediate trace name library_cache level 12';
Session altered.
dump file rac3_ora_20819.trc
首先比较一下游标的状态:select count(*) from xcy_test_libmutex where a=2 的状态。
[ 1 ]
[ 2 ]
------------------------------------------------------------------------------------------------------------------------
1 * Bucket: #=113838 Mutex=87fd8270(0, 409, 0, 6)
* Bucket: #=113838 Mutex=87fd8270(0, 415, 0, 6)
2 * LibraryHandle: Address=7c88c430 Hash=1771bcae LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
* LibraryHandle: Address=7c88c430 Hash=1771bcae LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
=======
3 ObjectName: Name=select count(*) from xcy_test_libmutex where a=2
4
5 FullHashValue=776a59a01699d77e6d17abdf1771bcae Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=393329838 OwnerIdn=93
=======
6 * Statistics: InvalidationCount=0 ExecutionCount=1 LoadCount=2 ActiveLocks=0 TotalLockCount=1 TotalPinCount=1
* Statistics: InvalidationCount=0 ExecutionCount=2 LoadCount=3 ActiveLocks=1 TotalLockCount=2 TotalPinCount=1
7 * Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=1 KeepHandle=1 BucketInUse=0 HandleInUse=0
* Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=2 KeepHandle=2 BucketInUse=1 HandleInUse=1
8 * Concurrency: DependencyMutex=7c88c4e0(0, 0, 0, 0) Mutex=7c88c558(223, 20, 0, 6)
* Concurrency: DependencyMutex=7c88c4e0(0, 1, 0, 0) Mutex=7c88c558(223, 37, 0, 6)
=======
9 Flags=RON/PIN/TIM/PN0/DBN/[10012841]
10 WaitersLists:
11 Lock=7c88c4c0[7c88c4c0,7c88c4c0]
12 Pin=7c88c4d0[7c88c4a0,7c88c4a0]
13 Timestamp: Current=08-12-2010 10:46:20
14 LibraryObject: Address=81cd18b0 HeapMask=0000-0001-0001 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
15 DataBlocks:
16 Block: #='0' name=PCUR^1771bcae pins=0 Change=NONE
17 Heap=39bbfb38 Pointer=81cd1998 Extent=81cd1830 Flags=I/-/P/A/-/-
18 FreedLocation=0 Alloc=1.515625 Size=4.000000 LoadTime=4551826430
19 ChildTable: size='16'
20 Child: id='0' Table=81cd2760 Reference=81cd2200 Handle=4f25fb70
=======
21 *
* Child: id='1' Table=81cd2760 Reference=81cd2518 Handle=4fb6dea0
22 * NamespaceDump:
* NamespaceDump:
23 * Parent Cursor: sql_id=6u5xbvwbr3g5f parent=0x181cd1998 maxchild=1 plk=n ppn=n
* Parent Cursor: sql_id=6u5xbvwbr3g5f parent=0x181cd1998 maxchild=2 plk=y ppn=n
!> Current Cursor Sharing Diagnostics Nodes:
!> Child Node: 0 ID=16 reason=Statistics Row Source mismatch(0) size=2x4
!> sqlstat_enabled: 1
!> kxstCheckStsEnv(OCT code)?: 0
------------------------------------------------------------------------------------------------------------------------
发现多一个子游标, 统计信息也发生改变了。
6 * Statistics: InvalidationCount=0 ExecutionCount=1 LoadCount=2 ActiveLocks=0 TotalLockCount=1 TotalPinCount=1
* Statistics: InvalidationCount=0 ExecutionCount=2 LoadCount=3 ActiveLocks=1 TotalLockCount=2 TotalPinCount=1
7 * Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=1 KeepHandle=1 BucketInUse=0 HandleInUse=0
* Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=2 KeepHandle=2 BucketInUse=1 HandleInUse=1
另外,我们发现下面这个游标也执行了,且(也)产生了一个子游标。 所以,上面的sql 并没有正在被软解析。
Bucket: #=97758 Mutex=8bf7f370(0, 231, 0, 6)
LibraryHandle: Address=66bcd480 Hash=cebf7dde LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
ObjectName: Name=SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1") FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("XCY_TEST_LIBMUTEX") FULL("XCY_TEST_LIBMUTEX") NO_PARALLEL_INDEX("XCY_TEST_LIBMUTEX") */ :"SYS_B_2" AS C1, CASE WHEN "XCY_TEST_LIBMUTEX"."A"=:"SYS_B_3" THEN :"SYS_B_4" ELSE :"SYS_B_5" END AS C2 FROM "XCY_TEST_LIBMUTEX" "XCY_TEST_LIBMUTEX")
FullHashValue=728498d5b9686fef4b9145dfcebf7dde Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=3468656094 OwnerIdn=93
Statistics: InvalidationCount=0 ExecutionCount=3 LoadCount=3 ActiveLocks=0 TotalLockCount=3 TotalPinCount=1
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=2 KeepHandle=2 BucketInUse=2 HandleInUse=2
Concurrency: DependencyMutex=66bcd530(0, 2, 0, 0) Mutex=66bcd5a8(223, 45, 0, 6)
Flags=RON/PIN/TIM/PN0/DBN/[10012841]
WaitersLists:
Lock=66bcd510[66bcd510,66bcd510]
Pin=66bcd520[66bcd4f0,66bcd4f0]
Timestamp: Current=08-12-2010 10:32:07
LibraryObject: Address=81bd5058 HeapMask=0000-0001-0001 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
DataBlocks:
Block: #='0' name=PCUR^cebf7dde pins=0 Change=NONE
Heap=7c904de8 Pointer=81bd5140 Extent=81bd4fd8 Flags=I/-/P/A/-/-
FreedLocation=0 Alloc=1.515625 Size=4.000000 LoadTime=4550974550
ChildTable: size='16'
Child: id='0' Table=81bd5f08 Reference=81bd59a8 Handle=4f670510
Child: id='1' Table=81bd5f08 Reference=81bd5d50 Handle=7c9e4ee0
NamespaceDump:
Parent Cursor: sql_id=4r4a5vz7byzfy parent=0x181bd5140 maxchild=2 plk=n ppn=n
Current Cursor Sharing Diagnostics Nodes:
Child Node: 0 ID=16 reason=Statistics Row Source mismatch(0) size=2x4
sqlstat_enabled: 1
kxstCheckStsEnv(OCT code)?: 0
再来看看表的library_cache 里面的信息。(省略data block 的部分)。
Bucket: #=112546 Mutex=8bfc7210(0, 490, 0, 6)
LibraryHandle: Address=8e7dc168 Hash=1b6bb7a2 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
ObjectName: Name=ALIBANK.XCY_TEST_LIBMUTEX
FullHashValue=00f4874b8a77eb299738b5d61b6bb7a2 Namespace=TABLE/PROCEDURE(01) Type=TABLE(02) Identifier=115776 OwnerIdn=93
Statistics: InvalidationCount=0 ExecutionCount=0 LoadCount=1 ActiveLocks=0 TotalLockCount=6 TotalPinCount=6
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=0 KeepHandle=0 BucketInUse=7 HandleInUse=7
Concurrency: DependencyMutex=8e7dc218(0, 7, 0, 0) Mutex=8e7dc290(223, 100, 0, 6)
Flags=PIN/TIM/[00002801]
WaitersLists:
Lock=8e7dc1f8[8e7dc1f8,8e7dc1f8]
Pin=8e7dc208[8e7dc1d8,8e7dc1d8]
Timestamp: Current=08-12-2010 10:21:01
HandleReference: Address=8e7dc330 Handle=7cd85808 Flags=OWN[200]
LockInstance: id='LB00f4874b8a77eb29' GlobalEnqueue=0 ReleaseCount=0
PinInstance: id='NB00f4874b8a77eb29' GlobalEnqueue=0
ReferenceList:
Reference: Address=4ac600c0 Handle=7c9e4ee0 Flags=DEP[01]
Timestamp=08-12-2010 10:21:01 InvalidatedFrom=0
Reference: Address=4ac60f68 Handle=4fb6dea0 Flags=DEP[01]
Timestamp=08-12-2010 10:21:01 InvalidatedFrom=0
Reference: Address=81cd0fa8 Handle=4f25fb70 Flags=DEP[01]
Timestamp=08-12-2010 10:21:01 InvalidatedFrom=0
Reference: Address=81bd48a8 Handle=4f670510 Flags=DEP[01]
Timestamp=08-12-2010 10:21:01 InvalidatedFrom=0
Reference: Address=81bde750 Handle=39cdb448 Flags=DEP[01]
Timestamp=08-12-2010 10:21:01 InvalidatedFrom=0
发现多了两个Reference, 多了两次TotalLockCount,TotalPinCount,
Statistics: InvalidationCount=0 ExecutionCount=0 LoadCount=1 ActiveLocks=0 TotalLockCount=4 TotalPinCount=4
Statistics: InvalidationCount=0 ExecutionCount=0 LoadCount=1 ActiveLocks=0 TotalLockCount=6 TotalPinCount=6
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=0 KeepHandle=0 BucketInUse=4 HandleInUse=4
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=0 KeepHandle=0 BucketInUse=7 HandleInUse=7
所以,如果发生子游标的解析,也是需要lock 与pin refrence 的object ,因为需要修改reference 的object 的reference list,
所以需要获得排他锁。
在同一个session 再执行select count(*) from xcy_test_libmutex where a=2;
select">alibank@rac3>select count(*) from xcy_test_libmutex where a=2;
COUNT(*)
----------
0
select">alibank@rac3>select count(*) from xcy_test_libmutex where a=2;
COUNT(*)
----------
0
然后进行dump library cache.
alter">sys@rac3>alter session set events 'immediate trace name library_cache level 12';
Session altered.
根据dump 里面的信息发现如下:
Bucket: #=113838 Mutex=87fd8270(0, 429, 0, 6)
LibraryHandle: Address=7c88c430 Hash=1771bcae LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
ObjectName: Name=select count(*) from xcy_test_libmutex where a=2
FullHashValue=776a59a01699d77e6d17abdf1771bcae Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=393329838 OwnerIdn=93
Statistics: InvalidationCount=0 ExecutionCount=3 LoadCount=3 ActiveLocks=1 TotalLockCount=3 TotalPinCount=1
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=2 KeepHandle=2 BucketInUse=2 HandleInUse=2
Concurrency: DependencyMutex=7c88c4e0(0, 3, 0, 0) Mutex=7c88c558(223, 55, 0, 6)
比较前一次的dump 信息,
ExecutionCount=3 比前一次多1, TotalLockCount=3 多1, TotalPinCount=1 不变。
Counters: BucketInUse=2 HandleInUse=2 次数各多1,其他不变。
推测: BucketInUse=2 表示的bucket 使用的次数, HandleInUse 是handle 使用的次数。
ActiveLocks=1 是指这个sursor 被lock 的个数,一般是session 数。session 退出,ActiveLocks 消失或者减少。
这个cursor 的统计值不变。
Bucket: #=97758 Mutex=8bf7f370(0, 231, 0, 6)
LibraryHandle: Address=66bcd480 Hash=cebf7dde LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
ObjectName: Name=SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1") FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("XCY_TEST_LIBMUTEX") FULL("XCY_TEST_LIBMUTEX") NO_PARALLEL_INDEX("XCY_TEST_LIBMUTEX") */ :"SYS_B_2" AS C1, CASE WHEN "XCY_TEST_LIBMUTEX"."A"=:"SYS_B_3" THEN :"SYS_B_4" ELSE :"SYS_B_5" END AS C2 FROM "XCY_TEST_LIBMUTEX" "XCY_TEST_LIBMUTEX")
cursor 所依赖表的统计信息
Bucket: #=112546 Mutex=8bfc7210(0, 490, 0, 6)
LibraryHandle: Address=8e7dc168 Hash=1b6bb7a2 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
ObjectName: Name=ALIBANK.XCY_TEST_LIBMUTEX
。。。。。
Statistics: InvalidationCount=0 ExecutionCount=0 LoadCount=1 ActiveLocks=0 TotalLockCount=7 TotalPinCount=7
totallockcount +1 , TotalPinCount +1.
其他信息不变。
注意:第一次使用共享的子游标时,会到refrence 的对象进行lock,第二次使用共享子游标时,就不再需要对依赖的object 进行 lock 与pin
操作,下面的实验中得到验证。
继续执行重复测试:repeat 10 times .
select count(*) from xcy_test_libmutex where a=2;
Bucket: #=113838 Mutex=87fd8270(0, 434, 0, 6)
LibraryHandle: Address=7c88c430 Hash=1771bcae LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
ObjectName: Name=select count(*) from xcy_test_libmutex where a=2
FullHashValue=776a59a01699d77e6d17abdf1771bcae Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=393329838 OwnerIdn=93
Statistics: InvalidationCount=0 ExecutionCount=13 LoadCount=3 ActiveLocks=1 TotalLockCount=4 TotalPinCount=1
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=2 KeepHandle=2 BucketInUse=3 HandleInUse=3
Concurrency: DependencyMutex=7c88c4e0(0, 4, 0, 0) Mutex=7c88c558(223, 63, 0, 6)
Flags=RON/PIN/TIM/PN0/DBN/[10012841]
WaitersLists:
Lock=7c88c4c0[7c88c4c0,7c88c4c0]
Pin=7c88c4d0[7c88c4a0,7c88c4a0]
Timestamp: Current=08-12-2010 10:46:20
LibraryObject: Address=81cd18b0 HeapMask=0000-0001-0001 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
DataBlocks:
Block: #='0' name=PCUR^1771bcae pins=0 Change=NONE
Heap=39bbfb38 Pointer=81cd1998 Extent=81cd1830 Flags=I/-/P/A/-/-
FreedLocation=0 Alloc=1.515625 Size=4.000000 LoadTime=4551826430
ChildTable: size='16'
Child: id='0' Table=81cd2760 Reference=81cd2200 Handle=4f25fb70
Child: id='1' Table=81cd2760 Reference=81cd2518 Handle=4fb6dea0
NamespaceDump:
Parent Cursor: sql_id=6u5xbvwbr3g5f parent=0x181cd1998 maxchild=2 plk=y ppn=n
Current Cursor Sharing Diagnostics Nodes:
Child Node: 0 ID=16 reason=Statistics Row Source mismatch(0) size=2x4
sqlstat_enabled: 1
kxstCheckStsEnv(OCT code)?: 0
发现:
Statistics: InvalidationCount=0 ExecutionCount=13 LoadCount=3 ActiveLocks=1 TotalLockCount=4 TotalPinCount=1
ExecutionCount=13比原来多10一次。
TotalLockCount=4 比原来多1次。
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=2 KeepHandle=2 BucketInUse=3 HandleInUse=3
BucketInUse=3 HandleInUse=3 比原来多1次.
推测, 同一个session ,对cursor 进行一次lock , 可以多次执行。
另外,发现
Bucket: #=112546 Mutex=8bfc7210(0, 490, 0, 6)
LibraryHandle: Address=8e7dc168 Hash=1b6bb7a2 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
ObjectName: Name=ALIBANK.XCY_TEST_LIBMUTEX
的统计信息不变。
select count(*) from xcy_test_libmutex where a=2;
来验证上面的猜测是否正确:
来看游标的信息。 发现
Bucket: #=113838 Mutex=87fd8270(0, 441, 0, 6)
LibraryHandle: Address=7c88c430 Hash=1771bcae LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
ObjectName: Name=select count(*) from xcy_test_libmutex where a=2
FullHashValue=776a59a01699d77e6d17abdf1771bcae Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=393329838 OwnerIdn=93
Statistics: InvalidationCount=0 ExecutionCount=17 LoadCount=3 ActiveLocks=1 TotalLockCount=5 TotalPinCount=1
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=2 KeepHandle=2 BucketInUse=4 HandleInUse=4
Concurrency: DependencyMutex=7c88c4e0(0, 5, 0, 0) Mutex=7c88c558(223, 73, 0, 6)
发现
Statistics: InvalidationCount=0 ExecutionCount=17 LoadCount=3 ActiveLocks=1 TotalLockCount=5 TotalPinCount=1
ExecutionCount=17 执行次数多了4次 TotalLockCount=5 多了一次。
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=2 KeepHandle=2 BucketInUse=4 HandleInUse=4
BucketInUse=4 HandleInUse=4 比前面各多了1次。
再来看表xcy_test_libmutex 的信息。
Bucket: #=112546 Mutex=8bfc7210(0, 504, 0, 6)
LibraryHandle: Address=8e7dc168 Hash=1b6bb7a2 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
ObjectName: Name=ALIBANK.XCY_TEST_LIBMUTEX
FullHashValue=00f4874b8a77eb299738b5d61b6bb7a2 Namespace=TABLE/PROCEDURE(01) Type=TABLE(02) Identifier=115776 OwnerIdn=93
Statistics: InvalidationCount=0 ExecutionCount=0 LoadCount=1 ActiveLocks=0 TotalLockCount=7 TotalPinCount=7
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=0 KeepHandle=0 BucketInUse=7 HandleInUse=7
Concurrency: DependencyMutex=8e7dc218(0, 11, 0, 0) Mutex=8e7dc290(223, 123, 0, 6)
没有任何改变。
由此推断, 不进行解析时执行sql(直接使用子游标执行) , 是不需要对依赖的对象加锁的,不涉及对依赖表的访问。
不进行解析时,10046 的trace 文件如下。
alter">alibank@rac3>alter session set events '10046 trace name context forever, level 12';
Session altered.
select">alibank@rac3>select count(*) from xcy_test_libmutex where a=2;
COUNT(*)
----------
0
alter">alibank@rac3>alter session set events '10046 trace name context off';
Session altered.
执行的SQL 仅一个,没有其他关联的SQL .
PARSING IN CURSOR #1 len=50 dep=0 uid=93 oct=3 lid=93 tim=1281594256904784 hv=393329838 ad='17c88c430' sqlid='6u5xbvwbr3g5f'
select count(*) from xcy_test_libmutex where a=2
END OF STMT
PARSE #1:c=0,e=265,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1918266520,tim=1281594256904782
EXEC #1:c=0,e=59,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1918266520,tim=1281594256904891
WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1281594256904920
WAIT #1: nam='Disk file operations I/O' ela= 6 FileOperation=2 fileno=14 filetype=2 obj#=-1 tim=1281594256905022
FETCH #1:c=1000,e=237,p=0,cr=7,cu=0,mis=0,r=1,dep=0,og=1,plh=1918266520,tim=1281594256905180
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=7 pr=0 pw=0 time=0 us)'
STAT #1 id=2 cnt=0 pid=1 pos=1 obj=115776 op='TABLE ACCESS FULL XCY_TEST_LIBMUTEX (cr=7 pr=0 pw=0 time=0 us cost=3 size=13 card=1)'
WAIT #1: nam='SQL*Net message from client' ela= 174 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1281594256905460
FETCH #1:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=1918266520,tim=1281594256905485
WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1281594256905503
WAIT #1: nam='SQL*Net message from client' ela= 392 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1281594256905906
第一部分研究游标与对象之间的关系,第二部分进行等待事件的研究。
[@more@]来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22795861/viewspace-1036691/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/22795861/viewspace-1036691/