Although tuning specific queries individually can lead to performance gains, the system will still be slow if the parameters for the initialization file are not set correctly because the initialization file plays such an integral role in the overall performance of an Oracle database. While you can spend time setting all the initialization parameters, there are a few main parameters that need to be set correctly to realize significant performance gains:
- MEMORY_TARGET
- MEMORY_MAX_TARGET
- SGA_TARGET
- SGA_MAX_SIZE
- PGA_AGGREGATE_TARGET
- DB_CACHE_SIZE
- SHARED_POOL_SIZE
TIP
The key initialization parameters in Oracle are MEMORY_TARGET, MEMORY_MAX_TARGET, SGA_TARGET, SGA_MAX_SIZE, PGA_AGGREGATE_TARGET, DB_CACHE_SIZE, and SHARED_POOL_SIZE.
If you use MEMORY_TARGET (and optionally MEMORY_MAX_TARGET), Oracle will use Automatic Memory Management (AMM) for the rest (Metalink note 443746.1 describes this in detail). In 10g, SGA_TARGET and SGA_MAX_SIZE can also be set so Oracle manages the shared memory on your system with Automatic Shared Memory Management (ASMM) (Metalink Note 295626.1 describes this in detail). The Oracle Applications Development team recommends using SGA_TARGET and SGA_MAX_SIZE for both 10g and 11g in 11i Apps (Metalink note 216205.1) and for 11g in Release 12 Apps (Metalink note 396009.1).
I would like to see MEMORY_TARGET mature a bit more before I hand the “keys to the car” to Oracle, but I like the approach to simplicity, especially for beginners. The following query can be used to find the current settings of the key initialization parameters on your database (if SGA_TARGET is set to a non-zero value, then some of these parameters are set to zero, which means Oracle sets it). For this example, I manually set the shared pool:
Leave a Reply