Translate

Search This Blog

easier way to get query execution plan from 10g onwards

traditional method before 10g

select * from v$sql where address='&ADDRESS' and hash_value=&HASH_VALUE;

10g :

select * from v$sql where sql_id='&SQL_ID';
select * from v$sqlstats where sql_id='&SQL_ID';
select * from dba_hist_sqlstat where sql_id='&SQL_ID';

select * from table(DBMS_XPLAN.DISPLAY_CURSOR('&SQL_ID', NULL, 'ALL'));

select sql_id from top sqls in awr report and then see its execution plan as below:
select * from table (dbms_xplan.display_awr(sql_id)); This shows execution plan for all occurence of query with sql_id while below gives execution plan specific to plan_hash

select * from table(DBMS_XPLAN.DISPLAY_AWR('&SQL_ID',&PLAN_HASH,NULL,'ALL'));




WHAT 9i: gave:

explain plan FOR SELECT * FROM dual

SELECT * FROM TABLE(dbms_xplan.display)

CREATE TABLE t6 AS SELECT * FROM dual WHERE 1=2

explain plan FOR INSERT INTO t6 SELECT * FROM dual

SELECT * FROM TABLE(dbms_xplan.display)

explain plan FOR CREATE TABLE t8 AS SELECT * FROM dual WHERE 1=2


SELECT * FROM TABLE(dbms_xplan.display)

Plan hash value: 2951603110

-------------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | 1 | 2 | 1 (0)| 00:00:01 |
| 1 | LOAD AS SELECT | T8 | | | | |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS FULL | DUAL | 1 | 2 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

2 - filter(NULL IS NOT NULL)

Follow by Email