Initialization parameter recommendations after upgrade from 9i/10g to 11g

posted Aug 4, 2011, 4:52 PM by Sachchida Ojha

The following init.ora parameter recommendations were identified andimplemented during the tests:
• Remove all Oracle 9i specific parameters from the initialization parameterfile, such as:
• _ALWAYS_ANTI_JOIN='OFF'
• _ALWAYS_SEMI_JOIN ='OFF'
• _SHARED_POOL_RESERVED_MIN_ALLOC=4000
• _UNNEST_SUBQUERY=FALSE
• DISK_ASYNCH_IO=FALSE
• SORT_AREA_SIZE=4194304
• Change a few parameters to values required by Oracle Database 11g:
• LOG_ARCHIVE_FORMAT='_%r_%t_%s.arc'
The ‘%r’ option is required since Oracle Database 10g specifies the resetlogs-ID withinthe naming format for the archive logs.

• TIMED_STATISTICS=TRUE
Always set this parameter to true to get useful and correct performance information.
• Set new parameters:
• CURSOR_SPACE_FOR_TIME=TRUE
• DIAGNOSTIC_DEST='/m161/oracle/admin'
Specifies the new Automatic Diagnostic Repository location for all traces and dumps.This replaces parameters such as background_dump_dest and user_dump_dest.

• OPTIMITER_INDEX_COST_ADJUST=75
This parameter was modified to favor full table scans over index scans – the workloadin this case is more CPU bound – IO is not an issue.

• OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE
• OPTIMIZER_USE_SQL_PLAN_BASELINES=FALSE
• PGA_AGGREGATE_TARGET=1000M
• RECYCLEBIN=OFF
The recycle bin has been switched off because it won’t get used in the currentproduction environment.

• SESSION_CACHED_CURSORS=500
• SHARED_POOL_SIZE=1250M
• _DISABLE_FLASHBACK_ARCHIVER=1
The background process FBDA has been switched off because Total Recall is a featurewhich is not used in the production environment.
Comments