Home » RDBMS Server » Performance Tuning » how to reduce the response time of the sql query (oracl 10 linux)
how to reduce the response time of the sql query [message #438332] Fri, 08 January 2010 02:15 Go to next message
dhanamukesh
Messages: 51
Registered: January 2009
Member
The following query 's cost would be 229 and cpu cost will be 6820445 and response time will be around 6 seconds.

UPDATE NOTIFICATION_MSG MSG
SET LAST_UPDATE_USERID = 'NOTIFICATION_SERVICE',
LAST_UPDATE_DATE =SYSDATE,
BATCH_PRCS_ID = :1, --'9999'
MSG_SND_TRY_CNT = MSG_SND_TRY_CNT + 1
WHERE NOTF_MSG_STAT_CODE = :2 --'SENT'
AND (BATCH_PRCS_ID IS NULL OR BATCH_PRCS_ID = :3)
AND MSG_SND_TRY_CNT < :5 -- 2
AND CREATE_DATE < = (SYSDATE - INTERVAL '5' MINUTE)
AND EXISTS (SELECT NOTF_MSG_ID
FROM NOTIFICATION_MSG_PARM PARMS
WHERE PARMS.NOTF_MSG_ID = MSG.NOTF_MSG_ID)
AND ROWNUM <= :4 --100000;

Because of the high cost, i have tuned the query like below:

BEGIN
FOR c1 IN (SELECT NOTF_MSG_ID
FROM NOTIFICATION_MSG_PARM PARMS)
LOOP
UPDATE NOTIFICATION_MSG MSG
SET LAST_UPDATE_USERID = 'NOTIFICATION_SERVICE',
LAST_UPDATE_DATE =SYSDATE,
BATCH_PRCS_ID = :1,'-9999'
MSG_SND_TRY_CNT = MSG_SND_TRY_CNT + 1
WHERE NOTF_MSG_STAT_CODE = :2 --'SENT'
AND CREATE_DATE < = (SYSDATE - INTERVAL '5' MINUTE)
AND (BATCH_PRCS_ID IS NULL OR BATCH_PRCS_ID = :3)
AND MSG_SND_TRY_CNT < :5 --2
AND MSG.NOTF_MSG_ID = c1.NOTF_MSG_ID
AND ROWNUM <=:4; -- 100000 ;
END LOOP;
END;

The cost and cpu cost reduced respectively like 1 and 2290.But the response tine got very high.

Even i tried with global temp table to store the subquery details and used those values in the main query.But still the query response time is very very high.


I have attached both statistics and explain plan with this.

Please guide me to tune this query.


Thanks,
Dhanalakshmi.


Re: how to reduce the response time of the sql query [message #438350 is a reply to message #438332] Fri, 08 January 2010 04:31 Go to previous messageGo to next message
cookiemonster
Messages: 12412
Registered: September 2008
Location: Rainy Manchester
Senior Member
First off when posting code can you please use code tags - see the orafaq forum guide if you're not sure how.

Those updates aren't equivalent. The First updates 100000 rows. The 2nd updates 100000 rows per matching
row in NOTIFICATION_MSG_PARM.
So the 2nd update is probably updating a lot more data than the first.
How many rows are in each of the two tables?
Which query is the explain plan for? And next time put explain plans in the message (use code tags).
And why are you limiting this by rownum?
Re: how to reduce the response time of the sql query [message #438356 is a reply to message #438350] Fri, 08 January 2010 05:33 Go to previous messageGo to next message
dhanamukesh
Messages: 51
Registered: January 2009
Member
Total no of records in the table notification_msg will be
2455128.

and for the table notification_msg_parm will be 17703990.

The plan is for the original query.i.e for the first query.

And rownum is not limiited.. It is a user input variable.It may vary from 100 to max i.e 10000 or more.

Thanks,
Dhanalakshmi.
Re: how to reduce the response time of the sql query [message #438365 is a reply to message #438332] Fri, 08 January 2010 06:12 Go to previous messageGo to next message
cookiemonster
Messages: 12412
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well looping over a table that contains aprox 18m rows is always going to take a long time.
What table and columns are the indexes IX2_NOTIFICATION_MSG and IX1_NOTIFICATION_MSG_PARM on?

As for rownum, why is it there at all?
You're not doing any ordering so the rows that are updated out of the possible ones that match the rest of the where clause is random. I would have thought you'd want to update all rows that match the rest of the where clause.
Rownum is generally only used for top-n queries - where you want to get the first (or last) n rows according to some criteria, but that requires an order by.
Re: how to reduce the response time of the sql query [message #438558 is a reply to message #438365] Sun, 10 January 2010 22:21 Go to previous messageGo to next message
dhanamukesh
Messages: 51
Registered: January 2009
Member
I got your point.Thank you very much.
The index IX2_NOTIFICATION_MSG taken from NOTIFICATION_MSG
and IX1_NOTIFICATION_MSG_PARM taken from NOTIFICATION_MSG_PARM.

Please help me in this regard.


Thanks,
Dhanalakhsmi.
Re: how to reduce the response time of the sql query [message #438624 is a reply to message #438332] Mon, 11 January 2010 04:49 Go to previous messageGo to next message
cookiemonster
Messages: 12412
Registered: September 2008
Location: Rainy Manchester
Senior Member
I asked for the column list for those indexes as well as the tables.
Re: how to reduce the response time of the sql query [message #438626 is a reply to message #438624] Mon, 11 January 2010 04:57 Go to previous messageGo to next message
dhanamukesh
Messages: 51
Registered: January 2009
Member
The columns NOTF_MSG_STAT_CODE, BATCH_PRCS_ID are indexed as IX2_NOTIFICATION_MSG in the table NOTIFICATION_MSG.


And the column NOTF_MSG_ID is indexed as IX1_NOTIFICATION_MSG_PARM in the table NOTIFICATION_MSG_PARM.


Thanks,
Dhanalakshmi
Re: how to reduce the response time of the sql query [message #438631 is a reply to message #438332] Mon, 11 January 2010 05:16 Go to previous messageGo to next message
cookiemonster
Messages: 12412
Registered: September 2008
Location: Rainy Manchester
Senior Member
Try an index on notification_msg(NOTF_MSG_STAT_CODE, BATCH_PRCS_ID, CREATE_DATE, NOTF_MSG_ID, MSG_SND_TRY_CNT)
using your first update.
Re: how to reduce the response time of the sql query [message #438639 is a reply to message #438631] Mon, 11 January 2010 06:00 Go to previous messageGo to next message
dhanamukesh
Messages: 51
Registered: January 2009
Member
After creating this index , the statistics going veery high.

Original Query's statistics :
Statistics
----------------------------------------------------------
18 recursive calls
12 db block gets
89 consistent gets
0 physical reads
0 redo size
2243 bytes sent via SQL*Net to client
446 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
26 rows processed


After creating the index,

Statistics
----------------------------------------------------------
3118 recursive calls
24 db block gets
2754 consistent gets
208 physical reads
0 redo size
2186 bytes sent via SQL*Net to client
446 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
38 sorts (memory)
0 sorts (disk)
26 rows processed


Please look into this.

Thanks,
Dhanalakshmi.
Re: how to reduce the response time of the sql query [message #438641 is a reply to message #438332] Mon, 11 January 2010 06:05 Go to previous messageGo to next message
cookiemonster
Messages: 12412
Registered: September 2008
Location: Rainy Manchester
Senior Member
Show us the new explain plan.
Re: how to reduce the response time of the sql query [message #438652 is a reply to message #438639] Mon, 11 January 2010 07:31 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Something doesn't add up here - in the attachment to your original post, you've got the stats as this:
Statistics
----------------------------------------------------------
       1488  recursive calls
         22  db block gets
       2370  consistent gets
        156  physical reads
          0  redo size
       3483  bytes sent via SQL*Net to client
        457  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
         20  sorts (memory)
          0  sorts (disk)
         40  rows processed


Even allowing for the possibility that those are the stats for the explain plan display, I'm still doubtful that a query that updates 100,000 rows will manage to do so with 89 consistent gets.
Previous Topic: what is the best ? (merged 3)
Next Topic: Query monitoring/intercepting
Goto Forum:
  


Current Time: Wed Dec 07 08:39:57 CST 2016

Total time taken to generate the page: 0.17553 seconds