Oracle IO問題解析（7）

season0891發表於2010-08-05
Oracle

3.2.2      db file scattered read

這是另外一個常見的引起資料庫IO效能問題的等待事件。它通常發生在Oracle將“多資料塊”讀取到Buffer Cache中的非連續（分散的 Scattered）區域。多資料塊讀就是我們上述所說的一次讀取“DB_FILE_MULTIBLOCK_READ_COUNT”塊資料塊，前面提到，它通常發生在全表掃描（Full Table Scan）和快速全索引掃描（Fast Full Index Scan）時。當發現db file scattered read等待事件是系統引起IO效能的主要原因時，我們可以採取以下措施對系統進行最佳化。

3.2.2.1  最佳化存在Full Table Scan和Fast Full Index Scan的SQL語句

 

我們可以首先從statspack或者awr報告中的“SQL ordered by Reads”部分中找出存在Full Table Scan和Fast Full Index Scan的Top SQL。因為這些Top SQL往往是整個系統的瓶頸。

從9i開始，我們還可以透過檢視V$SQL_PLAN來查詢系統中存在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通常是由於以下幾個原因引起的：

 

  • 條件欄位上沒有索引；

 

在這種情況下，如果表的資料量比較大，我們就需要在相應欄位上建立起索引。

 

  • CBO中，物件的統計資料不正確

 

CBO中，如果物件的統計資料或者其柱狀圖（Histogram）資訊不正確，會導致最佳化器計算出錯誤的查詢計劃，從而選擇全表掃描。這種情況下，我們要做的就重新分析（Analyze）表、索引及欄位。

 

  • CBO中，SQL語句中引用到了無法估算統計資料的物件

 

在PLSQL中，可以建立一些高階的資料型別，如“TABLE OF”、ARRAY等，透過TABLE、CAST函式可以在SQL語句中將這些物件當成表來處理。而這些物件的資料只存在於呼叫PLSQL的會話中，因此他們沒有相應的統計資料，Oracle會為他們生產一些假的統計資料以完成查詢計劃代價估算。但是基於這些假的資料計算出的查詢計劃一般是錯誤的。我們可以考慮透過提示來強制SQL使用索引或者強制SQL採用RBO最佳化器。

此外，如果SQL中引用到了臨時表（Temporary Table）也會產生同樣的問題。其原因和解決方法和上面相同。

 

  • 最佳化器認為索引掃描代價過高；

 

在Oracle中存在一個引數optimizer_index_cost_adj，該引數的值代表一個百分數，如果對索引掃描的代價達到或超過全表掃描的代價的這個百分比值時，最佳化器就採用全表掃描。

optimizer_index_cost_adj是一個全域性性的引數，它的合理值是透過長期調整出來的。一般來說是一個介於1到100之間的數字。我們可以按照以下方法來選取optimizer_index_cost_adj的合理值。

先由以下語句得出optimizer_index_cost_adj的一個初始值：

 

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是我們系統的初始值。在系統經過一段時間執行後，再次執行上面的語句，重新調整optimizer_index_cost_adj的值。經過多次如此反覆的調整之後，最終上面語句得出值趨於穩定，這時這個值就是符合我們系統效能需求的最合理的值。

當然這個數值也可以透過statspack的歷史資料來調整，在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;

 

當optimizer_index_cost_adj的值對於整個系統來說已經是比較合理的值，而某些語句由於該值選擇了全表掃描掃描導致了IO效能問題時，我們可以考慮透過提示來強制語句命中索引。

 

  • 建立在條件欄位上的索引的選擇性不高，結合上一條導致全表掃描；

 

當索引的選擇性不高，且其代價過高，系統則會選擇全表掃描來讀取資料。這時我們可以考慮透過選擇/建立選擇性比較高的索引，使查詢命中索引從而避免全表掃描。

 

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 wner = '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 wner = '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  調整DB_FILE_MULTIBLOCK_READ_COUNT

當SQL已經沒有最佳化餘地後，問題仍沒有解決，我們可以考慮調整DB_FILE_MULTIBLOCK_READ_COUNT大小。其作用我們在3.1.2中有做敘述，這裡不再贅述。不過要注意一點就是，DB_FILE_MULTIBLOCK_READ_COUNT * DB_BLOCK_SIZE是一次IO讀取的傳輸量，它不能大於系統的max_io_size大小。

從Oracle 10gR2開始，如果沒有設定DB_FILE_MULTIBLOCK_READ_COUNT的大小，Oracle會自動為其調整一個預設值，這個預設值的大小與平臺最大IO大小（max_io_size）相關（對大多數平臺來說max_io_size是1M），其大小被設定為（max_io_size / DB_BLOCK_SIZE）。

3.2.2.3  將頻繁訪問的全掃描的表CACHE住

由於透過Full Table Scan和Fast Full Index Scan讀取的資料塊會被放置到Buffer Cache的LRU連結串列的LRU端，從而使資料塊儘快從Buffer Cache中移出。因此，對於那些會被頻繁訪問到全掃描的表，且其資料量不大的情況下，我們可以考慮將它們CACHE住。

 

SQL> 
 
Table altered.

 

對於Fast Full Index Scan的索引物件，則可以考慮把它放置在KEEP池中。

 

SQL> alter index t_test1_idx1 storage(buffer_pool keep);
 
Index altered.

 

利用V$SESSION_EVENT檢視，我們同樣可以找到當前系統中發生全掃描的物件。

 

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  利用分割槽表減少全掃描操作讀取的資料塊數量

前面我們有介紹分割槽裁剪（Partition Pruning）技術。將表分割槽，利用分割槽裁剪技術，在進行全掃描時只會掃描在WHERE條件中出現的分割槽，從而可以減少全掃描所讀取到的資料塊數量。

3.2.2.5  Housekeep歷史資料

同樣，housekeep不需要的、歷史的資料，減少資料段中的資料塊數量，也能減少全掃描的IO請求次數。

come from：

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/90618/viewspace-670383/，如需轉載，請註明出處，否則將追究法律責任。

相關文章