oracle 频繁io,Oracle IO问题解析
3.2.2 db file scattered read
這是另外一個(gè)常見的引起數(shù)據(jù)庫IO性能問題的等待事件。它通常發(fā)生在Oracle將“多數(shù)據(jù)塊”讀取到Buffer Cache中的非連續(xù)(分散的 Scattered)區(qū)域。多數(shù)據(jù)塊讀就是我們上述所說的一次讀取“DB_FILE_MULTIBLOCK_READ_COUNT”塊數(shù)據(jù)塊，前面提到，它通常發(fā)生在全表掃描(Full Table Scan)和快速全索引掃描(Fast Full Index Scan)時(shí)。當(dāng)發(fā)現(xiàn)db file scattered read等待事件是系統(tǒng)引起IO性能的主要原因時(shí)，我們可以采取以下措施對系統(tǒng)進(jìn)行優(yōu)化。
3.2.2.1 優(yōu)化存在Full Table Scan和Fast Full Index Scan的SQL語句
我們可以首先從statspack或者awr報(bào)告中的“SQL ordered by Reads”部分中找出存在Full Table Scan和Fast Full Index Scan的Top SQL。因?yàn)檫@些Top SQL往往是整個(gè)系統(tǒng)的瓶頸。
從9i開始，我們還可以通過視圖V$SQL_PLAN來查找系統(tǒng)中存在Full Table Scan和Fast Full Index Scan的SQL語句。查找Full Table Scan的語句：
select sql_text from v$sqlarea t, v$sql_plan p
where t.hash_value=p.hash_value and p.operation='TABLE ACCESS'
and p.options='FULL';
查找Fast Full Index Scan的語句
select sql_text from v$sqlarea t, v$sql_plan p
where t.hash_value=p.hash_value and p.operation='INDEX'
and p.options='FULL SCAN';
Full Table Scan通常是由于以下幾個(gè)原因引起的：
條件字段上沒有索引;
在這種情況下，如果表的數(shù)據(jù)量比較大，我們就需要在相應(yīng)字段上建立起索引。
CBO中，對象的統(tǒng)計(jì)數(shù)據(jù)不正確
CBO中，如果對象的統(tǒng)計(jì)數(shù)據(jù)或者其柱狀圖(Histogram)信息不正確，會(huì)導(dǎo)致優(yōu)化器計(jì)算出錯(cuò)誤的查詢計(jì)劃，從而選擇全表掃描。這種情況下，我們要做的就重新分析(Analyze)表、索引及字段。
CBO中，SQL語句中引用到了無法估算統(tǒng)計(jì)數(shù)據(jù)的對象
在PLSQL中，可以建立一些高級的數(shù)據(jù)類型，如“TABLE OF”、ARRAY等，通過TABLE、CAST函數(shù)可以在SQL語句中將這些對象當(dāng)成表來處理。而這些對象的數(shù)據(jù)只存在于調(diào)用PLSQL的會(huì)話中，因此他們沒有相應(yīng)的統(tǒng)計(jì)數(shù)據(jù)，Oracle會(huì)為他們生產(chǎn)一些假的統(tǒng)計(jì)數(shù)據(jù)以完成查詢計(jì)劃代價(jià)估算。但是基于這些假的數(shù)據(jù)計(jì)算出的查詢計(jì)劃一般是錯(cuò)誤的。我們可以考慮通過提示來強(qiáng)制SQL使用索引或者強(qiáng)制SQL采用RBO優(yōu)化器。
此外，如果SQL中引用到了臨時(shí)表(Temporary Table)也會(huì)產(chǎn)生同樣的問題。其原因和解決方法和上面相同。
優(yōu)化器認(rèn)為索引掃描代價(jià)過高;
在Oracle中存在一個(gè)參數(shù)optimizer_index_cost_adj，該參數(shù)的值代表一個(gè)百分?jǐn)?shù)，如果對索引掃描的代價(jià)達(dá)到或超過全表掃描的代價(jià)的這個(gè)百分比值時(shí)，優(yōu)化器就采用全表掃描。
optimizer_index_cost_adj是一個(gè)全局性的參數(shù)，它的合理值是通過長期調(diào)整出來的。一般來說是一個(gè)介于1到100之間的數(shù)字。我們可以按照以下方法來選取optimizer_index_cost_adj的合理值。
先由以下語句得出optimizer_index_cost_adj的一個(gè)初始值：
SQL> select
2 a.average_wait "Average Waits FTS"
3 ,b.average_wait "Average Waits Index Read"
4 ,a.total_waits /(a.total_waits + b.total_waits) "Percent of FTS"
5 ,b.total_waits /(a.total_waits + b.total_waits) "Percent of Index Scans"
6 ,(b.average_wait / a.average_wait)*100 "optimizer_index_cost_adj"
7 from
8 v$system_event a,
9 v$system_event b
10 where a.EVENT = 'db file sequential read'
11 and b.EVENT = 'db file scattered read';
Average Waits FTS Average Waits Index Read Percent of FTS Percent of Index Scans
----------------- ------------------------ -------------- ----------------------
optimizer_index_cost_adj
------------------------
1.25 1.06 .041867874 .958132126
84.8
這里，84.8是我們系統(tǒng)的初始值。在系統(tǒng)經(jīng)過一段時(shí)間運(yùn)行后，再次運(yùn)行上面的語句，重新調(diào)整optimizer_index_cost_adj的值。經(jīng)過多次如此反復(fù)的調(diào)整之后，最終上面語句得出值趨于穩(wěn)定，這時(shí)這個(gè)值就是符合我們系統(tǒng)性能需求的最合理的值。
當(dāng)然這個(gè)數(shù)值也可以通過statspack的歷史數(shù)據(jù)來調(diào)整，在9i中：
select to_char(c.end_interval_time, 'MM/DD/YYYY') "Date",
sum(a.time_waited_micro)/sum(a.total_waits)/10000 "Average Waits FTS",
sum(b.time_waited_micro)/sum(b.total_waits)/10000 "Average Waits Index Read",
(sum(a.total_waits) / sum(a.total_waits + b.total_waits)) * 100 "Percent of FTS",
(sum(b.total_waits) / sum(a.total_waits + b.total_waits)) * 100 "Percent of Index Scans",
(sum(b.time_waited_micro)/sum(b.total_waits)) /
(sum(a.time_waited_micro)/sum(a.total_waits)) * 100 "optimizer_index_cost_adj"
from dba_hist_system_event a, dba_hist_system_event b, dba_hist_snapshot c
where a.event_name = 'db file scattered read'
and b.event_name = 'db file sequential read'
and a.snap_id = c.snap_id
and b.snap_id = c.snap_id
group by c.end_interval_time
order by 1;
10g中：
select to_char(c.snap_time, 'MM/DD/YYYY') "Date",
sum(a.time_waited_micro)/sum(a.total_waits)/10000 "Average Waits FTS",
sum(b.time_waited_micro)/sum(b.total_waits)/10000 "Average Waits Index Read",
(sum(a.total_waits) / sum(a.total_waits + b.total_waits)) * 100 "Percent of FTS",
(sum(b.total_waits) / sum(a.total_waits + b.total_waits)) * 100 "Percent of Index Scans",
(sum(b.time_waited_micro)/sum(b.total_waits)) /
(sum(a.time_waited_micro)/sum(a.total_waits)) * 100 "optimizer_index_cost_adj"
from stats$system_event a, stats$system_event b, stats$snapshot c
where a.event = 'db file scattered read'
and b.event = 'db file sequential read'
and a.snap_id = c.snap_id
and b.snap_id = c.snap_id
group by c.snap_time
order by 1;
當(dāng)optimizer_index_cost_adj的值對于整個(gè)系統(tǒng)來說已經(jīng)是比較合理的值，而某些語句由于該值選擇了全表掃描掃描導(dǎo)致了IO性能問題時(shí)，我們可以考慮通過提示來強(qiáng)制語句命中索引。
建立在條件字段上的索引的選擇性不高，結(jié)合上一條導(dǎo)致全表掃描;
當(dāng)索引的選擇性不高，且其代價(jià)過高，系統(tǒng)則會(huì)選擇全表掃描來讀取數(shù)據(jù)。這時(shí)我們可以考慮通過選擇/建立選擇性比較高的索引，使查詢命中索引從而避免全表掃描。
SQL> create index t_test1_idx1 on t_test1(owner) compute statistics;
Index created.
SQL> set autot trace
SQL> select object_name
2 from t_test1
3 where owner = 'SYS'
4 and created > sysdate - 30;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1883417357
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49 | 1715 | 152 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T_TEST1 | 49 | 1715 | 152 (2)| 00:00:02 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS' AND "CREATED">SYSDATE@!-30)
... ...
SQL> create index t_test1_idx2 on t_test1(owner, created) compute statistics;
Index created.
SQL> select object_name
2 from t_test1
3 where owner = 'SYS'
4 and created > sysdate - 30;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3417015015
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49 | 1715 | 2 (0)
| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_TEST1 | 49 | 1715 | 2 (0)
| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_TEST1_IDX2 | 49 | | 1 (0)
| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYS' AND "CREATED">SYSDATE@!-30)
... ...
3.2.2.2 調(diào)整DB_FILE_MULTIBLOCK_READ_COUNT
當(dāng)SQL已經(jīng)沒有優(yōu)化余地后，問題仍沒有解決，我們可以考慮調(diào)整DB_FILE_MULTIBLOCK_READ_COUNT大小。其作用我們在3.1.2中有做敘述，這里不再贅述。不過要注意一點(diǎn)就是，DB_FILE_MULTIBLOCK_READ_COUNT * DB_BLOCK_SIZE是一次IO讀取的傳輸量，它不能大于系統(tǒng)的max_io_size大小。
從Oracle 10gR2開始，如果沒有設(shè)置DB_FILE_MULTIBLOCK_READ_COUNT的大小，Oracle會(huì)自動(dòng)為其調(diào)整一個(gè)默認(rèn)值，這個(gè)默認(rèn)值的大小與平臺(tái)最大IO大小(max_io_size)相關(guān)(對大多數(shù)平臺(tái)來說max_io_size是1M)，其大小被設(shè)置為(max_io_size / DB_BLOCK_SIZE)。
3.2.2.3 將頻繁訪問的全掃描的表CACHE住
由于通過Full Table Scan和Fast Full Index Scan讀取的數(shù)據(jù)塊會(huì)被放置到Buffer Cache的LRU鏈表的LRU端，從而使數(shù)據(jù)塊盡快從Buffer Cache中移出。因此，對于那些會(huì)被頻繁訪問到全掃描的表，且其數(shù)據(jù)量不大的情況下，我們可以考慮將它們CACHE住。
SQL> alter table t_test1 cache;
Table altered.
對于Fast Full Index Scan的索引對象，則可以考慮把它放置在KEEP池中。
SQL> alter index t_test1_idx1 storage(buffer_pool keep);
Index altered.
利用V$SESSION_EVENT視圖，我們同樣可以找到當(dāng)前系統(tǒng)中發(fā)生全掃描的對象。
SQL> select p1 "fileid", p2 "block_id", p3 "block_num"
2 from v$session_wait
3 where event = 'db file scattered read';
fileid block_id block_num
---------- ---------- ----------
359 152972 16
SQL> select
2 segment_name "Segment Name",
3 segment_type "Segment Type",
4 block_id "First Block of Segment",
5 block_id+blocks "Last Block of Segment"
6 from dba_extents
7 where &fileid = file_id
8 and &block_id >= block_id
9 and &block_id <= block_id+blocks;
Enter value for fileid: 359
old 7: where &fileid = file_id
new 7: where 359 = file_id
Enter value for block_id: 152972
old 8: and &block_id >= block_id
new 8: and 152972 >= block_id
Enter value for block_id: 152972
old 9: and &block_id <= block_id+blocks
new 9: and 152972 <= block_id+blocks
Segment Name
--------------------------------------------------------------------------------
Segment Type First Block of Segment Last Block of Segment
------------------ ---------------------- ---------------------
CSS_TP_SHMT_QUEUE
TABLE 152969 153001
3.2.2.4 利用分區(qū)表減少全掃描操作讀取的數(shù)據(jù)塊數(shù)量
前面我們有介紹分區(qū)裁剪(Partition Pruning)技術(shù)。將表分區(qū)，利用分區(qū)裁剪技術(shù)，在進(jìn)行全掃描時(shí)只會(huì)掃描在WHERE條件中出現(xiàn)的分區(qū)，從而可以減少全掃描所讀取到的數(shù)據(jù)塊數(shù)量。
3.2.2.5 Housekeep歷史數(shù)據(jù)
同樣，housekeep不需要的、歷史的數(shù)據(jù)，減少數(shù)據(jù)段中的數(shù)據(jù)塊數(shù)量，也能減少全掃描的IO請求次數(shù)。
總結(jié)
以上是生活随笔為你收集整理的oracle 频繁io,Oracle IO问题解析的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： banner特效php,jQuery自适
- 下一篇： 重启oracle后报01034,ORAC