Execution_Explain Plan

Whenever we get request for application slowness or sql query then we need to identify sql_id and to analyze sql_id, we need to check SQL execution/explain plan.

1.

EXECUTION_PLAN:

select id,plan_hash_value,operation, options,object_name,cardinality,cost,bytes,cpu_cost,io_cost,time,partition_start,partition_stop,

object_node,other_tag,qblock_name,other_xml from dba_hist_sql_plan where sql_id='<SQL_ID>';

or

select * from table(dbms_xplan.display_awr('<SQL_ID>'));

EXPLAIN_PLAN:

explain plan for <SQL_QUERY>;

select * from table(dbms_xplan.display);