Impact of Increasing processes parameter
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Impact of Increasing processes parameter

Hybrid View

  1. #1
    Join Date
    Apr 2001
    Posts
    112

    Impact of Increasing processes parameter

    Hello,
    1. I wanted to find out if I need to increase the setting for open_cursors if I am bumping up the processes parameter.

    2. Besides the system level semaphore settings what additional things I should be looking at ? eg db_block_buffer, shared_pool, sort_area_size.

    3. How do I calculate the amt of memory taken by once connection? This will help in calculating how much additional I need to add for the delta in the increase in connections?

    Thanks

  2. #2
    Join Date
    Mar 2006
    Posts
    40
    1. No. The open_cursors parameter is per session limit of cursors i.e. the number of cursors a session can open at any time, so increasing the number of processes has no relation to this parameter.

    2.There is no direct correlation between processes and thos parameters that you mentioned. If you have more concurent processes executing different SQLs, working with data from different tables you might need bigger buffer cache (more db_block_buffers), but if the more processes means more SQLs redaing the same data then you don't need to increase it. I would not increase it, but later when the number of processe increase anaylze the buffer cache usage.
    It is same for the shared_pool parameter - as it is a cache for SQL statements, if more processes means more SQls thar are different are executed you should incraese the shared_pool, otherwise no.
    The sort_area_size limits the amount of memory that a session can allocate for sorting, joining tables and stuff like that, so as it is a per session parameetr you don't need to increase it.

    3. If you have a pre 9i version of Oracle sort_area_size is the amount of memory each session can allocate - but it doesn't mean it will it is a maximum. Sort_area_retained_sizes a size that it is allocated. If you have a 9i or 10g you can set this parameter to 0 and set the pga_aggregate_target parameter which is a shared memory for all the sessions for the purpose of sorting, joining, etc..So if you have more processes you might consider ibcreasing this parameter (chech the advisor views for this).
    If you have many concurent sessions and worry that you will have memory issues you might considering runnign the database in a shared_server mode.
    go to the oracle clinic
    Quick and easy Oracle database support and consulting

  3. #3
    Join Date
    Apr 2001
    Posts
    112
    If the workload is increasing( the reason processes is being increased) then we need to tune these parameter. What do you think?

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    how about setting them to what you actually need without guessing?

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