OraFAQ Forum: Performance Tuning » Long run time of query with Order by clause

Home » RDBMS Server » Performance Tuning » Long run time of query with Order by clause (Oracle 10g)

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

Long run time of query with Order by clause [message #442211]

Fri, 05 February 2010 06:20

ganesh104
Messages: 5
Registered: November 2009
Location: India

Junior Member

Hi,

I am having a query which is executing fine(in 2 mins) but when i am using order by clause in it, its taking around 13 mins.
Can anyone suggest what could be th reason and how to execute the same query are get the ordered record?

thanks.

Report message to a moderator

Re: Long run time of query with Order by clause [message #442212 is a reply to message #442211]

Fri, 05 February 2010 06:25

Michel Cadot
Messages: 68650
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Just because sorting requires work.
Also ask your DBA to check the sort parameters.

Regards
Michel

Report message to a moderator

Re: Long run time of query with Order by clause [message #442213 is a reply to message #442211]

Fri, 05 February 2010 06:54

cookiemonster
Messages: 13922
Registered: September 2008
Location: Rainy Manchester

Senior Member

An index on the columns you're ordering by may help, then again it may not, without a lot more information I can't tell.

Report message to a moderator

Re: Long run time of query with Order by clause [message #442237 is a reply to message #442211]

Fri, 05 February 2010 15:06

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Can anyone suggest what could be th reason and how to execute the same query are get the ordered record?

What you report may be caused by Oracle utilizing FIRST ROWS hint.
Oracle may start providing results after 2 minutes but has not yet completed finding all rows to be returned; only 1st screenful.

When ORDER BY is included Oracle has to wait until all rows are obtained & SORTED (& the sort itself may only be a small part on the total time).

Please post EXPLAIN PLAN for both SQL (1 with ORDER BY & 1 without)

Report message to a moderator

Previous Topic:	HASH cluster building very Slow
Next Topic:	Index is not used after running dbms_stats.gather_table_stats

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 11 12:11:26 CDT 2024