Home » RDBMS Server » Server Administration » PGA content (oracle9i)
PGA content [message #325325] Thu, 05 June 2008 14:43 Go to next message
shrinika
Messages: 266
Registered: April 2008
Senior Member
Dear All,

I have one clarification in PGA memory.

Here is the oracle version i am using

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production


My understanding here is, The PGA consist of below.

1. user’s session variables and arrays,
2. session addresses,
3. stack space information
4. UGA(if the server is running on Dedicated server. If the server is running in MTS, then UGA will be part of SGA(large pool))

My question here is, where would fit the below memory area? Are these part of PGA??
SORT_AREA_SIZE
HASH_AREA_SIZE
BITMAP_MERGE_AREA_SIZE
CREATE_BITMAP_AREA_SIZE 

I do underand that, these above *AREA_SIZE parameters are automatically tuned for dedicated server by using couple of init paramters(PGA_AGGREGATE_TARGET, WOKR_AREA_SIZE_POLICY). But in MTS, these paramters needs to be specified in parameter file.

I would appreciate if you any one could clarify my question. Thanks




Re: PGA content [message #325365 is a reply to message #325325] Fri, 06 June 2008 00:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are part of the PGA.
There are automatically sized if you use pga_aggregate_target or workarea_size_policy=AUTO for your session (or instance) otherwise there are set by the parameters you give on your session or instance.

Regards
Michel
Re: PGA content [message #325501 is a reply to message #325365] Fri, 06 June 2008 07:17 Go to previous messageGo to next message
shrinika
Messages: 266
Registered: April 2008
Senior Member
Thank you Michel.
Re: PGA content [message #325595 is a reply to message #325501] Fri, 06 June 2008 11:20 Go to previous messageGo to next message
shrinika
Messages: 266
Registered: April 2008
Senior Member
Michel, One clarification in sort_area_retained_size.

In MTS, When we allocate SORT_AREA_RETAINED_SIZE, it is part PGA. When sort completes, it release the SORT_AREA_RETAINED_SIZE to UGA. It means that, the sort_area_retained_size will be part of SGA after the sort is completed(before the final sort). Since UGA is part of SGA in MTS.

Am i correct on the above part??

Thanks

Re: PGA content [message #325602 is a reply to message #325595] Fri, 06 June 2008 12:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Am i correct on the above part??

Yes, because Oracle must be able to retrieve the "retained area" at the next call.

Regards
Michel
Re: PGA content [message #325789 is a reply to message #325602] Sun, 08 June 2008 19:36 Go to previous message
shrinika
Messages: 266
Registered: April 2008
Senior Member
Thank you !!!
Previous Topic: delete statement is hanging when undo reaches 12G space
Next Topic: archiver process
Goto Forum:
  


Current Time: Thu Dec 08 12:52:12 CST 2016

Total time taken to generate the page: 0.14683 seconds