[20210910]table scan相關統計.txt
[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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Cell smart table scan等待事件事件
- [20181201]奇怪的INDEX SKIP SCAN執行計劃.txtIndex
- [20201109]cluvfy comp scan [-verbose].txt
- numpy. 統計相關
- Numpy---統計相關
- css中關於table的相關設定CSS
- [20190929]bash使用bc計算的相關問題.txt
- [20191202]關於hugepages相關問題.txt
- numpy3.統計相關
- [20180725]index skip-scan operation.txtIndex
- 統計學三大相關係數之Pearson相關係數、Spearman相關係數
- [20190524]Table Elimination.txt
- [20230908]Oracle Index Range Scan with LIKE Condition on Wildcard '_'.txtOracleIndex
- 【SCRIPT】Oracle統計資訊相關SQLOracleSQL
- 系統設計 相關面試題面試題
- 【系統設計】併發相關概念
- [20220913]hugepage相關引數含義.txt
- [20191204]hugepage相關引數含義.txt
- [20190108]rlwrap sqlplus tee相關問題.txtSQL
- [20180413]熱備模式相關問題.txt模式
- [20240313]toad gather_plan_statistics執行計劃相關問題.txt
- [20200819]12c Global Temporary table 統計資訊的收集的疑問.txt
- [20180427]SCAN_IP DNS 反向解析2.txtDNS
- [20190522]DISABLE TABLE LOCK.txt
- GreatSQL統計資訊相關知識點SQL
- Oracle 統計資訊相關命令彙總Oracle
- [20201210]11G ACS相關問題.txt
- [20191220]關於共享記憶體段相關問題.txt記憶體
- [20190505]關於latch 一些統計資訊.txt
- OCT影像分類1：相關論文統計
- [20191206]確定sys.file$相關資訊.txt
- [20190110]rlwrap sqlplus tee相關問題3.txtSQL
- [20180413]熱備模式相關問題2.txt模式
- [20221227]a mutating table error without a trigger!.txtError
- [20191223]關於共享記憶體段相關問題3.txt記憶體
- MySQL索引統計資訊更新相關的引數MySql索引
- AirNet系統航跡和飛行計劃相關AI
- [20230306]學習UNIFIED audit--dg相關問題.txtNifi