正德厚生,臻于至善

Oracle 根据SQL_ID查询并杀会话,清空执行计划缓冲池

1. 查询最近五分钟内最高频次SQL,查看event
select t.SQL_OPNAME,t.SQL_ID,count(*) from v$active_session_history t where t.SAMPLE_TIME>to_timestamp('20180906-09:55:00','YYYYMMDD-hh24:mi:ss') and t.SESSION_TYPE='FOREGROUND' group by t.SQL_OPNAME,t.SQL_ID order by count(*) desc;

2、 根据SQL 查询到操作用户
select s.username from v$active_session_history t,dba_users s  where t.USER_ID=s.user_id and t.SQL_ID='0nx7fbv1w5xg2';

3、查询并获取当前sql的杀会话语句
select 'alter system kill session '''|| t.SID||','||t.SERIAL#||''';' from v$session t where t.SQL_ID='0nx7fbv1w5xg2';

4、查询并获取当前会话的执行计划清空过程语句
select SQL_TEXT,sql_id, address, hash_value, executions, loads, parse_calls, invalidations from v$sqlarea where sql_id='0nx7fbv1w5xg2';
call sys.dbms_shared_pool.purge('0000000816530A98,3284334050','c');
select SQL_TEXT,sql_id, address, hash_value, executions, loads, parse_calls, invalidations  
from v$sqlarea  where sql_id='1ubrfsjjn7016';

select sql_id, address, hash_value, executions, loads, parse_calls, invalidations  
from v$sqlarea  where sql_id='1ubrfsjjn7016';

call sys.dbms_shared_pool.purge('0700010DF10C9C08,1665368102','c');

select name,created,category,sql_text from dba_sql_profiles where name like '%1ubrfsjjn7016%' order by created desc;

begin
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'coe_1ubrfsjjn7016_3135409287');
end;
/
赞(0) 打赏
未经允许不得转载:徐万新之路 » Oracle 根据SQL_ID查询并杀会话,清空执行计划缓冲池
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

联系我们

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