Page tree

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

ParameterValueImprovement
db_block_sizeMinimum 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.

  • If you are installing on a legacy database, verify that the database block size is 8 KB or higher.
  • If not, try to install the PMDB on a different Oracle instance.

To review the default block size value, check the db_block_size parameter in the init.ora file.

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

ParameterValueImprovement / Remarks
log_bufferIf the version < 9.2.0.7, then the log_buffer=3MB, else the log_buffer=16MB
shared_pool_size1/4 of the physical memoryThe shared pool should not be too small (<250MB) or too large (>2GB)
log_checkpoint_timeout900
open_cursors2000Make sure you have enough open cursors for the application to execute.
Processes300Make sure you have enough open processes for the application to execute.
db_writer_processesShould be set according to the number of I/O devices you have for the databaseImproves I/O performance in cases of large I/O. Each writer is writing to a different disk (improves loads, for example).

Table 3 10g parameters

ParameterValueImprovement / Remarks
sga_targetAutomatic memory management. 1/4th of the physical memory.

Replaces the following parameters:

db_cache_size, shared_pool_size, large_pool_size, and java_pool_size.

Make sure to initialize them with 0 or the minimum value you need.

init.ora example

The following is an example of the init.ora file for an Oracle server with 2GB RAM and two disks:

db_block_size=16384 # 16K
db_cache_size = 268435456 # 256MB
log_buffer=5242880 # 5 MB
shared_pool_size=471859200 # 450MB
log_checkpoint_timeout=900 # 15 minutes
pga_aggregate_target=262144000 # 256MB
session_cached_cursors=300
open_cursors=2000 processes=300
undo_management=AUTOundo_tablespace=UNDOTBS1
undo_retention=900 # 15 minutes

Additional DBA settings

To improve performance, the DBA on site must modify the Rollback segment settings and the Redo log file settings. The following table describes those settings.

Table 4 Rollback segment and Redo log file settings

SettingDescriptionValue
Rollback segments (For 8i)Minimum tablespace size

2GB*

*4GB recommended in larger installations.

Rollback segments (For 8i)Minimum number of segments

20

Rollback segments (For 8i)Minimum initial segment size

5MB*

*Each Rollback segment should be able to grow to the size of the tablespace.

Enable AUM (For 9i and above)

Automatic Undo Management 

See Enabling Automatic Undo Management (AUM)

For 9i and higher

Temporary tablespaceMinimum temporary tablespace size

2GB*

*The temporary tablespace is most likely to grow according to the PMDB load.

If the Redo file is switching every 20 minutes, enlarge the file.

Redo log fileMinimum redo log file size

1GB*

*4GB recommended in larger installations

If the Redo file is switching every 20 minutes, enlarge the file.

Additional Rollback segment and Redo log file information

Each Rollback segment should contain a minimum of five extents. The size of each extent within a Rollback segment should be at least 1MB. Their optimal size should be set to the summed size of those extents. For example, if creating rollback segment with five extents of 1MB each, they should set the optimal size to 5MB. This is to prevent the changing size of the rollback segment to exhaust the tablespace's free space.

If possible, keep data tablespace indexes and temporary tablespace on different disk drives. Make sure that you disable the recycle bin in Oracle. See How to disable the recycle bin in Oracle version 10g. For more information, refer to the Using Flashback Drop and Managing the Recycle Bin section in the Oracle Administration Guide.

Four Redo logs should be created, each with 1 mirror (2 members for each group). Remember that hardware mirroring is better than software mirroring.

In Oracle version 9i and higher, it is recommended to configure the Undo space management, otherwise you must configure the rollback segments according to the rules in Table 3-6.

If you are using an archive (log_archive_start set to true) and you enable parallel archiving writers, then the log_archive_max_processes should be set to more than 1 (same consideration as in the db_writer_process).

How to disable the recycle bin in Oracle 10g

Our recommendation is to disable the recycle bin and this section describes the background and the action on how to disable the recycle bin.

The recycle bin is actually a data dictionary table containing information about dropped objects. Dropped tables and any associated objects such as indexes, constraints, nested tables, and the likes are not removed and still occupy space. They continue to count against user space quotas, until specifically purged from the recycle bin or the unlikely situation where they must be purged by the database because of tablespace space constraints.

The following information is taken from the Oracle Database Administration Guide.

In version 10.0 and 10.1, use the following command:

alter system set
"_recyclebin"=FALSE scope=BOTH;

In version 10.2, use the following command:

alter system set recyclebin=off scope=BOTH

Enabling Automatic Undo Management (AUM)

To enable Automatic Undo Management

  1. Create the undo tablespace by running the following command:
      CREATE UNDO TABLESPACE tablespace_name DATAFILE 'data file name' SIZE 32M REUSE AUTOEXTEND ON EXTENT MANAGEMENT LOCAL
  2. Set the following init.ora parameters:
      undo_management = AUTO
      undo_tablespace = tablespace_name
      undo_retention = 1800

    If the undo tablespace size is too large you can decrease the number of seconds.

For more information on how to enable AUM, see the Managing Undo Space section in the Oracle Database Administrator's Guide.

Backing up the database and creating archiving

The PMDB holds information for up to three years, by default. If you want to back up this information, you should enable the Redo log archiving, and then create a backup using RMAN or a backup utility. For more information, refer to the official Oracle Backup and Recovery documentation.

If you do not create a backup, information is lost when you have a database crash and you cannot recover data. If you create a backup without using the archive mode, you can recover data to the time of the backup. All new operations (after the logs were archived) are lost.

Maintenance operations

Every week the maintenance process analysis the PMDB, but to improve the database performance, the DBA must check and perform a rebuild to the indexes using the alter index {name} rebuild command.

Changing the size of tables and index extents

The PMDB uses the products\dbms\files\tables_definition\ps_00_db_entities.xml file to create the tablespaces for the tables and the indexes. To change the default sizes, you can change the file before the PMDB installation or alter the tablespaces after the installation.

The following table shows the default extents sizes.

Table 5 Default extents sizes

TablespaceSize
Large table2MB
Medium table512KB
Small table80KB
Large index1MB
Medium index256KB
Small index80KB



IDERA |  Products | Purchase | Support |  Community |  Resources |  About Us  | Legal