Order By clause is slowing my query

[tao]

Hi,

I'm migrating from 8.0.4 on a HP-UX 10.20 bi 120Mhz to 8.1.7.4 on a HP-UX 11.00 bi 650 Mhz. My question is :
What kind of problem could be able to increase the time of a query which is identical on my 2 systems ?
On the old one, The result of my query is instantaneous and on the new one, I've got the result after ....17 minutes !!
On the new database, I've got to delete the Order By clause for the query to be as fast as on the old system !
PS : The order by clause in on an Index

Thanks a lot for your help,

 

[hallux]

Have you run analyze on the tables and indexes? On our system the tables had not been analyzed after making some changes and it caused queries to slow down.

Check for the last analyze date for tables
SELECT table_name, num_rows, last_analyzed
FROM user_tables;

and for indexes
SELECT table_name, num_rows, last_analyzed
FROM user_indexes;

-Brent

 

[tao]

Thanks Brent,

It seems the only solution was to Drop and recreate the Index ! Analyse action on the tables and on the Indexes had no effects !
I open a call in ORACLE technical Center but still wait for the answer !

Thanks again and have a nice day !

 

[sem]

The obvious reason is lack of statistics. Try to use dbms_stats package instead of 'analyze table'. Oracle preformance depends not only on hardware, but also on settings being used. You may have some gigs of memory, but use only 10 megs or less for SGA. Check SORT_AREA_SIZE parameter and TEMPORARY TABLESPACE used by your user.

Regards, Dima