Home » RDBMS Server » Performance Tuning » Help :Getting Inner query executions from v$sql_plan_statistics_all
Help :Getting Inner query executions from v$sql_plan_statistics_all [message #270311] Wed, 26 September 2007 08:57 Go to next message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member

Hi,

Oracle 9i R2 (RAC) on Linux

Please refer following co-related query.

SELECT strItemKey, strUser, dtCreated, 'Transfer' action,
strToWorkQname, nToWorkQType ,
(
SELECT POL_NBR
FROM WF_REP_ITEM_DTL wrid
WHERE wrid.strItemKey=wre.strITemKey
AND nevent=1)
FROM wf_rep_events wre
WHERE neventtype=3;

Is it possible to know how times the inner query will execute from the view v$sql_pla_statistics_all without actually executing the query?

In case it is not possible (or even not possible) without executing the query, can anybody please advice me on how to get it from the view?

I tried with
alter session set statistics_level=all;

but could not got it.

Apart from this which is other imp information i can get stepwise?

Thanks and Regards,
Pratap
Re: Help :Getting Inner query executions from v$sql_plan_statistics_all [message #270312 is a reply to message #270311] Wed, 26 September 2007 09:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
150 more posts and you still don't know how to format and post a correct topic.
Tell me how I answer, I can't remember?

Regards
Michel
Re: Help :Getting Inner query executions from v$sql_plan_statistics_all [message #270317 is a reply to message #270311] Wed, 26 September 2007 09:09 Go to previous messageGo to next message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member

Michel,

Mistake Corrected here...

Oracle 9i R2 (RAC) on Linux

Please refer following co-related query.


SELECT strItemKey, strUser, dtCreated, 'Transfer' action, 
strToWorkQname, nToWorkQType ,
(
SELECT POL_NBR 
FROM WF_REP_ITEM_DTL wrid 
WHERE wrid.strItemKey=wre.strITemKey 
AND nevent=1) 
FROM wf_rep_events wre 
WHERE neventtype=3;

With the Plan as following

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=11615 Card=73856 Byt
          es=2289536)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'WF_REP_ITEM_DTL' (Cost=3
           Card=1 Bytes=19)

   2    1     INDEX (UNIQUE SCAN) OF 'PK_WFREPITMDTL' (UNIQUE) (Cost=3
           Card=1)

   3    0   TABLE ACCESS (FULL) OF 'WF_REP_EVENTS' (Cost=11615 Card=73
          856 Bytes=2289536)




Is it possible to know how times the inner query will execute from the view v$sql_pla_statistics_all without actually executing the query?

In case it is not possible (or even not possible) without executing the query, can anybody please advice me on how to get it from the view?

I tried with
alter session set statistics_level=all;

but could not got it.

Apart from this which is other imp information i can get stepwise?

Thanks and Regards,
Pratap
Re: Help :Getting Inner query executions from v$sql_plan_statistics_all [message #270338 is a reply to message #270317] Wed, 26 September 2007 10:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Correct your format.
Indent the query.
Fix broken lines in plan.
Remove blank lines.
Show a little respect for those who read you.

Regards
Michel
Re: Help :Getting Inner query executions from v$sql_plan_statistics_all [message #270352 is a reply to message #270311] Wed, 26 September 2007 10:51 Go to previous messageGo to next message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member

Michel,

Formatted one

select stritemkey, struser, dtcreated, 'Transfer' action, strtoworkqname,
       ntoworkqtype,
       ( select pol_nbr
        FROM   wf_rep_item_dtl wrid
        WHERE  wrid.stritemkey = wre.stritemkey
        AND    nevent = 1 )
FROM   wf_rep_events wre
WHERE  neventtype = 3;


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     6833      9.72     117.55      87979     388800          0      102466
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     6835      9.72     117.55      87979     388800          0      102466


Rows     Row Source Operation
-------  ---------------------------------------------------
 101119  TABLE ACCESS BY INDEX ROWID WF_REP_ITEM_DTL
 101119   INDEX UNIQUE SCAN PK_WFREPITMDTL (object id 156676)
 102466  TABLE ACCESS FULL WF_REP_EVENTS


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
 101119   TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
              'WF_REP_ITEM_DTL'
 101119    INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_WFREPITMDTL'
               (UNIQUE)
 102466   TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'WF_REP_EVENTS'



And yes Respect is there for every participent in this forum.

Don't take these mistkaes in wrong way

Thanks and Regards,
Pratap
Re: Help :Getting Inner query executions from v$sql_plan_statistics_all [message #270357 is a reply to message #270352] Wed, 26 September 2007 11:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The scalar subquery is executed once per row elected in the outer query.
So it easy to estimate it (and Oracle can do it via explain plan) if you have the correct statistics: it is the number of returned rows.

Regards
Michel
Re: Help :Getting Inner query executions from v$sql_plan_statistics_all [message #270358 is a reply to message #270311] Wed, 26 September 2007 11:12 Go to previous messageGo to next message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member

Michel,

I want to know how can i get similar or more precise information from v$sql_plan_statistics_all?

Thanks and Regards,
Pratap

Re: Help :Getting Inner query executions from v$sql_plan_statistics_all [message #270361 is a reply to message #270358] Wed, 26 September 2007 11:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't.

Regards
Michel

Re: Help :Getting Inner query executions from v$sql_plan_statistics_all [message #270367 is a reply to message #270361] Wed, 26 September 2007 12:06 Go to previous message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member


Michel Cadot wrote
The scalar subquery is executed once per row elected in the outer query.
So it easy to estimate it (and Oracle can do it via explain plan) if you have the correct statistics: it is the number of returned rows.

Regards
Michel


Thank you Michel!!
You consolidate my thoughts!

Regards,


mson77
Previous Topic: Performance Tuning in SQL queries and PL/SQL
Next Topic: slow perf with Bulk collect and FORALL
Goto Forum:
  


Current Time: Sat Dec 03 04:07:30 CST 2016

Total time taken to generate the page: 0.19466 seconds