[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.