DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: ny experience with the init parameter _pga_max_size ?

  1. #1
    Join Date
    Mar 2002
    Posts
    534

    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.

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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

  3. #3
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    maybe tamil is referring to sort_area_size

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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

  5. #5
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    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?

  6. #6
    Join Date
    Mar 2002
    Posts
    534
    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.

  7. #7
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    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.

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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
  •  


Click Here to Expand Forum to Full Width