简单查询plan

->

alter session set statistics_level=all;

select /*+ gathe_plan_statistics */ * from ts.ts_record t where system_name='人员小车闸口' order by pass_datetime desc;

 

select sql_text,sql_id from v$sql where sql_text like 'select%*%人员小车闸口%';

 

-> pl/sql developer

login in app user

run the app sql

get from sql from session monitor .

-->

 

sqlplus / as sysdba

spool D:\dba\tmp\sql_dev.log

 

select * from table(dbms_xplan.display_cursor('36tvbv2uth9j9',0,'runstats_last')); 

spool off

 

注意有的时候，通过dbms_xplan.display_cursor 显示不出结果，有可能需要从 dbms_xplan.display_awr 获取结果，

select * from table(dbms_xplan.display_cursor('2mqzgq65dy5sr',null,'advanced'));

select * from table(dbms_xplan.display_awr('2mqzgq65dy5sr'));

 

 

select * from table(dbms_xplan.display_cursor(null,0,'allstats last'));

select * from table(dbms_xplan.display_cursor(null,null,'advanced'));      

 

 ---查看上一条sql 的 plan  :

select * from table(dbms_xplan.display_cursor());      

 

-->

sqlplus / as sysdba

SET LONG 1000000 SET FEEDBACK OFF

spool monitor_sql.html

SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id =>'20wfgydukawbw',type=> 'HTML') AS report FROM dual;  

spool off    

                   

 

->

 SQL> alter session set events '10046 trace name context forever,level 12';

Session altered.

SQL> /

Session altered.



SQL> alter session set events '10046 trace name context off';

Session altered.

SQL>select * from v$diag_info;

 

 

案例可以参考 http://blog.mchz.com.cn/?p=404