OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Query performance

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query performance [message #302754]

Tue, 26 February 2008 22:39

ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai

Senior Member

Hi Experts,

I need help regarding performance of the query.


update TEST_TAB 
set fail=1, msg='HARD'
where id in (
    select src.id from TEST_TAB src
        inner join TEST_TAB l_1 on src.email=l_1.email and l_1.database_id=335090 and l_1.msg='HARD' and l_1.fail=1
        inner join TEST_TAB l_2 on src.email=l_2.email and l_2.database_id=338310 and l_2.msg='HARD' and l_2.fail=1
        inner join TEST_TAB l_3 on src.email=l_3.email and l_3.database_id=338470 and l_3.msg='HARD' and l_3.fail=1
    where src.database_id=1111111;
)

This query is running for too long, takes >1 hour and it updates 26000 records.

But, if we run inner select query


select src.id from TEST_TAB src
        inner join TEST_TAB l_1 on src.email=l_1.email and l_1.database_id=335090 and l_1.msg='HARD' and l_1.fail=1
        inner join TEST_TAB l_2 on src.email=l_2.email and l_2.database_id=338310 and l_2.msg='HARD' and l_2.fail=1
        inner join TEST_TAB l_3 on src.email=l_3.email and l_3.database_id=338470 and l_3.msg='HARD' and l_3.fail=1
    where src.database_id=1111111

It takes <1 minute to execute.
Please give me suggetions in the update query so that i will improve performance of the query.

Thanks in advance.

Report message to a moderator

Re: Query performance [message #302757 is a reply to message #302754]

Tue, 26 February 2008 22:42

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Does the SELECT on its own return ALL rows in less than a minute? Or just the FIRST page of rows?

How long does this take?

select * from (
    select src.id from TEST_TAB src
        inner join TEST_TAB l_1 on src.email=l_1.email and l_1.database_id=335090 and l_1.msg='HARD' and l_1.fail=1
        inner join TEST_TAB l_2 on src.email=l_2.email and l_2.database_id=338310 and l_2.msg='HARD' and l_2.fail=1
        inner join TEST_TAB l_3 on src.email=l_3.email and l_3.database_id=338470 and l_3.msg='HARD' and l_3.fail=1
    where src.database_id=1111111
)
where rownum > 1

Also, show us the Explain Plan for the UPDATE statement and the Explain Plan for the SELECT.

Ross Leishman

Report message to a moderator

Re: Query performance [message #302758 is a reply to message #302757]

Tue, 26 February 2008 22:54

ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai

Senior Member

Thanks rleishman for your reply.

1. The select query returns all rows less than a minute.
2. And regarding explain plan i need to execute the update query to get the explain plan. Once i will get the plan i will reply.

For the time being can you suggest any solution.

Thanks in advance.

Report message to a moderator

Re: Query performance [message #302828 is a reply to message #302754]

Wed, 27 February 2008 02:13

ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai

Senior Member

For Select query

Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS		1  	 	10453  	 	      	 	 
  TABLE ACCESS BY INDEX ROWID	LEAD	1  	32  	27  	 	      	 	 
    NESTED LOOPS		1  	130  	10453  	 	      	 	 
      HASH JOIN		1  	98  	10426  	 	      	 	 
        HASH JOIN		199  	12 K	6950  	 	      	 	 
          TABLE ACCESS BY INDEX ROWID	LEAD	202  	6 K	3476  	 	      	 	 
            INDEX RANGE SCAN	LEAD_DATABASE_FK_I	94 K	 	259  	 	      	 	 
          TABLE ACCESS BY INDEX ROWID	LEAD	94 K	3 M	3473  	 	      	 	 
            INDEX RANGE SCAN	LEAD_DATABASE_FK_I	94 K	 	259  	 	      	 	 
        TABLE ACCESS BY INDEX ROWID	LEAD	202  	6 K	3476  	 	      	 	 
          INDEX RANGE SCAN	LEAD_DATABASE_FK_I	94 K	 	259  	 	      	 	 
      INDEX RANGE SCAN	LEAD_IDX_4	24  	 	3

[Updated on: Wed, 27 February 2008 02:15]

Report message to a moderator

Re: Query performance [message #302870 is a reply to message #302754]

Wed, 27 February 2008 03:46

ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai

Senior Member

When i run the following query it retrives the output less than a munute.

select src.id from TEST_TAB src
        inner join TEST_TAB l_1 on src.email=l_1.email and l_1.database_id=335090 and l_1.bounce_msg_t='HARD' and l_1.fail=1
        inner join TEST_TAB l_2 on src.email=l_2.email and l_2.database_id=338310 and l_2.bounce_msg_t='HARD' and l_2.fail=1
        inner join TEST_TAB l_3 on src.email=l_3.email and l_3.database_id=338470 and l_3.bounce_msg_t='HARD' and l_3.fail=1
    where src.database_id=264170


Explain plan

Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS		1  	 	10453  	 	      	 	 
  TABLE ACCESS BY INDEX ROWID	TEST_TAB	1  	32  	27  	 	      	 	 
    NESTED LOOPS		1  	130  	10453  	 	      	 	 
      HASH JOIN		1  	98  	10426  	 	      	 	 
        HASH JOIN		199  	12 K	6950  	 	      	 	 
          TABLE ACCESS BY INDEX ROWID	TEST_TAB	202  	6 K	3476  	 	      	 	 
            INDEX RANGE SCAN	TEST_TAB_DATABASE_FK_I	94 K	 	259  	 	      	 	 
          TABLE ACCESS BY INDEX ROWID	TEST_TAB	94 K	3 M	3473  	 	      	 	 
            INDEX RANGE SCAN	TEST_TAB_DATABASE_FK_I	94 K	 	259  	 	      	 	 
        TABLE ACCESS BY INDEX ROWID	TEST_TAB	202  	6 K	3476  	 	      	 	 
          INDEX RANGE SCAN	TEST_TAB_DATABASE_FK_I	94 K	 	259  	 	      	 	 
      INDEX RANGE SCAN	TEST_TAB_IDX_4	24  	 	3  	 	      	 	 




But when i count records then this query takes so much time.

Then my question is ther any need to tune the query?


SELECT COUNT(src.id) FROM TEST_TAB src
        inner join TEST_TAB l_1 ON src.email=l_1.email AND l_1.database_id=335090 AND l_1.bounce_msg_t='HARD' AND l_1.fail=1
   inner join TEST_TAB l_2 ON src.email=l_2.email AND l_2.database_id=338310 AND l_2.bounce_msg_t='HARD' AND l_2.fail=1
  4          inner join TEST_TAB l_3 ON src.email=l_3.email AND l_3.database_id=338470 AND l_3.bounce_msg_t='HARD' AND l_3.fail=1
    WHERE src.database_id=264170;

[Updated on: Wed, 27 February 2008 03:48]

Report message to a moderator

Re: Query performance [message #302877 is a reply to message #302754]

Wed, 27 February 2008 04:15

MarcS
Messages: 312
Registered: March 2007
Location: Antwerp

Senior Member

ora_2007 wrote on Wed, 27 February 2008 05:39

update TEST_TAB 
set fail=1, msg='HARD'
where id in (
    select src.id from TEST_TAB src
        inner join TEST_TAB l_1 on src.email=l_1.email and l_1.database_id=335090 and l_1.msg='HARD' and l_1.fail=1
        inner join TEST_TAB l_2 on src.email=l_2.email and l_2.database_id=338310 and l_2.msg='HARD' and l_2.fail=1
        inner join TEST_TAB l_3 on src.email=l_3.email and l_3.database_id=338470 and l_3.msg='HARD' and l_3.fail=1
    where src.database_id=1111111;
)

This looks like a (Hibernate?) generated query.

I could be wrong, but could the INNER SELECT be rewritten as follows:

select src.id from TEST_TAB src
        inner join TEST_TAB l_1 on src.email=l_1.email and l_1.database_id in (335090,338310,338470) and l_1.msg='HARD' and l_1.fail=1
    where src.database_id=1111111;

I'm not sure it would have an impact performance though.

Report message to a moderator

Re: Query performance [message #303068 is a reply to message #302877]

Wed, 27 February 2008 21:05

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

You can get the Explain Plan for the UPDATE without running it. If you are using a GUI, just hit the Explain button. If you are using SQL*Plus, follow the instructions in the Oracle Performance Tuning manual. Don't use AUTOTRACE - that WILL run the update.

Since the SQL on its own is fast, you have 1 of two problems:
- The cost of finding the rows is low, but the cost of updating them is high. You might not be able to do anything about this.
- The UPDATE uses a different plan.

Until you give us the Explain Plan of the update, we cannot tell which is true.

Ross Leishman

Report message to a moderator

Re: Query performance [message #303148 is a reply to message #303068]

Thu, 28 February 2008 03:04

ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai

Senior Member

Thanks to all for replies.

When we execute select query we can see that query is executed within a minute.

That might be because TOAD's/ SQL navigator’s/PL-SQL developer’s – Tools anti-tuning features such as showing the time taken to produce the first few rows instead of running the whole query, along with the illegible explain plans.

When we run the inner select query on the command prompt then it takes much time to execute i.e around 40minutes

I guess we need to tune the select query.
Please suggest me to tune the SQL query.

Report message to a moderator

Re: Query performance [message #303152 is a reply to message #303148]

Thu, 28 February 2008 03:09

MarcS
Messages: 312
Registered: March 2007
Location: Antwerp

Senior Member

ora_2007 wrote on Thu, 28 February 2008 10:04

I guess we need to tune the select query.
Please suggest me to tune the SQL query.

Did you try/read my suggestion - a few posts above - already?

Report message to a moderator

Previous Topic:	Rebuild all the indexes of schema
Next Topic:	Index rebuild issue

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 18:06:26 CST 2024