oracle CPU usage 100% 处理一例

1. 确定cpu usage top sqls

select * from (
	select
		 SQL_ID ,
		 sum(decode(session_state,'ON CPU',1,0)) as CPU,
		 sum(decode(session_state,'WAITING',1,0)) - sum(decode(session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) as WAIT,
		 sum(decode(session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) as IO,
		 sum(decode(session_state,'ON CPU',1,1)) as TOTAL
	from v$active_session_history
	where SQL_ID is not NULL
    and sample_time > sysdate - 30/(24*60)
	group by sql_id
	order by sum(decode(session_state,'ON CPU',1,1))   desc
	)
where rownum <11

2. 确定sql text & 执行计划

select sql_text from v$sqlarea where (hash_value,address)=(select sql_hash_value,sql_address from v$session where sid=&sid);
select * from table(dbms_xplan.display_cursor('9gnxb8a0ucfb6',0,'ADVANCED'));
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('9gnxb8a0ucfb6'));

3. kill sqls

select SID,SERIAL# , machine from v$session where SQL_ID='9gnxb8a0ucfb6';
select 'alter system kill session '''|| t.SID||','||t.SERIAL#||''';' from v$session t where t.SQL_ID='9gnxb8a0ucfb6' and machine='lxtkmpsg12';

4.查找sql plan history

col btime for a25
select 
a.sql_id,a.plan_hash_value, 
to_char(begin_interval_time,'dd-mon-yy hh24:mi') btime, 
executions_delta executions, 
round(ELAPSED_TIME_delta/1000000/greatest(executions_delta,1),1) avg_duration_sec 
from dba_hist_SQLSTAT a, dba_hist_snapshot b
where sql_id='&sql_id' 
and a.snap_id=b.snap_id
and a.instance_number=b.instance_number
order by a.snap_id desc, elapsed_time_delta desc
/

5. 发现执行计划有变化， 固定高效执行计划

DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (sql_id => '9gnxb8a0ucfb6', plan_hash_value => '2344486238');
END;
/


select sql_handle,plan_name, origin, enabled, accepted, fixed, reproduced, autopurge from dba_sql_plan_baselines where sql_text like '%select transactio0_.ID as ID1_201_, transactio0_.MSG_ID as%';

var res number
exec :res := DBMS_SPM.ALTER_SQL_PLAN_BASELINE ( sql_handle => 'SQL_e1598a8982aab13d', plan_name => 'SQL_PLAN_f2qcaj61apc9x967af349', attribute_name => 'FIXED', attribute_value => 'YES' );