Page History
...
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.
| 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 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.
| Info |
|---|
It is recommended to use a block size of 16 Bytes. |
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
| Parameter | Value |
|---|---|
SGA_TARGET | 45 GB |
SGA_MAX_TARGET | 45 GB |
LOG_BUFFER | 5 MB |
SESSIONS | 1500 |
| PROCESSES | 1000 |
UNDO_RETENTION | 7200 |
DB_WRITER_PROCESSES | 4 |
Table 9 Oracle 10g-based PMDB sizing recommendation
| Description | Value |
|---|---|
Redo Log | 500 MB for 90 instances 1000 MB for 180 instances 2000 MB for 450 instances |
PMDB Temporary Tablespace | 4 files and each file 32 GB |
Undo tablespace | 4 files and each file 32 GB |
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 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.
SQL Server-based PMDB configuration
...