Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, gs.

Asked: June 11, 2003 - 9:06 pm UTC

Last updated: August 08, 2019 - 2:28 am UTC

Version: 9.2.0.3

Viewed 10K+ times! This question is

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 Tom 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...



Rating

  (56 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

does it mean sql_trace needs to be set?

gs, June 12, 2003 - 1:19 pm UTC

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?



Tom Kyte
June 12, 2003 - 1:28 pm UTC

yes, it is part of SQL_TRACE'ing. It can be quite expensive to gather at this level of detail.



ORA-03113: end-of-file on communication channel

Sikandar Hayat, July 26, 2003 - 2:40 pm UTC

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.
 

Tom Kyte
July 26, 2003 - 4:17 pm UTC

contact support (as for all 3113's) please.

reader

A reader, February 18, 2005 - 8:01 am UTC

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

Tom Kyte
February 18, 2005 - 9:22 am UTC

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.

<quote from book>
(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.

</quote> 

V$SQL_PLAN table result

Bob, March 01, 2005 - 11:10 am UTC

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

Tom Kyte
March 01, 2005 - 11:28 am UTC

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.

version-specific use of dynamic_plan_table

Igor Neyman, DBA, November 09, 2005 - 11:34 am UTC

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

Tom Kyte
November 11, 2005 - 10:13 am UTC

add a plan_id constant to the view.

snapshot of the sql_plans

Orakle_Lover, May 10, 2006 - 3:00 pm UTC

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

Still waiting for you advice

A reader, May 24, 2006 - 2:14 pm UTC


Tom Kyte
May 25, 2006 - 1:11 pm UTC

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



which column in v$sql uniquely identfies the sql_text

orakle10gdba@gmail.com, May 24, 2006 - 3:10 pm UTC

Hash_value or address, which one of this uniquely identifies the sql text in the v$sql view

Tom Kyte
May 25, 2006 - 1:14 pm UTC

take both together until you get to 10g with sql id's

Very Useful informatoin about plan

Syed Aman Ali, August 10, 2006 - 4:32 pm UTC

Very Useful informatoin about plan

This has helped alot ... however ..

A reader, August 16, 2006 - 10:36 am UTC

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!


hash_value and address

A reader, October 04, 2006 - 1:07 am UTC

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.


Tom Kyte
October 04, 2006 - 7:04 am UTC

#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).

thanks

A reader, October 04, 2006 - 1:10 pm UTC

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 ?

Tom Kyte
October 04, 2006 - 5:18 pm UTC

address is likely sufficient, but hash + address is what you use in 9i and before.

10g ?

A reader, October 04, 2006 - 6:16 pm UTC

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.


Tom Kyte
October 04, 2006 - 7:38 pm UTC

sql_id survives database instance restarts and reloads.

ADDRESS does not, it is very transient.

so, for long term tracking......

hash_value and sql_id

Roderick, October 04, 2006 - 10:57 pm UTC

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.

</code> http://mathforum.org/dr.math/faq/faq.birthdayprob.html <code>

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.

good one

A reader, October 04, 2006 - 11:39 pm UTC

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 ?


Tom Kyte
October 05, 2006 - 8:09 am UTC

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.

sorry

A reader, October 05, 2006 - 12:08 am UTC

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 ?
**************************

Tom Kyte
October 05, 2006 - 8:09 am UTC

the child number would be very fragile again, as it changes as things get aged out and such

Hash values

Jonathan Lewis, October 05, 2006 - 1:25 am UTC

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.

Identifying current execution plan for a given active session

Marc Beaudoin, October 14, 2006 - 11:07 am UTC

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.

Tom Kyte
October 14, 2006 - 7:35 pm UTC

the only real way I know to do it - is to sql trace it.

session child number

Roderick, October 15, 2006 - 1:16 am UTC

Maybe another reason to upgrade to 10gR2 as v$session
there includes a sql_child_number column.
</code> http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2088.htm#sthref3985 <code>


Tom Kyte
October 15, 2006 - 5:27 am UTC

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" :)

Join between V$SQL_Plan and V$Session

Marc Beaudoin, October 16, 2006 - 9:59 am UTC

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.

Joining V$Session and V$SQL_Plan with Oracle 9i

Marc Beaudoin, October 19, 2006 - 6:37 pm UTC

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;


New version of dynamic_plan_table

Serge Shmygelsky, June 20, 2007 - 3:07 am UTC

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.
Tom Kyte
June 20, 2007 - 10:37 am UTC

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.

Serge Shmygelsky, June 21, 2007 - 4:49 am UTC

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.

v$sql_plan_statistics_all

A reader, December 12, 2008 - 6:43 pm UTC

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

Tom Kyte
December 13, 2008 - 7:25 am UTC

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


v$sql_plan_statistics_all

A reader, March 09, 2009 - 4:24 pm UTC

I execxuted a SQL with the /*+ gather_plan_statistics */ hint and used the following SQL to display the exeuction plan. For security reasons, I've masked the table names in the execution plans.

SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL, NULL, 'iostats last'));


---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS BY INDEX ROWID        | TABLEXXXXXXXXXXX       |     37 |      1 |     10 |00:00:00.76 |     154 |     51 |
|*  2 |   INDEX RANGE SCAN                  | TABLEXXXXXXXXXXX_FK1   |     37 |      2 |     76 |00:00:00.35 |     111 |     24 |
|*  3 |  VIEW                               |                        |      1 |     25 |     47 |00:00:02.04 |     426 |    148 |
|*  4 |   COUNT STOPKEY                     |                        |      1 |        |     47 |00:00:02.04 |     426 |    148 |
|   5 |    VIEW                             |                        |      1 |     25 |     47 |00:00:02.04 |     426 |    148 |
|*  6 |     SORT ORDER BY STOPKEY           |                        |      1 |     25 |     47 |00:00:02.04 |     426 |    148 |
|*  7 |      HASH JOIN                      |                        |      1 |     25 |     47 |00:00:01.29 |     272 |     97 |
|*  8 |       HASH JOIN                     |                        |      1 |     25 |     47 |00:00:01.28 |     269 |     96 |
|   9 |        NESTED LOOPS                 |                        |      1 |     25 |     47 |00:00:03.90 |     266 |     96 |
|  10 |         NESTED LOOPS                |                        |      1 |     22 |     37 |00:00:01.65 |     153 |     54 |
|  11 |          TABLE ACCESS BY INDEX ROWID| TABLEYYYYYYYYYYYY      |      1 |     22 |     37 |00:00:00.01 |      40 |      1 |
|* 12 |           INDEX RANGE SCAN          | TABLEYYYYYYYYYYYY_FB4A |      1 |     22 |     37 |00:00:00.01 |       3 |      1 |
|* 13 |          TABLE ACCESS BY INDEX ROWID| TABLEZZ                |     37 |      1 |     37 |00:00:00.72 |     113 |     53 |
|* 14 |           INDEX RANGE SCAN          | TABLEZZ_FK1            |     37 |      1 |     37 |00:00:00.55 |      76 |     41 |
|* 15 |         TABLE ACCESS BY INDEX ROWID | TABLEAAA               |     37 |      1 |     47 |00:00:00.54 |     113 |     42 |
|* 16 |          INDEX RANGE SCAN           | TABLEAAA_N2            |     37 |      1 |     47 |00:00:00.54 |      76 |     42 |
|  17 |        TABLE ACCESS FULL            | TABLECCCCCCCCC         |      1 |      6 |      6 |00:00:00.01 |       3 |      0 |
|  18 |       TABLE ACCESS FULL             | TABLED                 |      1 |     44 |     44 |00:00:00.01 |       3 |      1 |
---------------------------------------------------------------------------------------------------------------------------------


From the output above,

* What's the logical IO's consumed by the SQL? Is it 426 (id = 3) or 580 (buffers from id 1 + buffers from id 3)?

Since the format of the execution plan table may not come out nicely, I'd like to note that step 1 and step 3 both have parent_id of 0; i.e. they're both the top level operations.

* What about the elapsed time? Is it the sum of "A-Time" of all steps?

* Is "A-Time" CPU time or the total elapsed time? I've seen a lot of cases where A-Time is much greater than the time reported by setting timing on in SQL*Plus? How it that possible?

Tom Kyte
March 09, 2009 - 4:45 pm UTC

o looks like a top level something is missing - it would be the output of the view plus the accesses to the table to pick it up, add them up

o same as above, looks like a formatting issue for this particular plan

o as far as I know, it is actual time, elapsed time. Do you have an example of one of these cases - with tracing on? and what is "much greater", what does that mean?

/*+ gather_plan_statistics */

A reader, March 09, 2009 - 5:41 pm UTC

Here's a test case. It seems the elapsed time from SQL*Plus matches the A-Time of the operation id 1. However, operation id 3 has a much greater A-Time.

SQL> set timing on
SQL> 
SQL> SELECT /*+ gather_plan_statistics */ 
  2         NVL(SUM(seven_day), 0) seven_day,
  3         NVL(SUM(thirty_day), 0) thirty_day,
  4         NVL(SUM(total), 0) total
  5  FROM (
  6     SELECT CASE WHEN age < 7 AND age >= 0  THEN 1 ELSE 0 END seven_day,
  7            CASE WHEN age < 30 AND age >= 0 THEN 1 ELSE 0 END thirty_day,
  8            1 total
  9     FROM (
 10        SELECT (TRUNC(SYSTIMESTAMP) - TRUNC(m.messagedate)) age
 11        FROM   This_Is_a_MV m
 12        WHERE  EXISTS (
 13                  SELECT /*+ dynamic_sampling(t 4) */ 1
 14                  FROM   This_is_a_table t
 15                  WHERE  m.MessageID = t.MessageID AND
 16                         LOWER(t.Name) IN ('login.txt')
 17        )
 18     )
 19  );

 SEVEN_DAY THIRTY_DAY      TOTAL
---------- ---------- ----------
         0          0      51037

Elapsed: 00:00:01.85
SQL> 
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL, NULL, 'iostats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
SQL_ID  9ck18b4nhu06z, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */        NVL(SUM(seven_day), 0) seven_day,        NVL(SUM(thirty_day), 0)
thirty_day,        NVL(SUM(total), 0) total FROM (    SELECT CASE WHEN age < 7 AND age >= 0  THEN 1 ELSE 0 END
seven_day,           CASE WHEN age < 30 AND age >= 0 THEN 1 ELSE 0 END thirty_day,           1 total    FROM (
   SELECT (TRUNC(SYSTIMESTAMP) - TRUNC(m.messagedate)) age       FROM   This_Is_a_MV m       WHERE  EXISTS (
           SELECT /*+ dynamic_sampling(t 4) */ 1                 FROM   This_is_a_table t                 WHERE
m.MessageID = t.MessageID AND                        LOWER(t.Name) IN ('login.txt')       )    ) )

Plan hash value: 219935036

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE                 |                     |      1 |      1 |      1 |00:00:01.86 |   83722 |   2731 |
|   2 |   MAT_VIEW ACCESS BY INDEX ROWID| THIS_IS_A_MV        |      1 |      1 |  51037 |00:00:01.85 |   83722 |   2731 |
|   3 |    NESTED LOOPS                 |                     |      1 |  23807 |  76742 |00:02:45.68 |   60750 |   2731 |
|   4 |     SORT UNIQUE                 |                     |      1 |  23673 |  25704 |00:00:01.29 |    9226 |   2731 |
|   5 |      TABLE ACCESS BY INDEX ROWID| THIS_IS_A_TABLE     |      1 |  23673 |  25704 |00:00:01.18 |    9226 |   2731 |
|*  6 |       INDEX RANGE SCAN          | THIS_IS_A_TABLE_IDX |      1 |   7887 |  25704 |00:00:00.03 |      78 |      0 |
|*  7 |     INDEX RANGE SCAN            | THIS_IS_A_MV_N1     |  25704 |      1 |  51037 |00:00:00.18 |   51524 |      0 |
--------------------------------------------------------------------------------------------------------------------------

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

   6 - access("T"."SYS_NC00010$"='login.txt')
   7 - access("M"."MESSAGEID"="T"."MESSAGEID")

Note
-----
   - dynamic sampling used for this statement


33 rows selected.

Elapsed: 00:00:00.04

-- -------------------------------------------------------------------------------
-- With Tracing on
-- -------------------------------------------------------------------------------
=====================
PARSING IN CURSOR #30 len=622 dep=0 uid=47 oct=3 lid=47 tim=1838144278 hv=3643507410 ad='4722fb98'
SELECT NVL(SUM(seven_day), 0) seven_day,
       NVL(SUM(thirty_day), 0) thirty_day,
       NVL(SUM(total), 0) total
FROM (
   SELECT CASE WHEN age < 7 AND age >= 0  THEN 1 ELSE 0 END seven_day,
          CASE WHEN age < 30 AND age >= 0 THEN 1 ELSE 0 END thirty_day,
          1 total
   FROM (
      SELECT (TRUNC(SYSTIMESTAMP) - TRUNC(m.messagedate)) age
      FROM   This_Is_a_MV m
      WHERE  EXISTS (
                SELECT /*+ dynamic_sampling(t 4) */ 1
                FROM   This_is_a_table t
                WHERE  m.MessageID = t.MessageID AND
                       LOWER(t.Name) IN ('login.txt')
      )
   )
)
END OF STMT
PARSE #30:c=31250,e=27347,p=0,cr=245,cu=0,mis=1,r=0,dep=0,og=1,tim=1838144275
EXEC #30:c=0,e=56,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1838145689
WAIT #30: nam='SQL*Net message to client' ela= 7 driver id=1111838976 #bytes=1 p3=0 obj#=8862 tim=1838145774
FETCH #30:c=625000,e=619870,p=0,cr=83722,cu=0,mis=0,r=1,dep=0,og=1,tim=1838765720
WAIT #30: nam='SQL*Net message from client' ela= 264 driver id=1111838976 #bytes=1 p3=0 obj#=8862 tim=1838766439
FETCH #30:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1838766620
WAIT #30: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=8862 tim=1838766738
WAIT #30: nam='SQL*Net message from client' ela= 259474 driver id=1111838976 #bytes=1 p3=0 obj#=8862 tim=1839026288
STAT #30 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=83722 pr=0 pw=0 time=619877 us)'
STAT #30 id=2 cnt=51037 pid=1 pos=1 obj=101990 op='MAT_VIEW ACCESS BY INDEX ROWID THIS_IS_A_MV (cr=83722 pr=0 pw=0 time=610773 us)'
STAT #30 id=3 cnt=76742 pid=2 pos=1 obj=0 op='NESTED LOOPS  (cr=60750 pr=0 pw=0 time=6523535 us)'
STAT #30 id=4 cnt=25704 pid=3 pos=1 obj=0 op='SORT UNIQUE (cr=9226 pr=0 pw=0 time=49342 us)'
STAT #30 id=5 cnt=25704 pid=4 pos=1 obj=84173 op='TABLE ACCESS BY INDEX ROWID THIS_IS_A_TABLE (cr=9226 pr=0 pw=0 time=51431 us)'
STAT #30 id=6 cnt=25704 pid=5 pos=1 obj=102027 op='INDEX RANGE SCAN THIS_IS_A_TABLE_IDX (cr=78 pr=0 pw=0 time=19 us)'
STAT #30 id=7 cnt=51037 pid=3 pos=2 obj=101998 op='INDEX RANGE SCAN THIS_IS_A_MV_N1 (cr=51524 pr=0 pw=0 time=180487 us)'
=====================

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.62       0.61          0      83722          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.62       0.62          0      83723          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 47  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=83722 pr=0 pw=0 time=619877 us)
  51037   MAT_VIEW ACCESS BY INDEX ROWID THIS_IS_A_MV (cr=83722 pr=0 pw=0 time=610773 us)
  76742    NESTED LOOPS  (cr=60750 pr=0 pw=0 time=6523535 us)
  25704     SORT UNIQUE (cr=9226 pr=0 pw=0 time=49342 us)
  25704      TABLE ACCESS BY INDEX ROWID THIS_IS_A_TABLE (cr=9226 pr=0 pw=0 time=51431 us)
  25704       INDEX RANGE SCAN THIS_IS_A_TABLE_IDX (cr=78 pr=0 pw=0 time=19 us)(object id 102027)
  51037     INDEX RANGE SCAN THIS_IS_A_MV_N1 (cr=51524 pr=0 pw=0 time=180487 us)(object id 101998)

