ITPub博客

首页 > 数据库 > Oracle > [20210910]table scan相关统计.txt

[20210910]table scan相关统计.txt

原创 Oracle 作者:lfree 时间:2021-09-10 09:48:47 0 删除 编辑

[20210910]table scan相关统计.txt

--//昨天看了连接的测试,做个给出一个测试,问相关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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2791355/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    3031
  • 访问量
    6775260