Yaping's Weblog

August 30, 2008

Format v$sql_plan output

Filed under: Oracle — Yaping @ 4:14 am
Tags:
When we query v$sql_plan view to examine execution plan, we expect to query and read the result easily.
List two motheds in oracle 9i and 10g.
Version 9i
=============================
SQL> create or replace view myplan as select to_char(hash_value) statement_id,sysdate timestamp,a.* from v$sql_plan a;
View created.

SQL> grant select on myplan to public;
Grant succeeded.

SQL> create public synonym myplan for myplan;
Synonym created.

SQL> select HASH_VALUE,SQL_TEXT from v$sql where SQL_TEXT like ‘select * from t1%’;
HASH_VALUE SQL_TEXT
———- ————————————————————————
2615572450 select * from t1 where id=100

SQL> select * from table(dbms_xplan.display(‘myplan’,’2615572450′));
PLAN_TABLE_OUTPUT
——————————————————————————–
——————————————————————–
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
——————————————————————–
|   0 | SELECT STATEMENT     |             |       |       |       |
|*  1 |  INDEX RANGE SCAN    | IDX_T1_ID   |       |       |       |
——————————————————————–
Predicate Information (identified by operation id):
—————————————————

PLAN_TABLE_OUTPUT
——————————————————————————–
   1 – access(“ID”=100)

Note: rule based optimization

14 rows selected.

Version 10g
=====================================================
SQL> select SQL_ID,CHILD_NUMBER,HASH_VALUE,SQL_TEXT from v$sql where SQL_TEXT like ‘select * from test.t1 where%’;
SQL_ID        CHILD_NUMBER HASH_VALUE SQL_TEXT
————- ———— ———- ————————————————–
6abjbgck9yvn5            0  614428293 select * from test.t1 where id=555

SQL> select * from table(dbms_xplan.DISPLAY_CURSOR(‘6abjbgck9yvn5’,0));
PLAN_TABLE_OUTPUT
—————————————————————————————-
SQL_ID  6abjbgck9yvn5, child number 0
————————————-
select * from test.t1 where id=555

Plan hash value: 3653646128

——————————————————————————
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
——————————————————————————
|   0 | SELECT STATEMENT |           |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| IDX_T1_ID |     1 |    13 |     1   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
——————————————————————————
——————————————————————————

Predicate Information (identified by operation id):
—————————————————

   1 – access(“ID”=555)

Note
—–
   – dynamic sampling used for this statement

22 rows selected.

If you query v$sql_plan, you should be careful. There’re bugs on this view.

 

 

 

 

 

 

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: