Explain Plan in Oracle

Easiest way to SQL Tuning

Here is the sample format:

explain plan for your-precious-sql-statement;

select * from table(dbms_xplan.display);

Sample Query:

explain plan for
select * from srgt_guarantee where srgt_facility_grading_id=1882

select * from table(dbms_xplan.display);

Here is the Results:

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 |
| 1 | SORT AGGREGATE | | 1 | |
| 2 | INDEX FAST FULL SCAN| PK_SRGT_GUARANTEE | 763 | 2 |
-------------------------------------------------------------------

Note
-----
- 'PLAN_TABLE' is old version

You can also create your own plan table in ur schema. Howevery it shud be in the following format:

CREATE TABLE SRGT_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)
);

and you can explain plan the query into the table:

explain plan into SRGT_PLAN_TABLE for (select context_id,EFF_TIMESTAMP from srgt_ref_client_info
where srgt_ref_client_id=(select srgt_ref_client_id from srgt_client_grading_info where
srgt_client_grading_id=(select srgt_client_grading_id from srgt_v_facility_grading_info
where srgt_ref_facility_id=2113)))

SELECT * FROM SRGT_PLAN_TABLE


Deafult way of doing it is:

explain plan FOR (select context_id,EFF_TIMESTAMP from srgt_ref_client_info
where srgt_ref_client_id=(select srgt_ref_client_id from srgt_client_grading_info where
srgt_client_grading_id=(select srgt_client_grading_id from srgt_v_facility_grading_info
where srgt_ref_facility_id=2113)))

select * from table(dbms_xplan.display);

will display the results of the query just executed =)



---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 13 |
| 1 | TABLE ACCESS BY INDEX ROWID | SRGT_REF_CLIENT_INFO | 1 | 14 | 2 |
| 2 | INDEX UNIQUE SCAN | PK_SRGT_REF_CLIENT_INFO | 1 | | 1 |
| 3 | TABLE ACCESS BY INDEX ROWID| SRGT_CLIENT_GRADING_INFO | 1 | 8 | 2 |
| 4 | INDEX UNIQUE SCAN | PK_SRGT_CLIENT_GRADING_INFO | 1 | | 1 |
| 5 | NESTED LOOPS OUTER | | 1 | 22 | 9 |
| 6 | NESTED LOOPS OUTER | | 1 | 18 | 9 |
| 7 | TABLE ACCESS FULL | SRGT_FACILITY_GRADING_INFO | 1 | 14 | 9 |
| 8 | INDEX UNIQUE SCAN | PK_SRGT_REF_FACILITY_INFO | 1 | 4 | 0 |
| 9 | INDEX UNIQUE SCAN | PK_SRGT_TEMP_FACILITY_INFO | 1 | 4 | 0 |
---------------------------------------------------------------------------------------------

Note
-----
- 'PLAN_TABLE' is old version


Setting and using a Statement Id in Explain Plan:

explain plan SET STATEMENT_ID = 'srgt context' FOR (select context_id,EFF_TIMESTAMP from srgt_ref_client_info
where srgt_ref_client_id=(select srgt_ref_client_id from srgt_client_grading_info where
srgt_client_grading_id=(select srgt_client_grading_id from srgt_v_facility_grading_info
where srgt_ref_facility_id=2113)))


Form Plan table execute this query to get the values:

SELECT operation, options, object_name, id, parent_id, position, cost, cardinality,
other_tag, optimizer
FROM plan_table
WHERE statement_id = 'srgt context'
ORDER BY id;

More Details see: http://www.csee.umbc.edu/help/oracle8/server.815/a67775/ch13_exp.htm


Now I know abt explain plan in Oracle..!! yay !!

-Sunita