[20190423]oradebug peek測試指令碼.txt
[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.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2642981/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20190423]簡單測試latch nowilling等待模式.txt模式
- [20190416]exclusive latch測試指令碼.txt指令碼
- [20190423]簡單測試user和SYS_CONTEXT ('USERENV','CURRENT_USER').txtContext
- PYTHON測試指令碼Python指令碼
- 壓力測試指令碼指令碼
- [Mysql]效能測試指令碼MySql指令碼
- python效能測試指令碼Python指令碼
- [20190416]完善shared latch測試指令碼2.txt指令碼
- [python] request 介面測試自動化指令碼轉化為 [locust] 效能測試指令碼Python指令碼
- iOS17 效能測試指令碼iOS指令碼
- 介面測試（apipost、jmeter和python指令碼）——測試工具APIJMeterPython指令碼
- [20230510]測試使用tpt ddl指令碼是否產生日誌.txt指令碼
- loadrunner 資料庫測試指令碼資料庫指令碼
- RMAN備份恢復測試指令碼指令碼
- unittest 單元測試框架教程 1-執行測試指令碼框架指令碼
- apache ab壓力測試工具-批次壓測指令碼Apache指令碼
- 效能測試——壓測工具locust——指令碼初步編寫指令碼
- shell指令碼和python指令碼實現批量ping IP測試指令碼Python
- node 執行JavaScript 指令碼 測試axiosJavaScript指令碼iOS
- 介面測試 (apipost、jmeter 和 python 指令碼)APIJMeterPython指令碼
- 介面測試（apipost、jmeter和python指令碼）APIJMeterPython指令碼
- Linux系統效能測試指令碼（unixbenchnmon）Linux指令碼
- 網路兩端延時測試指令碼指令碼
- 【shell 指令碼】算術測試需要使用(( ))指令碼
- LoadRunner長連線效能測試指令碼指令碼
- 如何用 JMeter 編寫效能測試指令碼？JMeter指令碼
- Shell指令碼 | 效能測試之啟動時間指令碼
- 搬瓦工 VPS 效能一鍵測試指令碼指令碼
- Appium iOS 測試指令碼開發實戰APPiOS指令碼
- 通過RFT命令列執行測試指令碼命令列指令碼
- 使用 Bash shell 指令碼進行功能測試(轉)指令碼
- WebSphere MQ測試常用指令WebMQ
- 程式碼安全測試第十五期：跨站指令碼漏洞指令碼
- 用 Cloud Performance Test 怎麼錄製測試指令碼CloudORM指令碼
- 用 Cloud Performance Test怎麼錄製測試指令碼CloudORM指令碼
- 如何編寫測試團隊通用的Jmeter指令碼JMeter指令碼
- pgbench 壓力測試指令碼作為參考.指令碼
- 用Python開發自動化測試指令碼Python指令碼