[20190423]oradebug peek測試指令碼.txt

--//工作測試需要寫一個oradebug peek測試指令碼,不斷看某個區域記憶體地址的值。

1.環境:

SCOTT@book> @ ver1

PORT_STRING VERSION BANNER

------------------------------ -------------- --------------------------------------------------------------------------------

x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.建立測試例子：

create table t as select rownum id,'test' name from dual ;

create unique index pk_t on t(id);

alter table t modify id not null ;

--//分析表和索引略.

SCOTT@book> select rowid,t.* from t;

ROWID ID NAME

------------------ ---------- ----------------------------------------

AAAknXAAEAAAAILAAA 1 test

SCOTT@book> @ rowid AAAknXAAEAAAAILAAA

OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT

---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------

149975 4 523 0 0x100020B 4,523 alter system dump datafile 4 block 523 ;

SCOTT@book> select header_file,header_block from dba_segments where owner=user and segment_name='PK_T';

HEADER_FILE HEADER_BLOCK

----------- ------------

4 554

--//從以上資訊可以知道dba=4,523資料塊,dba=4,522表T段頭.dba=4,555(554+1)是索引的root塊(因為索引很小也是葉子和分支塊)

SYS@book> @ bh 4 522

HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME

---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------

0000000084DA2730 4 522 4 segment header xcur 1 0 0 0 0 0 0000000071F46000 T

SYS@book> @ bh 4 523

HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME

---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------

0000000084C92150 4 523 1 data block xcur 2 0 0 0 0 0 0000000072208000 T

SYS@book> @ bh 4 555

HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME

---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------

000000008577A438 4 555 1 data block xcur 1 0 0 0 0 0 00000000721E2000 PK_T

--//獲得這些塊的cbc latch地址。

--//0000000084da2730,0000000084c92150,000000008577a438

3.編寫指令碼:

$ cat peek_laddr.sh

#! /bin/bash

# argument : laddr_list(delimiter using ,) Monitor_count peek_length sleep_duration

vdate=$(date '+%Y%m%d%H%M%S')

echo $vdate

laddr_list=$(echo $1| tr ',' '\n')

# p=$(echo "$laddr_list"|wc -l)

# echo $p

>| /tmp/pp_${vdate}.txt

for a in $laddr_list

do

sqlplus -s -l / as sysdba <<EOF | timestamp.pl >> /tmp/pp_${vdate}_${a}.txt &

oradebug setmypid

$(seq $2 | xargs -I{} echo -e "oradebug peek 0x$a $3\nhost sleep $4" )

quit

EOF

done

--//寫的很醜陋,不過能用^_^.

$ cat z1.txt

set verify off

host sleep $(echo &&3/50 | bc -l )

variable vmethod varchar2(20);

exec :vmethod := '&&2';

insert into job_times values ( sys_context ('userenv', 'sid') ,dbms_utility.get_time ,:vmethod) ;

commit ;

declare

v_id number;

v_d date;

v_name varchar2(4) ;

begin

for i in 1 .. &&1 loop

--//select /*+ index(t) &&3 */ count (*) into v_id from t ;

select /*+ full(t) &&3 */ count (*) into v_id from t ;

end loop;

end ;

/

update job_times set time_ela = dbms_utility.get_time - time_ela where sid=sys_context ('userenv', 'sid') and method=:vmethod;

commit;

quit

4.測試指令碼

-//編寫指令碼如下：

$ cat aaa.sh

#! /bin/bash

#vdate=$(date '+%Y%m%d%H%M%S')

#echo $vdate

laddr_list="$1"

echo $laddr_list

source peek_laddr.sh ${laddr_list} 200 8 0.1 &

seq 50 | xargs -I{} -P 50 sqlplus -s -l scott/book @z1.txt 1e5 id=index1 {} >/dev/null &

--//說明測試最好避開awr報表生成時間點.沒有其它事務執行sql語句.

$ . aaa.sh 000000084da2730,0000000084c92150,000000008577a438

000000084da2730,0000000084c92150,000000008577a438

20190430104022

[1]- Done source peek_laddr.sh ${laddr_list} 100 8 0.1

