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.

Sam