Shulou(Shulou.com)06/01 Report--

Continue to verify the cbc latch-related situation when using the index, using dtrace in solaris

Write dtrace scripts

More cbc.dshipping pid$1::sskgslcas:entry pid$1::sskgslcas:entry memnr=copyin (arg0,12); latchaddr=arg0-s-n char * memnr;int latchaddr;dtrace:::BEGIN {iTun1; latchaddr=0;} Printf ("[% 2x%2x%2x%2x |% 2x%2x%2x%2x |% 2x%2x%2x%2x]", memnr [3], memnr [2], memnr [1], memnr [0], memnr [7], memnr [6], memnr [5], memnr [4], memnr [11], memnr [10], memnr [9], memnr [8]) Printf ("iTunes% d PID::entry:==%s:%s:%s:%s% x% x", I, probeprov, probemod, probefunc, probename,arg0,arg1,arg2,arg3,arg4,arg5); iTunes 1;} pid$1::sskgslcas:return {memnr=copyin (latchaddr,12) Printf ("[% 2x%2x%2x%2x |% 2x%2x%2x%2x |% 2x%2x%2x%2x]", memnr [3], memnr [2], memnr [1], memnr [0], memnr [7], memnr [6], memnr [5], memnr [4], memnr [11], memnr [10], memnr [9], memnr [8]) Printf ("iTunes% d PID::entry:==%s:%s:%s:%s% x% x% x", I, probeprov, probemod, probefunc, probename,latchaddr,arg0,arg1); iTunes 1;}

Test the non-unique index first:

Session1: create the corresponding object and execute the query statement multiple times

Create table an as select * from dba_objects;create index idx_a on a (object_id); select owner from a where object_id='30000';SQL > select c. Sid1. SERIAL # from (select sid from v$mystat where rownum select segment_name,header_file,header_block from dba_segments where segment_name=upper ('IDX_A') SEGMENT_NAME HEADER_FILE HEADER_BLOCK -IDX_A 1 60641

60641 pairs of segment header blocks, 60642 for indexed root blocks and 62091 for leaf blocks

Execute the dtrace script, do the trace, and then go back to session1 to execute the query statement again

. / cbc.d 1355 > logic_read.log

Bash-3.2# cat logic_read.log | wc-l

twenty-five

Session1:60642 corresponds to 5F0FC938. When scanning an index, it is a shared cbc latch for root blocks

SQL > oradebug call sskgslcas 0x5f0fc938 0 1Function returned 1SQL > select owner from a where object_id='30000';OWNER--PUBLICPUBLICSQL > oradebug call sskgsldecr 0x5f0fc938 1Function returned 1

62091 is a leaf block. When scanning an index, it is a shared cbc latch for a leaf block and an exclusive cbc latch for three times.

Bash-3.2# cat logic_read.log | grep-I 5F14F564 1 53243 sskgslcas:entry [0 000 | 0 068 | 0 07a] I PID::entry:==pid1355:oracle:sskgslcas:entry 5f14f564 0 1 fdc4d278 fdc4d270 fdc4d134 1 53244 sskgslcas:return [0 000 | 0 068 | 0 007 a] I PID::entry:==pid1355:oracle:sskgslcas:return 5f14f564 16 1 1 53243 sskgslcas:entry [0 000 | 00 6 9 | 00 07a] iTunes 11 PID::entry:==pid1355:oracle:sskgslcas:entry 5f14f564 0 2000000f ffffffff fdc4d278 fdc4d130 1 53244 sskgslcas:return [2000 f | 00 69 | 00 07a] iTunes 12 PID::entry:==pid1355:oracle:sskgslcas:return 5f14f564 16 11 53243 sskgslcas:entry [0000 | 00 6a | 00 07a] ilegs 13 PID::entry:==pid1355:oracle:sskgslcas:entry 5f14f564 0 2000000f c4a89cc fdc4d134 fdc4d270 1 53244 Sskgslcas:return [2000 f | 00 6a | 00 07a] iTunes 14 PID::entry:==pid1355:oracle:sskgslcas:return 5f14f564 16 1 1 53243 sskgslcas:entry [0000 | 00 6b | 00 07a] iTunes 15 PID::entry:==pid1355:oracle:sskgslcas:entry 5f14f564 0 2000000f 0 fdc4d278 fdc4d26c 1 53244 sskgslcas:return [2000 f | 00 6b | 00 07a] iTunes 16 PID::entry:==pid1355:oracle:sskgslcas:return 5f14f564 16 1

The above is that when the index is non-unique, the following procedure for testing is related to cbc latch.

Test the unique index again:

Create table an as select * from dba_objects;create unique index idx_a on a (object_id); SQL > select c. Sid. spidrect. Seria # from (select sid from v$mystat where rownum select file#,dbablk,tch,ba,HLADDR from x$bh a dbaa objects b where a.obj=b.data_object_id and object_name='IDX_A' order by FILE#,DBABLK. FILE# DBABLK TCH BA HLADDR- 1 60641 1 595EA000 5F13949C 1 60642 4 5A752000 5F0FC938 1 61343 3 584A0000 5F122270SQL > / FILE# DBABLK TCH BA HLADDR- 1 60641 1 595EA000 5F13949C 1 60642 6 5A752000 5F0FC938 1 61343 5 584A0000 5F122270SQL > select segment_name Header_file,header_block from dba_segments where segment_name=upper ('IDX_A') SEGMENT_NAME----HEADER_FILE HEADER_BLOCK--IDX_A 1 6064160642 is a root block 61343 is leaf block bash-3.2# cat logic_read1.logCPU ID FUNCTION:NAME 0 53243 sskgslcas:entry [000 0 | 0 930001 5d76abac 5ceb6fe8 5f8c4c6c 0 53244 sskgslcas:return [0 53243 PID::entry:==pid1374:oracle:sskgslcas:entry 5ceb6fe8 0 930001 5d76abac 5ceb6fe8 5f8c4c6c 0 0 0 sskgslcas:return [093 01 | 00 074 | 000 0] iBi2 PID::entry:==pid1374:oracle:sskgslcas:return 5ceb6fe8 16 1 0 53243 Sskgslcas:entry [0 0000 | 0 0 5db | 0 007a] PID::entry:==pid1374:oracle:sskgslcas:entry 5f0fc938 0 1 804510c 8045104 fdc18bf8 0 53244 sskgslcas:return [0 001 | 0 0 5db | 0 0 07a] PID::entry:==pid1374:oracle:sskgslcas:return 5f0fc938 4 PID::entry:==pid1374:oracle:sskgslcas:return 5f0fc938 16 10 53243 sskgslcas:entry [0 000 | 0 052 | 0 007a] iCo5 PID::entry:==pid1374:oracle:sskgslcas: Entry 5f122270 0 1 804510c 8045104 fdc18bf8 0 53244 sskgslcas:return [00 01 | 00 52 | 00 07a] iTunel 6 PID::entry:==pid1374:oracle:sskgslcas:return 5f122270 16 10 53243 sskgslcas:entry [00 00 | 00 698 | 00 07a] iatro7 PID::entry:==pid1374:oracle:sskgslcas:entry 5f106340 0 1 fdc18f60 fdc18f58 fdc18f00 053244 sskgslcas:return [00 01 | 00 698 | 00 07a] iCo8 PID :: entry:==pid1374:oracle:sskgslcas:return 5f106340 16 10 53243 sskgslcas:entry [930000 PID::entry:==pid1374:oracle:sskgslcas:entry 5ceb6fe8 1 930000 c4a88d0 5ceb6fe8 5f8c4c6c 0 53244 sskgslcas:return [093 00 | 00 075 | 0000] grep 10 PID::entry:==pid1374:oracle:sskgslcas:return 5ceb6fe8 16 1bash-3.2# cat logic_read1.log | grep-I 5F0FC938 053243 Sskgslcas:entry [0 0000 | 0 0 5db | 0 007a] PID::entry:==pid1374:oracle:sskgslcas:entry 5f0fc938 0 1 804510c 8045104 fdc18bf8 0 53244 sskgslcas:return [0 001 | 0 0 5db | 0 0 07a] iFix 4 PID::entry:==pid1374:oracle:sskgslcas:return 5f0fc938 16 1bash-3.2# cat logic_read1.log | grep-I 5F122270 0 53243 sskgslcas:entry [0 000 | 0 052 | 0 07a] iExhibit 5 PID::entry:==pid1374:oracle:sskgslcas:entry 5f122270 0 1 804510c 8045104 fdc18bf8 0 53244 sskgslcas:return [00 01 | 00 52 | 00 07a] iTunel 6 PID::entry:==pid1374:oracle:sskgslcas:return 5f122270 16 1bash-3.2# cat logic_read1.log | grep-I 5F106340 053243 sskgslcas:entry [00 00 | 00 698 | 00 07a] iCo7 PID::entry:==pid1374:oracle:sskgslcas:entry 5f106340 0 1 fdc18f60 fdc18f58 fdc18f00 053244 Sskgslcas:return [0 001 | 0 0698 | 0 007a] iObject8 PID::entry:==pid1374:oracle:sskgslcas:return 5f106340 16 1 unique index scan It is a shared cbc latch for both root and leaf blocks, and a shared cbc latch for table blocks.

Cbc latch in logical Reading (1): http://qhd2004.blog.51cto.com/629417/1576554

Cbc latch in logical Reading (2): http://qhd2004.blog.51cto.com/629417/1576579

Reference: http://blog.itpub.net/321157/viewspace-730936/

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.