[2]+ Done seq 50 | xargs -I{} -P 50 sqlplus -s -l scott/book @z1.txt 1e5 id=full50 {} > /dev/null

--//等10秒看看.

SCOTT@book> Select method,count(*),round(avg(TIME_ELA),0),sum(TIME_ELA) from job_times group by method order by 3 ;

METHOD COUNT(*) ROUND(AVG(TIME_ELA),0) SUM(TIME_ELA)

-------------------- ---------- ---------------------- -------------

id=full50 50 977 48864

$ echo 000000084da2730,0000000084c92150,000000008577a438 | tr ',' '\n' | xargs -I{} grep -v '^.*: $' /tmp/pp_20190430104022_{}.txt |cut -c10- |sort| uniq -c

71 [084C92150, 084C92158) = 00000000 00000000

21 [084C92150, 084C92158) = 00000001 00000000

7 [084C92150, 084C92158) = 00000002 00000000

1 [084C92150, 084C92158) = 00000004 00000000

60 [084DA2730, 084DA2738) = 00000000 00000000

27 [084DA2730, 084DA2738) = 00000001 00000000

12 [084DA2730, 084DA2738) = 00000002 00000000

1 [084DA2730, 084DA2738) = 00000003 00000000

100 [08577A438, 08577A440) = 00000000 00000000

3 Statement processed.

--//沒有訪問索引root節點塊.

--//你可以發現大部分都是00000000 00000000.

--//可以發現沒有任何阻塞,shared latch,我在連結的測試總結如下:http://blog.itpub.net/267265/viewspace-2641414/

--//總結:

--//A. S mode 下: peek記錄的前4位持有S mode的數量.後4位是0x0. (這裡針對的64位的系統)

--//B. S mode 下,如果出現X mode,peek記錄的前4位持有S mode的數量.後4位是0x40000000.

--//一旦X mode持有變成前4位持有會話PID號,後4位0x20000000.

--//你可以發現全表掃描的情況下這些塊都是S模式獲取.

--//我加大資料量(2e5)以及監測時間(200次)

$ cat aaa.sh

#! /bin/bash

#vdate=$(date '+%Y%m%d%H%M%S')

#echo $vdate

laddr_list="$1"

echo $laddr_list

source peek_laddr.sh ${laddr_list} 200 8 0.1 &

seq 50 | xargs -I{} -P 50 sqlplus -s -l scott/book @z1.txt 2e5 id=full50 {} >/dev/null &

$ echo 000000084da2730,0000000084c92150,000000008577a438 | tr ',' '\n' | xargs -I{} grep -v '^.*: $' /tmp/pp_20190430104409_{}.txt |cut -c10- |sort| uniq -c

150 [084C92150, 084C92158) = 00000000 00000000

23 [084C92150, 084C92158) = 00000001 00000000

22 [084C92150, 084C92158) = 00000002 00000000

5 [084C92150, 084C92158) = 00000003 00000000

124 [084DA2730, 084DA2738) = 00000000 00000000

35 [084DA2730, 084DA2738) = 00000001 00000000

25 [084DA2730, 084DA2738) = 00000002 00000000

11 [084DA2730, 084DA2738) = 00000003 00000000

4 [084DA2730, 084DA2738) = 00000004 00000000

1 [084DA2730, 084DA2738) = 00000005 00000000

200 [08577A438, 08577A440) = 00000000 00000000

3 Statement processed.

--//後4位全部是00000000.

5.如果改用索引呢?

--//修改z1.txt如下:

$ cat z1.txt

set verify off

host sleep $(echo &&3/50 | bc -l )

variable vmethod varchar2(20);

exec :vmethod := '&&2';

insert into job_times values ( sys_context ('userenv', 'sid') ,dbms_utility.get_time ,:vmethod) ;

commit ;

declare

v_id number;

v_d date;

v_name varchar2(4) ;

begin

for i in 1 .. &&1 loop

select /*+ index(t) &&3 */ count (*) into v_id from t ;

--//select /*+ full(t) &&3 */ count (*) into v_id from t ;

end loop;

end ;

/

update job_times set time_ela = dbms_utility.get_time - time_ela where sid=sys_context ('userenv', 'sid') and method=:vmethod;

commit;

quit

$ cat aaa.sh

