---
my version
create view v_$plan_table as
select
rawtohex(address)||'_'||child_number statement_id,
sysdate timestamp,
sp.*
from v$sql_plan sp;
create public synonym v$plan_table for v_$plan_table;
select rawtohex(address)||'_'||child_number statement_id
from v$sql
where sql_text='select * from t t1 where object_id > 32000';
select * from table(dbms_xplan.DISPLAY('V$PLAN_TABLE','A9E3099C_0'));
---
http://www.scaleabilities.co.uk/index.php/James/Using-DBMS_XPLAN.DISPLAY-on-V$SQL_PLAN.html
create view liveplan as
select sp.*,
to_char(hash_value) statement_id,
sysdate timestamp
from v$sql_plan sp;
--- the original implementation by tom kate
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10353453905351
ops$tkyte@ORA920> create or replace view dynamic_plan_table
2 as
3 select
4 rawtohex(address) || '_' || child_number statement_id,
5 sysdate timestamp, operation, options, object_node,
6 object_owner, object_name, 0 object_instance,
7 optimizer, search_columns, id, parent_id, position,
8 cost, cardinality, bytes, other_tag, partition_start,
9 partition_stop, partition_id, other, distribution,
10 cpu_cost, io_cost, temp_space, access_predicates,
11 filter_predicates
12 from v$sql_plan;
View created.
Now, you can query any plan from the database with a single query:
ops$tkyte@ORA920> select plan_table_output
2 from TABLE( dbms_xplan.display
3 ( 'dynamic_plan_table',
4 (select rawtohex(address)||'_'||child_number x
5 from v$sql
6 where sql_text='select * from t t1 where object_id > 32000' ),
7 'serial' ) )
8 /
PLAN_TABLE_OUTPUT
------------------------------------------------------------------
| Id | Operation | Name|Rows| Bytes |Cst(%CPU)|
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | TABLE ACCESS BY INDEX ROWID| T |291 | 27936 | 25 (0)|
|* 2 | INDEX RANGE SCAN | T_PK|291 | | 2 (0)|
------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">32000)
---
the original article
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:10353453905351
Ask Tom Logo
QuestionsResourcesArchivesLinksPopularHotFiles
Home>Question Details
gs -- Thanks for the question regarding "V$SQL_PLAN_STATISTICS", version 9.2.0.3
Submitted on 11-Jun-2003 21:06 Eastern US time
Tom's latest followup | Bookmark | Bottom
Last updated 13-Dec-2008 7:25
You Asked
Dear Tom,
Does V$SQL_PLAN_STATISTICS shows only the statics about the current (connected) session?
SQL> select count(*) from v$sql_plan ;
--I can get the plan details here
COUNT(*)
----------
18540
SQL> select count(*) from V$SQL_PLAN_STATISTICS ;
COUNT(*)
----------
0
and we said...
no, it is across sessions:
ops$tkyte@ORA920> select * from v$sql_plan_statistics;
no rows selected
ops$tkyte@ORA920> select count(*) from all_objects;
COUNT(*)
----------
30254
ops$tkyte@ORA920> select * from v$sql_plan_statistics;
no rows selected
ops$tkyte@ORA920> alter session set sql_trace=true;
Session altered.
ops$tkyte@ORA920> select count(*) from all_objects;
COUNT(*)
----------
30254
ops$tkyte@ORA920> select count(*) from v$sql_plan_statistics;
COUNT(*)
----------
20
ops$tkyte@ORA920> @connect /
ops$tkyte@ORA920> select count(*) from v$sql_plan_statistics;
COUNT(*)
----------
20
ops$tkyte@ORA920> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.3.0 -
Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
[tkyte@tkyte-pc Desktop]$ plus
SQL*Plus: Release 9.2.0.3.0 - Production on Thu Jun 12 11:00:48 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
ops$tkyte@ORA920> select count(*) from v$sql_plan_statistics;
COUNT(*)
----------
20
ops$tkyte@ORA920>
as shown there...
Reviews
4 stars does it mean sql_trace needs to be set? June 12, 2003 - 1pm US/Eastern
Bookmark | Bottom | Top
Reviewer: gs
Thanks tom for your quick response.
V$SQL_PLAN is populated automatically. But for the plan_statistics, do we need to set the
sql_trace?
Followup June 12, 2003 - 1pm US/Eastern:
yes, it is part of SQL_TRACE'ing. It can be quite expensive to gather at this level of detail.
5 stars ORA-03113: end-of-file on communication channel July 26, 2003 - 2pm US/Eastern
Bookmark | Bottom | Top
Reviewer: Sikandar Hayat from Pindi Bhattian - Pakistan
I don't know TOM why the following is happening,
SQL> connect scott
Enter password: *****
Connected.
SQL> select * from v$sql_plan;
select * from v$sql_plan
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
SQL>
The scott has DBA privs and I am running this query on Oracle 9.2 on Win2K box.
Followup July 26, 2003 - 4pm US/Eastern:
contact support (as for all 3113's) please.
5 stars reader February 18, 2005 - 8am US/Eastern
Bookmark | Bottom | Top
Reviewer: A reader
Do you have a script by any chance script to
convert data from v$sql_plan to output in a
EXPLAIN PLAN format
On a tkprof command explain="/ as sysdba", errors.
Is there a format to explain as SYS user without
using SYS password
Followup February 18, 2005 - 9am US/Eastern:
on a tkprof -- you should never use explain=, the plan would already be in the trace file? you
don't need to use explain with tkprof.
What you do need to do is close the cursors or exit the session, all of the STAT records with real
row counts (which is what you need -- the plan with row counts) will be in there.
If you have effective oracle by design, in there I have the technique to let you use
dbms_xplan.display right against the v$ stuff.
(2)Use DBMS_XPLAN and V$SQL_PLAN
If you edit the script utlxpls.sql in Oracle9i Release 2, you'll discover it is effectively
one-line long:
select plan_table_output
from table( dbms_xplan.display( 'plan_table',null,'serial'))
If you edit that same script in Oracle9i Release 1 or before, you'll find a huge query.
DBMS_XPLAN.DISPLAY is a better method for querying and displaying the plan output. It is a function
that simply returns a collection, which is a procedurally formatted EXPLAIN PLAN output, including
the supplemental information at the bottom of the report (new in Oracle9i Release 2). This is a
side effect of using the new DBMS_XPLAN package.
So, if you do not have access to the utlxpls.sql script, the simple query shown here will perform
the same function. In fact, the DBMS_XPLAN package is so good at adjusting its output based on the
inputs that you do not even need to supply the inputs as utlxpls.sql does. This simple line
suffices:
select * from table(dbms_xplan.display)
Using this feature coupled with the V$SQL_PLAN dynamic performance view, you can easily dump the
query plans for already executed statements, directly from the database.
In the previous section, I demonstrated how you can use an INSERT into the PLAN_TABLE and then run
utlxpls or utlxplp to see the plan. In Oracle9i Release 2, using DBMS_XPLAN and a view you can
create, it becomes even easier. If you use a schema that has been granted SELECT privileges on
SYS.V_$SQL_PLAN directly, you'll be able to create this view:
ops$tkyte@ORA920> create or replace view dynamic_plan_table
2 as
3 select
4 rawtohex(address) || '_' || child_number statement_id,
5 sysdate timestamp, operation, options, object_node,
6 object_owner, object_name, 0 object_instance,
7 optimizer, search_columns, id, parent_id, position,
8 cost, cardinality, bytes, other_tag, partition_start,
9 partition_stop, partition_id, other, distribution,
10 cpu_cost, io_cost, temp_space, access_predicates,
11 filter_predicates
12 from v$sql_plan;
View created.
Now, you can query any plan from the database with a single query:
ops$tkyte@ORA920> select plan_table_output
2 from TABLE( dbms_xplan.display
3 ( 'dynamic_plan_table',
4 (select rawtohex(address)||'_'||child_number x
5 from v$sql
6 where sql_text='select * from t t1 where object_id > 32000' ),
7 'serial' ) )
8 /
PLAN_TABLE_OUTPUT
------------------------------------------------------------------
| Id | Operation | Name|Rows| Bytes |Cst(%CPU)|
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | TABLE ACCESS BY INDEX ROWID| T |291 | 27936 | 25 (0)|
|* 2 | INDEX RANGE SCAN | T_PK|291 | | 2 (0)|
------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">32000)
13 rows selected.
The emphasized text in the code is a query that gets the STATEMENT_ID. In this query, you can use
whatever values you want to identify the exact query plan you wanted to review. The use of this
technique, querying the V$ table rather then inserting the contents of V$SQL_PLAN into a "real
table" is appropriate if you will be generating the explain plan for this query once. Access to V$
tables can be quite expensive latch wise on a busy system. So, if you plan on running the explain
plan for a given statement many times over - copying the information to a temporary working table
would be preferred.
4 stars V$SQL_PLAN table result March 1, 2005 - 11am US/Eastern
Bookmark | Bottom | Top
Reviewer: Bob from UK
Hi Tom
I have read you site quite considerably.
With regard to explain plans, autotrace, tkprof in that explain plans and autotrace might not
report the corrent plan of an executed statement however the raw output of a trace does.
If i had a raw trace file that reports use of a unique index
TABLE ACCESS FULL OBJ#(386375)
TABLE ACCESS BY INDEX ROWID OBJ#(386361)
INDEX UNIQUE SCAN OBJ#(386362) (object id 386362)
yet when querying V$SQL_PLAN I get a hash join
TABLE ACCESS GOAL: ANALYZED (FULL) OF 'JF'
TABLE ACCESS GOAL: ANALYZED (FULL) OF 'JM'
which should I trust ( My assumption is that again itwould be the raw trace)
Regards
Followup March 1, 2005 - 11am US/Eastern:
the trace shows the actual plan that was used when the trace file was generated.
v$sql_plan shows the plan being used right now.
They could be different due to "trace file was generated 10 minutes ago, stats were collected and
plan has changed"
or "there is more than one plan for this query in v$sql_plan and you are looking at the wrong one"
for example.
5 stars version-specific use of dynamic_plan_table November 9, 2005 - 11am US/Eastern
Bookmark | Bottom | Top
Reviewer: Igor Neyman, DBA from MI, USA
Tom,
Your method to query v$sql_plan directly (using dynamic_plan_table) works under Oracle9.
When I am trying to use it under Oracle10, I am getting an error.
i.e:
SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY
('dynamic_plan_table',
(SELECT RAWTOHEX(address) || '_' || child_number x
FROM v$sql
WHERE sql_text =
'select * from dual'), -- YOUR statement goes here
'serial'))
/
results in:
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
An uncaught error happened in fetching the records : ORA-00904: "PLAN_ID": invalid identifier
ORA-00904: "PLAN_ID": invalid identifier
2 rows selected.
So, the question is: do you have a modified (for Oracle10) version of 'dynamic_plan_table' view,
which includes "PLAN_ID", which is new column in PLAN_TABLE (Oracle10)?
Thank you, in advance
Igor
Followup November 11, 2005 - 10am US/Eastern:
add a plan_id constant to the view.
5 stars snapshot of the sql_plans May 10, 2006 - 3pm US/Eastern
Bookmark | Bottom | Top
Reviewer: Orakle_Lover from Texas
Hi Tom,
I would like to collect the changes happened to a sql plans after i changed some parameters
dynamically?
AND
Here is some idea:
I want to capture the sql plans once in a day and store in a table. For the next day i want to
compare the the sql plans with the stored table. If i find any difference found those statements
should page the DBA's?
Is it possible? If yes, please could you give me an idea i will work in that way.
Thanks
5 stars Still waiting for you advice May 24, 2006 - 2pm US/Eastern
Bookmark | Bottom | Top
Reviewer: A reader from tx
Followup May 25, 2006 - 1pm US/Eastern:
I don't read or see every one of these.
I don't followup every one of these.
I scan these reviews/followups quickly, as time permits...
v$sql_plan has a column plan_hash_value. You would likely be "good enough" if you saved off the
hash_value (of the sql text) and the plan_hash_value (of the plan) and just compared the
plan_hash_value's
5 stars which column in v$sql uniquely identfies the sql_text May 24, 2006 - 3pm US/Eastern
Bookmark | Bottom | Top
Reviewer: orakle10gdba@gmail.com from austin,TX
Hash_value or address, which one of this uniquely identifies the sql text in the v$sql view
Followup May 25, 2006 - 1pm US/Eastern:
take both together until you get to 10g with sql id's
4 stars Very Useful informatoin about plan August 10, 2006 - 4pm US/Eastern
Bookmark | Bottom | Top
Reviewer: Syed Aman Ali
Very Useful informatoin about plan
4 stars This has helped alot ... however .. August 16, 2006 - 10am US/Eastern
Bookmark | Bottom | Top
Reviewer: A reader
I'm encountering strange behaviour with this:
When I have a query and run it multiple times with slight variations (ie different alias on the
table to try to force a hard parse) ... It seems the query starts to have trouble pulling back just
1 plan ... I end up with multiple rows of data coming back ...
=========
Plan hash value: 758935370
----------------------------------------------------------------------------------------------------
---
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
----------------------------------------------------------------------------------------------------
---
| 0 | SELECT STATEMENT | | | | 2 (100)|
|
| 0 | SELECT STATEMENT | | | | 2 (100)|
|
| 0 | SELECT STATEMENT | | | | 2 (100)|
|
| 0 | SELECT STATEMENT | | | | 2 (100)|
|
| 0 | SELECT STATEMENT | | | | 2 (100)|
|
| 1 | SORT AGGREGATE | | 1 | 40 | |
|
| 1 | SORT AGGREGATE | | 1 | 40 | |
|
| 1 | SORT AGGREGATE | | 1 | 40 | |
|
| 1 | SORT AGGREGATE | | 1 | 40 | |
|
| 1 | SORT AGGREGATE | | 1 | 40 | |
|
| 2 | NESTED LOOPS | | 1 | 40 | 2 (0)|
00:00:01 |
| 2 | NESTED LOOPS | | 1 | 40 | 2 (0)|
00:00:01 |
| 2 | NESTED LOOPS | | 1 | 40 | 2 (0)|
00:00:01 |
| 2 | NESTED LOOPS | | 1 | 40 | 2 (0)|
00:00:01 |
| 2 | NESTED LOOPS | | 1 | 40 | 2 (0)|
00:00:01 |
==========================
when I query the dynamic_plan_table directly to see how it looks in there, it seems fine ... just 1
row .. not the multiples ...
I did alter the query a little bit to suit our needs:
(ie drive off the sql_id from v$session not the sql_text .. and make sure I only pull the latest
sql_id - I've hit multiples in the past ... )
select plan_table_output
from TABLE( dbms_xplan.display
( 'dynamic_plan_table',
(select x
from ( select rawtohex(address)||'_'||child_number x,
row_number() over ( order by LAST_LOAD_TIME desc ) rnum
from v$sql
where sql_id = '&lsql_id' )
where rnum = 1),
'serial' ) )
/
Is the sql_id what's causing the multiple rows again?
Thanks!
5 stars hash_value and address October 4, 2006 - 1am US/Eastern
Bookmark | Bottom | Top
Reviewer: A reader
Hello Tom,
can you please give examples where for 2 statements,
- the hash_value is same but address is different(not child_address)
- the address is same but the hash_value is different.
Lets assume this is 10g.
I tried to think of scenarios for the above, but in vain.
Appreciayte your help as always.
Followup October 4, 2006 - 7am US/Eastern:
#2 will never happen.
and why for #1? I'm not even really thinking it is something I want to think about (whether it
could or could not happen).
5 stars thanks October 4, 2006 - 1pm US/Eastern
Bookmark | Bottom | Top
Reviewer: A reader
Thanks Tom.
So, I will never see the 1st or the 2nd condition. So, in my queries I could either use hash_value
or address, and it is not necessary to use the combination (hash_value + address) as a key ?
Followup October 4, 2006 - 5pm US/Eastern:
address is likely sufficient, but hash + address is what you use in 9i and before.
5 stars 10g ? October 4, 2006 - 6pm US/Eastern
Bookmark | Bottom | Top
Reviewer: A reader
if address is sufficient, then why was sql_id introduced in 10g. If address is sufficient to trace
a statement, then what additional benefit/convenience does sql_id gives which address doesn't ?
As always, appreciate your comments.
Followup October 4, 2006 - 7pm US/Eastern:
sql_id survives database instance restarts and reloads.
ADDRESS does not, it is very transient.
so, for long term tracking......
5 stars hash_value and sql_id October 4, 2006 - 10pm US/Eastern
Bookmark | Bottom | Top
Reviewer: Roderick
Just to add a teensy bit of trivia, the hash_value for a given sql_text is typically constant but
only allows a range of values from around 0 to around 2^32. That seems like a wide range in the
sense that you'd need 2^32 + 1 distinct SQL statements before you are guaranteed to have two that
hash to the same value. However there is an interesting probability equation that shows that you
can get above 50% sooner than most people might think.
http://mathforum.org/dr.math/faq/faq.birthdayprob.html
I didn't feel like plugging in 2^32 into the calculation though. I also can't remember ever seeing
two SQL statements that hashed to the same hash_value (other than because of a bug that briefly
existed way back in the Oracle 7.2 timeframe).
Anyway, I imagine to be safe, SQL_ID in 10g allows for a much higher range of hash values.
5 stars good one October 4, 2006 - 11pm US/Eastern
Bookmark | Bottom | Top
Reviewer: A reader
Roderick intersting site/problem.
Tom, agreed that address could change over instance startup. But, the hash_value will not change
over instance restart, does it?.
Lets says, there are 2 similar statements run with different optimizer settings or different bind
variable types can have the same hash_value but their child_number will be different. So, in order
to identify a unique statement, I could either use sql_id or (hash_value + child_number). Am I
correct in my understanding ?
Followup October 5, 2006 - 8am US/Eastern:
the old hash was not a good hash, the new hash is a better hash, the sql_id is a better hash.
two entirely different sql statements could have the same hash, the hash is based on the text of
the sql statment. And we have an infinite universe of possible sql statements but only a finite
universe of hash values.
5 stars sorry October 5, 2006 - 12am US/Eastern
Bookmark | Bottom | Top
Reviewer: A reader
I take my last statement. I realised that I was wrong.
** INCORRECT STATEMENT ***
Lets says, there are 2 similar statements run with different optimizer settings
or different bind variable types can have the same hash_value but their
child_number will be different. So, in order to identify a unique statement, I could either use
sql_id or (hash_value + child_number). Am I correct in my
understanding ?
**************************
*** What I meant was *****
Lets says, there are 2 similar statements run with different optimizer settings
or different bind variable types can have the same hash_value but their
child_number/child_address will be different. So, in order to identify a unique statement, I
could either use (sql_id + child_number/child_address) or (hash_value +
child_number/child_address). Am I correct in my
understanding ?
**************************
Followup October 5, 2006 - 8am US/Eastern:
the child number would be very fragile again, as it changes as things get aged out and such
5 stars Hash values October 5, 2006 - 1am US/Eastern
Bookmark | Bottom | Top
Reviewer: Jonathan Lewis from UK
The best reason for using the hash_value (possibly combined with address) rather than the address
by itself is that the hash_value gives Oracle an efficiency way to identify the library cache hash
bucket where the statement ought to be.
Querying v$sql by hash_value allows Oracle to hit just the relevant library cache latch, then scan
the correct bucket. Querying v$sql by address requires Oracle to sweep the entire library cache,
hammering latches as it goes.
It is an interesting point that the hash_value and sql_id both allow high-precision access into
v$sql in 10g - this suggests that either the strategy above has changed, or the algorithm for the
sql_id has been designed as a superset of the hash_value that always collapses to identify the same
hash bucket as the hash_value.
2 stars Identifying current execution plan for a given active session October 14, 2006 - 11am US/Eastern
Bookmark | Bottom | Top
Reviewer: Marc Beaudoin from Montréal, Québec, Canada
I was looking for a way to identify current execution plans for active sessions.
I am wondering how to choose the right CHILD_NUMBER value. Since V$Session has no CHILD_NUMBER
column, it is not trivial to join V$Session and V$SQL_Plan.
Followup October 14, 2006 - 7pm US/Eastern:
the only real way I know to do it - is to sql trace it.
5 stars session child number October 15, 2006 - 1am US/Eastern
Bookmark | Bottom | Top
Reviewer: Roderick
Maybe another reason to upgrade to 10gR2 as v$session
there includes a sql_child_number column.
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2088.htm#sthref3985
Followup October 15, 2006 - 5am US/Eastern:
added to list of "what I learned new today" ...
I have to remember to keep peeking at the v$ tables before answering - my answers are usually right
- but need a "timestamp" :)
3 stars Join between V$SQL_Plan and V$Session October 16, 2006 - 9am US/Eastern
Bookmark | Bottom | Top
Reviewer: Marc Beaudoin from Montréal, Québec, Canada
Thank you for your answers.
Unfortunately, I am stuck with 9iR2.
There must be a way, since Enterprise Manager is able to extract execution plans for sessions.
Since I am programming a script to gather execution plans for active sessions, Enterprise Manager
is not what I am looking for. Any ideas?
Thank you,
Marc.
5 stars Joining V$Session and V$SQL_Plan with Oracle 9i October 19, 2006 - 6pm US/Eastern
Bookmark | Bottom | Top
Reviewer: Marc Beaudoin from Montréal, Québec, Canada
I found something on MetaLink document #626031.995.
The join is not with CHILD_NUMBER but with V$SQL's CHILD_ADDRESS. Also, one has to use x$kgllk, a
dynamic view underlying V$Open_Cursor.
SELECT GV$Session.Inst_Id,
GV$Session.SId,
GV$Session.Serial#,
To_Char(GV$Session.SId),
To_Char(GV$Session.Serial#),
GV$Session.UserName,
GV$Session.Machine,
GV$Session.Process,
GV$Session.SQL_Address,
GV$Session.SQL_Hash_Value,
GV$SQL.Child_Number,
To_Char(GV$Session.SQL_Hash_Value) || '.' || To_Char(GV$SQL.Child_Number)
FROM GV$Session,
X$KGLLK,
GV$SQL
WHERE GV$Session.Status
= 'ACTIVE'
AND GV$Session.Type
= 'USER'
AND GV$Session.AudSId
> 0
AND X$KGLLK.Inst_Id
= GV$Session.Inst_Id
AND X$KGLLK.KGLLKSNM
= GV$Session.SId
AND X$KGLLK.KGLHDPAR
= GV$Session.SQL_Address
AND X$KGLLK.KGLNAHSH
= GV$Session.SQL_Hash_Value
AND X$KGLLK.KGLHDNSP
= 0
AND X$KGLLK.KGLHDPAR
!= X$KGLLK.KGLLKHDL
AND GV$SQL.Address
= X$KGLLK.KGLHDPAR
AND GV$SQL.Hash_Value
= X$KGLLK.KGLNAHSH
AND GV$SQL.Child_Address
= X$KGLLK.KGLLKHDL;
4 stars New version of dynamic_plan_table June 20, 2007 - 3am US/Eastern
Bookmark | Bottom | Top
Reviewer: Serge Shmygelsky from Kiev, Ukraine
If I use the view show by Tom in Oracle 10G, it says that it is an old version. So I've created a new version of the view for 10G:
CREATE OR REPLACE
VIEW dynamic_plan_table
AS
SELECT
rawtohex(address) || '_' || child_number statement_id,
plan_hash_value AS plan_id,
sysdate timestamp,
operation,
options,
object_node,
object_owner,
object_name,
object_type,
object_alias,
0 AS object_instance,
optimizer,
search_columns,
id,
parent_id,
position,
cost,
cardinality,
bytes,
other_tag,
partition_start,
partition_stop,
partition_id,
other,
distribution,
cpu_cost,
io_cost,
temp_space,
access_predicates,
filter_predicates,
time,
qblock_name,
projection,
other_xml,
depth,
remarks
FROM v$sql_plan
/
It works without any problems.
Followup June 20, 2007 - 10am US/Eastern:
in 10g, you can just use dbms_xplan without this - passing in either the sql id OR by calling display_cursor to display the most recently executed query in your session.
4 stars June 21, 2007 - 4am US/Eastern
Bookmark | Bottom | Top
Reviewer: Serge Shmygelsky from Kiev, Ukraine
Thanks, I checked the documentation and it really works for DISPLAY_CURSOR. But I cannot understand what should be transferred as STATEMENT_ID into DISPLAY function. I tried SQL_ID but is didn't work.
5 stars v$sql_plan_statistics_all December 12, 2008 - 6pm US/Eastern
Bookmark | Bottom | Top
Reviewer: A reader
I'm puzzled by how v$sql_plan_statistics_all stores data. Please refer to the example below.
* Why are there two records in v$sql_plan_statistics_all even though the statement has only been executed once. Based on the timestamp, it appears that they were executed around the same time, but the elapsed time is very different.
* What's child_number in v$sql_plan_statistics_all?
* Why does v$sql_plan_statistics_all.timestamp reported an earlier time than the "current_time"? The SQL was obviously executed after executing "SELECT SYSDATE FROM dual".
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
SQL>
SQL> SELECT TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') current_time
2 FROM dual;
CURRENT_TIME
-------------------
2008-12-12 15:31:40
SQL>
SQL> SELECT /*+ gather_plan_statistics */ COUNT(*)
2 FROM x;
COUNT(*)
----------
47900
SQL>
SQL> SELECT plan_table_output
2 FROM TABLE(dbms_xplan.display_cursor(NULL, NULL, 'iostats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
------
SQL_ID 60g08tgjy01cx, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ COUNT(*) FROM x
Plan hash value: 989401810
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.02 | 664 |
| 2 | TABLE ACCESS FULL| X | 1 | 44686 | 47900 |00:00:00.05 | 664 |
-------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
17 rows selected.
SQL>
SQL>
SQL> SELECT sql_id, child_number, TO_CHAR(timestamp, 'yyyy-mm-dd hh24:mi:ss') timestamp,
last_elapsed_time
2 FROM v$sql_plan_statistics_all
3 WHERE sql_id = '60g08tgjy01cx';
SQL_ID CHILD_NUMBER TIMESTAMP LAST_ELAPSED_TIME
------------- ------------ ------------------- -----------------
60g08tgjy01cx 0 2008-12-12 15:31:39 16916
60g08tgjy01cx 0 2008-12-12 15:31:39 47929
Followup December 13, 2008 - 7am US/Eastern:
v$sql_plan_statistics_all is like a plan_table
you had two steps in your plan - full scan PLUS sort aggregate.
Hence, two rows in v$sql_plan_statistics_all, if you query it correctly, you would see output similar to a tkprof "row source operation" report
Write a Review
All information and materials provided here are provided "as-is"; Oracle disclaims all express and implied warranties, including, the implied warranties of merchantability or fitness for a particular use. Oracle shall not be liable for any damages, including, direct, indirect, incidental, special or consequential damages for loss of profits, revenue, data or data use, incurred by you or any third party in connection with the use of this information or these materials.
About Oracle | Legal Notices and Terms of Use | Privacy Statement
No comments:
Post a Comment