This was asked to one of my friend for a senior level dba,
1) How does parallel server work;
2) What is dynamic partitioning;
3) What are the SGA Parameters that has to be tuned for having a optimum SGA performance.
Could you hint on these?
SGA tuning is like,
Specifying DB_Block_buffers, Shared_pool_area and No of cursors and redo log buffers are main components to name
1. OPS consists of 2 or more instances accessing the same db. In case one of the instance fails, the other(s) are available to access the db. This is also used as an application failover mechanism.
2. I believe this might be something like automatic partitioning of the table upon it's growth?? not sure though.
Answering to the question #3, SGA is composed of
(db_block_buffers * db_block_size) + shared_pool_size + log_buffers + java_pool_size + large_pool_size
Now coming to the performance issue, you have to check that
you have enough redo logs and roll back segments.
Then comes the OPEN_CURSOR parameter, this should be sufficient enough to have the recursive calls by the session. Then comes that dml_locks. You should have enough dml_locks from running out of locks. Then one of the primary parameters is SORT_AREA_SIZE and SORT_RETAIN_SIZE. This would help to do the sorting in memory, but be careful when you set this parameter, b'cos this would be allocated per user basis.
At this point I'm interested to know of the dynamic partitioning.
Life is a journey, not a destination!
I Guess Dynamic partitioning must be has partitioning??
Click Here to Expand Forum to Full Width