Home » SQL & PL/SQL » SQL & PL/SQL » Optimization in the where clause (Oracle 10g)
Optimization in the where clause [message #421425] Tue, 08 September 2009 04:51 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
I have been given to optimize a query which goes like this. Its a part of the procedure where some parameter are being sent and used in this query. The query executes very slowly when number of concurrent users increases by Mid day.
I am not very much sure where to start ...so want your suggestions. The data is very huge so partition is done and only first 3 days data resides in first partition. So the main criteria here is search by Date. The column CLACTDATE is searched for 2 parameters 'CallFromDate' and 'CallToDate'. But I am not sure if the usage in where clause is done correctly or if we can use it in some inner query......!!

Please suggest something so that I can work on the optimization part. I have made some small changes in the below query so its might not look correct. Please advice me something so that I can work on te improvement part.

sqlstr := 'SELECT A.CLCALLSTATUS FROM  CLLOG A ' ||           
          ' INNER JOIN USER_CLTYPE B ON (A.CLCALLTYPE=B.CALLTYPE) ' ||            
          ' INNER JOIN CALLMASTER C ON ' || 
          ' (B.CALLNAME=C.CALLNAME AND A.CLCALLTYPE=C.CALLTYPE AND A.CLCALLACTION = C.CALLACTION) ' ||           
          '          INNER JOIN (   ' ||       
          '             SELECT F.CITYCODE, F.CITYDESC,CUSTBRNAME,CUSTCODE,CUSTBRCODE,COMPCODE,OFFCODE ' ||           
          '               FROM MCUSTOMERBRANCH D ,MCITY F ' ||          
          '              WHERE D.CITYCODE=F.CITYCODE ' ||         
          '                  )P ON (A.CLCUSTCD=P.CUSTCODE AND A.CLCUSTBRCD=P.CUSTBRCODE ' ||
          '                         AND A.COMPCODE=P.COMPCODE AND A.CLOFFCD=P.OFFCODE) ' ||           
          '  LEFT OUTER JOIN '||           
          '    (          '||
          '     SELECT R.RTCODE,Q.FIX,Q.CLCALLNO,Q.CLACTCD,Q.CLCUSTCD,Q.CLCUSTBRCD, '|| 
          '            Q.CLOFFCD,Q.COMPCODE,Q.CLGENDATE, ' ||          
          '            Q.CLCUSTCUSTCD FROM CLEXEC Q,MROUTE R  ' ||        
          '     WHERE  Q.RTCODE=R.RTCODE AND Q.COMPCODE=R.COMPCODE AND Q.CLOFFCD=R.OFFCODE    ' ||      
          '    )X ON (A.CLCALLNO=X.CLCALLNO AND A.CLACTCD=X.CLACTCD  '|| 
          '     and A.CLCUSTCD=X.CLCUSTCD AND A.CLCUSTBRCD=X.CLCUSTBRCD   ' ||       
          '     AND A.CLOFFCD=X.CLOFFCD AND A.COMPCODE=X.COMPCODE  ' ||
          '     AND A.CLGENDATE=X.CLGENDATE AND (A.CLCUSTCUSTCD=X.CLCUSTCUSTCD OR '||
          '     A.CLCUSTCUSTCD is null))' || argClientList  ||
          '  WHERE to_date('||A.CLACTDATE||','''dd/mm/yyyy''') ' ||
          ' BETWEEN to_date('||CallFromDate||','''dd/mm/yyyy''') and to_date('|| CallToDate ||','''dd/mm/yyyy''') ' ||
          '  AND A.COMPCODE = '''||CompCode ||''' AND B.USERID='''|| UserID ||''' AND B.CALLNAME IN ('||CallTypeCode ||')' ||           
          '  AND A.CLCUSTBRCD IN ('||CallBank ||') AND P.CITYCODE IN ('||CallCity ||')    ' ||
          '  AND A.CLNATURE IN('||CallFreq ||') AND A.CLCALLSTATUS IN ('||CallStatus ||') AND X.RTCODE IN('||CallRoute ||') AND ' ||           
          '  A.LoginAuthUserId is not null AND A.ClOffCd IN ('|| CLOffCd ||') AND A.ClCustCd IN ('||CLCustCd ||') ' ||          
          '  GROUP BY A.CLCALLSTATUS ORDER BY A.CLCALLSTATUS '



Regards,
Mahi
Re: Optimization in the where clause [message #421426 is a reply to message #421425] Tue, 08 September 2009 04:59 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
What's the datatype of CLACTDATE?
Re: Optimization in the where clause [message #421427 is a reply to message #421425] Tue, 08 September 2009 04:59 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Why is this being done in dynamic SQL?
Re: Optimization in the where clause [message #421432 is a reply to message #421427] Tue, 08 September 2009 05:05 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
datatype of CLACTDATE is varchar.

Its done dynamically as the query is prepared based on dynamic values.

I have to rewrite the query to improve performance.... I am not an expert in this...so seeking your advice.

Please help me on this,
Mahi
Re: Optimization in the where clause [message #421433 is a reply to message #421432] Tue, 08 September 2009 05:07 Go to previous messageGo to next message
ThomasG
Messages: 3186
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
virmahi wrote on Tue, 08 September 2009 12:05
Its done dynamically as the query is prepared based on dynamic values.



You can use dynamic values in normal SQL or PL/SQL, you know.
Re: Optimization in the where clause [message #421434 is a reply to message #421433] Tue, 08 September 2009 05:15 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Yes, I know it can be done in a pl/sql. I have written a part of the procedure as its too big to paste here. There are some if...else condition and based on some another dynamic values one of the sql query which is dynamic is executed. All the sql queries are more or less same...so if this one is optimized it will help.

Please give some suggestions in case you find that can be changed. Then I will test and see if it helps.
Re: Optimization in the where clause [message #421436 is a reply to message #421425] Tue, 08 September 2009 05:16 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
I stongly suggest you make CLACTDATE a date. That way oracle can use an index to do the between - which might help a lot.
After that have a look at this: varying in-list
and see if you can get rid of the dynamic sql
Re: Optimization in the where clause [message #421437 is a reply to message #421434] Tue, 08 September 2009 05:18 Go to previous messageGo to next message
ThomasG
Messages: 3186
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Read the sticky post in the performance tuning section.

Then have a look at the execution plan and/or the TKPROF output of a session trace.

That will most likely show you where the bottleneck is.
Re: Optimization in the where clause [message #421439 is a reply to message #421436] Tue, 08 September 2009 05:26 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Yes I will make it a datetype.

Actually there is a dateformat dynamically passed to the procedure which I removed from the sql query to simplify.

So I have to do in anyway the to_date and to_char function to convert the date format type as supplied by the parameter.

[CODE]WHERE to_date('||A.CLACTDATE||','''dd/mm/yyyy''') ' ||[/CODE

Here I hard coded 'dd/mm/yyyy' .... it comes as 'SQLDateFormat' parameter from the procedure.

Do you suggest something for the outer joins?

[Updated on: Tue, 08 September 2009 05:27]

Report message to a moderator

Re: Optimization in the where clause [message #421441 is a reply to message #421439] Tue, 08 September 2009 05:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What is the table partitioned on?
Re: Optimization in the where clause [message #421444 is a reply to message #421425] Tue, 08 September 2009 06:01 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Quote:
The query executes very slowly when number of concurrent users increases by Mid day.


Without using bind variables, that was bound to happen. Your dynamic query will be hard parsed every time.

[Updated on: Tue, 08 September 2009 06:02]

Report message to a moderator

Re: Optimization in the where clause [message #421447 is a reply to message #421444] Tue, 08 September 2009 06:15 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
The partition is done on loginuserid and clactdate on table cllog.

There are daily updates and inserts of more than 1 lacs records.
The data needed is mainly from latest 3 days.... the rest data are rarely used...so partition is done on latest 3 days.


Please tell me what to do in this case....

I can't make big changes like removing all dynamic things...and if I can then how to do that...afraid if it would take much long time.....
Re: Optimization in the where clause [message #421448 is a reply to message #421447] Tue, 08 September 2009 06:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If your table is partitioned on LOGINUSERID and CLACTDATE then you've got problems:

1) You're not specifying a LOGINUSERID in the query, so it's going to have to go and loook through a large range of partitions.

2) CLACTDATE (as used in your table and the partition) is a Varchar2 - your query only ever refers to it inside a TO_DATE funtion, so I suspect that your query is just going to look at all the partitions in the table, not the one for the latest 3 days.
Re: Optimization in the where clause [message #421449 is a reply to message #421425] Tue, 08 September 2009 06:35 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
Also the outer-join appears to be pointless - you're not selecting anything from it
Re: Optimization in the where clause [message #421450 is a reply to message #421448] Tue, 08 September 2009 06:41 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
I am so sorry, its my mistake.... I have been given this work today only...the datatype for the column CLACTDATE is date type only. And the partition done only on the column CLACTDATE on four tables where data is maximum. Its also partitioned on the table CLlog.

Please suggest accordingly. I am very sorry for the wrong information.
Re: Optimization in the where clause [message #421452 is a reply to message #421450] Tue, 08 September 2009 06:44 Go to previous messageGo to next message
ThomasG
Messages: 3186
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
ThomasG wrote on Tue, 08 September 2009 12:18
Read the sticky post in the performance tuning section.

Then have a look at the execution plan and/or the TKPROF output of a session trace.

That will most likely show you where the bottleneck is.


Re: Optimization in the where clause [message #421454 is a reply to message #421450] Tue, 08 September 2009 06:50 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
virmahi wrote on Tue, 08 September 2009 12:41
I am so sorry, its my mistake.... I have been given this work today only...the datatype for the column CLACTDATE is date type only. And the partition done only on the column CLACTDATE on four tables where data is maximum. Its also partitioned on the table CLlog.

Please suggest accordingly. I am very sorry for the wrong information.


Then remove the to_date on CLACTDATE and see what difference it makes.
Then follow ThomasG's instructions.
Re: Optimization in the where clause [message #421455 is a reply to message #421450] Tue, 08 September 2009 06:50 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Quote:
the datatype for the column CLACTDATE is date type only

Then this:
to_date('||A.CLACTDATE||','''dd/mm/yyyy''') '...
Is just plain daft. Why would you need to write code to convert a date into a ... er... DATE. (I realise that you're not the one who wrote this, I'm not getting at you.) Lose the date conversions around the A.CLACTDATE columns wherever you see them. Then do as others have already said and
Quote:
Read the sticky post in the performance tuning section.

Then have a look at the execution plan and/or the TKPROF output of a session trace.

That will most likely show you where the bottleneck is.

Re: Optimization in the where clause [message #421458 is a reply to message #421455] Tue, 08 September 2009 06:58 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Ya its not me who wrote this.
Do you think it will help if somehow I can use the date comparision part in an inner clause and not in the where clause.

I am not sure but feel that if data can be lessened before coming to where clause.

Re: Optimization in the where clause [message #421459 is a reply to message #421458] Tue, 08 September 2009 07:00 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Will you please just follow the suggestions that have been made. Pretty please?
Quote:
Ya its not me who wrote this.
I know, that's why I pointed it out.
Quote:
Do you think it will help if somehow I can use the date comparision part in an inner clause and not in the where clause.

Whast is an inner clause? I know of no inner clause. Why have you not followed the suggestions already given to you?
Quote:
I am not sure but feel that if data can be lessened before coming to where clause.

Why? Based on what? Feeling Nope, that doesn't cut it.

[Updated on: Tue, 08 September 2009 07:03]

Report message to a moderator

Re: Optimization in the where clause [message #421461 is a reply to message #421425] Tue, 08 September 2009 07:05 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Why are you using dynamic query when things can be done through static query?
And also as people said earlier if you are dynamic query also why are you not using bind variables(as the dynamic query will be hard parsed every time)?
Re: Optimization in the where clause [message #421462 is a reply to message #421461] Tue, 08 September 2009 07:09 Go to previous messageGo to next message
ThomasG
Messages: 3186
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Also, there is no need for any sort of "feeling"

Oracle would tell you EXACTLY what it is doing in the query if you would just have a look at the execution plan.

[Updated on: Tue, 08 September 2009 07:10]

Report message to a moderator

Re: Optimization in the where clause [message #421467 is a reply to message #421462] Tue, 08 September 2009 07:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I suspect that this line:
     '     A.CLCUSTCUSTCD is null))' || argClientList  ||
is a large part of the reason why they're using NDS.

Whether or not the need to depends entirely on the contents of that string, as the rest fo the query is fine for normal SQL.
Re: Optimization in the where clause [message #421555 is a reply to message #421467] Wed, 09 September 2009 04:13 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Thanks JRow for looking into it.
My main task is if I can convert it into a static query.

I can convert this
 '     A.CLCUSTCUSTCD is null))' || argClientList  ||
into static part.

But when I am getting values in the IN clause as variables where values comes seperated by single quotes, I am not sure how to make it static.


AND A.CLCUSTBRCD IN ('||CallBank ||') AND P.CITYCODE IN ('||CallCity ||')    ' ||


The value of callbank variable in IN clause will come as 'gsg','ewrgw','ergrg' which is a character value so single quotes are needed.

So not sure how to do this part if decide to go for static query.

[Updated on: Wed, 09 September 2009 04:14]

Report message to a moderator

Re: Optimization in the where clause [message #421556 is a reply to message #421555] Wed, 09 September 2009 04:15 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Have you followed and explored the suggestions already given to you with regards to 'your' (mis)use of the TO_DATE function?
If not, can you explain why not?
Re: Optimization in the where clause [message #421558 is a reply to message #421436] Wed, 09 September 2009 04:17 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
cookiemonster wrote on Tue, 08 September 2009 11:16
After that have a look at this: varying in-list
and see if you can get rid of the dynamic sql

Re: Optimization in the where clause [message #421564 is a reply to message #421556] Wed, 09 September 2009 04:36 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi pablolee,
As I said earlier that I have not started any coding on this procedure but only seeking suggestions so that I can finally give an estimate...so I am not touching the code now.

I have been told if I can convert it into static....

I am attching the explain of the upper part only .... without the where clause...as not sure how to do this. Also the database which I am having has only structure....I will get the day by end of day today.

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 679800974

----------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                    |     1 |   162 |    11  (10)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER             |                    |     1 |   162 |    11  (10)| 00:00:01 |
|   2 |   NESTED LOOPS                  |                    |     1 |   162 |     7  (15)| 00:00:01 |
|   3 |    NESTED LOOPS                 |                    |     1 |   157 |     7  (15)| 00:00:01 |
|   4 |     NESTED LOOPS                |                    |     1 |   132 |     6  (17)| 00:00:01 |
|*  5 |      HASH JOIN                  |                    |     1 |    95 |     5  (20)| 00:00:01 |
|   6 |       TABLE ACCESS FULL         | CLLOG              |     1 |    63 |     2   (0)| 00:00:01 |
|   7 |       TABLE ACCESS FULL         | USER_CLTYPE        |     1 |    32 |     2   (0)| 00:00:01 |
|*  8 |      TABLE ACCESS BY INDEX ROWID| CALLMASTER         |     1 |    37 |     1   (0)| 00:00:01 |
|*  9 |       INDEX UNIQUE SCAN         | PK_CALLMASTER      |     1 |       |     0   (0)| 00:00:01 |
|* 10 |     TABLE ACCESS BY INDEX ROWID | MCUSTOMERBRANCH    |     1 |    25 |     1   (0)| 00:00:01 |
|* 11 |      INDEX UNIQUE SCAN          | PK_MCUSTOMERBRANCH |     1 |       |     0   (0)| 00:00:01 |
|* 12 |    INDEX UNIQUE SCAN            | PK_MCITY           |     1 |     5 |     0   (0)| 00:00:01 |
|  13 |   VIEW                          |                    |     1 |       |     5  (20)| 00:00:01 |
|* 14 |    HASH JOIN                    |                    |     1 |    72 |     5  (20)| 00:00:01 |
|* 15 |     TABLE ACCESS FULL           | CLEXEC             |     1 |    57 |     2   (0)| 00:00:01 |
|  16 |     TABLE ACCESS FULL           | MROUTE             |     1 |    15 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   5 - access("A"."CLCALLTYPE"="B"."CALLTYPE")
   8 - filter("A"."CLCALLACTION"="C"."CALLACTION")
   9 - access("B"."CALLNAME"="C"."CALLNAME" AND "A"."CLCALLTYPE"="C"."CALLTYPE")
  10 - filter("A"."CLCUSTCD"="CUSTCODE" AND "A"."CLOFFCD"="OFFCODE")
  11 - access("A"."CLCUSTBRCD"="CUSTBRCODE" AND "A"."COMPCODE"="COMPCODE")
  12 - access("D"."CITYCODE"="F"."CITYCODE")
  14 - access("Q"."RTCODE"="R"."RTCODE" AND "Q"."COMPCODE"="R"."COMPCODE" AND
              "Q"."CLOFFCD"="R"."OFFCODE")
  15 - filter("A"."CLCALLNO"="Q"."CLCALLNO" AND "A"."CLACTCD"="Q"."CLACTCD" AND
              "A"."CLCUSTCD"="Q"."CLCUSTCD" AND "A"."CLCUSTBRCD"="Q"."CLCUSTBRCD" AND
              "A"."CLOFFCD"="Q"."CLOFFCD" AND "A"."COMPCODE"="Q"."COMPCODE" AND
              "A"."CLGENDATE"="Q"."CLGENDATE" AND ("A"."CLCUSTCUSTCD" IS NULL OR
              "A"."CLCUSTCUSTCD"="Q"."CLCUSTCUSTCD"))

[Updated on: Wed, 09 September 2009 04:38]

Report message to a moderator

Re: Optimization in the where clause [message #421566 is a reply to message #421555] Wed, 09 September 2009 04:38 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
But when I am getting values in the IN clause as variables where values comes seperated by single quotes, I am not sure how to make it static.



use REPLACE to replace single quotes with Null if the value comes like 'ahh' Smile
Re: Optimization in the where clause [message #421571 is a reply to message #421566] Wed, 09 September 2009 04:44 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
I need the single quotes....as they are string values.

In the explain Plan I see some "TABLE ACCESS FULL" on the big tables. Is it a sign to make the change in the query.... please guide me as what is wrong with the query... !!

Regards,

Mahi

Re: Optimization in the where clause [message #421574 is a reply to message #421571] Wed, 09 September 2009 04:46 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
without the where clause


It will go for full table scan only

Sorry for my earlier message use varying inlist as suggested

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:110612348061

[Updated on: Wed, 09 September 2009 04:49]

Report message to a moderator

Re: Optimization in the where clause [message #421575 is a reply to message #421571] Wed, 09 September 2009 04:51 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
You have been guided on several points as to likely problems in the query. Now go forth and implement the suggestions.
Re: Optimization in the where clause [message #421577 is a reply to message #421575] Wed, 09 September 2009 04:59 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
pablolee,
I am still not clear about the suggestions as I am looking how to make it static or whether if I can do it even as if you read the postings... I am still at a point where I have no answer except that I am reading the link given by ayush_anand.

I have not worked on the to_date suggestion because I said that I made small change as there is a dynamic variable for dateformat too which is coming in the query...to make it look simpler I have just drafted that change and pasted here. So please ignore the to_date thing....as its not in the main query.

The main thing I am looking for is mainly if I can change it into static...and how (some suggestions).
Also if the joins can be changed and if I can move the date condition from where clause to some inner query before the Where clause to make the search list smaller.


[Updated on: Wed, 09 September 2009 05:05]

Report message to a moderator

Re: Optimization in the where clause [message #421580 is a reply to message #421425] Wed, 09 September 2009 05:05 Go to previous messageGo to next message
cookiemonster
Messages: 12321
Registered: September 2008
Location: Rainy Manchester
Senior Member
Since this thread is getting quite long I'll be kind and summarise:

1) stop to_dateing dates.
2) rewrite as static sql if possible using the varying in-list technique from ask tom.
3) Have a long hard look at that outer-join because I'm pretty sure it should be an inner-join, but since I don't know exactly what the query is trying to do I can't say for sure.
4) Having done all those, if it's still slow post back here with explain plans (and don't remove the where clause when you do so).
Re: Optimization in the where clause [message #421581 is a reply to message #421577] Wed, 09 September 2009 05:05 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
What exactly are you unclear on? Are you unclear on the fact that you should not be wrapping to_date around A.CLACTDATE?
Do you not understand what bind variables are?
Are you aware that the link that ayush posted has already been posted twice before by cookiemonster? are you having problems with this?
Quote:
as if you read the postings
There is nothing to suggest that you do not understand the suggestions, all I see is you ignoring the suggestions. If you are unclear on something the be specific what is it that you don't understand.
[Responses to points made after virmahi's edit]
Quote:
I have not worked on the to_date suggestion because I said that I made small change as there is a dynamic variable for dateformat too which is coming in the query...to make it look simpler I have just drafted that change and pasted here. So please ignore the to_date thing....as its not in the main query.
No, let's not ignore the to_date thing. It is significant, so let's make a point of talking about the to_date thing. Either you are using to_date or you are not using to_date. If you are then you should not be. If you are not then you have posted a query that does not accurately resemble the query that you are actually running and as such you have wasted a lot of peoples' time. How about you post what you are actually running.
Quote:
The main thing I am looking for is mainly if I can change it into static...and how (some suggestions).

Answered with links several times
Quote:
Also if the joins can be changed and if I can move the date condition from where clause to some inner query before the Where clause to make the search list smaller.

Answered already, look at cookie's posts.

[Updated on: Wed, 09 September 2009 05:18]

Report message to a moderator

Re: Optimization in the where clause [message #421587 is a reply to message #421425] Wed, 09 September 2009 05:14 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I don't want to re-iterate what co-forum members have already been mentioned. I will mention the bits which others have not mentioned already.

a) Read about sql injection.
b) I think this is already mentioned in performance tuning section sticky guide. It's extremely difficult to fine tune or provide guidance on a query without knowing what it is doing. Explain plan you have posted is just a best guess from oracle what it thinks. But the actual plan could be completely different or it could be the same. So it's very difficult to suggest the changes.

So could you please run the query along with some representative data and turn on the 10046 trace and post the output of the tkprof. Also please post the table definition details and any other information whihc you might think could be useful. Is statspack or AWR running on the box where you are experiencing problems. If so try to run the statspack or AWR report during the peak time and try to identify what is the causing the slowdown and compare the report between the normal load and the peak load. You may be hitting some resource contention. So it need not be the sql always. There could be other reasons behind why the query is not performing.

Regards

Raj
Re: Optimization in the where clause [message #421591 is a reply to message #421587] Wed, 09 September 2009 05:30 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi Raj,
Thank you so much for looking into this. I will look into whatever has been suggested by all members and will try to implement it....

Pablolee,
sorry if I sound idiot, but believe me I am not much aware of tuning and not confident on using bind variables. I am looking into them and thanks for your patience.

Regards,
Mahi
Re: Optimization in the where clause [message #421592 is a reply to message #421591] Wed, 09 September 2009 05:36 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
virmahi wrote on Wed, 09 September 2009 11:30

sorry if I sound idiot,

Not my intention to imply that, at all. My issue is that you are either giving us the wrong information or you seem to be ignoring the suggestions simply because you don't understand them. It may well be that you are not ignoring them, and are actually looking into them, but that is not how your posts read.
Quote:
but believe me I am not much aware of tuning and not confident on using bind variables. I am looking into them and thanks for your patience.


And this statement makes it look as though you are ignoring the to_date issue and have taken no notice of the varying in-list technique. Now, as mentioned above, this may not be the case, but that is how it reads
Re: Optimization in the where clause [message #421593 is a reply to message #421592] Wed, 09 September 2009 05:37 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Personally, I reckon that fixing the TO_DATE issue might well improve the performance of the query by allowing it to restrict the partitions that it looks at.
Re: Optimization in the where clause [message #421594 is a reply to message #421593] Wed, 09 September 2009 05:41 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
JRowbottom wrote on Wed, 09 September 2009 11:37
Personally, I reckon that fixing the TO_DATE issue might well improve the performance of the query by allowing it to restrict the partitions that it looks at.

+1 (Which I believe you had already said further up the thread (one of the reasons I was pushing virmahi as to why he had not tested it.)
Re: Optimization in the where clause [message #421739 is a reply to message #421594] Thu, 10 September 2009 03:55 Go to previous messageGo to previous message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
I have created the query with some values and I have run explain plan with where clause.

I found some full table scan on big tables like cllog and clexec. Now have only 9000 records in these tables but in production they have millions of records.

The query is as :-

SELECT   a.clcallstatus, COUNT (*) statuscount
    FROM cllog a INNER JOIN user_cltype b ON (a.clcalltype = b.calltype)
         INNER JOIN callmaster c
         ON (    b.callname = c.callname
             AND a.clcalltype = c.calltype
             AND a.clcallaction = c.callaction
            )
         INNER JOIN
         (SELECT f.citycode, f.citydesc, custbrname, custcode, custbrcode,
                 compcode, offcode
            FROM mcustomerbranch d INNER JOIN mcity f ON d.citycode =
                                                                    f.citycode
                 ) p
         ON (    a.clcustcd = p.custcode
             AND a.clcustbrcd = p.custbrcode
             AND a.compcode = p.compcode
             AND a.cloffcd = p.offcode
            )
         LEFT OUTER JOIN
         (SELECT r.rtcode, q.fix, q.clcallno, q.clactcd, q.clcustcd,
                 q.clcustbrcd, q.cloffcd, q.compcode, q.clgendate,
                 q.clcustcustcd
            FROM clexec q INNER JOIN mroute r
                 ON q.rtcode = r.rtcode
               AND q.compcode = r.compcode
               AND q.cloffcd = r.offcode
                 ) x
         ON (    a.clcallno = x.clcallno
             AND a.clactcd = x.clactcd
             AND a.clcustcd = x.clcustcd
             AND a.clcustbrcd = x.clcustbrcd
             AND a.cloffcd = x.cloffcd
             AND a.compcode = x.compcode
             AND a.clgendate = x.clgendate
             AND (a.clcustcustcd = x.clcustcustcd OR a.clcustcustcd IS NULL)
            )
   WHERE a.clactdate BETWEEN TO_DATE ('07/21/2009', 'mm/dd/yyyy')
                         AND TO_DATE ('07/23/2009', 'mm/dd/yyyy')
     AND a.compcode = 'CSL'
     AND b.userid = 'HO0276'
     AND b.callname IN ('CASH DELIVERY', 'CASH PICKUP', 'CASH-CHEQUE PICKUP')
     AND a.clcustbrcd IN
            ('AXC276','CTC276','DBC276','HBC276','HD0276','ICC276','IDC276','IGC276','IP0276','RG0276','RL0276'
            )
     AND p.citycode IN ('02762')
     AND a.clnature IN ('B', 'R')
     AND a.clcallstatus IN ('OP', 'AS', 'AT', 'SK', 'CN', 'WA', 'DL', 'PN')
     AND a.loginauthuserid IS NOT NULL
     AND a.cloffcd IN ('02762')
     AND a.clcustcd IN
            ('A00001', 'A00002','A00003','A00004','A00005','A00006','A00007','A00008','A00009','A00010','A00011',
             'B00001', 'B00002','B00003','B00004','B00005','C00001','C00002','C00004','C00005','C00006','C00007',
             'C00008', 'C00009','D00001','D00002','D00003','D00004','G00001','H00001','H00002','H00003','H00004',
             'H00005','H00006','H00007','I00001','I00002','I00003','I00004','I00005','I00006','I00007','I00008',
             'I00009','I00010','I00011','I00012','K00001','K00002','O00002','R00001','R00002','R00003','R00004',
             'R00005','R00006','S00001','S00002','S00003','S00004','S00005','S00006','W00001'
            )
     AND (auth1 IS NULL OR auth2 IS NULL)
GROUP BY a.clcallstatus
ORDER BY a.clcallstatus



And the explain plan is as below :-
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1267785969

----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                    |     1 |   193 |   125   (4)| 00:00:02 |
|   1 |  SORT GROUP BY                 |                    |     1 |   193 |   125   (4)| 00:00:02 |
|   2 |   NESTED LOOPS OUTER           |                    |     1 |   193 |   124   (4)| 00:00:02 |
|   3 |    NESTED LOOPS                |                    |     1 |   193 |    77   (4)| 00:00:01 |
|   4 |     NESTED LOOPS               |                    |     1 |   168 |    76   (4)| 00:00:01 |
|*  5 |      HASH JOIN                 |                    |     2 |   262 |    76   (4)| 00:00:01 |
|   6 |       NESTED LOOPS             |                    |     3 |   126 |     4   (0)| 00:00:01 |
|*  7 |        INDEX UNIQUE SCAN       | PK_MCITY           |     1 |     5 |     1   (0)| 00:00:01 |
|*  8 |        TABLE ACCESS FULL       | CALLMASTER         |     3 |   111 |     3   (0)| 00:00:01 |
|*  9 |       TABLE ACCESS FULL        | CLLOG              |     5 |   445 |    71   (3)| 00:00:01 |
|* 10 |      INDEX UNIQUE SCAN         | PK_USER_CLTYPE     |     1 |    37 |     0   (0)| 00:00:01 |
|* 11 |     TABLE ACCESS BY INDEX ROWID| MCUSTOMERBRANCH    |     1 |    25 |     1   (0)| 00:00:01 |
|* 12 |      INDEX UNIQUE SCAN         | PK_MCUSTOMERBRANCH |     1 |       |     0   (0)| 00:00:01 |
|  13 |    VIEW                        |                    |     1 |       |    48   (5)| 00:00:01 |
|* 14 |     HASH JOIN                  |                    |     1 |    72 |    48   (5)| 00:00:01 |
|* 15 |      TABLE ACCESS FULL         | CLEXEC             |     1 |    57 |    42   (3)| 00:00:01 |
|  16 |      TABLE ACCESS FULL         | MROUTE             |   434 |  6510 |     5   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   5 - access("A"."CLCALLTYPE"="C"."CALLTYPE" AND "A"."CLCALLACTION"="C"."CALLACTION")
   7 - access("F"."CITYCODE"='02762')
   8 - filter("C"."CALLNAME"='CASH DELIVERY' OR "C"."CALLNAME"='CASH PICKUP' OR
              "C"."CALLNAME"='CASH-CHEQUE PICKUP')
   9 - filter("A"."LOGINAUTHUSERID" IS NOT NULL AND ("A"."AUTH1" IS NULL OR "A"."AUTH2" IS
              NULL) AND "A"."COMPCODE"='CSL' AND ("A"."CLCUSTBRCD"='AXC276' OR "A"."CLCUSTBRCD"='CTC276'
              OR "A"."CLCUSTBRCD"='DBC276' OR "A"."CLCUSTBRCD"='HBC276' OR "A"."CLCUSTBRCD"='HD0276' OR
              "A"."CLCUSTBRCD"='ICC276' OR "A"."CLCUSTBRCD"='IDC276' OR "A"."CLCUSTBRCD"='IGC276' OR
              "A"."CLCUSTBRCD"='IP0276' OR "A"."CLCUSTBRCD"='RG0276' OR "A"."CLCUSTBRCD"='RL0276') AND
              ("A"."CLNATURE"='B' OR "A"."CLNATURE"='R') AND ("A"."CLCALLSTATUS"='AS' OR
              "A"."CLCALLSTATUS"='AT' OR "A"."CLCALLSTATUS"='CN' OR "A"."CLCALLSTATUS"='DL' OR
              "A"."CLCALLSTATUS"='OP' OR "A"."CLCALLSTATUS"='PN' OR "A"."CLCALLSTATUS"='SK' OR
              "A"."CLCALLSTATUS"='WA') AND "A"."CLOFFCD"='02762' AND "A"."CLACTDATE">=TO_DATE('2009-07-21
              00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "A"."CLACTDATE"<=TO_DATE('2009-07-23 00:00:00',
              'yyyy-mm-dd hh24:mi:ss') AND ("A"."CLCUSTCD"='A00001' OR "A"."CLCUSTCD"='A00002' OR
              "A"."CLCUSTCD"='A00003' OR "A"."CLCUSTCD"='A00004' OR "A"."CLCUSTCD"='A00005' OR
              "A"."CLCUSTCD"='A00006' OR "A"."CLCUSTCD"='A00007' OR "A"."CLCUSTCD"='A00008' OR
              "A"."CLCUSTCD"='A00009' OR "A"."CLCUSTCD"='A00010' OR "A"."CLCUSTCD"='A00011' OR
              "A"."CLCUSTCD"='B00001' OR "A"."CLCUSTCD"='B00002' OR "A"."CLCUSTCD"='B00003' OR
              "A"."CLCUSTCD"='B00004' OR "A"."CLCUSTCD"='B00005' OR "A"."CLCUSTCD"='C00001' OR
              "A"."CLCUSTCD"='C00002' OR "A"."CLCUSTCD"='C00004' OR "A"."CLCUSTCD"='C00005' OR
              "A"."CLCUSTCD"='C00006' OR "A"."CLCUSTCD"='C00007' OR "A"."CLCUSTCD"='C00008' OR
              "A"."CLCUSTCD"='C00009' OR "A"."CLCUSTCD"='D00001' OR "A"."CLCUSTCD"='D00002' OR
              "A"."CLCUSTCD"='D00003' OR "A"."CLCUSTCD"='D00004' OR "A"."CLCUSTCD"='G00001' OR
              "A"."CLCUSTCD"='H00001' OR "A"."CLCUSTCD"='H00002' OR "A"."CLCUSTCD"='H00003' OR
              "A"."CLCUSTCD"='H00004' OR "A"."CLCUSTCD"='H00005' OR "A"."CLCUSTCD"='H00006' OR
              "A"."CLCUSTCD"='H00007' OR "A"."CLCUSTCD"='I00001' OR "A"."CLCUSTCD"='I00002' OR
              "A"."CLCUSTCD"='I00003' OR "A"."CLCUSTCD"='I00004' OR "A"."CLCUSTCD"='I00005' OR
              "A"."CLCUSTCD"='I00006' OR "A"."CLCUSTCD"='I00007' OR "A"."CLCUSTCD"='I00008' OR
              "A"."CLCUSTCD"='I00009' OR "A"."CLCUSTCD"='I00010' OR "A"."CLCUSTCD"='I00011' OR
              "A"."CLCUSTCD"='I00012' OR "A"."CLCUSTCD"='K00001' OR "A"."CLCUSTCD"='K00002' OR
              "A"."CLCUSTCD"='O00002' OR "A"."CLCUSTCD"='R00001' OR "A"."CLCUSTCD"='R00002' OR
              "A"."CLCUSTCD"='R00003' OR "A"."CLCUSTCD"='R00004' OR "A"."CLCUSTCD"='R00005' OR
              "A"."CLCUSTCD"='R00006' OR "A"."CLCUSTCD"='S00001' OR "A"."CLCUSTCD"='S00002' OR
              "A"."CLCUSTCD"='S00003' OR "A"."CLCUSTCD"='S00004' OR "A"."CLCUSTCD"='S00005' OR
              "A"."CLCUSTCD"='S00006' OR "A"."CLCUSTCD"='W00001'))
  10 - access("B"."USERID"='HO0276' AND "B"."CALLNAME"="C"."CALLNAME" AND
              "A"."CLCALLTYPE"="B"."CALLTYPE")
       filter("B"."CALLNAME"='CASH DELIVERY' OR "B"."CALLNAME"='CASH PICKUP' OR
              "B"."CALLNAME"='CASH-CHEQUE PICKUP')
  11 - filter("D"."CITYCODE"='02762' AND "D"."OFFCODE"='02762' AND ("D"."CUSTCODE"='A00001'
              OR "D"."CUSTCODE"='A00002' OR "D"."CUSTCODE"='A00003' OR "D"."CUSTCODE"='A00004' OR
              "D"."CUSTCODE"='A00005' OR "D"."CUSTCODE"='A00006' OR "D"."CUSTCODE"='A00007' OR
              "D"."CUSTCODE"='A00008' OR "D"."CUSTCODE"='A00009' OR "D"."CUSTCODE"='A00010' OR
              "D"."CUSTCODE"='A00011' OR "D"."CUSTCODE"='B00001' OR "D"."CUSTCODE"='B00002' OR
              "D"."CUSTCODE"='B00003' OR "D"."CUSTCODE"='B00004' OR "D"."CUSTCODE"='B00005' OR
              "D"."CUSTCODE"='C00001' OR "D"."CUSTCODE"='C00002' OR "D"."CUSTCODE"='C00004' OR
              "D"."CUSTCODE"='C00005' OR "D"."CUSTCODE"='C00006' OR "D"."CUSTCODE"='C00007' OR
              "D"."CUSTCODE"='C00008' OR "D"."CUSTCODE"='C00009' OR "D"."CUSTCODE"='D00001' OR
              "D"."CUSTCODE"='D00002' OR "D"."CUSTCODE"='D00003' OR "D"."CUSTCODE"='D00004' OR
              "D"."CUSTCODE"='G00001' OR "D"."CUSTCODE"='H00001' OR "D"."CUSTCODE"='H00002' OR
              "D"."CUSTCODE"='H00003' OR "D"."CUSTCODE"='H00004' OR "D"."CUSTCODE"='H00005' OR
              "D"."CUSTCODE"='H00006' OR "D"."CUSTCODE"='H00007' OR "D"."CUSTCODE"='I00001' OR
              "D"."CUSTCODE"='I00002' OR "D"."CUSTCODE"='I00003' OR "D"."CUSTCODE"='I00004' OR
              "D"."CUSTCODE"='I00005' OR "D"."CUSTCODE"='I00006' OR "D"."CUSTCODE"='I00007' OR
              "D"."CUSTCODE"='I00008' OR "D"."CUSTCODE"='I00009' OR "D"."CUSTCODE"='I00010' OR
              "D"."CUSTCODE"='I00011' OR "D"."CUSTCODE"='I00012' OR "D"."CUSTCODE"='K00001' OR
              "D"."CUSTCODE"='K00002' OR "D"."CUSTCODE"='O00002' OR "D"."CUSTCODE"='R00001' OR
              "D"."CUSTCODE"='R00002' OR "D"."CUSTCODE"='R00003' OR "D"."CUSTCODE"='R00004' OR
              "D"."CUSTCODE"='R00005' OR "D"."CUSTCODE"='R00006' OR "D"."CUSTCODE"='S00001' OR
              "D"."CUSTCODE"='S00002' OR "D"."CUSTCODE"='S00003' OR "D"."CUSTCODE"='S00004' OR
              "D"."CUSTCODE"='S00005' OR "D"."CUSTCODE"='S00006' OR "D"."CUSTCODE"='W00001') AND
              "A"."CLCUSTCD"="D"."CUSTCODE")
  12 - access("A"."CLCUSTBRCD"="D"."CUSTBRCODE" AND "D"."COMPCODE"='CSL')
       filter("D"."CUSTBRCODE"='AXC276' OR "D"."CUSTBRCODE"='CTC276' OR
              "D"."CUSTBRCODE"='DBC276' OR "D"."CUSTBRCODE"='HBC276' OR "D"."CUSTBRCODE"='HD0276' OR
              "D"."CUSTBRCODE"='ICC276' OR "D"."CUSTBRCODE"='IDC276' OR "D"."CUSTBRCODE"='IGC276' OR
              "D"."CUSTBRCODE"='IP0276' OR "D"."CUSTBRCODE"='RG0276' OR "D"."CUSTBRCODE"='RL0276')
  14 - access("Q"."RTCODE"="R"."RTCODE" AND "Q"."COMPCODE"="R"."COMPCODE" AND
              "Q"."CLOFFCD"="R"."OFFCODE")
  15 - filter("A"."CLCALLNO"="Q"."CLCALLNO" AND "A"."CLACTCD"="Q"."CLACTCD" AND
              "A"."CLCUSTCD"="Q"."CLCUSTCD" AND "A"."CLCUSTBRCD"="Q"."CLCUSTBRCD" AND
              "A"."CLOFFCD"="Q"."CLOFFCD" AND "A"."COMPCODE"="Q"."COMPCODE" AND
              "A"."CLGENDATE"="Q"."CLGENDATE" AND ("A"."CLCUSTCUSTCD" IS NULL OR
              "A"."CLCUSTCUSTCD"="Q"."CLCUSTCUSTCD"))



What can I do to remove the full table scan.... can they be one of the reason for the slowness of query?

What are other warning factors in the given explain plan.... I am not much proficient in understand the explain plan.


Regards,
Mahi
Previous Topic: DELETE statement deletes rows inserted after the DELETE statement began
Next Topic: ORA-06550: Error
Goto Forum:
  


Current Time: Sat Oct 01 14:27:39 CDT 2016

Total time taken to generate the page: 0.07869 seconds