[20190423]oradebug peek测试脚本.txt

来源：这里教程网时间：2026-03-03 13:28:49 作者：

[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

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推