#! /bin/bash

#vdate=$(date '+%Y%m%d%H%M%S')

#echo $vdate

laddr_list="$1"

echo $laddr_list

source peek_laddr.sh ${laddr_list} 240 8 0.1 &

seq 50 | xargs -I{} -P 50 sqlplus -s -l scott/book @z1.txt 1e5 id=index50 {} >/dev/null &

--//前面我的測試需要24秒之內完成.

$ . aaa.sh 000000084da2730,0000000084c92150,000000008577a438

000000084da2730,0000000084c92150,000000008577a438

$ 20190430104822

[1]- Done source peek_laddr.sh ${laddr_list} 240 8 0.1

[2]+ Done seq 50 | xargs -I{} -P 50 sqlplus -s -l scott/book @z1.txt 1e5 id=index50 {} > /dev/null

SCOTT@book> Select method,count(*),round(avg(TIME_ELA),0),sum(TIME_ELA) from job_times group by method order by 3 ;

METHOD COUNT(*) ROUND(AVG(TIME_ELA),0) SUM(TIME_ELA)

-------------------- ---------- ---------------------- -------------

id=index50 50 2268 113411

$ echo 000000084da2730,0000000084c92150,000000008577a438 | tr ',' '\n' | xargs -I{} grep -v '^.*: $' /tmp/pp_20190430104822_{}.txt |cut -c10- |sort| uniq -c

240 [084C92150, 084C92158) = 00000000 00000000

240 [084DA2730, 084DA2738) = 00000000 00000000

--//注:沒有訪問資料塊.dba=4,522 4,523.

29 [08577A438, 08577A440) = 00000000 00000000

18 [08577A438, 08577A440) = 00000000 20000000

19 [08577A438, 08577A440) = 00000001 00000000

12 [08577A438, 08577A440) = 00000001 20000000

3 [08577A438, 08577A440) = 00000001 40000000

17 [08577A438, 08577A440) = 00000002 00000000

2 [08577A438, 08577A440) = 00000002 20000000

1 [08577A438, 08577A440) = 00000002 40000000

11 [08577A438, 08577A440) = 00000003 00000000

1 [08577A438, 08577A440) = 00000003 40000000

7 [08577A438, 08577A440) = 00000004 00000000

1 [08577A438, 08577A440) = 00000005 00000000

1 [08577A438, 08577A440) = 00000006 00000000

1 [08577A438, 08577A440) = 00000007 00000000

2 [08577A438, 08577A440) = 0000001B 20000000

3 [08577A438, 08577A440) = 0000001D 20000000

2 [08577A438, 08577A440) = 0000001E 20000000

1 [08577A438, 08577A440) = 0000001F 00000000

3 [08577A438, 08577A440) = 0000001F 20000000

2 [08577A438, 08577A440) = 00000020 00000000

3 [08577A438, 08577A440) = 00000020 20000000

2 [08577A438, 08577A440) = 00000021 00000000

1 [08577A438, 08577A440) = 00000021 20000000

3 [08577A438, 08577A440) = 00000022 20000000

1 [08577A438, 08577A440) = 00000023 20000000

1 [08577A438, 08577A440) = 00000024 00000000

1 [08577A438, 08577A440) = 00000024 20000000

1 [08577A438, 08577A440) = 00000025 20000000

1 [08577A438, 08577A440) = 00000026 20000000

1 [08577A438, 08577A440) = 00000027 00000000

3 [08577A438, 08577A440) = 00000027 20000000

1 [08577A438, 08577A440) = 00000028 00000000

2 [08577A438, 08577A440) = 00000028 20000000

2 [08577A438, 08577A440) = 00000029 20000000

2 [08577A438, 08577A440) = 0000002A 00000000

2 [08577A438, 08577A440) = 0000002A 20000000

2 [08577A438, 08577A440) = 0000002B 20000000

3 [08577A438, 08577A440) = 0000002C 20000000

1 [08577A438, 08577A440) = 0000002E 20000000

3 [08577A438, 08577A440) = 0000002F 00000000

2 [08577A438, 08577A440) = 0000002F 20000000

2 [08577A438, 08577A440) = 00000030 20000000

2 [08577A438, 08577A440) = 00000031 20000000