<code>
</code>

What is parent and child?

Naresh Bhandare, March 11, 2009 - 11:03 am UTC

Hello Tom,


From the documentation for V$sql_plan

"ADDRESS Address of the handle to the parent for this cursor

HASH_VALUE Hash value of the parent statement in the library cache. The two columns ADDRESS and HASH_VALUE can be used to join with V$SQLAREA to add the cursor-specific information.

CHILD_NUMBER NUMBER Number of the child cursor that uses this execution plan. "

Can you kindly explain what is parent and child in this?

Thanks,
Naresh

Tom Kyte
March 12, 2009 - 7:43 am UTC

parent is just the first copy, any other copy that differs from the first will be a 'child'.

The parent is just the first one.

ops$tkyte%ORA10GR2> alter session set optimizer_mode=all_rows;

Session altered.

ops$tkyte%ORA10GR2> select /* look for me! */ * from dual;

D
-
X

ops$tkyte%ORA10GR2> alter session set optimizer_mode=first_rows;

Session altered.

ops$tkyte%ORA10GR2> select /* look for me! */ * from dual;

D
-
X

ops$tkyte%ORA10GR2> select sql_text, child_number from v$sql where sql_text like 'select /* look for me! */%';

SQL_TEXT
-------------------------------------------------------------------------------
CHILD_NUMBER
------------
select /* look for me! */ * from dual
           0

