Home » RDBMS Server » Performance Tuning » Long run time of query with Order by clause (Oracle 10g)
Long run time of query with Order by clause [message #442211] Fri, 05 February 2010 06:20 Go to next message
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.
Re: Long run time of query with Order by clause [message #442212 is a reply to message #442211] Fri, 05 February 2010 06:25 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
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
Re: Long run time of query with Order by clause [message #442213 is a reply to message #442211] Fri, 05 February 2010 06:54 Go to previous messageGo to next message
cookiemonster
Messages: 12320
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.
Re: Long run time of query with Order by clause [message #442237 is a reply to message #442211] Fri, 05 February 2010 15:06 Go to previous message
BlackSwan
Messages: 24905
Registered: January 2009
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)
Previous Topic: HASH cluster building very Slow
Next Topic: Index is not used after running dbms_stats.gather_table_stats
Goto Forum:
  


Current Time: Mon Sep 26 11:11:20 CDT 2016

Total time taken to generate the page: 0.09047 seconds