Execution plan with SQL Plan view with SQL ID or HASH VALUE

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.