Page History
...
Table 6-1 Additional DBA settings for the SQL Server-based PMDB
| Setting | Description | Value |
|---|---|---|
| Size of the datafile | The default size when the PMDB is created. | 2 GB |
| Size of the log file | The default size when the PMDB is created. | 500 MB |
| Auto Extent size for datafiles and logs | The size of the new segment to be allocated. | 30% of initial datafile (for example, if the datafile is initially 1 GB, the auto extent size should be 300 MB). |
| Tempdb location | Put the |
| Anchor | ||||
|---|---|---|---|---|
|
...
Table 6-2 Oracle Server Database block size prerequisite
| Parameter | Value | Improvement | ||
|---|---|---|---|---|
| db_block_size | Minimum 8 KB (16 KB 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.
|
Required INIT.ORA changes
...
Table 6-3 General parameters for the Oracle-based PMDB
| Parameter | Value | Improvement / Remarks |
|---|---|---|
| log_buffer | If the version <9.2.0.7, then the log_buffer = 3 MB, else the log_buffer= 16 MB. |
|
| shared_pool_size | 1/4 of the physical memory. | The shared pool should not be too small (< 250 MB) or too large (>2 GB). |
log_checkpoint_timeout | 900 | |
open_cursors | 300 | Verify that you have enough open cursors for the application to execute. |
Processes | 300 | Verify that you have enough 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 different disks (improves loads, for example). |
Table 6-4 Oracle 8i parameters
| Parameter | Value | Improvement / Remarks |
|---|---|---|
sort_area_size | (1/8th of the physical memory) / (concurrent users + 10), up to 6 MB. | For example: If you have 2 GB of Physical Memory and 90 users, then the calculation will be: (2 GB / 8) / 100 ~= 2.5 MB |
db_block_buffers | 1/3rd of the physical memory divided by the size of the | The given values help to keep the I/O wait of the PMDB queries low. |
Table 6-5 Oracle 9i parameters
| Parameter | Value | Improvements / Remarks |
|---|---|---|
db_cache_size | Replaces parameter | |
pga_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 | Verify that this parameter is set to | This parameter checks the work on the database and helps determine the recommended size of the Buffer pool. But this damages performance, so verify that it is on only for a short period. |
Table 6-6 Oracle 10g parameter
| Parameter | Value | Improvement / Remarks |
|---|---|---|
sga_target | Automatic memory management. 1/4th of the physical memory. | Replaces the following parameters:
Verify that they are initialized with either 0 or the minimum value you need. |
INIT.ORA example
The following is an example of the init.ora file for a Oracle 9.2 server with 2 GB RAM and 2 disks.
...
Table 6-7 Rollback segment and redo log file settingsAnchor Table67 Table67
| Setting | Description | Value | ||
|---|---|---|---|---|
Rollback segments (For Oracle 8i) | Minimum tablespace size | 2 GB (4 GB in large installations) | ||
Rollback segments (For Oracle 8i) | Minimum number of segments | 20 | ||
Rollback segments (For Oracle 8i) | Minimum initial segment size | 5 MB
| ||
Enable AUM (For Oracle 9i and later) | Automatic Undo Management | |||
Temporary tablespace | Minimum temporary tablespace size | 2 GB
| ||
Redo log file | Minimum redo log file size | 1 GB (4 GB in large installations)
|
Additional Rollback segment and Redo log file information
...
Table 6-8 Default extents sizes
| Tablespace | Size |
|---|---|
| Large table | 2 MB |
| Medium table | 512 KB |
| Small table | 80 KB |
| Large index | 1 MB |
| Medium index | 256 KB |
| Small index | 80 KB |
Precise. Performance intelligence from click to storage. Learn more > >
...
...
IDERA Website | Products | Buy | Support | Community | About Us | Resources | Legal