OraFAQ Forum: Performance Tuning » All sorting etc done on temp space

Home » RDBMS Server » Performance Tuning » All sorting etc done on temp space

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

All sorting etc done on temp space [message #131873]

Tue, 09 August 2005 19:58

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

Report message to a moderator

Re: All sorting etc done on temp space [message #132080 is a reply to message #131873]

Wed, 10 August 2005 14:25

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?

Report message to a moderator

Re: All sorting etc done on temp space [message #132098 is a reply to message #132080]

Wed, 10 August 2005 16:46

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

Report message to a moderator

Previous Topic:	I don't get Rows & Row Source Operation
Next Topic:	Full Table Scan

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 16:46:51 CST 2024