select /* look for me! */ * from dual
           1




the first one optimized was the 'parent', the second one the 'child', but it really isn't a classic parent/child relationship - just that one was parsed before the other.

How to see multiple explain plan for the same SQL

Reene, March 17, 2009 - 8:07 am UTC

Hi Tom

sometimes we see that plan for a query changed due to some reason or other. is there a way to see all the plans of a query .can you show it with an example.
how to know that - the plan for a SQL has changed recently.

Thanks
Tom Kyte
March 17, 2009 - 10:55 am UTC

ops$tkyte%ORA10GR2> drop table t;

Table dropped.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t
  2  as
  3  select 99 id, all_objects.*
  4    from all_objects
  5  /

Table created.

ops$tkyte%ORA10GR2> update t set id = 1 where rownum = 1;

1 row updated.

ops$tkyte%ORA10GR2> create index t_idx on t(id);

Index created.

ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T', method_opt=>'for columns id size 254', estimate_percent=> 100 );

PL/SQL procedure successfully completed.

<b>with that table, id = 99 will tend to full scan, id = 1 will tend to index range scan.  </b>

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> variable n number
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter session set optimizer_mode = all_rows;

Session altered.

ops$tkyte%ORA10GR2> exec :n := 99;

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select count(subobject_name) from t where id = :n;

COUNT(SUBOBJECT_NAME)
---------------------
                  425

ops$tkyte%ORA10GR2> alter session set optimizer_mode = first_rows;

Session altered.

ops$tkyte%ORA10GR2> exec :n := 1;

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select count(subobject_name) from t where id = :n;

COUNT(SUBOBJECT_NAME)
---------------------
                    0

<b>
By changing the optimizer mode, we forced two hard parses.

By changing the bind variable, we'll have changed the plan.
</b>

ops$tkyte%ORA10GR2> column sql_id new_value s_id
ops$tkyte%ORA10GR2> select child_number, sql_id from v$sql where sql_text = 'select count(subobject_name) from t where id = :n';

CHILD_NUMBER SQL_ID
------------ -------------
           0 98mx7jbn7jpm8
           1 98mx7jbn7jpm8

<b>You can see there are two versions - v$sql_plan has the plans, the easiest way to see them I think is</b>


ops$tkyte%ORA10GR2> select * from table(dbms_xplan.display_cursor( '&s_id', 0 ));
old   1: select * from table(dbms_xplan.display_cursor( '&s_id', 0 ))
new   1: select * from table(dbms_xplan.display_cursor( '98mx7jbn7jpm8', 0 ))

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
SQL_ID  98mx7jbn7jpm8, child number 0
-------------------------------------
select count(subobject_name) from t where id = :n

Plan hash value: 2966233522

------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   231 (100)|
|   1 |  SORT AGGREGATE    |      |     1 |    20 |            |
|*  2 |   TABLE ACCESS FULL| T    | 50057 |   977K|   231   (3)| 00:00:0
------------------------------------------------------------------------

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

   2 - filter("ID"=:N)


19 rows selected.

ops$tkyte%ORA10GR2> select * from table(dbms_xplan.display_cursor( '&s_id', 1 ));
old   1: select * from table(dbms_xplan.display_cursor( '&s_id', 1 ))
new   1: select * from table(dbms_xplan.display_cursor( '98mx7jbn7jpm8', 1 ))

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
SQL_ID  98mx7jbn7jpm8, child number 1
-------------------------------------
select count(subobject_name) from t where id = :n

Plan hash value: 1789076273

