ITPub博客

首页 > 数据库 > Oracle > ORACLE 数据库11.2.0.4 单实例服务器IO等待高问题分析

ORACLE 数据库11.2.0.4 单实例服务器IO等待高问题分析

原创 Oracle 作者:清风艾艾 时间:2018-09-30 23:56:02 0 删除 编辑

       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语句,相关优化措施见问题分析。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29357786/viewspace-2215382/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
个人喜欢IT行业,目前从事数据库工作,包括Oracle、mysql、mongodb、sqlserver等数据库的维护,喜欢专研开发技术,尤其对java程序的开发感兴趣。工作经历上,在中国联通系统集成公司、中公网医疗信息技术有限公司做过数据库技术支持;目前在海量数据,负责华东区oracle、mysql、mongodb的维护工作。

注册时间:2015-01-30

  • 博文量
    176
  • 访问量
    204334