| Help :Getting Inner query executions from v$sql_plan_statistics_all [message #270311] |
Wed, 26 September 2007 08:57  |
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 #270317 is a reply to message #270311] |
Wed, 26 September 2007 09:09  |
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 #270352 is a reply to message #270311] |
Wed, 26 September 2007 10:51 |
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
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
