看执行计划，多游标诊断及清理-凯发app官方网站

把语句提取后，加入些提示，让解析器重新执行
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');

阅读(5208) | 评论(0) | 转发(0) |

上一篇：清理共享池游标

下一篇：tidb 啊

给主人留下些什么吧！~~

| | | | |

感谢所有关心和支持过chinaunix的朋友们