Home » RDBMS Server » Performance Tuning » All sorting etc done on temp space
All sorting etc done on temp space [message #131873] Tue, 09 August 2005 19:58 Go to next message
djcot
Messages: 2
Registered: August 2005
Junior Member
Good evening,

We have recently seen a materialized view that took 35 mins take nearly 7hrs the next day. The only thing I noticed was that the temp space is doing all the work. We raised the pga_aggregate_target=1024M from 290M but it did not help. Also Oracle thinks it is doing a great job with it in memory. IE

Percent memory sorts
--------------------
99.59

Thanks in advance.

DJ

Re: All sorting etc done on temp space [message #132080 is a reply to message #131873] Wed, 10 August 2005 14:25 Go to previous messageGo to next message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
Had the size of any of the materialized view's underlying tables changed significantly over the course of that day? A big INSERT, for example?

Have the view's tables been analyzed sufficiently recently?
Re: All sorting etc done on temp space [message #132098 is a reply to message #132080] Wed, 10 August 2005 16:46 Go to previous message
djcot
Messages: 2
Registered: August 2005
Junior Member
Yes there was an increase on one of the tables but it was the day before we started having problems. The first day after we added data it went from 35 min to 42 min then a day later it went to 7hrs. We have done a compute statistics after we exp this problem.

It is thrashing the disks for the tmp space. I don't know how to force it to do more/all in memory. Any ideas?

Thanks,
DJ
Previous Topic: I don't get Rows & Row Source Operation
Next Topic: Full Table Scan
Goto Forum:
  


Current Time: Mon Nov 28 06:45:35 CST 2022