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語句,相關優化措施見問題分析。