We recommend using the following Oracle tools for optimizing your database performance with Hexagon products.
OPTIMIZER_MODE Initialization Parameter
The OPTIMIZER_MODE initialization parameter establishes the default behavior for choosing an optimization approach for the instance. This parameter can be set in the initSIDname.ora or init.ora file to one of the following four values. For more information, see the Oracle performance tuning documentation at http://pbscrm.intergraph.com/pbs/sppid/Freeware/OracleAnalyzer.zip.
CHOOSE
The optimizer chooses between a cost-based approach and a rule-based approach based on whether statistics are available for the CBO. If the data dictionary contains statistics for at least one of the accessed tables, then the optimizer uses a cost-based approach and optimizes with a goal of best throughput. If the data dictionary contains no statistics for any of the accessed tables, then the optimizer uses a rule-based approach. This is the default value for the parameter.
ALL_ROWS
The optimizer uses a cost-based approach for all SQL statements in the session regardless of the presence of statistics and optimizes with a goal of best throughput (minimum resource use to complete the entire statement). If you are not gathering statistics on your database, we do not recommend using this setting.
FIRST_ROWS
The optimizer uses a cost-based approach for all SQL statements in the session regardless of the presence of statistics and optimizes with a goal of best response time (minimum resource use to return the first row of the result set). If you are not gathering statistics on your database, we do not recommend using this setting.
RULE
The optimizer chooses a rule-based approach for all SQL statements regardless of the presence of statistics. If you are not gathering statistics on your database, we do not recommend using this setting.
Oracle Cost Based Optimizer (CBO)
We recommend using the Oracle Cost Based Optimizer (CBO) on Online Transaction Processing (OLTP) systems. Use the following settings to encourage the CBO to use indexes instead of full table scans.
OPTIMIZER_INDEX_CACHING = 90
OPTIMIZER_INDEX_COST_ADJ = 35
Before you can use the CBO, you must set the OPTIMIZER_MODE initialization parameter to CHOOSE.
ARCHIVELOG Mode
We also recommend running Oracle in ARCHIVELOG mode, which copies filled online redo logs to disk. You must specify the mode at database creation or by using the ALTER DATABASE statement. You can enable automatic archiving either dynamically using the ALTER SYSTEM statement or by setting the initialization parameter LOG_ARCHIVE_START to TRUE.
Running the database in ARCHIVELOG mode has several advantages over NOARCHIVELOG mode. You can:
-
Back up the database while it is open and being accessed by users.
-
Recover the database to any required point in time.
-
To protect the ARCHIVELOG mode database in case of failure, back up the archived logs.
For more information, please see the SQL*Plus Backup and Recovery Concepts document in your Oracle documentation.
Other Recommendations
Some users have found using the following settings beneficial.
-
System Tablespace Max_Extents = UNLIMITED (If you choose to set this parameter to 505, the database monitoring system will warn you if the system tablespace is growing heavily. This setting has no impact on performance.)
-
Tablespace System / initial und next set to 64KB (Setting the pctincrease parameter to 50 allows you to take advantage of the dynamic growth provided by Oracle, leading to larger extents in a large database.)
-
RollBackSegment und Temp-Tablespace create rollback segment <name> tablespace rbs storage (optimal 10MB)
-
begin dbms_stats.gather_database_stats (cascate => TRUE); end;/ (Gathers statistics on the schemas. Use with the OPTIMIZER_MODE variable set to ALL_ROWS.)