------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CP
------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |       |       |     2 (10
|   1 |  SORT AGGREGATE              |       |     1 |    20 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T     |     1 |    20 |     2   (
|*  3 |    INDEX RANGE SCAN          | T_IDX |     1 |       |     1   (
------------------------------------------------------------------------

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

   3 - access("ID"=:N)


20 rows selected.

A reader, March 17, 2009 - 4:00 pm UTC

Hi Tom, any chance you can take a look at the question I posted earlier in this thread? Thank you.

"/*+ gather_plan_statistics */ March 9, 2009 - 5pm US/Eastern"


Tom Kyte
March 18, 2009 - 7:53 am UTC

I agree, it looks wrong.

wonerful , extremely helpful

Reene, March 18, 2009 - 2:22 am UTC

Tom

thanks alot for explaining and showing how to get the multiple explain plan for the same SQL.

for 9i , you have already shown it one of your threads.

Regards

A reader, May 07, 2009 - 10:44 am UTC

Why would a SQL has various plan hash value , if it is executed from the same user id .
The columns referenced in the where clause are indexed .
One of the column in the select list is of varchar2(4000).

Select distinct plan_hash_value , dhsp.object_owner
from DBA_HIST_SQL_PLAN dhsp where sql_id = '53q29yzqngxsg'
and object_owner = 'OWNER_NEW'

PLAN_HASH_VALUE OBJECT_OWNER
--------------- ------------------------------
4051319526 OWNER_NEW
3769542639 OWNER_NEW
3153237866 OWNER_NEW
596110799 OWNER_NEW
1386469711 OWNER_NEW
707819092 OWNER_NEW



Note :

The cursor_sharing is force in this instance
Tom Kyte
May 11, 2009 - 1:29 pm UTC

cursor_sharing = force...

you have bigger problems than wondering about plan hash values if you ask me. You must have a security hole the size of Alaska (that is a big state, if you broke it into two state - Texas would become our third largest state) in your developed or purchased applications. A big, seriously bad, huge security hole.

SQL INJECTION


probably, you are seeing effects of bind peeking...


http://asktom.oracle.com/Misc/tuning-with-sqltracetrue.html
http://asktom.oracle.com/Misc/sqltracetrue-part-two.html

that talks about bind peeking if you don't know what it is - here is a small example.

You'll see two phenomena here - first is bind peeking

id1 is very skewed, id2 is very skewed, id1 is indexed, id2 is indexed - if we use id1=1 - we should use the index on id1. If we use id2=2 we should use the index in id2. else we should full scan. If we peek at the binds during a hard parse - that is what will happen.

data is a varying length field - if we bind a small string to it (0..32) it will use one cursor space. If we bind a medium string (33..128) it will use another. If we bind a large string (more than 128) it'll use a third. So, if we parse from small to large - we'll have three hard parses and three child cursors and three possible plans in this trivial example.


ops$tkyte%ORA10GR2> create table t
  2  as
  3  select case when rownum = 1 then 1 else 99 end id1,
  4         case when rownum = 2 then 2 else 99 end id2,
  5             cast( 'x' as varchar2(4000) ) data,
  6             a.*
  7    from all_objects a
  8  /

Table created.

ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T', estimate_percent=>100, method_opt=>'for columns id1, id2' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> create index t_id1_idx on t(id1);

Index created.

ops$tkyte%ORA10GR2> create index t_id2_idx on t(id2);

Index created.

ops$tkyte%ORA10GR2> alter session set cursor_sharing=force;

Session altered.

ops$tkyte%ORA10GR2> alter system flush shared_pool;

System altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> variable x varchar2(1);
ops$tkyte%ORA10GR2> select count(subobject_name) from t where id1 = 1 and id2 = 99 and data = :x;

COUNT(SUBOBJECT_NAME)
---------------------
                    0

ops$tkyte%ORA10GR2> variable x varchar2(100);
ops$tkyte%ORA10GR2> select count(subobject_name) from t where id1 = 99 and id2 = 99 and data = :x;

COUNT(SUBOBJECT_NAME)
---------------------
                    0

ops$tkyte%ORA10GR2> variable x varchar2(1000);
ops$tkyte%ORA10GR2> select count(subobject_name) from t where id1 = 99 and id2 = 2 and data = :x;

COUNT(SUBOBJECT_NAME)
---------------------
                    0

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> column sql_id new_val S
ops$tkyte%ORA10GR2> select sql_id from v$sql where sql_text like 'select count(subobject_name) from t where id1 = % and id2 = % and data = :x';

SQL_ID
-------------
9yzqsx1jqs744
9yzqsx1jqs744
9yzqsx1jqs744

ops$tkyte%ORA10GR2> select * from TABLE(dbms_xplan.display_cursor( '&S', 0, 'typical +peeked_binds' ));
old   1: select * from TABLE(dbms_xplan.display_cursor( '&S', 0, 'typical +peeked_binds' ))
new   1: select * from TABLE(dbms_xplan.display_cursor( '9yzqsx1jqs744', 0, 'typical +peeked_binds' ))

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  9yzqsx1jqs744, child number 0
-------------------------------------
select count(subobject_name) from t where id1 = :"SYS_B_0" and id2 = :"SYS_B_1"
and data = :x

Plan hash value: 151157257

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |           |     1 |   101 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T         |     1 |   101 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T_ID1_IDX |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :SYS_B_0 (NUMBER): 1
   2 - :SYS_B_1 (NUMBER): 99

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

   2 - filter(("DATA"=:X AND "ID2"=:SYS_B_1))
   3 - access("ID1"=:SYS_B_0)


28 rows selected.

ops$tkyte%ORA10GR2> select * from TABLE(dbms_xplan.display_cursor( '&S', 1, 'typical +peeked_binds' ));
old   1: select * from TABLE(dbms_xplan.display_cursor( '&S', 1, 'typical +peeked_binds' ))
new   1: select * from TABLE(dbms_xplan.display_cursor( '9yzqsx1jqs744', 1, 'typical +peeked_binds' ))

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  9yzqsx1jqs744, child number 1
-------------------------------------
select count(subobject_name) from t where id1 = :"SYS_B_0" and id2 =
:"SYS_B_1" and data = :x

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |    56 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |   101 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |   499 | 50399 |    56   (4)| 00:00:01 |
---------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :SYS_B_0 (NUMBER): 99
   2 - :SYS_B_1 (NUMBER): 99

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

   2 - filter(("DATA"=:X AND "ID1"=:SYS_B_0 AND "ID2"=:SYS_B_1))


26 rows selected.

ops$tkyte%ORA10GR2> select * from TABLE(dbms_xplan.display_cursor( '&S', 2, 'typical +peeked_binds' ));
old   1: select * from TABLE(dbms_xplan.display_cursor( '&S', 2, 'typical +peeked_binds' ))
new   1: select * from TABLE(dbms_xplan.display_cursor( '9yzqsx1jqs744', 2, 'typical +peeked_binds' ))

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  9yzqsx1jqs744, child number 2
-------------------------------------
select count(subobject_name) from t where id1 = :"SYS_B_0" and id2 = :"SYS_B_1"
and data = :x

Plan hash value: 1797356622

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE              |           |     1 |   101 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T         |     1 |   101 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T_ID2_IDX |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------

   1 - :SYS_B_0 (NUMBER): 99
   2 - :SYS_B_1 (NUMBER): 2

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

   2 - filter(("DATA"=:X AND "ID1"=:SYS_B_0))
   3 - access("ID2"=:SYS_B_1)


28 rows selected.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select sql_id, bind_mismatch from v$sql_shared_cursor where sql_id = '&S';
old   1: select sql_id, bind_mismatch from v$sql_shared_cursor where sql_id = '&S'
new   1: select sql_id, bind_mismatch from v$sql_shared_cursor where sql_id = '9yzqsx1jqs744'

SQL_ID        B
------------- -
9yzqsx1jqs744 N
9yzqsx1jqs744 Y
9yzqsx1jqs744 Y




You can use dbms_xplan as I did in 10g and above to verify the peeked binds.

V$sql_shared_cursor can be useful as well (if it isn't bind peeking, some other column(s) will be set to Y to tell you the underlying cause)

A reader, May 13, 2009 - 5:52 pm UTC

I don't see any ratioanle in v$sql_shared_cursor ( aka  None of the columns has the value of "Y". )
There is no more than one record in v$sql. 
However  , there are several version of the SQL with mutliple plan hash value.
 
SQL> select sql_id from v$sql where sql_id = '43q29yzqngxsg';

SQL_ID
-------------
43q29yzqngxsg

SQL> select sql_id ,  child_number from
  2  v$sql_shared_cursor where sql_id = '43q29yzqngxsg'
  3  /

SQL_ID        CHILD_NUMBER
------------- ------------
43q29yzqngxsg            0

SQL> SELECT distinct a.snap_id  ,
  2  a.plan_hash_value , a.sql_id , a.instance_number
  3  FROM
  4  dba_hist_sqlstat a
  5  WHERE a.sql_id in (
  6  '43q29yzqngxsg'
  7  )
  8  AND a.snap_id in ( 2824  ,  2825)
  9  and a.instance_number =1
 10  ;

   SNAP_ID PLAN_HASH_VALUE SQL_ID        INSTANCE_NUMBER
---------- --------------- ------------- ---------------
      2824         9214849 43q29yzqngxsg               1
      2825         9214849 43q29yzqngxsg               1

SQL>

Tom Kyte
May 13, 2009 - 7:46 pm UTC

and I don't see any differences in the plan hashes - so now what?


A reader, May 14, 2009 - 10:40 am UTC

OOPS . I forgot to add this . In Instance 2 , it is a different plan hash value . Is it expected ?

SQL> SELECT distinct a.snap_id  ,
  2      a.plan_hash_value , a.sql_id , a.instance_number
  3     FROM
  4      dba_hist_sqlstat a
  5      WHERE a.sql_id in (
  6      '43q29yzqngxsg'
  7      )
  8      AND a.snap_id in ( 2824  ,  2825)
  9      and a.instance_number =2
 10     ;

SQL> /

   SNAP_ID PLAN_HASH_VALUE SQL_ID        INSTANCE_NUMBER
---------- --------------- ------------- ---------------
      2824      3454841863 43q29yzqngxsg               2
      2825      3454841863 43q29yzqngxsg               2

A reader, May 29, 2009 - 10:13 am UTC

I have different plan hash value for a SQL . When I looked at v$sql_shared_cursor , I see the values of AUTH_CHECK_MISMATCH , TRANSLATION_MISMATCH to 'Y' . 
Looked at the oracle documentation . I am still not clear on the following documenation . 


Please explain . 

<oracle_docs>

AUTH_CHECK_MISMATCH  - Authorization/translation check failed for the existing child cursor 

TRANSLATION_MISMATCH  - The base objects of the existing child cursor do not match 

</oracle_docs>

<code>

  1  SELECT distinct
  2  a.instance_number ,  a.snap_id  ,
  3  a.plan_hash_value , a.sql_id
  4  FROM
  5  dba_hist_sqlstat a
  6  WHERE   sql_id in
  7  ('9bsfzknb22cun')
  8  AND a.snap_id in ( 3218  ,  3219)
  9* order by 1
SQL> /

INSTANCE_NUMBER    SNAP_ID PLAN_HASH_VALUE SQL_ID
--------------- ---------- --------------- -------------
              1       3218       287437572 9bsfzknb22cun
              1       3218       924111559 9bsfzknb22cun
              1       3219       924111559 9bsfzknb22cun
              1       3219       287437572 9bsfzknb22cun
              2       3218       924111559 9bsfzknb22cun
              2       3219      2068772121 9bsfzknb22cun
              2       3218      2068772121 9bsfzknb22cun
              2       3219       924111559 9bsfzknb22cun

8 rows selected.

SQL> SElect sql_id ,  AUTH_CHECK_MISMATCH , TRANSLATION_MISMATCH , gvs.INST_ID , gvs.ADDRESS , gvs.CHILD_ADDRESS , gvs.CHILD_NUMBER
  2  from GV$SQL_SHARED_CURSOR gvs  where sql_id = '9bsfzknb22cun'
  3  /

SQL_ID        A T    INST_ID ADDRESS          CHILD_ADDRESS    CHILD_NUMBER
------------- - - ---------- ---------------- ---------------- ------------
9bsfzknb22cun N N          1 000000025DCEC1E8 000000025D56CA10            0
9bsfzknb22cun Y Y          1 000000025DCEC1E8 000000025DC30728            1
9bsfzknb22cun N N          2 000000025D4DDE30 000000022F2574F0            0
9bsfzknb22cun Y Y          2 000000025D4DDE30 000000022F128F78            1

SQL> show parameter cursor_sharing

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
cursor_sharing                       string
FORCE
SQL> Select * from PRODUCT_COMPONENT_VERSION;

PRODUCT
----------------------------------------------------------------
VERSION
----------------------------------------------------------------
STATUS
----------------------------------------------------------------
NLSRTL
10.2.0.4.0
Production

Oracle Database 10g Enterprise Edition
10.2.0.4.0
64bi

PRODUCT
----------------------------------------------------------------
VERSION
----------------------------------------------------------------
STATUS
----------------------------------------------------------------

PL/SQL
10.2.0.4.0
Production

TNS for Linux:
10.2.0.4.0

PRODUCT
----------------------------------------------------------------
VERSION
----------------------------------------------------------------
STATUS
----------------------------------------------------------------
Production


SQL>



</code>
Tom Kyte
June 01, 2009 - 1:52 pm UTC

simple:

you have a query like "select * from t"

when YOU run it, it resolves to "select * from YOUR_SCHEMA.T"

when I run it - it resolves to "select * from TKYTE.T"

the sql text "select * from t" looks the same, but it references different objects - hence, the authid issue (different authorization schemas) and translation issues (different objects are referenced when name qualification is performed)

A reader, June 02, 2009 - 1:05 pm UTC

What is the difference between display_awr and display_cursor ?
I got more than plans from display_awr , where as display_cursor gets one.

Some times , I don¿t see the SQL in the AWR report . But I am able to get the plan from display_awr.

What is the meaning of ¿any loaded cursor¿ ?

<docs>
¿ DISPLAY_AWR - to format and display the contents of the execution plan of a stored SQL statement in the AWR.
¿ DISPLAY_CURSOR - to format and display the contents of the execution plan of any loaded cursor
</docs>

Tom Kyte
June 02, 2009 - 6:47 pm UTC

you can dump plans of things that are not in the SGA (the shared pool) with AWR - it has the history.

You can only dump things in the v$ tables (loaded) using display_cursor.



The AWR report doesn't show EVERY sql statement - it does "top" reports, the information is in the AW-repository but doesn't have to appear in "top" reports.

dbms_xplan.display_cursor

Hariharasudhan, September 11, 2009 - 12:21 am UTC

Tom i am trying to view  explain plan output through DBMS_XPLAN packaged procedures. 
when executed the following sql's

hr@ORCL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
User has no SELECT privilege on V$SQL_PLAN

since HR user does not have privileges over Dynamic views i connected as sys and granted necessary select privileges on the base tables

hr@ORCL> conn /as sysdba
Connected.

sys@ORCL> grant select on v_$sql_plan to hr;

Grant succeeded.

re connected as hr and was able to select from V$sql_plan
sys@ORCL> conn hr/hr
Connected.
hr@ORCL> select count(1) from v$sql_plan;

  COUNT(1)
----------
      7391

tried to reexecute the package but again same problem    
hr@ORCL> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
User has no SELECT privilege on V$SQL_PLAN

hr@ORCL>   

My understanding is since Grant Privilege is success , 
i thought pacakge should be able to execute using invokers rights, 
But why is it still pointing out that no privileges message
i am testing this on 11g installed on my laptop with xp os

Tom Kyte
September 14, 2009 - 11:55 am UTC

they meant to say "v$sql" the second time :)

ops$tkyte%ORA11GR1> create user test identified by test;

User created.

ops$tkyte%ORA11GR1> grant create session to test;

Grant succeeded.

ops$tkyte%ORA11GR1> connect test/test
Connected.
test%ORA11GR1> set serveroutput off
test%ORA11GR1> select * from dual;

D
-
X

test%ORA11GR1> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
User has no SELECT privilege on V$SQL_PLAN

test%ORA11GR1>
test%ORA11GR1> connect / as sysdba;
Connected.
sys%ORA11GR1> grant select on v_$sql_plan to test;

Grant succeeded.

sys%ORA11GR1>
sys%ORA11GR1> connect test/test
Connected.
test%ORA11GR1> set serveroutput off
test%ORA11GR1> select * from dual;

D
-
X

test%ORA11GR1> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
User has no SELECT privilege on V$SQL_PLAN

test%ORA11GR1>
test%ORA11GR1> connect / as sysdba;
Connected.
sys%ORA11GR1> grant select on v_$sql to test;

Grant succeeded.

sys%ORA11GR1>
sys%ORA11GR1> connect test/test
Connected.
test%ORA11GR1> set serveroutput off
test%ORA11GR1> select * from dual;

D
-
X

test%ORA11GR1> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
SQL_ID  a5ks9fhw2v9s1, child number 0
-------------------------------------
select * from dual

Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------


13 rows selected.

K.Hariharasudhan, September 16, 2009 - 10:28 pm UTC

Thanks Tom , your idea worked out.
output of dbms_xplan was misguiding.

How to find what tables are fragmented ?or having very high HWM.

A reader, March 25, 2010 - 6:45 am UTC

Hi Tom ,

How to find the tables which are candidate of re-organization as we know that there are quite few tables from which data is being deleted daily, How to find the one which are worst and good candidate to do a "alter table move ..." . These tables are staging tables in a data mart and the select queries off thesse tables are showing progressive slowdown, i can see many scripts to find out such tables, most of them are hard to understand.

also how to find the what tables are being full scanned.

Thanks
Tom Kyte
March 26, 2010 - 1:37 pm UTC

if you delete and insert constantly, they are fine.

It would be if you only delete - and then you sort of know, I just deleted 90% of the data :)

just look at the statistics, avg row length, number of rows and allocated blocks. It would give you a feeling as to "number of rows/block on average - vs - how many rows I think there should be based on avg row length"


... also how to find the what tables are being full scanned. ...

look at v$segment statistics or v$sql_plan. segment statistics will give you and indication of where you are spending your IOs (more important than "what do I full scan" if you ask me), v$sql_plan will tell you what you full scan.

How to see a plan which is not in v$sql_plan

A reader, March 26, 2010 - 5:04 am UTC

Hi Tom

I have a SQL_ID taken for AWR report, but when I try to see its plan in v$sql_plan - it does not exist.
is there a way to see the plan for this sql in any other view or tables.does it get stored anywhere.

database version is 10.2.0.4.

Thanks
Tom Kyte
March 26, 2010 - 3:30 pm UTC

If you have access to AWR, it is highly likely to be there, DBA_HIST_SQL_PLAN

strange question

A reader, March 31, 2010 - 9:22 am UTC

Hi Tom

I have a batch run ( collection of several programs,queries) in DW. It runs twice in a day. when it run in morning it takes 3 hrs. when it runs in night it takes 5 hours. I want to figure out that what are the SQL's or program which ran in Morning but not in evening , and the one ran in night but not in morning and then finally what were the common one.

using AWR , I can do it manually but AWR reprots top 10 queries .and it will be cumbersome. using AWR load profile i can see there is a difference in Load.

so is there a way I can get it -like a running a query off a table and giving the input as snap numbers of relevant duration ...

Thank you.

V$SQL Query

manoj, May 31, 2010 - 9:48 am UTC

Hi Tom,

I had a question regarding the FETCH column in the V$SQL view

Can you please explain what this FETCH column denotes? Also, if we have a full explanation somewhere in web? As I went through Oracle documentation and that said: Number of fetches for the SQL statement. But I would require an explanatory for that.


Tom Kyte
June 08, 2010 - 7:41 am UTC

it is the number of times the cursor was fetched from.

If you have 500 records in a result set and use an array size of 1 - fetched will be 500 (probably 501 to figure out "no more data" actually)

If you used an array size of 10, it would be 500/10 = 50 (plus 1 again)

If you used an array size of 100, it would be 500/100 = 5 (plus 1)

and so on.

consider:

ops$tkyte%ORA11GR2> create table t as select * from all_users;

Table created.

ops$tkyte%ORA11GR2> select count(*) from t;

  COUNT(*)
----------
        45

ops$tkyte%ORA11GR2> alter system flush shared_pool;

System altered.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace procedure p( p_array_size in number )
  2  as
  3          c sys_refcursor;
  4
  5          type array is table of t%rowtype index by binary_integer;
  6          l_data array;
  7  begin
  8          open c for select * from t look_for_me;
  9          loop
 10                  fetch c bulk collect into l_data limit p_array_size;
 11                  /* process */
 12                  exit when c%notfound;
 13          end loop;
 14          close c;
 15  end;
 16  /

Procedure created.

ops$tkyte%ORA11GR2> define F=0
ops$tkyte%ORA11GR2> column fetches new_val F
ops$tkyte%ORA11GR2> select sql_text, sum(fetches) fetches, sum(fetches)-&F diff from v$sql where sql_text like '%LOOK_FOR_ME' group by sql_text;
old   1: select sql_text, sum(fetches) fetches, sum(fetches)-&F diff from v$sql where sql_text like '%LOOK_FOR_ME' group by sql_text
new   1: select sql_text, sum(fetches) fetches, sum(fetches)-0 diff from v$sql where sql_text like '%LOOK_FOR_ME' group by sql_text

no rows selected

ops$tkyte%ORA11GR2> exec p(1);

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> select sql_text, sum(fetches) fetches, sum(fetches)-&F diff from v$sql where sql_text like '%LOOK_FOR_ME' group by sql_text;
old   1: select sql_text, sum(fetches) fetches, sum(fetches)-&F diff from v$sql where sql_text like '%LOOK_FOR_ME' group by sql_text
new   1: select sql_text, sum(fetches) fetches, sum(fetches)-0 diff from v$sql where sql_text like '%LOOK_FOR_ME' group by sql_text

SQL_TEXT                        FETCHES       DIFF
---------------------------- ---------- ----------
SELECT * FROM T LOOK_FOR_ME          46         46

<b>table has 45 rows, so fetches (one at a time) = 45+1 </b>

ops$tkyte%ORA11GR2> exec p(2);

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> select sql_text, sum(fetches) fetches, sum(fetches)-&F diff from v$sql where sql_text like '%LOOK_FOR_ME' group by sql_text;
old   1: select sql_text, sum(fetches) fetches, sum(fetches)-&F diff from v$sql where sql_text like '%LOOK_FOR_ME' group by sql_text
new   1: select sql_text, sum(fetches) fetches, sum(fetches)-        46 diff from v$sql where sql_text like '%LOOK_FOR_ME' group by sql_text

SQL_TEXT                        FETCHES       DIFF
---------------------------- ---------- ----------
SELECT * FROM T LOOK_FOR_ME          69         23

<b>45/2 = 22.5, since you cannot do half a fetch, 23 fetch calls are made, 23rd discovers "no more data" and we exit</b>

ops$tkyte%ORA11GR2> exec p(3);

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> select sql_text, sum(fetches) fetches, sum(fetches)-&F diff from v$sql where sql_text like '%LOOK_FOR_ME' group by sql_text;
old   1: select sql_text, sum(fetches) fetches, sum(fetches)-&F diff from v$sql where sql_text like '%LOOK_FOR_ME' group by sql_text
new   1: select sql_text, sum(fetches) fetches, sum(fetches)-        69 diff from v$sql where sql_text like '%LOOK_FOR_ME' group by sql_text

SQL_TEXT                        FETCHES       DIFF
---------------------------- ---------- ----------
SELECT * FROM T LOOK_FOR_ME          85         16

<b>45/3 = 15, the 15th did not raise no data found, so we did 16 altogether that time</b>


Fetch

manoj, June 08, 2010 - 9:43 am UTC

Hi Tom,

Thanks for the wonderful explanation with example.

I observed that the query was still fetching something which I could see from V$SQL that the value is increasing even though the execution of query had ended..

Although it showed the status of query as ACTIVE in the v$session.. Is it really possible or I had intepreted it wrong?

If it can happen, then what are the possible scenarios for the FETCH to increase while SQL execution is finished?

Thanks again.
Tom Kyte
June 09, 2010 - 9:01 am UTC

... I could see from
V$SQL that the value is increasing even though the execution of query had
ended..
..

nope, that is not possible, the query could NOT have ended if you are still fetching from it, it isn't over till it is over....


A query starts when you open it, a query continues as you fetch from it, a query finishes when you close it.


V$SQL

Manoj, June 10, 2010 - 6:08 am UTC

Hi Tom,

Thanks for the explanation.. I was wrong in intrepretation.

Can you please suggest a link which descibes the fields of v$sql and v$session in details? I have gone through Oracle pages but it is not that explanatory that I want..

or Any suggested book where can I find elaborated definition.. If with examples..wow that will be great..

Anyhow, Thanks again for the help.

Tom Kyte
June 10, 2010 - 2:30 pm UTC

sorry, that is about it (the documentation). You sort of have to know how sql is executed (it should be clear for example that a query is NOT complete until it is closed - the fetches take place in the middle for example). I don't know of anything that attempts to look at every possible meaning and tell you what it means.

For example, if you asked me to define FETCHES in V$SQL, I would say "number of fetch calls made against the cursor"

and I would think "that is about as complete as you can get".

Now, I wrote that before peeking at the documentation to see what it said - and it said:

"Number of fetches associated with the SQL statement"

Which is not really different from what I would have said...

SQL not captured in DBA_HIST_SQLSTAT

Vincent, January 19, 2011 - 7:02 pm UTC

Dear Tom,

1. Many sqls are not captured in DBA_HIST_SQLSTAT. Snap frequency is set to 1 hour. Please let me know if reducing the snap period to 30 minute would help capturing all sqls.

2. If I sum delta columns on dba_hist_sqlstat, I get ZERO for all _delta columns for some sqls. What could be the reason?

Thank you.

Tom Kyte
January 24, 2011 - 6:01 am UTC

1) it will NEVER happen, these are samples, it will never be 100%.

Say at time T1 the sql "select * from dual" is NOT in v$sql, it won't be captured during that snapshot.

Say at time T2 the sql "select * from dual" appears in v$sql. T2 > T1.

Say at time T3 the sql "select * from dual" is aged out of the shared pool. T3 > T2.

At time T4, T4>T3 another snapshot is taken. the sql "select * from dual" will not appear in the snapshot.


That is just the most common way for a sql statement to not make it into snapshots. Another would be "the sql is not considered interesting, it was not executed often, it did not really contribute to the work load".

dba_hist_sqlstat is for interesting sql that existed in the shared pool at the point in time the snapshots were taken, it will never be 100% comprehensive - never.


2) examples please, something small and comprehensible.

