Printable View
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
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.
If the workload is increasing( the reason processes is being increased) then we need to tune these parameter. What do you think?
how about setting them to what you actually need without guessing?