Saturday, September 17, 2011

Find Explain Plan for a given sql_id


You can check the explain plan for a given sql_id using following commands.

Suppose the sql_id given was ’6rata4x2qurst’.
The source of sql_id can be a badly performing query from statspack, awr etc.

SQL> select sql_fulltext from gv$sql where sql_id=’6rata4x2qurst’;
SQL_FULLTEXT
——————————————————————————–——————–——————–——————–——————–
select empno from emp where sal > 1000

SQL> explain plan for select empno from emp where sal > 1000;
Explained.
SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
—————————————————————————–——–——–——–——–——————–——————–——————–

Plan hash value: 3956160932
————————————————————————–——–——–——————–——————–——————–——————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–——–——–——–——————–——————–——————–——————–
| 0 | SELECT STATEMENT | | 13 | 104 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 13 | 104 | 3 (0)| 00:00:01 |
————————————————————————–——————–——————–——————–——————–——————–
Predicate Information (identified by operation id):
———————————————————————–——————–——————–——————–——————–——————–
PLAN_TABLE_OUTPUT
—————————————————————————–——————–——————–——————–——————–——————–
1 – filter(“SAL”>1000)
13 rows selected.

0 comments:

Post a Comment

 

ORA-BLOG. Copyright 2008 All Rights Reserved Revolution Two Church theme by Brian Gardner Converted into Blogger Template by Bloganol dot com