[20210910]table scan相关统计.txt
--//昨天看了连接https://jonathanlewis.wordpress.com/2021/09/05/quiz-night-36/的测试,做个给出一个测试,问相关table scane相
--//关的统计是多少,说句真心话在测试前,我仅仅猜对一个table scans (short tables)=1
--//table scans (short tables),table scan rows gotten,table scan blocks gotten
--//我重复测试,并且做一些简单的说明:
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
SCOTT@book> create table t1 as select rownum id from dual connect by level<=1e4;
Table created.
SCOTT@book> create index t1_i1 on t1(id);
Index created.
SCOTT@book> select blocks, num_rows from user_tables where table_name = 'T1' ;
BLOCKS NUM_ROWS
---------- ----------
20 10000
SCOTT@book> select DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid) n5 ,count(*) from t1 group by DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid) order by 1;
N5 COUNT(*)
---------- ----------
747 657
748 657
749 657
750 657
751 657
752 657
753 657
754 657
755 657
756 657
757 657
758 657
759 657
761 657
762 657
763 145
16 rows selected.
--//一共占用16块,其中15块每块记录657,最后一块145条记录.
2.建立测试脚本:
$ cat tscan.txt
set verify off
column name format a30
SELECT b.NAME, a.statistic#, a.VALUE,a.sid
FROM v$mystat a, v$statname b
WHERE lower(b.NAME) in ('table scans (short tables)','table scan rows gotten','table scan blocks gotten') AND a.statistic# = b.statistic#
and a.value>0;
$ cat test1.txt
declare
cursor c1
is
select * from t1;
rec c1%rowtype;
ch char(1);
begin
open c1;
loop
fetch c1 into rec;
exit when c1%notfound;
--select null into ch from t1 where id = rec.id;
--select null into ch from t1 where id = rec.id;
--select null into ch from t1 where id = rec.id;
end loop;
close c1;
end;
/
$ cat test2.txt
declare
cursor c1
is
select * from t1;
--rec c1%rowtype;
--ch char(10) := '0';
ch char(1);
begin
for rec in c1 loop
--select null into ch from t1 where id = rec.id;
--select null into ch from t1 where id = rec.id;
--select null into ch from t1 where id = rec.id;
null;
end loop;
end;
/
--//注解里面的select并不影响里面的测试结果.因为测试table scan相关统计.
--//很奇怪的地方是test2.txt并不需要开始定义rec c1%rowtype.搞不懂.
3.测试:
SCOTT@book> @ tscan.txt
NAME STATISTIC# VALUE SID
--------------------------- ---------- ---------- ----------
table scans (short tables) 409 4 86
table scan rows gotten 414 81 86
table scan blocks gotten 415 3 86
SCOTT@book> @ test1.txt
PL/SQL procedure successfully completed.
SCOTT@book> @ tscan.txt
NAME STATISTIC# VALUE SID
--------------------------- ---------- ---------- ----------
table scans (short tables) 409 5 86
table scan rows gotten 414 6495841 86
table scan blocks gotten 415 10003 86
--//前后相减.6495841-81 = 6495760
table scans (short tables) 1
table scan rows gotten 6495760
table scan blocks gotten 10000
--//为什么呢?看作者的解析:
--//了解6.5M的来源——我说我有15个657行，一个有145行：
15 * (657 * 657) + (145 * 145) = 6,495,760
When the PL/SQL loop is doing single row fetches it release the table block after each row, so has to do 10,000 gets on
the table – which are all counted towards the "table scan blocks gotten". Then, to fetch one row from a block the
code seems to believe that it has to visit every single row in the block, hence the bit of arithmetic that produced a
row scan count of around 6.5M for this "short table" tablescan.
当PL/SQL循环进行单行获取时，它在每一行之后释放表块，所以必须做10000个表上，这些都计入表扫描块。然后，要从块中获取一行，
代码似乎认为它必须访问块中的每一行，因此为短表产生约6.5M的行扫描计数的算数。
--//也就是每次fetch一行时,必须访问块中的每一行,1块记录657.每次就有657个table scan rows gotten.15块就是15 * (657 * 657),
--//还有1块仅仅有145,就是145*145,这样计算结果就是 15 * (657 * 657) + (145 * 145) = 6495760
4.继续:
--//作者也给出另外的测试,也就是我写的test2.txt,测试看看.
SCOTT@book> @ tscan.txt
NAME STATISTIC# VALUE SID
------------------------------ ---------- ---------- ----------
table scans (short tables) 409 4 295
table scan rows gotten 414 81 295
table scan blocks gotten 415 3 295
SCOTT@book> @ test2.txt
PL/SQL procedure successfully completed.
SCOTT@book> @ tscan.txt
NAME STATISTIC# VALUE SID
------------------------------ ---------- ---------- ----------
table scans (short tables) 409 5 295
table scan rows gotten 414 74612 295
table scan blocks gotten 415 118 295
--//前后相减.74612-81 = 74531
table scans (short tables) 1
table scan rows gotten 74531
table scan blocks gotten 115
--//作者的解析:
While you might think this won't make much (or any) difference you need to remember that quite a long time ago Oracle
introduced a PL/SQL optimisation behind the scenes that turns the single row loop into an array fetch of 100 rows (see
footnote), so the table scan results I got were as follows:
虽然你可能认为这不会有太大(或任何)区别你需要记住，很久以前Oracle引入了PL/SQL优化幕后把单行循环变成一个数组获取100行(见脚
注)，所以我得到的表扫描结果如下：
table scans (short tables) 1
table scan rows gotten 74,531
table scan blocks gotten 115
Again the numbers are higher than we might expect but rather more realistic. Again the results are due to double (and
treble, and quadruple…) counting at the start of each array fetch, but I haven't yet tried to work out the exact
numbers in detail – though you can see that since I have roughly 7 arrays per block it's not entirely surprising that
the "blocks gotten" should be somewhere in the region of 7 * "data blocks below hwm".
这些数字再次比我们预期的要高，但却更为现实。结果是在每个数组取开始时计数一倍(三倍，四…)，但我还没有试图详细计算出确切的
数字——尽管你可以看到，因为我每个块大约有7个数组，那么在hwm块以下的7*数据块也就不足为奇了。
Addendum
It's hard to leave out a little detail when the numbers don't quite fit expectations – so I enabled the 10202 trace at
level 4 while running the second loop and checked the resulting trace file for the buffer header dumps as the tablescan
took place. (I commented out the 3 indexed accesses before I did this). Here's a sample of the dump for one buffer
access:
当数字不太符合预期的时候，就很难遗漏一些细节——所以我在运行第二个循环时，在第4级启用了10202跟踪，并在表卡发生时检查了缓
冲区头转储的结果跟踪文件。(在我这样做之前，我注释掉了这3个索引的访问)。下面是一个缓冲区访问的转储示例：
--//作者给出使用10202时间跟踪,,我自己也重复测试看看.
$ oerr ora 10200
10200, 00000, "consistent read buffer status"
// *Cause:
// *Action:
$ oerr ora 10202
10202, 00000, "consistent read block header"
// *Cause:
// *Action:
5.使用10202事件.
SCOTT@book> alter session set events '10202 trace name context forever, level 4';
Session altered.
SCOTT@book> @ test2.txt
PL/SQL procedure successfully completed.
SCOTT@book> alter session set events '10202 trace name context off';
Session altered.
SCOTT@book> @ pp
TRACEFILE FILE_NAME
------------------------------------------------------------- ------------------
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_48271.trc book_ora_48271.trc
--//我过滤"^buffer tsn"没有输出.估计是oracle版本的问题.
$ grep -i "buffer tsn" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_48271.trc | uniq -c
$ grep "^Block header dump" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_48271.trc | sort | uniq -c|wc
16 80 624
$ grep "^Block header dump" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_48271.trc | sort | uniq -c
7 Block header dump: 0x010002eb 600 + 57
8 Block header dump: 0x010002ec 43 + 600 + 14
7 Block header dump: 0x010002ed 86 + 500 + 71
8 Block header dump: 0x010002ee 29 + 600 + 28
7 Block header dump: 0x010002ef 72 + 500 + 85
8 Block header dump: 0x010002f0 15 + 600 + 42
7 Block header dump: 0x010002f1 58 + 500 + 99
8 Block header dump: 0x010002f2 1 + 600 + 56
8 Block header dump: 0x010002f3 44 + 600 + 13
7 Block header dump: 0x010002f4 87 + 500 + 70
8 Block header dump: 0x010002f5 30 + 600 + 27
7 Block header dump: 0x010002f6 73 + 500 + 84
8 Block header dump: 0x010002f7 16 + 600 + 41
7 Block header dump: 0x010002f9 59 + 500 + 98
8 Block header dump: 0x010002fa 2 + 600 + 55
2 Block header dump: 0x010002fb 45 + 100
--//正好16块,前面的uniq的计数,也就是读dba=0x010002eb块测试7次.全部相加.
--//注:后面的信息是我copy and paste原链接的.说明fetch的次数,很容易理解,例如:
7 Block header dump: 0x010002eb 600 + 57
--//每块记录数 657,这样就是6*100+57,这样就是6+1=7次,正好对应前面的次数7.
$ grep "^Block header dump" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_48271.trc | sort | uniq -c|awk '{print $1}'| paste -sd+ | bc
115
To finish off the arithmetic, this also shows 113 visits to blocks with 657 rows and 2 visits to blocks with 145 rows:
(113 * 657) + (2 * 145) = 74,531 (as reported by "table scan rows gotten").
为了完成算术，这还显示了对657行块的113次访问和对145行块的2次访问：
(113*657)+(2*145)=74,531(由表扫描行报告)。
6.再来计算test1.txt的情况呢?
SCOTT@book> alter session set events '10202 trace name context forever, level 4';
Session altered.
SCOTT@book> @ test1.txt
PL/SQL procedure successfully completed.
--//好慢!!
SCOTT@book> alter session set events '10202 trace name context off';
Session altered.
SCOTT@book> @ pp
TRACEFILE FILE_NAME
-------------------------------------------------------------------------------- ------------------
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_48356.trc book_ora_48356.trc
$ grep "^Block header dump" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_48356.trc| sort | uniq -c
1 Block header dump: 0x00413567
1 Block header dump: 0x004154d1
3 Block header dump: 0x0041701d
657 Block header dump: 0x010002eb
657 Block header dump: 0x010002ec
657 Block header dump: 0x010002ed
657 Block header dump: 0x010002ee
657 Block header dump: 0x010002ef
657 Block header dump: 0x010002f0
657 Block header dump: 0x010002f1
657 Block header dump: 0x010002f2
657 Block header dump: 0x010002f3
657 Block header dump: 0x010002f4
657 Block header dump: 0x010002f5
657 Block header dump: 0x010002f6
657 Block header dump: 0x010002f7
657 Block header dump: 0x010002f9
657 Block header dump: 0x010002fa
145 Block header dump: 0x010002fb
--//0x00413567 = set dba 1,79207 = alter system dump datafile 1 block 79207 = 4273511
--//不知道前面3个rdba,也许是递归造成的.
--//顺便补充一下,10202事件使用level 1 就可以.
alter session set events '10202 trace name context forever, level 1';
--//跟踪显示如下:
*** 2021-09-10 09:40:04.321
*** SESSION ID:(295.29) 2021-09-10 09:40:04.321
*** CLIENT ID:() 2021-09-10 09:40:04.321
*** SERVICE NAME:(SYS$USERS) 2021-09-10 09:40:04.321
*** MODULE NAME:(SQL*Plus) 2021-09-10 09:40:04.321
*** ACTION NAME:() 2021-09-10 09:40:04.321
Consistent read complete...
Block header dump: 0x010002eb
Object id on Block? Y
seg/obj: 0x16d7e csc: 0x03.7f939519 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x10002e8 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0003.7f939519
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
Consistent read complete...
Block header dump: 0x010002eb
Object id on Block? Y
seg/obj: 0x16d7e csc: 0x03.7f939519 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x10002e8 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0003.7f939519
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
Consistent read complete...
...
$ grep "Block header dump" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_48458.trc | sort | uniq -c
7 Block header dump: 0x010002eb
8 Block header dump: 0x010002ec
7 Block header dump: 0x010002ed
8 Block header dump: 0x010002ee
7 Block header dump: 0x010002ef
8 Block header dump: 0x010002f0
7 Block header dump: 0x010002f1
8 Block header dump: 0x010002f2
8 Block header dump: 0x010002f3
7 Block header dump: 0x010002f4
8 Block header dump: 0x010002f5
7 Block header dump: 0x010002f6
8 Block header dump: 0x010002f7
7 Block header dump: 0x010002f9
8 Block header dump: 0x010002fa
2 Block header dump: 0x010002fb
[20210910]table scan相关统计.txt
-
lfree
2021-09-10 09:48:47 -
Oracle
- 原创