Home » RDBMS Server » Performance Tuning » pga_aggregate_target - Issue
pga_aggregate_target - Issue [message #249100] Tue, 03 July 2007 06:50 Go to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

Version - 9.2.07 HP-UX

I'm using pga_aggregate_target.

SQL> select * from v$pgastat;

NAME                                                    VALUE UNIT
-------------------------------------------------- ---------- ------------
aggregate PGA target parameter                      524288000 bytes
aggregate PGA auto target                           450146304 bytes
global memory bound                                  26214400 bytes
total PGA inuse                                      36227072 bytes
total PGA allocated                                  95754240 bytes
maximum PGA allocated                               518062080 bytes
total freeable PGA memory                            19398656 bytes
PGA memory freed back to OS                        1152909312 bytes
total PGA used for auto workareas                     6087680 bytes
maximum PGA used for auto workareas                 201482240 bytes
total PGA used for manual workareas                         0 bytes
maximum PGA used for manual workareas                 1060864 bytes
over allocation count                                       0
bytes processed                                    9126839296 bytes
extra bytes read/written                            234164224 bytes
cache hit percentage                                    97.49 percent

16 rows selected.

SQL> select name,value from v$sysstat
  2  where name like 'workarea executions%';

NAME                                                    VALUE
-------------------------------------------------- ----------
workarea executions - optimal                          182563
workarea executions - onepass                              38
workarea executions - multipass                             0

SQL>


Evenif workarea executions - multipass is 0, it has consumed Temporary tablespace and I have got the following error
ORA-1652: unable to extend temp segment by 1024 in tablespace                 TEMP


I understand that, 'workarea executions - multipass' consumed temporary tablespace multiple times because there isn't sort memory.

What could be the reason in this case it has used Temporary tablespace extensively. It was around 12GB. Is the single heavy job consumed the temporary tablespace completly.

Brayan.

Re: pga_aggregate_target - Issue [message #249104 is a reply to message #249100] Tue, 03 July 2007 07:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes it may be your first and lone sort exceeds the available temporary space.
Do you also have temporary tables?

Regards
Michel
Re: pga_aggregate_target - Issue [message #249105 is a reply to message #249100] Tue, 03 July 2007 07:05 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi Michel,

I do not have temporary tables.

Brayan
Re: pga_aggregate_target - Issue [message #249123 is a reply to message #249105] Tue, 03 July 2007 07:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Temporary LOBs?

Otherwise it seems this query uses the whole temp tablespace by itself.

Regards
Michel
Re: pga_aggregate_target - Issue [message #249126 is a reply to message #249100] Tue, 03 July 2007 07:48 Go to previous message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi Michel,

Neither I've LOB's.

Basically I'm refreshing a matrialzed view with the option "Complete". But the query does not have any sort/group by clauses.

I did not understand what you mean by
Quote:
query uses the whole temp tablespace by itself.



Thanks
Brayan
Previous Topic: Avoid Index to be used by Queries
Next Topic: Questions about Executing Explain Plan against a query.
Goto Forum:
  


Current Time: Wed Dec 07 03:08:45 CST 2016

Total time taken to generate the page: 0.11208 seconds