Find Explain Plan for a given sql_id

You can check the explain plan for a given sql_id using following commands.

Suppose the sql_id given was ’6rata4x2qurst’.
The source of sql_id can be a badly performing query from statspack, awr etc.

SQL> select sql_fulltext from gv$sql where sql_id=’6rata4x2qurst’; SQL_FULLTEXT ——————————————————————————–——————–——————–——————–——————– select empno from emp where sal > 1000 SQL> explain plan for select empno from emp where sal > 1000; Explained. SQL> select * from table(dbms_xplan.display()); PLAN_TABLE_OUTPUT —————————————————————————–——–——–——–——–——————–——————–——————– Plan hash value: 3956160932 ————————————————————————–——–——–——————–——————–——————–——————– | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ————————————————————————–——–——–——–——————–——————–——————–——————– | 0 | SELECT STATEMENT | | 13 | 104 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 13 | 104 | 3 (0)| 00:00:01 | ————————————————————————–——————–——————–——————–——————–——————– Predicate Information (identified by operation id): ———————————————————————–——————–——————–——————–——————–——————– PLAN_TABLE_OUTPUT —————————————————————————–——————–——————–——————–——————–——————– 1 – filter(“SAL”>1000) 13 rows selected.