oracle9

查看SQL历史执行信息：
SELECT SQL_ST.SQL_ID,PLAN_HASH_VALUE,TO_CHAR(ST.BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24:MI:SS') RUN_DATE,ST.SNAP_ID,ST.INSTANCE_NUMBER INS,
       SX.SQL_TEXT,ROUND(SQL_ST.ELAPSED_TIME_DELTA/1000000,2)ELAPSED_TIME_DELTA,SQL_ST.EXECUTIONS_DELTA,SQL_ST.ROWS_PROCESSED_DELTA,
       ROUND(SQL_ST.CLWAIT_DELTA/1000000,2) CLWAIT_DELTA, SQL_ST.BUFFER_GETS_DELTA,SQL_ST.DISK_READS_DELTA,SQL_ST.FETCHES_DELTA,
       ROUND(SQL_ST.CPU_TIME_DELTA/1000000,2) CPU_TIME_DELTA,
       SQL_ST.PX_SERVERS_EXECS_DELTA,ROUND(SQL_ST.IOWAIT_DELTA/1000000,2) IOWAIT_DELTA,
       ROUND(SQL_ST.CLWAIT_DELTA/1000000,2),SQL_ST.APWAIT_DELTA,SQL_ST.CCWAIT_DELTA,
       SQL_ST.DIRECT_WRITES_DELTA,SQL_ST.PLSEXEC_TIME_DELTA,SQL_ST.JAVEXEC_TIME_DELTA,SQL_ST.PHYSICAL_READ_REQUESTS_DELTA,
       SQL_ST.PHYSICAL_READ_BYTES_DELTA,SQL_ST.PHYSICAL_WRITE_REQUESTS_DELTA ,SQL_ST.PHYSICAL_WRITE_BYTES_DELTA ,
       OPTIMIZED_PHYSICAL_READS_DELTA ,CELL_UNCOMPRESSED_BYTES_DELTA,IO_OFFLOAD_RETURN_BYTES_DELTA,BIND_DATA ,FLAG ,
        MODULE,TO_CHAR(BEGIN_INTERVAL_TIME, 'YYYY-MM-DD HH24:MI') BEGIN_TIME,
       TO_CHAR(END_INTERVAL_TIME, 'YYYY-MM-DD HH24:MI') END_TIME 
  FROM DBA_HIST_SNAPSHOT ST, 
       DBA_HIST_SQLSTAT  SQL_ST, 
       DBA_HIST_SQLTEXT  SX
 WHERE ST.SNAP_ID         = SQL_ST.SNAP_ID
   AND ST.DBID            = SQL_ST.DBID
   AND ST.INSTANCE_NUMBER = SQL_ST.INSTANCE_NUMBER
   AND SQL_ST.SQL_ID      = SX.SQL_ID
   AND SQL_ST.DBID        = SX.DBID
   AND ST.BEGIN_INTERVAL_TIME >= TO_DATE('2014-8-1 01:30:16', 'YYYY-MM-DD HH24:MI:SS') 
   AND ST.BEGIN_INTERVAL_TIME <= TO_DATE('2014-8-1 10:50:16', 'YYYY-MM-DD HH24:MI:SS')
   AND SX.SQL_TEXT LIKE '%SELECT%use_hash(ocuu,oct,t,bct) parallel(oct,6)%'   
 ORDER BY SQL_ST.SQL_ID, ST.SNAP_ID, ST.INSTANCE_NUMBER 
   
 执行计划：
 select inst_id,sid,serial#,username,program,sql_id from gv$session where status='ACTIVE';


select sql_id,event, count(*)
    from v$session
   where user# <> 0
     and status = 'ACTIVE'
   group by sql_id
   order by count(*) desc;


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


SELECT * FROM  table(dbms_xplan.display_awr('sql_id',null));


SELECT * FROM  table(dbms_xplan.display_cursor('sql_id',null));
select  * from table(dbms_xplan.display_cursor('b04n8wmrp7zgc',null,  'all'));






select dbms_sqltune.report_sql_monitor('sql_id') from dual;


select plan_hash_value,timestamp from dba_hist_sql_plan where sql_id='' order by timestamp desc ;




EXPLAIN PLAN for select ename from emp where deptno in ( 20, 40 );
select * from table(dbms_xplan.display);


SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR('crdrz73gp6v5r') FROM DUAL;
SELECT DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id=>'crdrz73gp6v5r',type=>'TEXT') as report FROM DUAL;
---多个执行计划
select plan_hash_value,timestamp from gv$sql_plan where sql_id='';


awr：
AWR报告：
@?/rdbms/admin/awrrpt.sql




RAC awr报告：
@?/rdbms/admin/awrgrpt.sql