v$sql_plan oddity

A reader, June 04, 2011 - 10:49 pm UTC

Dear Sir,
i was trying to find execution plan details from v$sql_plan for a given sql_id

select plan_hash_value from v$sqlarea where sql_id='gkj4wpv49v5dt';

PLAN_HASH_VALUE
---------------
790887848

select * from v$sql_plan where sql_id='gkj4wpv49v5dt' and plan_hash_value =790887848;

no rows selected

I was expecting to find an output from v$sql_plan

I did further investigation

select sql_id from v$sql_plan where plan_hash_value=790887848;

SQL_ID
-------------
gvxjcntmbc1h1
gvxjcntmbc1h1
gvxjcntmbc1h1

select plan_hash_value from v$sql_plan where sql_id='gkj4wpv49v5dt';

no rows selected

I couldn't understand why plan_hash_value is linked to different sql's as reported in v$sql_plan and v$sqlarea. I hope you can clarify.
Oracle version is 10gR2
thanks
krishan
Tom Kyte
June 06, 2011 - 9:30 am UTC

different SQL's can have the same plan hash value - that isn't unusual.

ops$tkyte%ORA11GR2> create table t ( x int );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select * from t;

no rows selected

ops$tkyte%ORA11GR2> select * from T;

no rows selected

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select sql_id, sql_text from v$sql where lower(sql_text) = 'select * from t';

