使用全表掃描快取大表的相關問題
問題:
有一個批次操作，需要依次更新一個大表的大部分資料，而資料庫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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 關於分割槽表中的全partition掃描問題
- 優化Oracle with全表掃描的問題優化Oracle
- 優化Oracle with全表掃描的問題(二)優化Oracle
- 使用索引快速全掃描(Index FFS)避免全表掃描的若干場景索引Index
- 優化全表掃描優化
- delete 與全表掃描delete
- MySQL中的全表掃描和索引樹掃描MySql索引
- 查詢全表掃描的sqlSQL
- ORACLE全表掃描查詢Oracle
- 抓取全表掃描的表，篩選和分析
- zt:東軟醫保動態庫全表掃描問題
- oracle 全表掃描,索引範圍掃描與塊的理解Oracle索引
- 查詢全表掃描語句
- oracle優化：避免全表掃描Oracle優化
- oracle是如何進行全表掃描的Oracle
- Oracle中存取資料掃描Table及索引的方式(全表掃描，索引掃描等)Oracle索引
- 關係型資料庫全表掃描分片詳解資料庫
- (轉)索引掃描還是全表掃描（Index Scan Or Full Table Scan）索引Index
- 轉)索引掃描還是全表掃描（Index Scan Or Full Table Scan）索引Index
- Greenplum儲存過程使用分割槽表將進行全表掃描儲存過程
- 一條全表掃描sql語句的分析SQL
- 使用10046 event trace跟蹤全表掃描操作
- delete 刪除資料 全表掃描還是掃描所有塊的測試delete
- 24_Oracle資料庫全表掃描詳解(四)_全表掃描生產最佳化案例三則Oracle資料庫
- 有索引卻走全表掃描的實驗分析索引
- noworkload下全表掃描cost的計算
- 查詢出資料庫中預設會以全表掃描方式訪問的表資料庫
- oracle實驗記錄（分割槽全表掃描（全區掃描） FTS 時候的成本計算）Oracle
- Oracle JDBC驅動使用setDate()、setTimestamp()導致全表掃描OracleJDBC
- 隱形轉換導致全表掃描案例
- oracle sql tuning 8--優化全表掃描OracleSQL優化
- 全表掃描的cost 與 索引掃描Cost的比較 – 無直方圖（10.1.0.3以後）索引直方圖
- PostgreSQL DBA(55) - MVCC#8(對全表掃描的影響)SQLMVCC#
- 索引全掃描和索引快速全掃描的區別索引
- 23_Oracle資料庫全表掃描詳解(三)Oracle資料庫
- 22_Oracle資料庫全表掃描詳解(二)Oracle資料庫
- 21_Oracle資料庫全表掃描詳解(一)Oracle資料庫
- oracle實驗記錄 （全表掃描COST計算方法）Oracle