Simple queries for explain plan

Use these queries for example from TOAD.

First version if you can access “plan_table”:


DELETE PLAN_TABLE WHERE STATEMENT_ID = '...here_your_key...';

EXPLAIN PLAN
SET STATEMENT_ID = '...here_your_key...'
FOR
SELECT ....here_your_whole_query.....
;

COMMIT;  --if you commit you can access explan plan later - depends also on your autocommit settings

SELECT timestamp, operation, options, object_node, object_owner, object_name, p.cost, p.bytes
FROM plan_table p
CONNECT BY prior id = parent_id
AND prior statement_id = statement_id
START WITH id = 0 AND statement_id = '...here_your_key...'
ORDER BY id;

 

Second version if you cannot access “plan table” you have to create your own plan_table first:


-- Create table
create table MY_PLAN_TABLE
(
STATEMENT_ID    VARCHAR2(30),
TIMESTAMP       DATE,
REMARKS         VARCHAR2(80),
OPERATION       VARCHAR2(30),
OPTIONS         VARCHAR2(30),
OBJECT_NODE     VARCHAR2(128),
OBJECT_OWNER    VARCHAR2(30),
OBJECT_NAME     VARCHAR2(30),
OBJECT_INSTANCE NUMBER(38),
OBJECT_TYPE     VARCHAR2(30),
OPTIMIZER       VARCHAR2(255),
SEARCH_COLUMNS  NUMBER,
ID              NUMBER(38),
PARENT_ID       NUMBER(38),
POSITION        NUMBER(38),
COST            NUMBER(38),
CARDINALITY     NUMBER(38),
BYTES           NUMBER(38),
OTHER_TAG       VARCHAR2(255),
PARTITION_START VARCHAR2(255),
PARTITION_STOP  VARCHAR2(255),
PARTITION_ID    NUMBER(38),
OTHER           LONG,
DISTRIBUTION    VARCHAR2(30),
CPU_COST          INTEGER        ,
IO_COST           INTEGER        ,
TEMP_SPACE        INTEGER        ,
ACCESS_PREDICATES VARCHAR2(4000) ,
FILTER_PREDICATES VARCHAR2(4000) ,
PROJECTION        VARCHAR2(4000) ,
TIME              INTEGER        ,
QBLOCK_NAME       VARCHAR2(30)
)
tablespace ...your_table_space...
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 40K
next 40K
minextents 1
maxextents unlimited
pctincrease 0
);

And as second step run slightly different script:


DELETE MY_PLAN_TABLE;

EXPLAIN PLAN
into MY_PLAN_TABLE FOR
select ....your_query_here....

COMMIT; --the same as above

SELECT timestamp,
operation,
options,
object_node,
object_owner,
object_name
FROM MY_PLAN_TABLE t
CONNECT BY prior id = parent_id
START WITH id = 0
ORDER BY id;

Leave a Reply

Your email address will not be published. Required fields are marked *