简单查询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

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