ORACLE 資料庫11.2.0.4 單例項伺服器IO等待高問題分析

       ORACLE 資料庫11.2.0.4 單例項伺服器IO等待高問題分析。分析過程中發現一條SQL消耗IO高的SQL，進一步分析發現有全表掃描，另外發現主機IO-WAIT高的時間段內有備份作業在執行，初步確定是備份作業和SQL全表掃描導致主機IO-wait高，觸發告警。

1、問題描述

    20180929，客戶反饋伺服器磁碟IOWAIT 較高，一般在10以上，高的時候，可達30%以上。

2、提取分析日誌，資料庫告警日誌、問題時段的AWR、及問題SQL的sqlhc、系統IOTOP監控記錄

3、問題分析

    a、觀察資料庫告警日誌，沒有發現異常的資料庫ORA報錯

    b、觀察系統IOTOP監控記錄發現問題時段有資料庫備份任務在執行：

    c、觀察問題時段資料庫的AWR發現一條又效能問題的SQL語句：

    SQL語句文字如下：

select a.inter_code, a.hq_date, a.repair_unit_nav as unit_nav  from dm_fund_hq a, dm_stock_info b  where a.inter_code = b.inter_code  and b.fund_style in (100301, 100101, 100201)  and a.hq_date > to_char(add_months(sysdate, -12), 'yyyyMMdd')  and a.inter_code = :1  order by a.hq_date

    SQL執行計劃如下：

 根據oracle的SQL自動調優，獲得如下建議：

FINDINGS SECTION (2 findings) 
------------------------------------------------------------------------------- 

1- SQL Profile Finding (see explain plans section below) 
-------------------------------------------------------- 
A potentially better execution plan was found for this statement. 

Recommendation (estimated benefit: 86.11%) 
------------------------------------------ 
- Consider accepting the recommended SQL profile to use parallel execution 
for this statement. 
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_16308', 
task_owner => 'SYS', replace => TRUE, profile_type => 
DBMS_SQLTUNE.PX_PROFILE); 

Executing this query parallel with DOP 8 will improve its response time 
86.11% over the original plan. However, there is some cost in enabling 
parallel execution. It will increase the statement's resource consumption by 
an estimated 11.11% which may result in a reduction of system throughput. 
Also, because these resources are consumed over a much smaller duration, the 
response time of concurrent statements might be negatively impacted if 
sufficient hardware capacity is not available. 

The following data shows some sampled statistics for this SQL from the past 
week and projected weekly values when parallel execution is enabled. 

Past week sampled statistics for this SQL 
----------------------------------------- 
Number of executions 0 
Percent of total activity 0 
Percent of samples with #Active Sessions > 2*CPU 0 
Weekly DB time (in sec) 0 

Projected statistics with Parallel Execution 
-------------------------------------------- 
Weekly DB time (in sec) 0 

2- Index Finding (see explain plans section below) 
-------------------------------------------------- 
The execution plan of this statement can be improved by creating one or more 
indices. 

Recommendation (estimated benefit: 99.98%) 
------------------------------------------ 
- Consider running the Access Advisor to improve the physical schema design 
or creating the recommended index. 
create index ETSPRD.IDX$$_3FB40001 on ETSPRD.DM_STOCK_INFO("INTER_CODE"); 

- Consider running the Access Advisor to improve the physical schema design 
or creating the recommended index. 
create index ETSPRD.IDX$$_3FB40002 on 
ETSPRD.DM_FUND_HQ("INTER_CODE","HQ_DATE","REPAIR_UNIT_NAV"); 

Rationale 
--------- 
Creating the recommended indices significantly improves the execution plan 
of this statement. However, it might be preferable to run "Access Advisor" 
using a representative SQL workload as opposed to a single statement. This 
will allow to get comprehensive index recommendations which takes into 
account index maintenance overhead and additional space consumption. 

