使用全表掃描快取大表的相關問題
問題:
有一個批次操作，需要依次更新一個大表的大部分資料，而資料庫buffer cache足夠大，可以容納該表.
是否可以透過並行全表掃描(db scattered read)將該表讀入buffer cache，避免單塊讀取(db sequential read)導致的大量物理IO?
後者物理讀次數是前者物理讀次數的db_file_multiblock_read_count(預設為16)倍.
為了驗證該方案，需要明確如下問題，在全表掃描時:
(1)將表的資料塊快取到buffer cache還是PGA?是否使用多塊讀？
(2)使用parallel提示情況下，快取到buffer cache還是PGA?是否使用多塊讀？
1 測試表準備
create table t
as
select * from dba_objects;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as pg@dev95
SQL>
SQL> select bytes，blocks，extents from user_segments where segment_name='T';
BYTES BLOCKS EXTENTS
---------- ---------- ----------
9437184 1152 24
SQL> select extent_id，bytes，blocks from user_extents where segment_name='T';
EXTENT_ID BYTES BLOCKS
---------- ---------- ----------
0 65536 8
1 65536 8
2 65536 8
3 65536 8
4 65536 8
5 65536 8
6 65536 8
7 65536 8
8 65536 8
9 65536 8
10 65536 8
11 65536 8
12 65536 8
13 65536 8
14 65536 8
15 65536 8
16 1048576 128
17 1048576 128
18 1048576 128
19 1048576 128
20 1048576 128
21 1048576 128
22 1048576 128
23 1048576 128
24 rows selected
SQL> select object_id，data_object_id from user_objects where object_name='T';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
155073 155073
SQL> select file#，block#，class#，status from v$bh where bjd=155073;
FILE# BLOCK# CLASS# STATUS
---------- ---------- ---------- -------
6 253578 8 xcur
16 56457 8 xcur
12 30649 8 xcur
15 57481 8 xcur
12 30665 8 xcur
20 26890 8 xcur
19 23306 8 xcur
15 57497 8 xcur
12 30681 8 xcur
18 44682 8 xcur
12 30634 9 xcur
7 160905 8 xcur
12 30697 8 xcur
17 555273 8 xcur
16 56458 8 xcur
8 126217 8 xcur
12 30713 8 xcur
6 253577 8 xcur
12 30635 4 xcur
7 160906 8 xcur
17 555274 8 xcur
20 26889 8 xcur
19 23305 8 xcur
8 126218 8 xcur
18 44681 8 xcur
12 30633 8 xcur
26 rows selected
2 全表掃描快取到buffer cache還是pga？是否使用多塊讀？
結論：快取到buffer cache；使用了多塊讀。
SQL>
SQL> alter system flush shared_pool;
System altered
SQL> alter system flush buffer_cache;
System altered
SQL>
SQL>
SQL> select status， count(*) from v$bh where bjd = 155073 group by status;
free 1026
SQL> select statistic_name，value
2 from v$segment_statistics ss
3 where wner = user
4 and object_name = 'T'
5 and value>0
6 order by 2;
segment scans 1
db block changes 256
physical writes direct 1125
physical reads 1126
physical writes 1151
logical reads 2704
space used 9216000
space allocated 9437184
8 rows selected
SQL> select sid， name， value
2 from v$mystat ses， v$statname sn
3 where ses.STATISTIC# = sn.STATISTIC#
4 and value > 0
5 and name like '%read%'
6 order by 3;
822 physical read total multi block requests 95
822 physical read total IO requests 694
822 physical read IO requests 694
822 physical reads cache prefetch 1018
822 physical reads 1712
822 physical reads cache 1712
822 no work - consistent read gets 641666
822 session logical reads 1094037
822 physical read total bytes 14024704
822 physical read bytes 14024704
10 rows selected
SQL> select event， total_waits
2 from v$session_event
3 where sid = 822
4 and wait_class = 'User I/O'
5 order by 1;
db file scattered read 95
db file sequential read 606
SQL> select count(*) from t;
80677
SQL> select sql_id，disk_reads，direct_writes，user_io_wait_time，buffer_gets，sql_text from v$sql where sql_text like '%count(*) from t%';
a3uxj45fdjv7m 1128 0 69853 1199 select count(*) from t
==〉v$sql.disk_reads:Number of disk reads for this child cursor
SQL> select * from table(dbms_xplan.display_cursor('a3uxj45fdjv7m'));
SQL_ID a3uxj45fdjv7m， child number 0
-------------------------------------
select count(*) from t
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 93 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 80442 | 93 (3)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
18 rows selected
SQL> select status， count(*) from v$bh where bjd = 155073 group by status;
xcur 1126 free 788
cr 1
==〉xcur：1126，說明快取到了buffer cache
SQL> select statistic_name，value
2 from v$segment_statistics ss
3 where wner = user
4 and object_name = 'T'
5 and value>0
6 order by 2;
segment scans 1
db block changes 256
physical writes direct 1125
physical writes 1151
physical reads 2252 logical reads 3904
space used 9216000
space allocated 9437184
8 rows selected
SQL> select sid， name， value
2 from v$mystat ses， v$statname sn
3 where ses.STATISTIC# = sn.STATISTIC#
4 and value > 0
5 and name like '%read%'
6 order by 3;
822 physical read total multi block requests 189 822 physical read total IO requests 822 822 physical read IO requests 822 822 physical reads cache prefetch 2032 822 physical reads 2854 822 physical reads cache 2854 822 no work - consistent read gets 648919
822 session logical reads 1107445
822 physical read total bytes 23379968
822 physical read bytes 23379968
==〉physical read total multi block requests：94，說明使用了多塊讀
SQL> select 94*16 from dual;
1504
SQL> select event， total_waits
2 from v$session_event
3 where sid = 822
4 and wait_class = 'User I/O'
5 order by 1;
db file scattered read 189 db file sequential read 633
==〉db file scattered read：94，說明使用了多塊讀
3，使用並行提示，全表掃描快取到buffer cache還是pga？是否使用多塊讀？
==> 讀入pga；使用了多塊讀。
SQL> alter system flush shared_pool;
System altered
SQL> alter system flush buffer_cache;
System altered
SQL> select status， count(*) from v$bh where bjd = 155073 group by status;
free 1232
SQL>
SQL> select statistic_name，value
2 from v$segment_statistics ss
3 where wner = user
4 and object_name = 'T'
5 and value>0
6 order by 2;
segment scans 1
db block changes 256
physical writes direct 1125
physical writes 1151
physical reads 3388
logical reads 7376
space used 9216000
space allocated 9437184
8 rows selected
SQL> select sid， name， value
2 from v$mystat ses， v$statname sn
3 where ses.STATISTIC# = sn.STATISTIC#
4 and value > 0
5 and name like '%read%'
6 order by 3;
822 physical read total multi block requests 272
822 physical read total IO requests 1546
822 physical read IO requests 1546
822 physical reads cache prefetch 3094
822 physical reads 4640
822 physical reads cache 4640
822 no work - consistent read gets 671268
822 session logical reads 1153284
822 physical read total bytes 38010880
822 physical read bytes 38010880
10 rows selected
SQL> select event， total_waits
2 from v$session_event
3 where sid = 822
4 and wait_class = 'User I/O'
5 order by 1;
db file scattered read 272
db file sequential read 1278
SQL> select /*+ parallel(t 8) */ count(*) from t;
80677
SQL> select sql_id，disk_reads，direct_writes，user_io_wait_time，buffer_gets，sql_text from v$sql where sql_text like '%count(*) from t%';
6861j0dxkvvr4 1136 0 1056 1388 select /*+ parallel(t 8) */ count(*) from t
SQL> select * from table(dbms_xplan.display_cursor('6861j0dxkvvr4'));
SQL_ID 6861j0dxkvvr4， child number 0
select /*+ parallel(t 8) */ count(*) from t
NOTE: cannot fetch plan for SQL_ID: 6861j0dxkvvr4， CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_p
8 rows selected
SQL> explain plan for select /*+ parallel(t 8) */ count(*) from t
2 ;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3126468333
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | T
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | PX COORDINATOR | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,
| 4 | SORT AGGREGATE | | 1 | | | Q1,
| 5 | PX BLOCK ITERATOR | | 80677 | 13 (0)| 00:00:01 | Q1,
| 6 | TABLE ACCESS FULL| T | 80677 | 13 (0)| 00:00:01 | Q1,
--------------------------------------------------------------------------------
13 rows selected
SQL> select status， count(*) from v$bh where bjd = 155073 group by status;
xcur 1
free 720
==〉xcur：1，說明快取到了pga
SQL>
SQL> select statistic_name，value
2 from v$segment_statistics ss
3 where wner = user
4 and object_name = 'T'
5 and value>0
6 order by 2;
segment scans 108
db block changes 256
physical reads direct 1125 physical writes direct 1125
physical writes 1151
physical reads 4514 logical reads 8720
space used 9216000
space allocated 9437184
==〉physical reads direct：1125，說明使用了直接讀
SQL> select sid， name， value
2 from v$mystat ses， v$statname sn
3 where ses.STATISTIC# = sn.STATISTIC#
4 and value > 0
5 and name like '%read%'
6 order by 3;
822 table scans (direct read) 107 822 physical read total multi block requests 384 822 physical reads direct 1125 822 physical read total IO requests 1733 822 physical read IO requests 1733 822 physical reads cache prefetch 3094
822 physical reads cache 4714
822 physical reads 5839 822 no work - consistent read gets 678775
822 session logical reads 1167932
822 physical read total bytes 47833088
822 physical read bytes 47833088
==〉physical read total multi block requests：112，說明使用了多塊讀
SQL> select event， total_waits
2 from v$session_event
3 where sid = 822
4 and wait_class = 'User I/O'
5 order by 1;
db file scattered read 272
db file sequential read 1348
==〉db file scattered read：0，說明沒有使用多塊讀或這種情況下的多塊讀不計入該事件計數。
4，遺留問題：
表上的cache屬性，可以控制在全表掃描時把資料放置在LRU的熱端。為了快取大表，該屬性是否影響快取效果？
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/18922393/viewspace-754214/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 全表掃描和全索引掃描索引
- [20210219]全表掃描邏輯讀問題.txt
- MySQL中的全表掃描和索引樹掃描MySql索引
- 全表掃描和全索引掃描繼續（PG-TiDB）索引TiDB
- oracle是如何進行全表掃描的Oracle
- 關係型資料庫全表掃描分片詳解資料庫
- 24_Oracle資料庫全表掃描詳解(四)_全表掃描生產最佳化案例三則Oracle資料庫
- [20190221]使用nmap掃描埠的問題.txt
- redis13_redis快取相關問題5Redis快取
- redis快取相關問題及解決方案Redis快取
- PostgreSQL DBA(55) - MVCC#8(對全表掃描的影響)SQLMVCC#
- 23_Oracle資料庫全表掃描詳解(三)Oracle資料庫
- 22_Oracle資料庫全表掃描詳解(二)Oracle資料庫
- 21_Oracle資料庫全表掃描詳解(一)Oracle資料庫
- 怎麼解決因全表掃描帶來的 Buffer Pool 汙染
- 【TUNE_ORACLE】列出NL（NESTED LOOPS）被驅動表走了全表掃描的SQL參考OracleOOPSQL
- PostgreSQL大表掃描策略-BAS_BULKREAD,synchronize_seqscansSQL
- 索引掃描可能不如全表掃描的場景的理解__純粹資料量而言，不涉及CLUSTERING_FACTOR索引
- 大模型相關問題大模型
- Android面試：大廠必問之OkHttp相關問題全解析Android面試HTTP
- [20210220]全索引掃描快速索引掃描的邏輯讀.txt索引
- 快取的問題快取
- http中和快取相關的headerHTTP快取Header
- 雷達氣象相關詞彙（一 掃描模式）模式
- 什麼是大報表？如何解決大報表的問題？
- 專案owner看這裡，MaxCompute全表掃描新功能，給你“失誤”的機會
- SonarQube系列-透過配置掃描分析範圍，聚焦關鍵問題
- 七大快取經典問題快取
- HTTP與快取相關的頭部HTTP快取
- Android 開發：使用繪製基金圖表類(帶快取的圖表類)Android快取
- awvs -網站掃描問題求指導網站
- Fotify掃描問題Dynamic Code Evaluation:Code Injection
- 報表資料的可控快取快取
- [20190815]索引快速全掃描的成本.txt索引
- 技術分享 | 為什麼 SELECT 查詢選擇全表掃描，而不走索引？索引
- 奧普快票通表票掃描識別系統
- SELECT COUNT(*) 會造成全表掃描？回去等通知吧
- 關於快取命中率的幾個關鍵問題！快取
- 在 dubbo 中使用 Threadlocal 的相關問題thread