Home » RDBMS Server » Performance Tuning » sort unique problem
sort unique problem [message #167257] Wed, 12 April 2006 03:15 Go to next message
hypnotoad
Messages: 2
Registered: April 2006
Junior Member
hi

i have a problematic query, running differently on the test and production servers.

the query is using the same access path on both servers,
the only the difference is that the last phase of it - the sort unique is taking 2 hours on the production server and 2 minutes in on the test server

both servers has about the same memory configuration and workload, the data volume is the same (imported the data to the test server)

how can i speed up this sort?

(running oracle 9.2)

thanks
Re: sort unique problem [message #167288 is a reply to message #167257] Wed, 12 April 2006 04:52 Go to previous messageGo to next message
dhar_kiran
Messages: 7
Registered: November 2005
Location: Delhi
Junior Member

Send me the query as well as the execution plan on both databases.
Re: sort unique problem [message #167577 is a reply to message #167288] Fri, 14 April 2006 02:07 Go to previous message
hypnotoad
Messages: 2
Registered: April 2006
Junior Member
the problem is the sort unique, this adds about 2 hours
and it's only 5m rows to sort
(the query cannot be altered)

SELECT STATEMENT, GOAL = CHOOSE
_VIEW
__SORT UNIQUE
___UNION-ALL
____NESTED LOOPS OUTER
_____TABLE ACCESS FULL TABLE__1
_____TABLE ACCESS BY INDEX ROWID TABLE_0
______INDEX RANGE SCAN INDEX_ON_TABLE_0
____NESTED LOOPS OUTER
_____TABLE ACCESS FULL TABLE__0
_____TABLE ACCESS BY INDEX ROWID TABLE__1
______INDEX RANGE SCAN INDEX_ON_TABLE_1

[Updated on: Fri, 14 April 2006 02:08]

Report message to a moderator

Previous Topic: Tool
Next Topic: Large SGA
Goto Forum:
  


Current Time: Sun May 26 22:00:36 CDT 2024