Home » RDBMS Server » Performance Tuning » Help: Identifying Problem in the query
| Help: Identifying Problem in the query [message #269563] |
Sun, 23 September 2007 08:59  |
kumarsz
Messages: 180
Registered: March 2005
Location: canada
|
Senior Member |
|
|
Hi,
I am using Oracle 9i R2 (RAC) on Linux
Following is Report query which executes for alomst 18-20 hours
It scans wf_rep_item_dtl more than 40 times
I know if field in outer query is referred in Inner query then such thing is possible (co-related query)
However here the situation is reverse.
Is it because workq being referred in Outer query?
Apologies for not being able to provide Tkprof for the same.
{another related question
I tried setting statistics_level=ALL and queried v$sql_plan_statistics_all to get the details but got 'no rows returned', does it needs any additional settings as well? }
SELECT
cup.strBrandcd strBrand,
Wf_Com_Reports_Pkg.wf_get_area_fnc(cup.struserid) area,
cup.strteamcd team,
cup.struserid struser,
Om_Get_User_Name_Fnc(cup.struserid) username,
pol_nbr policynbr ,
workq qname,
workqtype qtype,
DECODE(workQType,1,Om_Get_User_Name_Fnc(workq) || ' Personal Queue', 2,
(SELECT StrWorkQdesc FROM wf_work_queue wwq WHERE wwq.strWorkQname=WorkQ) ) qdesc,
TRUNC(a.dtCreated) Dateclosed,
TO_CHAR(a.dtcreated,'HH24:MI') timeclosed,
(SELECT COUNT(*) FROM wF_rep_item_dtl wrid WHERE wrid.strCreatedby=cup.struserid AND wrid.nevent=2 AND TRUNC(wrid.dtcreated)=TRUNC(SYSDATE) AND record_type_id != 'SYSINCALLS') "Count",
action action
FROM
(SELECT * FROM
(
SELECT strItemKey, strCreatedBy , dtCreated, action ,
row_number() OVER (PARTITION BY strCreatedBy ORDER BY dtCreated desc) ranker,
workq, workqtype , pol_nbr
FROM
(
SELECT strItemKey, strCreatedBy , dtCreated, 'Closed' action ,
DECODE(record_type_id,'1DEF',to_user_id,record_type_id) workq ,
DECODE(record_type_id,'1DEF',1,2) workqtype , pol_nbr
FROM wf_rep_item_dtl
WHERE nevent=2
AND recorD_type_id !='SYSINCALLS'
UNION ALL
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)
)
WHERE ranker=1 ) a ,
com_user_profile cup
WHERE
a.strCreatedBy=cup.strUserId
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2078554 Card=2026131
Bytes=8641448715)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'WF_WORK_QUEUE' (Cost=2 C
ard=1 Bytes=37)
2 1 INDEX (UNIQUE SCAN) OF 'SYS_C004481' (UNIQUE) (Cost=1 Ca
rd=1)
3 0 SORT (AGGREGATE)
4 3 TABLE ACCESS (FULL) OF 'WF_REP_ITEM_DTL' (Cost=50769 Car
d=53 Bytes=1590)
5 0 MERGE JOIN (Cost=2078554 Card=2026131 Bytes=8641448715)
6 5 VIEW (Cost=2078544 Card=2026131 Bytes=8604978357)
7 6 WINDOW (SORT PUSHED RANK) (Cost=2078544 Card=2026131 B
ytes=8578638654)
8 7 VIEW (Cost=62422 Card=2026131 Bytes=8578638654)
9 8 UNION-ALL
10 9 TABLE ACCESS (FULL) OF 'WF_REP_ITEM_DTL' (Cost=5
0807 Card=1952275 Bytes=80043275)
11 9 TABLE ACCESS (FULL) OF 'WF_REP_EVENTS' (Cost=116
15 Card=73856 Bytes=2289536)
12 5 SORT (JOIN) (Cost=11 Card=1249 Bytes=22482)
13 12 TABLE ACCESS (FULL) OF 'COM_USER_PROFILE' (Cost=10 Car
d=1249 Bytes=22482)
Thanks and Regards
Pratap
|
|
|
|
| Re: Help: Identifying Problem in the query [message #269564 is a reply to message #269563] |
Sun, 23 September 2007 09:13  |
kumarsz
Messages: 180
Registered: March 2005
Location: canada
|
Senior Member |
|
|
Hi,
I was little suspicious about the following part of the 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
So i changed the co-related query to join as following
SELECT wre.strItemKey, wre.strUser, wre.dtCreated, 'Transfer' action,
wre.strToWorkQname, wre.nToWorkQType,wrid.pol_nbr
FROM WF_REP_ITEM_DTL wrid,
wf_rep_events wre
WHERE wrid.strItemKey(+)=wre.strITemKey
AND wrid.nevent(+)=1
and wre.neventtype=3
But in the Explain Plan it shows huge cost and i can not execute and test the execution time.
Is it possible that the join query is better than co-related but still showing more cost since join takes HASH Join path and Co-Related takes Nested Loop path and while showing cost may not be taking into acount the Iterations?
I may be wrong here (since Optimer can calculate Consistent reads over Iterations, But does it do so?) but needs advice here?
And at last, is the Hot part in queries causing multiple scan of wf_rep_item_table?
Thanks and Regards,
Pratap
|
|
|
|
|
|
| Re: Help: Identifying Problem in the query [message #269568 is a reply to message #269563] |
Sun, 23 September 2007 09:47 |
kumarsz
Messages: 180
Registered: March 2005
Location: canada
|
Senior Member |
|
|
Hi
Thanks for quick reply
following are the Optimizer Setting Details
NAME_COL_PLUS_SHOW_PARAM TYPE VALUE_COL_
-------------------------------------------------- --------------- ----------
optimizer_dynamic_sampling integer 1
optimizer_features_enable string 9.2.0
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 80
optimizer_max_permutations integer 2000
optimizer_mode string CHOOSE
Regards,
Pratap
|
|
|
|
|
|
| Re: Help: Identifying Problem in the query [message #269571 is a reply to message #269563] |
Sun, 23 September 2007 10:18 |
kumarsz
Messages: 180
Registered: March 2005
Location: canada
|
Senior Member |
|
|
Hi,
What you suggested is perfectly Alright.
However, being a Live System its really scary to doo this
However, I tried this with values 30, 20 and 10 and got absolutely no difference in plan.
Thanks and Regards,
Pratap
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Help: Identifying Problem in the query [message #269624 is a reply to message #269577] |
Sun, 23 September 2007 22:24 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
I took the liberty of running your SQL through the formatter.
SELECT Cup.strBrandcd strBrand,
wf_com_Reports_pkg.Wf_get_area_fnc(Cup.stRuseRid) Area,
Cup.strTeamcd Team,
Cup.stRuseRid stRuser,
Om_get_user_name_fnc(Cup.stRuseRid) UserName,
pol_nbr Policynbr,
Workq qName,
WorkqType qType,
DECODE(WorkqType,1,Om_get_user_name_fnc(Workq)
||' Personal Queue',
2,(SELECT strWorkqdesc
FROM wf_Work_Queue wwq
WHERE wwq.strWorkqName = Workq)) qdesc,
Trunc(a.dtCreated) DateClosed,
To_char(a.dtCreated,'HH24:MI') TimeClosed,
(SELECT COUNT(* )
FROM wf_rep_Item_dtl wRid
WHERE wRid.strCreatedBy = Cup.stRuseRid
AND wRid.nEvent = 2
AND Trunc(wRid.dtCreated) = Trunc(SYSDATE)
AND Record_Type_Id != 'SYSINCALLS') "Count",
ACTION ACTION
FROM (SELECT *
FROM (SELECT sTritemKey,
strCreatedBy,
dtCreated,
ACTION,
Row_number() OVER(PARTITION BY strCreatedBy ORDER BY dtCreated DESC) Ranker,
Workq,
WorkqType,
pol_nbr
FROM (SELECT sTritemKey,
strCreatedBy,
dtCreated,
'Closed' ACTION,
DECODE(Record_Type_Id,'1DEF',To_User_Id,
Record_Type_Id) Workq,
DECODE(Record_Type_Id,'1DEF',1,
2) WorkqType,
pol_nbr
FROM wf_rep_Item_dtl
WHERE nEvent = 2
AND Record_Type_Id != 'SYSINCALLS'
UNION ALL
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))
WHERE Ranker = 1) a,
com_User_Profile Cup
WHERE a.strCreatedBy = Cup.stRuseRid
Looking at this query, we see two scalar subqueries in the SELECT clause:
(SELECT COUNT(* )
FROM wf_rep_Item_dtl wRid
WHERE wRid.strCreatedBy = Cup.stRuseRid
AND wRid.nEvent = 2
AND Trunc(wRid.dtCreated) = Trunc(SYSDATE)
AND Record_Type_Id != 'SYSINCALLS') "Count",
(SELECT strWorkqdesc
FROM wf_Work_Queue wwq
WHERE wwq.strWorkqName = Workq)
The important thaing to know about scalar subqueries in the SELECT clause is that they run a separate execution for EVERY ROW RETURNED. eg. 1M rows returned = 1M SQLs executed!!!
The query on wf_Work_Queue is not such a huge problem because it is using a unique scan on a unique index.
The query on wf_rep_Item_dtl is a big problem - it is a full table scan. So if you return 1000 rows, it will perform 1000 full scans of wf_rep_Item_dtl.
Scalar subqueries in the SELECT clause are best avoided altogether. Pretend they don't exist! Construct an equivalent statement by joining the table into the FROM clause of the main query.
Ross Leishman
|
|
|
|
| Re: Help: Identifying Problem in the query [message #269817 is a reply to message #269624] |
Mon, 24 September 2007 14:02 |
mson77
Messages: 208
Registered: August 2007
Location: Brazil
|
Senior Member |
|
|
Hello Ross,
Excuse me for any inconvenience.
I would like to learn a bit from you if you do not mind.
Regarding the execution plan showed by pratapsz... I formatted the execution plan to become more easy to understand.
Below is the execution plan:
[0]| SELECT STATEMENT Optimizer=CHOOSE (Cost=2078554 Card=2026131 Bytes=8641448715)
|
| <------- [1] | TABLE ACCESS (BY INDEX ROWID) OF 'WF_WORK_QUEUE' (Cost=2 Card=1 Bytes=37)
| |
| | <-------- [2] INDEX (UNIQUE SCAN) OF 'SYS_C004481' (UNIQUE) (Cost=1 Card=1)
|
|
| <-------- [3]| SORT (AGGREGATE)
| |
| | <-------- [4] TABLE ACCESS (FULL) OF 'WF_REP_ITEM_DTL' (Cost=50769 Card=53 Bytes=1590)
|
|
| <-------- [5]| MERGE JOIN (Cost=2078554 Card=2026131 Bytes=8641448715)
| |
| | <-------- [6]| VIEW (Cost=2078544 Card=2026131 Bytes=8604978357)
| |
| | <---- [7]| WINDOW (SORT PUSHED RANK) (Cost=2078544 Card=2026131 Bytes=8578638654)
| |
| | <---- [8]| VIEW (Cost=62422 Card=2026131 Bytes=8578638654)
| |
| | <---- [9]| UNION-ALL
| |
| | <--- [10] TABLE ACCESS (FULL) OF 'WF_REP_ITEM_DTL' (Cost=50807 Card=1952275 Bytes=80043275)
| |
| | <--- [11] TABLE ACCESS (FULL) OF 'WF_REP_EVENTS' (Cost=11615 Card=73856 Bytes=2289536)
|
|
|
|
| <--------- [12]| SORT (JOIN) (Cost=11 Card=1249 Bytes=22482)
| |
| | <---- [13] TABLE ACCESS (FULL) OF 'COM_USER_PROFILE' (Cost=10 Card=1249 Bytes=22482)
Then I can see the critical path in terms of cost.
In my thought it comes from [8] with cardinality Card=2026131 and flows toward with "Cost=2078544 Card=2026131" as below:
[7] --> [6] --> [5] --> [0]
Would you mind to write something regarding the point [8]?
I mean... itens [11],[10],[9] those go to [8].
Thank you very much!
Regards,
mson77
|
|
|
|
| Re: Help: Identifying Problem in the query [message #270090 is a reply to message #269563] |
Tue, 25 September 2007 12:34 |
kumarsz
Messages: 180
Registered: March 2005
Location: canada
|
Senior Member |
|
|
Ross,
"
The query on wf_rep_Item_dtl is a big problem - it is a full table scan. So if you return 1000 rows, it will perform 1000 full scans of wf_rep_Item_dtl.
"
Although i am unable to avoid the Scalar subquery for count(*) in this paericular query ,
it is really helpful for me that you have spotted the exact problem in the query
Thank you very much
Regards,
Pratap
|
|
|
|
| Re: Help: Identifying Problem in the query [message #270113 is a reply to message #270090] |
Tue, 25 September 2007 15:11 |
mson77
Messages: 208
Registered: August 2007
Location: Brazil
|
Senior Member |
|
|
Hello pratapsz,
In my last message I talked about huge loading block: item[8]
As you can see: item [8] derives from item [9].
And item [9] is composed by 02 items: [10] and [11].
Below is the block [10] plus [11]:
<--- [10] TABLE ACCESS (FULL) OF 'WF_REP_ITEM_DTL' (Cost=50807 Card=1952275 Bytes=80043275)
<--- [11] TABLE ACCESS (FULL) OF 'WF_REP_EVENTS' (Cost=11615 Card=73856 Bytes=2289536)
==========================================================
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
But let's compare with the initial select statement:
SELECT Cup.strBrandcd strBrand,
wf_com_Reports_pkg.Wf_get_area_fnc(Cup.stRuseRid) Area,
Cup.strTeamcd Team,
Cup.stRuseRid stRuser,
Om_get_user_name_fnc(Cup.stRuseRid) UserName,
pol_nbr Policynbr,
Workq qName,
WorkqType qType,
DECODE(WorkqType,1,Om_get_user_name_fnc(Workq) ||' Personal Queue', 2,
(SELECT strWorkqdesc
FROM wf_Work_Queue wwq
WHERE wwq.strWorkqName = Workq
)
) qdesc,
Trunc(a.dtCreated) DateClosed,
To_char(a.dtCreated,'HH24:MI') TimeClosed,
(SELECT COUNT(* )
FROM wf_rep_Item_dtl wRid
WHERE wRid.strCreatedBy = Cup.stRuseRid
AND wRid.nEvent = 2
AND Trunc(wRid.dtCreated) = Trunc(SYSDATE)
AND Record_Type_Id != 'SYSINCALLS'
)
"Count",
ACTION ACTION
FROM xxxxxxx......
According to Ross... we have to take care about scalar subqueries.
Regarding the scalar subqueries below:
(SELECT COUNT(* )
FROM wf_rep_Item_dtl wRid
WHERE wRid.strCreatedBy = Cup.stRuseRid
AND wRid.nEvent = 2
AND Trunc(wRid.dtCreated) = Trunc(SYSDATE)
AND Record_Type_Id != 'SYSINCALLS'
)
"Count",
and (SELECT strWorkqdesc
FROM wf_Work_Queue wwq
WHERE wwq.strWorkqName = Workq
)
Perfect why?
Because scalar subqueries must return just only one value or null. And these 02 scalar subqueries return one value each one.
The first select ==> count(*) ... one value;
The second select ==> you should guarantee just one value.
But... when I see the block composed by [10] and [11]... there is scalar subquery there... Please go back and realize.
And then... this usage of scalar subquery is mortal. Because it does not return just ONE value as result of the select command (item [10]).
I think that this is the cause of your problem.
Regards,
mson77
[Updated on: Tue, 25 September 2007 18:21] Report message to a moderator |
|
|
|
| Re: Help: Identifying Problem in the query [message #270132 is a reply to message #270113] |
Tue, 25 September 2007 18:43 |
mson77
Messages: 208
Registered: August 2007
Location: Brazil
|
Senior Member |
|
|
My recommendation is:
==> rewrite this code below...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
without using scalar subquery.
Regards,
mson77
|
|
|
|
| Re: Help: Identifying Problem in the query [message #270150 is a reply to message #270132] |
Tue, 25 September 2007 22:58 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Going back a few posts:
| mson77 wrote | Would you mind to write something regarding the point [8]?
I mean... itens [11],[10],[9] those go to [8].
|
Step 8 (and indeed steps 9, 10, and 11) are executed only once. Yes 2M rows is a reasonable hit, but it only hits you once, and full scans are lightning fast.
Steps 1,2,3, and 4 show a small cardinality, true, but you need to know how many times they will be executed. In this case, about 2M.
Cardinality does not tell us how many rows will be hit by a step in the plan, just how many will be hit by A SINGLE EXECUTION of that step in the plan.
I wrote something about this problem in this article, but I think I omitted the issue of scalar sub-queries in the SELECT. I might revisit that article some day.
Ross Leishman
|
|
|
|
| Re: Help: Identifying Problem in the query [message #270189 is a reply to message #270150] |
Wed, 26 September 2007 01:28 |
mson77
Messages: 208
Registered: August 2007
Location: Brazil
|
Senior Member |
|
|
Hello Ross,
Thank you very much for your kind reply and information.
Now I understood perfectly what you say.
Regarding the code below (just an example from me):
SELECT c.id ,
c.name ,
(SELECT max(idate)
FROM invoice_details i
WHERE c.id = i.customer_id
)
FROM customers c;
For each row from table customers... the scalar subquery is executed to provide one singular information.
Based on this thought... should the code on items [10] and [11] as shown below... be rewritten?
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
Thank you again!
Regards,
mson77
|
|
|
|
| Re: Help: Identifying Problem in the query [message #270232 is a reply to message #269563] |
Wed, 26 September 2007 03:59 |
kumarsz
Messages: 180
Registered: March 2005
Location: canada
|
Senior Member |
|
|
Hello mson77,
Thanks for your follow up and advice
If you see the message 269564 i have changed this co-related part there.
While i was monitoring this query being executed, i could see that the execution time was in hours since wf_rep_item_dtl was being scanned N number of times.
As adviced by Ross i am pretty sure it is the following line which is culprit
SELECT COUNT(*) FROM wF_rep_item_dtl wrid WHERE wrid.strCreatedby=cup.struserid AND wrid.nevent=2 AND TRUNC(wrid.dtcreated)=TRUNC(SYSDATE) AND record_type_id != 'SYSINCALLS'
Althugh this particular line (executing many times) returning single value each time, we are retriving too much data in the main query and our system is not god for single block I/O (consiedering ora parameters as well) i was reluctant to add indexes.
However, adding composite index on
TRUNC(wrid.dtcreated),nevent,,record_type_id made the query execute in 20 minutes which was taking more than 12 hrs
This also proved that the 'SELECT COUNT(*) ' was the problem'
Thanks and Regards,
Pratap
|
|
|
|
| Re: Help: Identifying Problem in the query [message #270319 is a reply to message #270232] |
Wed, 26 September 2007 09:14 |
mson77
Messages: 208
Registered: August 2007
Location: Brazil
|
Senior Member |
|
|
Hello pratapsz,
Thank you for your feedback.
Thank you for writing here your results and thoughts.
Yes... I am absolutely sure about Ross advice too. Starting from his comment on message #269624... I googled for "scalar subqueries" to understand it in deep. And as far as I read about this issue I realize what Ross said. And also I did not find anyone who says clearly: "Pretend they don't exist."
Why?
Regarding my simple sql example using scalar subquery on message #270189... we can analyze 02 points:
a) number of rows from the table customers;
b) time to execute the scalar subquery.
Below we can understand how cost evolves comparing two cases:
case 1: we don't have the scalar subquery
==> the select just return each row of the table customers and that is all.
case 2: we have the scalar subquery
==> for each row returned... oracle will execute the scalar subquery. But... suppose each execution of this scalar subquery takes 01 full day long. Then our main select would take +(N-days) to complete... where N=number of rows in the table customers.
But... if the scalar subquery takes 01 second instead... then our main select would take +(N-seconds).
I learned the following: "Pretend they don't exist."
Thank you Ross and pratapsz... for clarifying my thought and guidance!!
Pratapsz... would you mind to let me know the last state? I mean about sql with execution plan as you showed in your first message. Also your comments about are valuable.
Regards,
mson77
|
|
|
|
| Re: Help: Identifying Problem in the query [message #270438 is a reply to message #270319] |
Wed, 26 September 2007 21:48 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
I think we're missing the point here. My advice was to pretend they don't exist. The logical extension is to rewrite the query using joins. The OP seems unwilling to do this: creating an index so much easier! But it will still deliver sub-optimal performance in almost all cases.
Just so we're clear, this is what I had in mind:
SELECT Cup.strBrandcd strBrand,
wf_com_Reports_pkg.Wf_get_area_fnc(Cup.stRuseRid) Area,
Cup.strTeamcd Team,
Cup.stRuseRid stRuser,
Om_get_user_name_fnc(Cup.stRuseRid) UserName,
pol_nbr Policynbr,
Workq qName,
WorkqType qType,
DECODE(WorkqType,1,Om_get_user_name_fnc(Workq)
||' Personal Queue',
2,(SELECT strWorkqdesc
FROM wf_Work_Queue wwq
WHERE wwq.strWorkqName = Workq)) qdesc,
Trunc(a.dtCreated) DateClosed,
To_char(a.dtCreated,'HH24:MI') TimeClosed,
cnt."Count",
ACTION ACTION
FROM (SELECT *
FROM (SELECT sTritemKey,
strCreatedBy,
dtCreated,
ACTION,
Row_number() OVER(PARTITION BY strCreatedBy ORDER BY dtCreated DESC) Ranker,
Workq,
WorkqType,
pol_nbr
FROM (SELECT sTritemKey,
strCreatedBy,
dtCreated,
'Closed' ACTION,
DECODE(Record_Type_Id,'1DEF',To_User_Id,
Record_Type_Id) Workq,
DECODE(Record_Type_Id,'1DEF',1,
2) WorkqType,
pol_nbr
FROM wf_rep_Item_dtl
WHERE nEvent = 2
AND Record_Type_Id != 'SYSINCALLS'
UNION ALL
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))
WHERE Ranker = 1) a,
com_User_Profile Cup
, (SELECT strCreatedBy, COUNT(* ) AS "Count"
FROM wf_rep_Item_dtl wRid
WHERE wRid.nEvent = 2
AND Trunc(wRid.dtCreated) = Trunc(SYSDATE)
AND Record_Type_Id != 'SYSINCALLS'
GROUP BY strCreatedBy
) cnt
WHERE a.strCreatedBy = Cup.stRuseRid
AND cnt.strCreatedBy = Cup.stRuseRid
I only changed one instance - I leave the other two as an exercise.
Ross Leishman
|
|
|
|
| Re: Help: Identifying Problem in the query [message #270545 is a reply to message #269563] |
Thu, 27 September 2007 08:03 |
kumarsz
Messages: 180
Registered: March 2005
Location: canada
|
Senior Member |
|
|
Hello mson77,
Thanks for your interest and contribution is this thread.
Here are the latest details
After Creating index CREATE INDEX HSASYS.WRID_EVENT_RECID_DT ON HSASYS.WF_REP_ITEM_DTL
(NEVENT, RECORD_TYPE_ID, TRUNC("DTCREATED"))
The current execution and subsequent plan is as following
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.01 2 6 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 37 1063.94 1286.99 388986 8001139 23 528
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 39 1063.96 1287.01 388988 8001145 23 528
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 37 (HSASYS)
Rows Row Source Operation
------- ---------------------------------------------------
528 SORT AGGREGATE
2159 TABLE ACCESS BY INDEX ROWID WF_REP_ITEM_DTL
1161072 INDEX RANGE SCAN WRID_EVENT_RECID_DT (object id 284226)
528 MERGE JOIN
534 VIEW
1214 WINDOW SORT PUSHED RANK
2062469 VIEW
2062469 UNION-ALL
1959769 TABLE ACCESS FULL WF_REP_ITEM_DTL
102700 TABLE ACCESS FULL WF_REP_EVENTS
528 SORT JOIN
1250 TABLE ACCESS FULL COM_USER_PROFILE
Ross- >
I got busy into other things and could try more on this although after your message mentioning Scalar Subquery, i got correct direction of 'what needs to be done' for this query.
Many Thanks for suggesting the changes.
I will see to complete the remaining exercise and share the results in this thread.
Thanks and Regards,
Pratap
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 13:58:16 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
