Common SQL for Oracle---(3)Check Execution Plan

---Check Execution Plan----

---sql_plan@awr

select plan_table_output from table (dbms_xplan.display_awr('&sql_id',null,null,'ADVANCED +PEEKED_BINDS'));

---all sql_plan@awr

SELECT A.INSTANCE_NUMBER,

       A.SNAP_ID,

       A.SQL_ID,

       A.PLAN_HASH_VALUE,

       to_char(b.begin_interval_time, 'yyyy/mm/dd hh24:mi:ss')

  FROM DBA_HIST_SQLSTAT A, DBA_HIST_SNAPSHOT B

 WHERE SQL_ID = '3ug1bja99mtkv'

   AND A.SNAP_ID = B.SNAP_ID

   

   

 ---all sql_plan cost@awr  

SELECT A.INSTANCE_NUMBER INST,A.OPTIMIZER_MODE OPT_MODE,A.OPTIMIZER_COST OPT_COST,A.MODULE,A.SQL_ID,

       A.PLAN_HASH_VALUE,TO_CHAR((B.BEGIN_INTERVAL_TIME),'YYYY/MM/DD HH24:MI:SS') BEGIN_TIME

  FROM DBA_HIST_SQLSTAT A, DBA_HIST_SNAPSHOT B

 WHERE SQL_ID=LOWER('d8rjbdatc87pf')

   AND A.SNAP_ID=B.SNAP_ID

   AND A.INSTANCE_NUMBER=B.INSTANCE_NUMBER

 ORDER BY B.BEGIN_INTERVAL_TIME DESC