[20210910]table scan相关统计.txt

[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