Page History
...
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 10g parameters
Parameter | Value | Improvement / Remarks |
---|---|---|
sortsga_area_size target | Automatic memory management. ( 1/8th 4th 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
...
. | Replaces the following parameters:
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
...
Table 5 10g parameters
...
Replaces the following parameters:
db_cache_size
, shared_pool_size
, large_pool_size
, and jave_pool_size
.
Make sure to initialize them with 0 or the minimum value you need.
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
Setting | Description | Value | ||
---|---|---|---|---|
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 tablespace | Minimum temporary tablespace size | 2GB* *The temporary tablespace is most likely to grow according to the PMDB load.
| ||
Redo log file | Minimum redo log file size | 1GB* *4GB recommended in larger installations
|
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
- 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
- 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
Tablespace | Size |
---|---|
Large table | 2MB |
Medium table | 512KB |
Small table | 80KB |
Large index | 1MB |
Medium index | 256KB |
Small index | 80KB |
...