4、分析總結

    a、問題時段有備份任務執行，消耗系統伺服器CPU、IO資源；

    b、觀察問題時段AWR發現一條可最佳化的SQL語句，相關最佳化措施見問題分析。       ORACLE 資料庫11.2.0.4 單例項伺服器IO等待高問題分析。分析過程中發現一條SQL消耗IO高的SQL，進一步分析發現有全表掃描，另外發現主機IO-WAIT高的時間段內有備份作業在執行，初步確定是備份作業和SQL全表掃描導致主機IO-wait高，觸發告警。

1、問題描述

    20180929，客戶反饋伺服器磁碟IOWAIT 較高，一般在10以上，高的時候，可達30%以上。

2、提取分析日誌，資料庫告警日誌、問題時段的AWR、及問題SQL的sqlhc、系統IOTOP監控記錄

3、問題分析

    a、觀察資料庫告警日誌，沒有發現異常的資料庫ORA報錯

    b、觀察系統IOTOP監控記錄發現問題時段有資料庫備份任務在執行：

    c、觀察問題時段資料庫的AWR發現一條又效能問題的SQL語句：

    SQL語句文字如下：

select a.inter_code, a.hq_date, a.repair_unit_nav as unit_nav  from dm_fund_hq a, dm_stock_info b  where a.inter_code = b.inter_code  and b.fund_style in (100301, 100101, 100201)  and a.hq_date > to_char(add_months(sysdate, -12), 'yyyyMMdd')  and a.inter_code = :1  order by a.hq_date

    SQL執行計劃如下：

 根據oracle的SQL自動調優，獲得如下建議：

FINDINGS SECTION (2 findings) 
------------------------------------------------------------------------------- 

1- SQL Profile Finding (see explain plans section below) 
-------------------------------------------------------- 
A potentially better execution plan was found for this statement. 

Recommendation (estimated benefit: 86.11%) 
------------------------------------------ 
- Consider accepting the recommended SQL profile to use parallel execution 
for this statement. 
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_16308', 
task_owner => 'SYS', replace => TRUE, profile_type => 
DBMS_SQLTUNE.PX_PROFILE); 

Executing this query parallel with DOP 8 will improve its response time 
86.11% over the original plan. However, there is some cost in enabling 
parallel execution. It will increase the statement's resource consumption by 
an estimated 11.11% which may result in a reduction of system throughput. 
Also, because these resources are consumed over a much smaller duration, the 
response time of concurrent statements might be negatively impacted if 
sufficient hardware capacity is not available. 

The following data shows some sampled statistics for this SQL from the past 
week and projected weekly values when parallel execution is enabled. 

Past week sampled statistics for this SQL 
----------------------------------------- 
Number of executions 0 
Percent of total activity 0 
Percent of samples with #Active Sessions > 2*CPU 0 
Weekly DB time (in sec) 0 

Projected statistics with Parallel Execution 
-------------------------------------------- 
Weekly DB time (in sec) 0 

2- Index Finding (see explain plans section below) 
-------------------------------------------------- 
The execution plan of this statement can be improved by creating one or more 
indices. 

Recommendation (estimated benefit: 99.98%) 
------------------------------------------ 
- Consider running the Access Advisor to improve the physical schema design 
or creating the recommended index. 
create index ETSPRD.IDX$$_3FB40001 on ETSPRD.DM_STOCK_INFO("INTER_CODE"); 

- Consider running the Access Advisor to improve the physical schema design 
or creating the recommended index. 
create index ETSPRD.IDX$$_3FB40002 on 
ETSPRD.DM_FUND_HQ("INTER_CODE","HQ_DATE","REPAIR_UNIT_NAV"); 

Rationale 
--------- 
Creating the recommended indices significantly improves the execution plan 
of this statement. However, it might be preferable to run "Access Advisor" 
using a representative SQL workload as opposed to a single statement. This 
will allow to get comprehensive index recommendations which takes into 
account index maintenance overhead and additional space consumption. 

4、分析總結

    a、問題時段有備份任務執行，消耗系統伺服器CPU、IO資源；

    b、觀察問題時段AWR發現一條可最佳化的SQL語句，相關最佳化措施見問題分析。