1 [08577A438, 08577A440) = 00000032 20000000

2 [08577A438, 08577A440) = 00000033 00000000

1 [08577A438, 08577A440) = 00000034 00000000

6 [08577A438, 08577A440) = 00000034 20000000

3 [08577A438, 08577A440) = 00000035 20000000

1 [08577A438, 08577A440) = 00000036 00000000

1 [08577A438, 08577A440) = 00000037 00000000

1 [08577A438, 08577A440) = 00000038 00000000

3 [08577A438, 08577A440) = 00000038 20000000

1 [08577A438, 08577A440) = 00000039 20000000

3 [08577A438, 08577A440) = 0000003A 20000000

2 [08577A438, 08577A440) = 0000003B 20000000

1 [08577A438, 08577A440) = 0000003C 00000000

2 [08577A438, 08577A440) = 0000003C 20000000

1 [08577A438, 08577A440) = 0000003D 00000000

1 [08577A438, 08577A440) = 0000003D 20000000

2 [08577A438, 08577A440) = 0000003E 20000000

2 [08577A438, 08577A440) = 0000003F 00000000

1 [08577A438, 08577A440) = 0000003F 20000000

1 [08577A438, 08577A440) = 00000040 20000000

2 [08577A438, 08577A440) = 00000041 20000000

2 [08577A438, 08577A440) = 00000042 20000000

1 [08577A438, 08577A440) = 00000044 20000000

2 [08577A438, 08577A440) = 00000045 20000000

1 [08577A438, 08577A440) = 00000046 00000000

3 [08577A438, 08577A440) = 00000046 20000000

3 [08577A438, 08577A440) = 00000047 20000000

2 [08577A438, 08577A440) = 00000048 20000000

1 [08577A438, 08577A440) = 00000049 20000000

1 [08577A438, 08577A440) = 0000004A 20000000

1 [08577A438, 08577A440) = 0000004B 00000000

2 [08577A438, 08577A440) = 0000004B 20000000

5 [08577A438, 08577A440) = 0000004C 20000000

3 Statement processed.

$ echo 000000084da2730,0000000084c92150,000000008577a438 | tr ',' '\n' | xargs -I{} grep -v '^.*: $' /tmp/pp_20190430104822_{}.txt |cut -c10- |sort| uniq -c | grep 08577A438| sort -nr | head

29 [08577A438, 08577A440) = 00000000 00000000

19 [08577A438, 08577A440) = 00000001 00000000

18 [08577A438, 08577A440) = 00000000 20000000

17 [08577A438, 08577A440) = 00000002 00000000

12 [08577A438, 08577A440) = 00000001 20000000

11 [08577A438, 08577A440) = 00000003 00000000

7 [08577A438, 08577A440) = 00000004 00000000

6 [08577A438, 08577A440) = 00000034 20000000

5 [08577A438, 08577A440) = 0000004C 20000000

3 [08577A438, 08577A440) = 00000047 20000000

$ grep " 40000000$" /tmp/pp_20190430104822_000000008577a438.txt

10:48:23: [08577A438, 08577A440) = 00000001 40000000

10:48:35: [08577A438, 08577A440) = 00000001 40000000

10:48:37: [08577A438, 08577A440) = 00000003 40000000

10:48:44: [08577A438, 08577A440) = 00000001 40000000

10:48:45: [08577A438, 08577A440) = 00000002 40000000

--//我估計全部掃描全部使用是S mode獲取cbc latch.而 INDEX FULL SCAN我估計有部分操作採用X mode獲取cbc latch.

--//這也許就是為什麼11g全表掃描快於INDEX FULL SCAN的原因嗎?

--//實際上這個給oracle一些最佳化提供一些資訊,比如一個表dept,經常查詢deptno,DNAME兩個欄位,如果透過建立符合索引減少邏輯讀.

--//如果程式出現大量頻繁訪問,反而全表掃描會更快.因為可能遇到cbc latch更少.

--//最佳化一定要考慮這些細節.有許多還是不是很清楚,先放一下...實際上僅僅11.2.0.4才會出現這樣的情況.全表掃描快於INDEX FULL SCAN.

[20190423]oradebug peek測試指令碼.txt

相關文章