SQL_ID        SQL_TEXT
------------- ------------------------------
89km4qj1thh13 select * from t
ahgbnyrbh7bp1 select * from T

ops$tkyte%ORA11GR2> select sql_id, plan_hash_value
  2    from v$sql_plan
  3   where sql_id in
  4  (select sql_id from v$sql where lower(sql_text) = 'select * from t');

SQL_ID        PLAN_HASH_VALUE
------------- ---------------
89km4qj1thh13      1601196873
89km4qj1thh13      1601196873
ahgbnyrbh7bp1      1601196873
ahgbnyrbh7bp1      1601196873



And the values in v$ tables related to SQL statements are subject to flushing out all of the time - due to insufficient space, becoming invalidated for some reason - whatever. The data is transient.

All you are seeing here is two sql statements that PROBABLY have the same plan - but different SQL_TEXT - hence different sql_ids. (probably have the same plan because it is entirely possible for two different plans to have the same hash value, hashing is not a "unique" thing - it would be unusual but not impossible for two different plans to have the same hash).

And - only one of the sql statements still has it's plan in v$sql_plan, the other one had the data flushed from v$sql_plan to make room for something else or perhaps because it become invalidated.


List of all the SQL's for which the plan has changed

Ajeet, November 21, 2011 - 2:27 am UTC

