| CHECKPOINT_PROCESS | Turns the CKPT background process. Checkpoints can hvae a negative impact on performance, as the DBWR process writes data to the database and the LGWR updates the database and control files to record the current log sequence number (required for archive recovery) and writes entries from the redo log buffer to the redo log file. CKPT updates the database and control files; this takes a load off the LGWR process and allows it to concentrate on the task of flushing the log buffer to the redo log. CHECKPOINT_PROCESS has a greater influence if the frequency of checkpoints is high and there are numerous database files. |
| CURSOR_SPACE_FOR_TIME | Setting this parameter to TRUE gives you a minor performance improvement by pinning SQL areas in the shared context area and stopping private SQL areas from being deallocated until the application cursors are closed. If the parameter is set to FALSE, a check has to be made to see if the SQL statement is contained in a shared SQL area in the library cache. You must allocate a shared pool that is large enough (see SHARED_POOL_SIZE). If its not large enough and this parameter is set to TRUE, Oracle will display a message telling you it cant parse the statement. If it cant maintain the prive SQL area because of insufficent memory a message will be given to that fact. |
| DB_BLOCK_BUFFERS | Sets the size of the database buffer cache in buffer. The buffer cache stores tables, indexes,
clusters, and rollback segments. Generally, the higher the number of block buffers, the less I/O and
the better your system will perform. If excessive paging and swapping activity occurs for user processes
or if any paging or swapping of the SGA occurs, you will have to reduce DB_BLOCK_BUFFERS to free memory.
You should enlarge this parameter only after you're certain you've set the SHARED_POOL_SIZE parameter correctly.
For a typical medium-sized system, set this parameter to 500 or more; for a larger system, try 4000 or more. |
| DB_BLOCK_CHECKPOINT_BATCH | Number of blocks the DBWR background process users at any one time. You can increase this parameter to allow checkpoints to complete a fraction faster. If you make it lower, you'll give a higher share of database writes to other modified blocks. Some analysts suggest that you make this parameter the same size as DB_BLOCK_WRITE_BATCH to speed up the checkpoint write process. |
| _DB_BLOCK_MAX_SCAN_CNT | Maximum number of buffers that the user will scan before DBWR is invoked. This parameter can use an excessive amount of CPU if it is set too high, particulary for a database that has a very hihhg percentage of queries and very few updates. DBWR scanning will continue until either the number of modified blocks specified in DB_BLOCK_WRITE_BATCH has been found or the _DB_BLOCK_MAX_SCAN_CNT number of blocks has been scanned. The default value is adequate for most sites. If you have a DB_BLOCK_BUFFER size greater than 10M, though, consider increasing this parameter, especially if you are experiency CPU problems. |
| DB_BLOCK_WRITE_BATCH | Number of blocks DBWR passes at one time to the operating system for writing. Setting this parameter higher allows the o/s to write to different disks in parallel and to write adjacent blocks in a single I/O. If your buffer cache is small, having a high value will increase the wait time to modify a block that is in the batch being written. |
| DB_BLOCK_SIZE | Size of each database buffer. Oracle recommends that, unless your rows are very long or short, you should go with the default. This value should only be modified prior to database creation. It should always be equal to or a multiple of the O/S block size. |
| DB_FILES | The number of database files that can be open when the database is running. Set this value lower than the default if you are not using 32 data files ( to reduce the space used in the SGA). You can increase this value by shutting down your database, changing the parameter, and restarting the database. |
| DB_FILE_MULTIBLOCK_READ_COUNT | Number of blocks read at once when performing a sequention scan. This parameter is often set higher
for full table scans performing by overnight runs, and is then adjusted back for high-volume daily processing.
At the time the database is created, there will often be sequential scans of tables to create indexes. For best performance, set this parameter higher than the default. Typically, 8-20. |
| DDL_LOCKS | Maximum number of parse locks held simultaneously. If five users are modifying data in five tables, five
locks are required. Although, this parameter has bearing on performance it must be set large enough for
applications to function. A typical setting is 20*SESSIONS.
Note that SESSIONS has a default of 1.1*PROCESSES and PROCESSES has a default of 25 |
| DISCRETE_TRANSACTIONS_ENABLES |