There are at least some parameters in your init.ora that puzzle me
(many of them have been discussed).
I would reduce the sizes for most of your memory pools dramatically
especially the shared_pool - don't go (yet) into the big numbers like
10GB for the SGA.
I would also reduce following parameters :
- session_cached_cursors = 1000
- enqueue_resources = 8000
- log_checkpoint_interval (take 50% of size redo log file and divide it
through os block size)
- optimizer_max_permutations = 10000 (default is 80000)
- remove all _parameters
But my best guess lies in the query plans of your sql statements, it's
all about statistics. The application has not changed but the optimizer
has...my guess is that many sql statements now produce a different
query plan than before. Look at the query plans in detail and try to
compare them with your previous database (if available).
You could also try to run statistics with histograms: this gives the
optimizer more information and helps him to generate better query plan.
Try this :
exec dbms_stats.gather_schema_stats('???',5,true,method_opt=>'FOR ALL
INDEXED COLUMNS SIZE SKEWONLY',degree=>8,cascade=>true);
Received on Mon May 08 2006 - 10:34:09 CDT
