某类等待事件集中的sql通用优化流程

---------查找特定等待事件的sql;
select sql_id, count(*)
  from dba_hist_active_sess_history
 where event = 'enq: TX - row lock contention'
 group by sql_id;


select sql_id, count(*)
  from v$active_session_history
 where event like 'direct path read'
 group by sql_id
 order by 2 desc;
 
select sql_id, count(*)
  from v$active_session_history
 where event like 'db file parallel read'
 group by sql_id
 order by 2 desc;
 
 select sql_id, count(*)
  from v$active_session_history
 where event like 'db file sequential read'
 group by sql_id
 order by 2 desc;
 
  select sql_id, count(*)
  from v$active_session_history
 where event like 'log file sync'
 group by sql_id
 order by 2 desc;
 
 
select * from v$sqltext where sql_id = 'd15cdr0zt3vtp';


------针对特定的sql_id 查看其执行计划的历史信息；
--方法1；
-- @awrsqrpi.sql
-- @awrsqrpt.sql




select min(snap_id),max(snap_id) from dba_hist_ash_snapshot;




---方法2；


select output
  from table(dbms_workload_repository.awr_sql_report_text(l_dbid     => 1830148213,
                                                          l_inst_num => 1,
                                                          l_bid      => 8500,
                                                          l_eid      => 9602,
                                                          l_sqlid    => '0h7nfdar8ysc5'));
                                                          
                                                          
                                                          
                                                          
                                                          
----对sql提出优化建议；








sqlplus:


var v_sql_id varchar2(13)
var v_task_name varchar2


begin


:v_sql_id := '1273jtxw8hrj1';
:v_task_name := dbms_sqltune.create_tuning_task(sql_id => :v_sql_id);
dbms_output.put_line(:v_task_name);
dbms_sqltune.execute_tuning_task(:v_task_name);
commit;
end;






----查看优化建议；


select dbms_sqltune.report_tuning_task('TASK_30746') from dual;


select dbms_sqltune.report_tuning_task('TASK_30746','TEXT','ALL') from dual;


select dbms_sqltune.script_tuning_task('TASK_30746') from dual;                                                     

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

转载于:http://blog.itpub.net/29677883/viewspace-1170227/