Home » RDBMS Server » Performance Tuning » Regd: Tuning required (Oracle 9I)
Regd: Tuning required [message #319629] Mon, 12 May 2008 06:54 Go to next message
VEDDETA
Messages: 54
Registered: May 2008
Member
I have created the below query to get the count based on certain conditions.

But is the below query is usefull ( from performance perspective) while there are millions of records.
Is there any convenient way to optimize this query?


SELECT  
	COUNT(CASE WHEN(  A.TMS1> SYSTIMESTAMP - INTERVAL '30' MINUTE)  THEN 1  END),
	COUNT(CASE WHEN  A.LAST_UPD_TMS> SYSTIMESTAMP - INTERVAL '2' hour  AND A.TMS1<= SYSTIMESTAMP - INTERVAL '1' hour THEN  1  END),
	COUNT(CASE WHEN  A.TMS1>= SYSTIMESTAMP - INTERVAL '24' hour  AND A.TMS1<= SYSTIMESTAMP - INTERVAL '2' hour THEN  1  END),
	COUNT(CASE WHEN (  A.TMS1< SYSTIMESTAMP - INTERVAL '1' DAY)  THEN  1  END),
        COUNT(*) AS CNT 
FROM MQ A,CODE B
WHERE SUBSTR(A.PROCESS_NAME,1,4) = B.INDICATOR_TXT
	--// Pass application name
	AND B.IND_TXT = p_application_nm
	AND A.LOCATION_ID = p_loc_id;

[Updated on: Mon, 12 May 2008 06:58] by Moderator

Report message to a moderator

Re: Regd: Tuning required [message #319630 is a reply to message #319629] Mon, 12 May 2008 06:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If your concern is about the COUNTs as you have to do it there is nothing to do.
The main issue is in WHERE clause.

Regards
Michel

Report message to a moderator

Re: Regd: Tuning required [message #319642 is a reply to message #319629] Mon, 12 May 2008 07:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What is the current explain plan for this query?

What indexes do you have on tables MQ and CODE?

Report message to a moderator

Re: Regd: Tuning required [message #319661 is a reply to message #319629] Mon, 12 May 2008 09:11 Go to previous messageGo to next message
VEDDETA
Messages: 54
Registered: May 2008
Member
Thanks for the quick responses.

Process_name and Indicator_txt are the two indexes that has been created on the tables MO & Code respectively.

Right now I am trying with a less amount of data. But if there are millions of rowns then do I need to optimize the query?

In addition to the condition in where clause, does not the comparison in case statement will imapact on performance?



Regards,
Veddeta

Report message to a moderator

Re: Regd: Tuning required [message #319664 is a reply to message #319661] Mon, 12 May 2008 09:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
does not the comparison in case statement will imapact on performance?

Do you need these results or not?
If yes, then you have to put them.
If no, then remove them.
There is nothing you can do.

Regards
Michel

Report message to a moderator

Re: Regd: Tuning required [message #319666 is a reply to message #319664] Mon, 12 May 2008 09:30 Go to previous messageGo to next message
VEDDETA
Messages: 54
Registered: May 2008
Member
Michel Cadot wrote on Mon, 12 May 2008 09:25
Quote:
does not the comparison in case statement will imapact on performance?

Do you need these results or not?
If yes, then you have to put them.
If no, then remove them.
There is nothing you can do.

Regards
Michel



Thanks Michel, for making me understand. I need the count. Ok, that means there is no other option to modify in case comparison statement, right?

Can we modify the substring funtion in where clause?

Report message to a moderator

Re: Regd: Tuning required [message #319669 is a reply to message #319666] Mon, 12 May 2008 09:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Can you give us an idea of the number of rows in the two tables that will be looked at by this query?

Report message to a moderator

Re: Regd: Tuning required [message #319680 is a reply to message #319666] Mon, 12 May 2008 10:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Can we modify the substring funtion in where clause?

I don't know if your join condition is based upon the first 4 characters you either have to use:
SUBSTR(A.PROCESS_NAME,1,4) = B.INDICATOR_TXT
A.PROCESS_NAME LIKE B.INDICATOR_TXT || '%'
(assuming B.INDICATOR_TXT is a 4 characters string)

The index on A.PROCESS_NAME is useless in this case.

Regards
Michel

Report message to a moderator

Re: Regd: Tuning required [message #319797 is a reply to message #319669] Tue, 13 May 2008 01:36 Go to previous messageGo to next message
VEDDETA
Messages: 54
Registered: May 2008
Member
JRowbottom wrote on Mon, 12 May 2008 09:44
Can you give us an idea of the number of rows in the two tables that will be looked at by this query?




Thanks for giving your valuable time..
Right now, Its not possible for me to give u adjactly how many rows are there....
But yah, the MQ table is going to have millions of records...
Thanks,
Veddeta

Report message to a moderator

Re: Regd: Tuning required [message #319803 is a reply to message #319680] Tue, 13 May 2008 01:41 Go to previous messageGo to next message
VEDDETA
Messages: 54
Registered: May 2008
Member
Michel Cadot wrote on Mon, 12 May 2008 10:13
Quote:
Can we modify the substring funtion in where clause?

I don't know if your join condition is based upon the first 4 characters you either have to use:
SUBSTR(A.PROCESS_NAME,1,4) = B.INDICATOR_TXT
A.PROCESS_NAME LIKE B.INDICATOR_TXT || '%'
(assuming B.INDICATOR_TXT is a 4 characters string)

The index on A.PROCESS_NAME is useless in this case.

Regards
Michel



Thanks Michel ..
The join condition is based upon the first 4 characters...
Michel, can I get some document link where I can study more about Oracle SQL Tuning?
If I am not wrong, when we use Like operator with '%' Full table scan is not done..right? I want to know more abount
tuning..where can I get that?

Report message to a moderator

Re: Regd: Tuning required [message #319804 is a reply to message #319803] Tue, 13 May 2008 01:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
can I get some document link where I can study more about Oracle SQL Tuning?

- OraFAQ Forum Guide, Performance Tuning section
- How to Identify Performance Problem and Bottleneck
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide
- Database Performance Tuning Guide.

Quote:
If I am not wrong, when we use Like operator with '%' Full table scan is not done..right?

It depends. FTS might or not be used.

Regards
Michel

Report message to a moderator

Re: Regd: Tuning required [message #319856 is a reply to message #319804] Tue, 13 May 2008 04:09 Go to previous messageGo to next message
VEDDETA
Messages: 54
Registered: May 2008
Member
Michel Cadot wrote on Tue, 13 May 2008 01:44
Quote:
can I get some document link where I can study more about Oracle SQL Tuning?

- OraFAQ Forum Guide, Performance Tuning section
- How to Identify Performance Problem and Bottleneck
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide
- Database Performance Tuning Guide.

Quote:
If I am not wrong, when we use Like operator with '%' Full table scan is not done..right?

It depends. FTS might or not be used.

Regards
Michel



Michel thanks for your help!
Quote:
It depends. FTS might or not be used.



On which conditions ( factor) it depends?

Report message to a moderator

Re: Regd: Tuning required [message #319861 is a reply to message #319856] Tue, 13 May 2008 04:42 Go to previous message
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
On which conditions ( factor) it depends?

Indexes, number of rows, values distribution and many other statistics like system ones without forgetting some configuration parameters in init.ora/spfile.

Regards
Michel

Report message to a moderator

Previous Topic: Tuning
Next Topic: DB performing very slowly,
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Jun 30 14:16:36 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.