Home » SQL & PL/SQL » SQL & PL/SQL » Performabce tuning in SQL (Oracle,10g,Linux)
Performabce tuning in SQL [message #419348] Fri, 21 August 2009 06:58 Go to next message
sundarfaq
Messages: 235
Registered: October 2007
Location: Chennai
Senior Member
Hi,

i have executed below SQL statements.it takes more times to execute
SELECT COUNT (1)
  FROM rt_rate_udf r1, rt_rate_udf r2
 WHERE r1.rate_id = 132733
   AND r2.rate_id = 132733
   AND r1.udf_id = r2.udf_id
  [b] AND NVL (r1.udf_value_str, '***') = NVL(r2.udf_value_str, '***')[/b]
   AND NVL (r1.udf_value_int, -0.1) = NVL (r2.udf_value_int, -0.1)
   AND NVL (r1.udf_value_dbl, -0.1) = NVL (r2.udf_value_dbl, -0.1)
   AND NVL (r1.udf_value_dt, TO_DATE ('01/01/1000', 'DD/MM/YYYY')) =
                   NVL (r2.udf_value_dt, TO_DATE ('01/01/1000', 'DD/MM/YYYY'))
   AND NVL (r1.inclusive_flag, 'Y') = 'Y'
   AND NVL (r2.inclusive_flag, 'Y') = 'Y'
   AND r1.udf_id = 'BILLTO'

I have removed udf_value_str column in where conditions from above SQL statements.After that it takes less time to execute.the code listed below
SELECT COUNT (1)
  FROM rt_rate_udf r1, rt_rate_udf r2
 WHERE r1.rate_id = 132733
   AND r2.rate_id = 132733
   AND r1.udf_id = r2.udf_id
   AND NVL (r1.udf_value_int, -0.1) = NVL (r2.udf_value_int, -0.1)
   AND NVL (r1.udf_value_dbl, -0.1) = NVL (r2.udf_value_dbl, -0.1)
   AND NVL (r1.udf_value_dt, TO_DATE ('01/01/1000', 'DD/MM/YYYY')) =
                   NVL (r2.udf_value_dt, TO_DATE ('01/01/1000', 'DD/MM/YYYY'))
   AND NVL (r1.inclusive_flag, 'Y') = 'Y'
   AND NVL (r2.inclusive_flag, 'Y') = 'Y'
   AND r1.udf_id = 'BILLTO'

I have enclosed RT_RATE_UDF table desc file with it.

the RT_RATE_UDF table has million of records.

Please give an idea how to resolve it.

I have tried hundred of times but i am unable to correct this sql statements.

[Updated on: Fri, 21 August 2009 09:14] by Moderator

Report message to a moderator

Re: Performabce tuning in SQL [message #419350 is a reply to message #419348] Fri, 21 August 2009 07:13 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What sort of difference in time are we talking about?

Can you post an Explain plan for bother versions of the query.
Re: Performabce tuning in SQL [message #419361 is a reply to message #419348] Fri, 21 August 2009 09:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
More than 120 posts and you still don't know that posting code is between [code] and [/code] not between <<code>>.

Regards
Michel
Re: Performabce tuning in SQL [message #419362 is a reply to message #419348] Fri, 21 August 2009 09:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And to answer the question, replace
AND NVL (r1.udf_value_str, '***') = NVL(r2.udf_value_str, '***')

by
AND (r1.udf_value_str = r2.udf_value_str
   OR (r1.udf_value_str is null AND r2.udf_value_str is null))

Regards
Michel
Re: Performabce tuning in SQL [message #419363 is a reply to message #419362] Fri, 21 August 2009 09:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Given that the query performs a large number of NVL comparisons on the same column, I'm not convinced that this is the cause of the problem.
Re: Performabce tuning in SQL [message #419364 is a reply to message #419348] Fri, 21 August 2009 09:23 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Unless you have an index on nvl(udf_value_string, '***'), the only reason I can think of why removing such a where-clause would result in a faster result, would be the executing your first query did the Disk-IO necessary for the second one.
Re: Performabce tuning in SQL [message #419599 is a reply to message #419348] Mon, 24 August 2009 07:54 Go to previous messageGo to next message
sundarfaq
Messages: 235
Registered: October 2007
Location: Chennai
Senior Member
Hi,

I have tried all options which you are mentioned in our conversations but it takes time to execute. I have enclosed explain plan for this query. Please help me..
  • Attachment: explain.sql
    (Size: 3.39KB, Downloaded 114 times)
Re: Performabce tuning in SQL [message #419600 is a reply to message #419348] Mon, 24 August 2009 07:57 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
How much time?

Becuase if those explain plans are accurate those queries should be really fast.
Re: Performabce tuning in SQL [message #419601 is a reply to message #419348] Mon, 24 August 2009 08:02 Go to previous messageGo to next message
sundarfaq
Messages: 235
Registered: October 2007
Location: Chennai
Senior Member
first query takes millisecond but second query takes 8 second.
Re: Performabce tuning in SQL [message #419622 is a reply to message #419601] Mon, 24 August 2009 09:02 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- Performances Tuning sticky
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide
- Tuning High-Volume SQL Wiki page

Then provide the required and usual information.

Regards
Michel
Previous Topic: dblink
Next Topic: Stored proc using merge
Goto Forum:
  


Current Time: Wed Dec 07 18:57:52 CST 2016

Total time taken to generate the page: 0.28167 seconds