Hi Tom,
I am using Oracle 11g R2 (11.2.0.2). I want to get the list of all the SQL's for which there is more than one plan in last 7 days or may be in last one month. is there a way to get this from DBA_HIST_SQL_PLAN or any other table. We have not enabled SQL PLAN management yet in our database.

Regards
Ajeet


Tom Kyte
November 21, 2011 - 2:02 pm UTC

you want DBA_HIST_SQLSTAT

you just want sql id's for sql's that have more than one plan_hash_value in a given snapshot range.

that'll show you sql's with different plans. you can then use dba_hist_sql_plan to see what the actual plans were.

I tried this..

Ajeet, November 21, 2011 - 2:35 am UTC

Hello Tom,

I tried a query like below to get the multiple plan for the same query - not sure if this is the right way to do.so kindly explain.

SELECT sql_id,count(*) from dba_hist_sql_plan group by sql_id having count(*) > 1 ;

Regards
Tom Kyte
November 21, 2011 - 2:02 pm UTC

see above.

correction in sql

Ajeet, November 21, 2011 - 3:44 am UTC

Hi Tom.

I think the below sql is more meaningful :

SELECT sql_id,id,count(*) from dba_hist_sql_plan group by sql_id,id having count(*) > 1 order by sql_id,id ;

if I group by id also then, it will tell me the sql_id's for which there are more than one plan...is this correct ?

Regards

which was the best plan

Ajeet, November 22, 2011 - 12:36 am UTC

Hello Tom,

Thanks for your answer. Using dba_hist_sqlstat I can get the multiple plans for the same sql_id. now my question is - how to say which plan was best, basically I want to get the elapsed time for that plan. is it possible to get the elapsed time as well for each of these plans (i mean to say that elapsed time for the query when that plan was used). in dba_hist_sqlstat , I can see a column ELAPSED_TIME_DELTA - what is this column, will it give the elapsed time somehow.

Regards
Ajeet
Tom Kyte
November 22, 2011 - 8:10 am UTC

yes, you can use the elapsed times and the execution counts to get an "average" execution time for each.

one last question

Ajeet, November 22, 2011 - 8:32 am UTC

Hi Tom,

Thanks again for your answers . one last question on this , I can see few sql_id's with plan_hash_value as 0. does this mean something different.

Regards
Ajeet
Tom Kyte
November 22, 2011 - 9:03 am UTC

means the plan isn't there, probably plsql blocks.

very useful

A reader, November 23, 2011 - 7:54 am UTC

Thanks Tom, Your answers have really helped me again today.

Regards

Extract order of operations from v$sql_plan

Matthew, June 29, 2017 - 9:00 pm UTC

I am trying to give the order the plan is processed in from a query from v$sql_plan. For novices, its a little tough to explain you read the explain plan from most indented/depth to least indented and items with the same depth the earlier one is processed first. I want to have a sql statement that will generate the real order oracle will process the plan in.

For instance, given my simulated explain plan:

SQL> set lines 150 pages 999
SQL> select banner from v$version

BANNER                                                                          
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production    
PL/SQL Release 12.1.0.2.0 - Production                                          
CORE 12.1.0.2.0 Production                                                      
TNS for Linux: Version 12.1.0.2.0 - Production                                  
NLSRTL Version 12.1.0.2.0 - Production                                          

5 rows selected.
SQL> DROP TABLE SYSTEM.FAKE_V_SQL_PLAN CASCADE CONSTRAINTS
Table dropped.
SQL> CREATE TABLE SYSTEM.FAKE_V_SQL_PLAN
(
  ID            NUMBER,
  PARENT_ID     NUMBER,
  DEPTH         NUMBER,
  POSITION      NUMBER,
  CHILD_NUMBER  NUMBER,
  OPERATION     VARCHAR2(30 BYTE),
  OPTIONS       VARCHAR2(30 BYTE),
  OBJECT_OWNER  VARCHAR2(30 BYTE),
  OBJECT_NAME   VARCHAR2(30 BYTE)
)
Table created.
SQL> Insert into SYSTEM.FAKE_V_SQL_PLAN Values (0, NULL, 0, 93669, 0, 'SELECT STATEMENT', NULL, NULL, NULL)
1 row created.
SQL> Insert into SYSTEM.FAKE_V_SQL_PLAN Values (1, 0, 1, 1, 0, 'SORT', 'GROUP BY', NULL, NULL)
1 row created.
SQL> Insert into SYSTEM.FAKE_V_SQL_PLAN Values (2, 1, 2, 1, 0, 'NESTED LOOPS', NULL, NULL, NULL)
1 row created.
SQL> Insert into SYSTEM.FAKE_V_SQL_PLAN Values (3, 2, 3, 1, 0, 'NESTED LOOPS', NULL, NULL, NULL)
1 row created.
SQL> Insert into SYSTEM.FAKE_V_SQL_PLAN Values (4, 3, 4, 1, 0, 'TABLE ACCESS', 'BY INDEX ROWID', 'TOM', 'TAB_A')
1 row created.
SQL> Insert into SYSTEM.FAKE_V_SQL_PLAN Values (5, 4, 5, 1, 0, 'INDEX', 'RANGE SCAN', 'TOM', 'TAB_A_IDX')
1 row created.
SQL> Insert into SYSTEM.FAKE_V_SQL_PLAN Values (6, 3, 4, 2, 0, 'PARTITION RANGE', 'ALL', NULL, NULL)
1 row created.
SQL> Insert into SYSTEM.FAKE_V_SQL_PLAN Values (7, 6, 5, 1, 0, 'INDEX', 'RANGE SCAN', 'TOM', 'TAB_B_IDX1')
1 row created.
SQL> Insert into SYSTEM.FAKE_V_SQL_PLAN Values (8, 2, 3, 2, 0, 'TABLE ACCESS', 'BY LOCAL INDEX ROWID', 'TOM', 'TAB_B')
1 row created.
SQL> COMMIT
Commit complete.
SQL> set lines 150 pages 999
SQL> column operations format a51
SQL> column id format 9
SQL> column parent_id format 99999999
SQL> column depth format 9999
SQL> column position format 9999999
SQL> column operation format a17
SQL> column options format a20
SQL> column object_owner format a10
SQL> column object_name format a11
SQL> SELECT p.id, p.parent_id, p.depth, p.position
     , lpad('   ', p.depth * 2) || p.operation || ' ' || p.options || nvl2(p.object_name, ' [' || p.object_owner || '.' || p.object_name || ']', NULL) operations
     , p.operation, p.options, p.object_owner, p.object_name 
  FROM system.fake_v_sql_plan p
 order by p.child_number, p.id

ID PARENT_ID DEPTH POSITION OPERATIONS                                          OPERATION         OPTIONS              OBJECT_OWN OBJECT_NAME
-- --------- ----- -------- --------------------------------------------------- ----------------- -------------------- ---------- -----------
 0               0    93669 SELECT STATEMENT                                    SELECT STATEMENT                                             
 1         0     1        1   SORT GROUP BY                                     SORT              GROUP BY                                   
 2         1     2        1     NESTED LOOPS                                    NESTED LOOPS                                                 
 3         2     3        1       NESTED LOOPS                                  NESTED LOOPS                                                 
 4         3     4        1         TABLE ACCESS BY INDEX ROWID [TOM.TAB_A]     TABLE ACCESS      BY INDEX ROWID       TOM        TAB_A      
 5         4     5        1           INDEX RANGE SCAN [TOM.TAB_A_IDX]          INDEX             RANGE SCAN           TOM        TAB_A_IDX  
 6         3     4        2         PARTITION RANGE ALL                         PARTITION RANGE   ALL                                        
 7         6     5        1           INDEX RANGE SCAN [TOM.TAB_B_IDX1]         INDEX             RANGE SCAN           TOM        TAB_B_IDX1 
 8         2     3        2       TABLE ACCESS BY LOCAL INDEX ROWID [TOM.TAB_B] TABLE ACCESS      BY LOCAL INDEX ROWID TOM        TAB_B      

9 rows selected.


The item (INDEX RANGE SCAN [TOM.TAB_A_IDX]) with ID=5 is processed first
Next is (TABLE ACCESS BY INDEX ROWID [TOM.TAB_A]) with ID=4 is processed next
Then is (INDEX RANGE SCAN [TOM.TAB_B_IDX1]) with ID=7 is processed next
Then is (PARTITION RANGE ALL) with ID=6 is processed next
Then is (NESTED LOOPS) with ID=3 is processed next
and so on

So what I really want for the output is a combination of the indentation (which I already have) and a number representing the order of operations that oracle will process things in:

