Fetch Execution plan with SQL Plan View with SQL ID or HASH Value Parameters
Find the execution plan with help of V$SQL_PLAN view
--Find with help of SQL ID
col object_owner for a5
col name for a20
col operation for a30
select id,operation,PLAN_HASH_VALUE ,object_owner||'-'||object_name as Name,BYTES,cpu_cost,time from v$SQL_PLAN where sql_id ='40j9r2fbg3h8g';
--Find with help of Plan Hash Value
col object_owner for a5
col name for a20
col operation for a30
select id,operation ,object_owner||'-'||object_name as Name,BYTES,cpu_cost,time from v$SQL_PLAN where PLAN_HASH_VALUE= '1377175647';
Column Details of V$SQL_PLAN View as follows if you want to select new columns in execution plan:
SQL> desc v$sql_plan; Name Type -------------------------- ----------------------- ADDRESS RAW(8) HASH_VALUE NUMBER SQL_ID VARCHAR2(13) PLAN_HASH_VALUE NUMBER CHILD_ADDRESS RAW(8) CHILD_NUMBER NUMBER TIMESTAMP DATE OPERATION VARCHAR2(120) OPTIONS VARCHAR2(120) OBJECT_NODE VARCHAR2(160) OBJECT# NUMBER OBJECT_OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(30) OBJECT_ALIAS VARCHAR2(65) OBJECT_TYPE VARCHAR2(80) OPTIMIZER VARCHAR2(80) ID NUMBER PARENT_ID NUMBER DEPTH NUMBER POSITION NUMBER SEARCH_COLUMNS NUMBER COST NUMBER CARDINALITY NUMBER BYTES NUMBER OTHER_TAG VARCHAR2(140) PARTITION_START VARCHAR2(256) PARTITION_STOP VARCHAR2(256) PARTITION_ID NUMBER OTHER VARCHAR2(4000) DISTRIBUTION VARCHAR2(80) CPU_COST NUMBER IO_COST NUMBER TEMP_SPACE NUMBER ACCESS_PREDICATES VARCHAR2(4000) FILTER_PREDICATES VARCHAR2(4000) PROJECTION VARCHAR2(4000) TIME NUMBER QBLOCK_NAME VARCHAR2(30) REMARKS VARCHAR2(4000) OTHER_XML CLOB
