Self Tuning of Oracle Database Using SQL Scripts

Nota:

• The Oracle professionals or DBA’s

1. must know accurately predict the 'high-water' mark of RAM demands
   1. They must reserve enough memory to accommodate the user connections
   2. the goal is to keep as memory RAM Connections demanded database
2. that can precisely control the amount of RAM which it is used by the database instance System Global Area (SGA).
3. the Oracle database administrator can issue alter system command to change the RAM memory areas, and can grow and shrink the RAM memory areas on as needed basis.

1. is a combination
   1. Oracle Instance
   2. Datafiles Again Oracle Instance
2. TYPES OF MEMORY
   1. SGA (System Global Area)
      1. fixed
         1. is a component of the SGA that varies in size from platform to platform
      2. variable
         1. main components
            1. Database Buffer Cache
               1. This is used to hold the data into the memory
            2. Redo Log Buffer
               1. This memory block hold the data which is going to be written to redo log file.
            3. Shared Pool
               1. This contains 2 memory section, 1) Library Cache 2) Dictionary Cache
            4. Large Pool
               1. used for heavy operations such as bulk copy during backup or during restore operation.
         2. They can alter the size of each of the components manually using ALTER SYSTEM
            1. the size is determined by INIT.ORA parameters.++
      3. data structure
         1. Database buffer cache
         2. Redo log buffer
         3. Shared pool
         4. Java pool
         5. Large pool (optional)
         6. Data dictionary cache
         7. Other miscellaneous information
   2. PGA (Program Global Area)
      1. contains information about bind variables, sort areas, and other aspect of cursor handling
      2. each user has its own PGA.
         1. the value upon which the user want to execute the select or update statement cannot be shared
      3. cache
         1. Default Cache
         2. Keep Cache
         3. Recycle Cache
         4. If we define the cache size using DB_CACHE_SIZE (or DB_BLOCK_BUFFER and specify the block size) then this will be default cache
         5. has a limited size

1. contains the parsed SQL statements and execution plans.
   1. New parsed SQL and execution plans comes and old one gets aged out and hence overwritten.
2. SHARED_POOL_RESERVED_SIZE. This will reserve some additional space other then

1. categories
   1. Server Process
   2. Background Process

1. writes the contents of buffers to datafiles.

1. writes all redo entries that have been copied into the buffer since the last time it wrote

1. When a checkpoint occurs, Oracle must update the headers of all datafiles to record the details of the checkpoint

1. performs crash recovery, if necessary
2. responsible for cleaning up temporary segments that are no longer in use