ORDER OPERATIONS                                         
----- --------------------------------------------------- 
 9    SELECT STATEMENT                                    
 8      SORT GROUP BY                                     
 7        NESTED LOOPS                                    
 5          NESTED LOOPS                                   
 2            TABLE ACCESS BY INDEX ROWID [TOM.TAB_A]     
 1              INDEX RANGE SCAN [TOM.TAB_A_IDX]          
 4            PARTITION RANGE ALL                         
 3              INDEX RANGE SCAN [TOM.TAB_B_IDX1]         
 6          TABLE ACCESS BY LOCAL INDEX ROWID [TOM.TAB_B]


I've racked my brain for a couple of days trying to use the ID, PARENT_ID, DEPTH and POSITION columns and combinations of analytic functions (dense_rank mostly, but also lead and lag) and connect_by queries without success. Do you see any way this can be accomplished?

Any help is greatly appreciated

-matt
Connor McDonald
July 06, 2017 - 3:10 am UTC

Well...I'm not entirely of the validity of that concept, when you consider how the database can run things nowadays, eg, it might scan *batches* of index entries, and then defer the table lookup until later for efficiency etc.

But that aside, I've taken the lazy option to use a pipeline to make the computation easier

I'm sure others can contribute nice recursive or model solutions if they are inclined to do so:-)

SQL> create or replace
  2  function hack_job return sys.odcivarchar2list pipelined is
  3    cursor c is select s.*, ' ' processed from system.fake_v_sql_plan s order by id ;
  4    type t is table of c%rowtype index by pls_integer;
  5    r t;
  6
  7    l_id int;
  8    l_depth    int;
  9    l_cnt      int;
 10
 11  begin
 12    for i in c loop
 13      r(i.id) := i;
 14    end loop;
 15    l_cnt := r.count;
 16
 17    loop
 18      exit when l_cnt = 0;
 19    --
 20    -- deepest entry
 21    --
 22      l_id := -1;
 23      l_depth    := -1;
 24      for i in r.first .. r.last loop
 25        if r(i).processed = ' ' then
 26           if r(i).depth > l_depth then
 27              l_id := r(i).id;
 28              l_depth := r(i).depth;
 29           end if;
 30        end if;
 31      end loop;
 32
 33      r(l_id).processed := 'Y';
 34      pipe row ( lpad(l_id,10)||' '||
 35                 rpad(lpad('   ', r(l_id).depth * 2) || r(l_id).operation,40)||
 36                 rpad(r(l_id).options,20)||
 37                 rpad(r(l_id).object_owner,10)||
 38                 r(l_id).object_name
 39                  );
 40      l_cnt := l_cnt - 1;
 41
 42    --
 43    -- any entry with same parent as deepest entry
 44    --
 45      for i in l_id .. r.last loop
 46        if r(i).processed = ' ' then
 47           if r(i).parent_id = r(l_id).parent_id then
 48              pipe row ( lpad(i,10)||' '||
 49                 rpad(lpad('   ', r(i).depth * 2) || r(i).operation,40)||
 50                 rpad(r(i).options,20)||
 51                 rpad(r(i).object_owner,10)||
 52                 r(i).object_name
 53                  );
 54              r(i).processed := 'Y';
 55              l_cnt := l_cnt - 1;
 56           end if;
 57        end if;
 58      end loop;
 59
 60    --
 61    -- then the parent
 62    --
 63      l_id := r(l_id).parent_id;
 64      r(l_id).processed := 'Y';
 65      pipe row ( lpad(l_id,10)||' '||
 66                 rpad(lpad('   ', r(l_id).depth * 2) || r(l_id).operation,40)||
 67                 rpad(r(l_id).options,20)||
 68                 rpad(r(l_id).object_owner,10)||
 69                 r(l_id).object_name
 70                  );
 71       l_cnt := l_cnt - 1;
 72
 73    end loop;
 74    return;
 75  end;
 76  /

Function created.

SQL>
SQL> select * from table(hack_job);

COLUMN_VALUE
-----------------------------------------------------------------------------------------------------------------
         5           INDEX                         RANGE SCAN          SCOTT     TAB_A_IDX
         4         TABLE ACCESS                    BY INDEX ROWID      SCOTT     TAB_A
         7           INDEX                         RANGE SCAN          SCOTT     TAB_B_IDX1
         6         PARTITION RANGE                 ALL
         3       NESTED LOOPS
         8       INDEX                             UNIQUE SCAN         SCOTT     SOME_IOT
         2     NESTED LOOPS
         1   SORT                                  GROUP BY
         0 SELECT STATEMENT

9 rows selected.


Plan changes in 11g

Rajeshwaran, Jeyabal, July 11, 2018 - 8:53 am UTC

Team,

we have an application running on Oracle 11g database and yesterday afternoon there was a plan change (child cursor=1) for a sql statement, all of sudden things ran slow( no stats gather done on the base tables of this sql )

the DBA at the client place got baseline this sql with the old plan (child cursor=0).

Any reason how can i identity the reason for this plan change? looked into v$sql_shared_cursor - but no entries there since it got aged out.

any DBA_HIST* dictionary available for verify the reason for this plan change for historical execution?

kindly advice.

Hash value of sql_id along with session source details

Diwakar Krishnakumar, July 25, 2019 - 10:41 pm UTC

Hello team, In the above post, there is this statement:

select plan_hash_value from v$sql_plan where sql_id='gkj4wpv49v5dt';

I have a similar situation where I need to grab the sessions details (sid,serial#,machine,osuser,terminal,module) along with the plan_hash_value of a particular sql_id.

It needs to be the one in the cache, rather than from the history tables.

Is this possible, if yes, at a high level, can I have the name of the base table/view and the joining column that could get me the details.

Thanks in advance.

Regards,
K.Diwakar
Connor McDonald
July 26, 2019 - 3:27 am UTC

Thats not really how it works, because any session could run or be running that sql statement.

What you could do is mine the ASH data to see what SQL statements were being run by active sessions at a particular point in time and work from there. ASH to map the SQL to the session.

SQL> desc v$active_session_history
 Name                                                                    Null?    Type
 ----------------------------------------------------------------------- -------- ------------------
 SAMPLE_ID                                                                        NUMBER
 SAMPLE_TIME                                                                      TIMESTAMP(3)
 SAMPLE_TIME_UTC                                                                  TIMESTAMP(3)
 USECS_PER_ROW                                                                    NUMBER
 IS_AWR_SAMPLE                                                                    VARCHAR2(1)
 SESSION_ID                                                                       NUMBER
 SESSION_SERIAL#                                                                  NUMBER
 SESSION_TYPE                                                                     VARCHAR2(10)
 FLAGS                                                                            NUMBER
 USER_ID                                                                          NUMBER
 SQL_ID                                                                           VARCHAR2(13)
 IS_SQLID_CURRENT                                                                 VARCHAR2(1)
 SQL_CHILD_NUMBER                                                                 NUMBER
 SQL_OPCODE                                                                       NUMBER
 SQL_OPNAME                                                                       VARCHAR2(64)
 FORCE_MATCHING_SIGNATURE                                                         NUMBER

Hash value of sql_id along with session source details

Diwakar Krishnakumar, July 26, 2019 - 6:37 pm UTC

Hi,

Thank you for getting back.
Correct me here:

-- I am looking for one more step apart from what sql's are triggered by a session and their details,
rather we are looking at the session details and plan_hash_Value together for active sessions executing a particular SQL_ID - please let me know if this is possible and if we have to use active_session_history, please let us know the appropriate source for plan_hash_value as well.....


-- Yes any session could be executing the sql - the same sql_id

-- the values being passed into the bind variables would be local to that session and the session that is coming from a specific source might be passing a different set of values than that coming from a different one

-- as a result, might trigger a different plan altogether - need your clarification on this

-- If this is true, we want to know and analyze the user pattern, might be look at what plan(plan_hash_value) is getting triggered for a specific sql_id by what user and from what source.

-- Yes, we are working for specific sql_id's that are most important to us for that specific database and would like to know if getting these details is possible


Connor McDonald
July 30, 2019 - 3:14 am UTC

v$active_session_history rows contain

SESSION_ID
SESSION_SERIAL#

for the session, and also

SQL_ID
SQL_CHILD_NUMBER

to drive into V$SQL, which gives you PLAN_HASH_VALUE

Hash value of sql_id along with session source details

Diwakar Krishnakumar, August 07, 2019 - 6:32 pm UTC

Thank you for your reply.
Connor McDonald
August 08, 2019 - 2:28 am UTC

Glad we could help

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.