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 Go to next message
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 Go to previous messageGo to next message
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 #269565 is a reply to message #269564] Sun, 23 September 2007 09:41 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello pratapsz,

show parameter optimizer;


Would you mind let us know?
Regards,


mson77
Re: Help: Identifying Problem in the query [message #269568 is a reply to message #269563] Sun, 23 September 2007 09:47 Go to previous messageGo to next message
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 #269570 is a reply to message #269568] Sun, 23 September 2007 10:01 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello pratapsz,

I will tell you what I see from your scenario:

  • many full scan with high cost


And... your:
optimizer_index_cost_adj                           integer         80

Please read link below:
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams144.htm

If I were you... I would change setting just for my local session and check the explain plan.
You can start from "1" in your experiment.

Please take care because this parameter affects the whole database/sid when changed in spfile.

Regards,


mson77

Sorry: your database is 9iR2
http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm#47016

[Updated on: Sun, 23 September 2007 10:17]

Report message to a moderator

Re: Help: Identifying Problem in the query [message #269571 is a reply to message #269563] Sun, 23 September 2007 10:18 Go to previous messageGo to next message
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 #269572 is a reply to message #269563] Sun, 23 September 2007 10:19 Go to previous messageGo to next message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member

I tried it at session level ONLY
Re: Help: Identifying Problem in the query [message #269573 is a reply to message #269572] Sun, 23 September 2007 10:26 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello pratapsz,

Well... this is the point.

1) This parameter affects the whole system/database behavior;
2) You should have knowledge of the whole system;

Maybe you can check some other critical tasks/functions.
In general, I guess the critical tasks are reports or similar.

Regards,


mson77


optimizer_max_permutations                         integer         2000

Please read:
http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm#47021

Regards,


mson77

[Updated on: Sun, 23 September 2007 10:33]

Report message to a moderator

Re: Help: Identifying Problem in the query [message #269574 is a reply to message #269563] Sun, 23 September 2007 10:40 Go to previous messageGo to next message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member

Many thanks

I will go thr' the link

Meanwhile please suggest me on my query on v$sql_plan_statistics as well

Regards,
pratap
Re: Help: Identifying Problem in the query [message #269577 is a reply to message #269574] Sun, 23 September 2007 10:57 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello pratapsz,

Please open a new topic.
Provide as much as information possible.
There will be new readers reading your NEW thread.

Regards,


mson77
Re: Help: Identifying Problem in the query [message #269624 is a reply to message #269577] Sun, 23 September 2007 22:24 Go to previous messageGo to next message
rleishman
Messages: 3724
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",
and
(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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
        )
These scalar subqueries usage are perfect.
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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;
From the code above I can see how scalar subquery works.
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
Is this code above as bad as the other 2 scalar subqueries spotted by you?

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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous message
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
Previous Topic: multiple size of db_block
Next Topic: ORA-12560:TNS protocol adapter Error while upgrading oracle 8i to Oracle 9i
Goto Forum:
  


Current Time: Thu Dec 08 20:10:52 CST 2016

Total time taken to generate the page: 0.10451 seconds