Page History

...

...

Oracle-based PMDB configuration

This section is divided into information for Oracle 11g and 10g

Oracle 11g

In Oracle 11g the automated maintenance tasks infrastructure known as AutoTask enables Oracle to automatically schedule Automatic Maintenance Tasks. AutoTask schedules automatic maintenance tasks to run in a set of Oracle Scheduler windows known as maintenance windows. Maintenance windows are those windows that are members of the Oracle Scheduler window group MAINTENANCE_WINDOW_GROUP.

Oracle 11g includes three automated database maintenance tasks:

• Automatic Optimizer Statistics Collection - Gathers stale or missing statistics for all schema objects. The task name is auto optimizer stats collection.
• Automatic Segment Advisor - Identifies segments that could be reorganized to save space. The task name is auto space advisor.

• Automatic SQL Tuning Advisor - Identifies and attempts to tune high load SQL. The task name is sql tuning advisor.

  Info
  If you experience performance problems in Precise during the default maintenance times, we recommend to change the maintenance schedules or ask advice from your Oracle DBA. It is recommended to use a block size of 16 Kbytes.

The provides recommended values for Oracle configuration. The following tables describe the Oracle-based PMDB configuration parameters and sizing recommendations that need to be inserted in the init.ora file for Oracle 11g:. 

If Automatic Memory Management is not used, the following parameter values are recommended.

Table 6 Oracle 11g-based PMDB configuration parameters

Table 7 Oracle 11g-based PMDB sizing recommendation

...

If on a Linux server you get an ORA-845 error, you must mount /dev/shm needs to be mounted with its proper size. The size is directly influenced by the SGA size of your PMDB. If you set it too low you will get this error.

To mount it, log in as a root user and use the following command: # mount -t tmpfs shmfs -o size=<SGA size> /dev/shm

To activate the setting and make it permanent after a restart, add the entry in /etc/fstab.

Oracle 10g

By default Oracle 10g automatically gathers optimizer statistics using a scheduled job called GATHER_STATS_JOB. By default this job runs within a maintenance window between 10 P.M. to 6 A.M. week nights and all day on weekends. The job calls the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC internal procedure which gathers statistics for tables with either empty or stale statistics, similar to the DBMS_STATS.GATHER_DATABASE_STATS procedure using the GATHER AUTO option. The main difference is that the internal job prioritizes the work such that tables most urgently requiring statistics updates are processed first.

It is important to understand that there are two scheduled activities related to the collection of Oracle "statistics". These are very different:

• AWR statistics. Oracle has an automatic method to collect AWR "snapshots" of data that is used to create elapsed-time performance reports.

• Optimizer statistics. Oracle has an automatic job to collect statistics to help the optimizer make intelligent decisions about the best access method to fetch the desired rows.

The following tables describe the Oracle-based PMDB configuration parameters and sizing recommendations that need to be inserted in the init.ora file for Oracle 10g:

Table 8 Oracle 10g-based PMDB configuration parameters

Table 9 Oracle 10g-based PMDB sizing recommendation

If on a Linux server you get an ORA-845 error, /dev/shm needs to be mounted with its proper size. The size is directly influenced by the SGA size of your PMDB. If you set it too low you will get this errorThis error results when it is set too low.

To mount it, log in as a root user and use the following command: # mount -t tmpfs shmfs -o size=<SGA size> /dev/shm

...

Save