-
Any experience with the init parameter _pga_max_size ?
Hi,
I'm working on DWH Project using Oracle 9.2.0.5 Our production servers has 20cpus and around 40Gb of ram. The pga_aggregate_target is set to 16GB and workarea_size_policy is set to AUTO.
The big problem we have is that from these 16GB of PGA only 5GB are used (based on V$PGASTAT). The reason for that is that Oracle never allocates more then 100MB of PGA to a serial or a parallel process.
So to improve the performance of these queries which need more then 100MB of PGA I was thinking about to use the parameter "_pga_max_size" and to set it to 256MB.
Has anybody so experience with this parameter?
Thanks for any feedback
http://www.dbasupport.com/forums/sho...threadid=41361
Last edited by mike9; 07-09-2004 at 07:44 PM.
-
For A DW system, I would not opt for PGA_AGGREGATE_TARGET. Primarily this parameter is designed for OLTP system.
Use "_size" parameters optimally.
Tamil
-
maybe tamil is referring to sort_area_size
-
Code:
I am talking about the following parameters:
bitmap_merge_area_size integer 1048576
create_bitmap_area_size integer 8388608
db_16k_cache_size big integer 0
db_2k_cache_size big integer 0
db_32k_cache_size big integer 419430400
db_4k_cache_size big integer 0
db_8k_cache_size big integer 0
db_block_size integer 8192
db_cache_size big integer 3774873600
db_keep_cache_size big integer 419430400
db_recycle_cache_size big integer 0
global_context_pool_size string
hash_area_size integer 33554432
java_max_sessionspace_size integer 0
java_pool_size big integer 33554432
large_pool_size big integer 33554432
max_dump_file_size string 10240000
object_cache_max_size_percent integer 20
object_cache_optimal_size integer 10240000
olap_page_pool_size integer 33554432
oracle_trace_collection_size integer 5242880
parallel_execution_message_size integer 2152
sga_max_size big integer 5142974552
shared_pool_reserved_size big integer 30000000
shared_pool_size big integer 301989888
sort_area_retained_size integer 2097152
sort_area_size integer 32554432
Tamil
-
thanks a tamil,
but may i know what is the most influencing parameter among the *_size that greatly influence the use of PGA especially in a DWH?
-
reydp,
I would say that this depends on how your DWH is set up and how good your Data Marts have been designed. The 2 Parameters which will probably influence the most the total size of the PGA are the parameters sort_area_size and hash_area_size.
Tamil,
Thanks for your input. From what I found out so far I totaly agree with you that the "new" PGA management is not suitable for large DWH applications where you have a lot memory but only a few concurent users. That should be much better documented in the Oracle Doc.
I will try to convince my DBA to set the workarea policy to manual and set the PGA parameters back to the value they had when we were using 8i.
-
Originally posted by mike9
reydp,
I would say that this depends on how your DWH is set up and how good your Data Marts have been designed. The 2 Parameters which will probably influence the most the total size of the PGA are the parameters sort_area_size and hash_area_size.
mike9,
I just thought for a while if there are other init parameters that greatly influence the PGA more than the sort_area_size.
-
Apart from the "_size" parameters, the most influence parameter is PROCESSES. Depending upon the value of PROCESSES parameter, Oracle determines the max size for each process within the PGA memory. That is why I said, for DW system , it is not advisable to use PGA.
You may set processes to 200 in the init.ora, but in real life only 10 or 12 users use the system.
Tamil
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|