This section helps you configure the Oracle-based PMDB after you install Precise. This is not a replacement of the content within Installation and Administration. The configuration recommendations help you to fine-tune the PMDB, which then results in faster and more efficient performance.
Prerequisites
Before applying the information in this section, refer to the content within Installation and Administration . We recommend that the PMDB is an Oracle-dedicated instance.
Make sure that you are creating the database with the proper NLS_CHARACTERSET
. When the monitored Oracle instances use a multiple byte character set, use UTF8.
AL32UTF8 is not supported by the PMDB.
The following table specifies the database block size prerequisite.
Table 1 Database block size prerequisite
Parameter | Value | Improvement |
---|---|---|
db_block_size | Minimum 8KB (16KB recommended) | This value defines the Oracle block size. The block size is set when creating the database and cannot be changed afterwards. A value of at least 8 KB ensures that the PMDB can perform smoothly. A lower value may cause serious performance problems.
To review the default block size value, check the |
Required INIT.IRA changes
To improve performance, change some parameter values and make sure the values match those listed in the appropriate table.
You can change the parameter values on a new and on an existing Oracle instance. Tables 2 through 5 describe the general and Oracle version-specific parameters.
Table 2 General parameters
Parameter | Value | Improvement / Remarks |
---|---|---|
log_buffer | If the version < 9.2.0.7, then the log_buffer=3MB , else the log_buffer=16MB | |
shared_pool_size | 1/4 of the physical memory | The shared pool should not be too small (<250MB) or too large (>2GB) |
log_checkpoint_timeout | 900 | |
open_cursors | 2000 | Make sure you have enough open cursors for the application to execute. |
Processes | 300 | Make sure you have enough open processes for the application to execute. |
db_writer_processes | Should be set according to the number of I/O devices you have for the database | Improves I/O performance in cases of large I/O. Each writer is writing to a different disk (improves loads, for example). |
Table 3 8i parameters
Parameter | Value | Improvement / Remarks |
---|---|---|
sort_area_size | (1/8th of the physical memory)/(concurrent users + 10), up to 6MB | For example: If you have 2GB of physical memory and 90 users, then the calculation is: (2GB/8) / 100 ~= 2.5MB |
db_block_buffers | 1/3rd of the physical memory divided by the size of the db_block_size | The given values help to keep the I/O wait of the PMDB queries low. |
Table 4 9i parameters
Parameter | Value | Improvement / Remarks |
---|---|---|
db_cache_size | Replaces parameter db_block_buffers . Sets the final size of the buffer pool to 1/3rd of the physical memory. | |
pba_aggregate_target | 1/3rd of the physical memory | |
workarea_size_policy | auto | This parameter should be set to auto when we use the pga_aggregate_target parameter. |
session_cached_cursors | 300 | |
db_cache_advice | Make sure that this parameter is set to off because the default value is on . | This parameter checks the work on the database and helps determine the recommended size of the buffer pool. But this damages performance, so make sure it is on only for a short period. |
Table 5 10g parameters
Parameter | Value | Improvement / Remarks |
---|---|---|
sga_target | Automatic memory management. 1/4th of the physical memory. | Replaces the following parameters:
Make sure to initialize them with 0 or the minimum value you need. |