看执行计划，多游标诊断及清理

把语句提取后，加入些提示，让解析器重新执行
SELECT /*+ gather_plan_statistics test111 */ ...
执行2分钟后 Ctrl +C 中断
 
##获取SQL_ID
set linesize 200 pagesize 100
select sql_id,sql_text from v$sqlarea where sql_text like '%test111%' and sql_text not like '%sqlarea%';
 
 
##打印执行明细
select * from table(dbms_xplan.display_cursor('&sql_id_above_query', null, 'ALLSTATS LAST'));
 
select event,count(*) ,round(avg(time_waited/10000),0) avg_wait_ms from v$active_session_history where sql_id='&sql_id' and sample_time>sysdate - 20/1440 group by event order by 2;
 
select sid,sql_id,last_call_et,sql_child_numer,program from v$session where type='USER' and status='ACTIVE' order by last_call_et;
 
##收取sql_monitor
set linesize 300 pagesize 100 long 50000 longc 100000 select dbms_sqltune.report_sql_monitor( SQL_ID => '849j25612mfyk', session_id => 1702, type => 'text') c from dual;

select * from table(dbms_xplan.display_cursor('849j25612mfyk', null, 'ADVANCED'));

##检查当前计划有几个
alter session set nls_date_format='yyyymmdd hh24:mi:ss'; select child_number,plan_hash_value,first_load_time,last_load_time,last_active_time from v$sql where sql_id='849j25612mfyk';

##确认清理
select address,hash_value,version_count from v$sqlarea where sql_id='849j25612mfyk';
exec dbms_shared_pool.purge('','C');