Share this page
Oracle database tuning
This section provides the basic information needed to create an Oracle schema for StarTeam Server. We recommend using the Server Administration tool to create the schema, but if you prefer to create your own, follow the guidelines provided in this section.
The most efficient way to tune your Oracle database is to start with the recommended database settings and monitor the instance using the advisories. In addition to that, we recommend the use of Automatic Workload Repository (AWR) to collect performance statistics, including wait events used to identify performance problems. A detailed description of AWR is beyond the scope of this document. Please refer to your Oracle 10g performance tuning guide for more information.
Recommended initialization parameters
The following two tables recommend Oracle parameter settings for use with StarTeam Server databases.
This table lists common database configuration parameter values.
| Parameter | Recommended Value |
|---|---|
| Compatible |
10gR2: 10.2.0 11g: 11.1.0.0.0 |
| Cursor_sharing |
10gR2: Force 11g: Force |
| Log_checkpoint_interval | Greater than the redo log size |
| Log_checkpoint_timeout | 0 |
| Workarea_size_policy | Auto |
| Db_block_size | 16384 (16k) |
| Db_file_multi_block_read_count | 16 |
| Optimizer_mode | first_rows |
| Timed_statistics | True |
| Open_cursors | 400 |
| Undo_management | Auto |
| Undo_tablespace | (Name of the undo tablespace) |
| Undo_retention | 28800 |
| Processes | 250 |
| Statistics_level | Typical |
This table lists the database parameters based on total memory.
| Total Memory | Recommended 10gR2 Settings | Recommended 11g Settings |
|---|---|---|
| 1 GB |
SGA_TARGET = (Total Physical Memory * 80%) * 60%. We assume that 20% of the total memory will be used by the OS. Statistics level should be TYPICAL or ALL. LOG_BUFFER = 524288. PGA_AGGREGATE_TARGET = (Total Physical Memory * 80%) * 30%. 30% of the non-OS available memory. This is the starting value. This may need to be adjusted upwards. |
MEMORY_TARGET = Total Physical Memory * 75%. We assume that 20% of the total memory will be used by the OS. The Oracle instance should be running on a dedicated machine. Statistics level should be TYPICAL or ALL. LOG_BUFFER = 524288. |
| 2 GB |
SGA_TARGET = (Total Physical Memory * 80%) * 60%. We assume that 20% of the total memory will be used by the OS. Statistics level should be TYPICAL or ALL. LOG_BUFFER = 1048576. PGA_AGGREGATE_TARGET = (Total Physical Memory * 80%) * 30%. We assume that 20% of the total memory will be used by the OS. This is the starting value. This may need to be adjusted upwards. |
MEMORY_TARGET = Total Physical Memory * 75%. We assume that 20% of the total memory will be used by the OS. The Oracle instance should be running on a dedicated machine. Statistics level should be TYPICAL or ALL. LOG_BUFFER = 1048576. |
| 4 GB |
SGA_TARGET = (Total Physical Memory * 80%) * 60%. We assume that 20% of the total memory will be used by the OS. Statistics level should be TYPICAL or ALL. LOG_BUFFER = 1048576. PGA_AGGREGATE_TARGET = (Total Physical Memory * 80%) * 30%. We assume that 20% of the total memory will be used by the OS. This is the starting value. This may need to be adjusted upwards. |
MEMORY_TARGET = Total Physical Memory * 75%. We assume that 20% of the total memory will be used by the OS. The Oracle instance should be running on a dedicated machine. Statistics level should be TYPICAL or ALL. LOG_BUFFER = 1048576. |
Tuning Oracle 10gR2 Databases
This section provides information about tuning Oracle 10g databases.
Automatic Shared Memory Management
Oracle 10g utilizes Automatic Shared Memory Management (ASMM) of individual SGA components like shared pool, java pool, large pool and db cache. You do not need to estimate when setting the size of SGA components. In fact, there is no need to set any parameters defining SGA size.
All you have to do is to set a new parameter called SGA_TARGET. The parameter SGA_TARGET takes a value which indicates the maximum size of SGA required for your instance.
Consider that you set SGA_TARGET to say 800MB. This indicates that maximum size to which SGA can grow is 800MB. All the SGA components like shared pool, buffer cache, large pool, java pool will be allocated from this 800M maximum SGA. Oracle will automatically calculate the initial size of these components and resizes it as per the requirement without any manual intervention.
You do not have to explicitly define values for shared pool, buffer cache, large pool and java pool if you set SGA_TARGET. The SGA_TARGET will be limited by the SGA_MAX_SIZE value. The SGA_MAX_SIZE cannot be modified dynamically. If SGA_MAX_SIZE is not set, both the parameters have the same value and it will be not possible to increase the size of SGA_TARGET dynamically.
Automatic Segment Space Management
The Automatic Segment Space Management (ASSM) feature allows Oracle to use bitmaps to manage the free space within segments. The bitmap describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows. The current status of the space available in a data block is reflected in the bitmap allowing Oracle to manage free space automatically with ASSM.
ASSM tablespaces automate freelist management and remove the ability to specify PCTUSED, FREELISTS, and FREELIST GROUPS storage parameters for individual tables and indexes created in ASSM tablespaces. The values for parameters PCTUSED and FREELISTS are ignored and Oracle automatically manages the space for these tables and indexes inside the tablespace using bitmaps. PCTFREE can still be specified and is used with ASSM.
Tuning Oracle 11g Databases
This section provides information about tuning Oracle 11g databases.
Automatic Memory Management (AMM)
Beginning with version 11g, the Oracle database can automatically manage the SGA memory and the instance PGA memory. You only need to designate the total memory size to be used by the instance, and the Oracle database will dynamically exchange memory between the SGA and the instance PGA as needed to meet processing demands. This capability is referred to as automatic memory management. With this memory management method, the database also dynamically tunes the sizes of the individual SGA components and the sizes of the individual PGAs.
AMM is implemented using Memory_Target and Memory_max_target parameters. Use of MEMORY_MAX_TARGET is optional. When MEMORY_MAX_TARGET is not set, Oracle automatically sets MEMORY_MAX_TARGET to the value of MEMORY_TARGET.
To switch to AMM, define the value of MEMORY_TARGET in the spfile. It is important to ensure that the values of SGA_TARGET and PGA_AGGREGATE_TARGET are set to 0. Customers upgrading to 11g instance must set the value of SGA_TARGET and PGA_AGGREGATE_TARGET to 0.
