Please wait - loading…

Self Tuning of Oracle Database Using SQL Scripts


Diana Mayorga
Mind Map by Diana Mayorga, updated more than 1 year ago
Diana Mayorga
Created by Diana Mayorga over 6 years ago

Resource summary

Self Tuning of Oracle Database Using SQL Scripts
  1. The Oracle professionals or DBA’s


    • 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
        1. 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).
          1. 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.
          2. ORACLE
            1. is a combination
              1. Oracle Instance
                1. 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.
                              2. Shared Pool
                                1. This contains 2 memory section, 1) Library Cache 2) Dictionary Cache
                                2. Large Pool
                                  1. used for heavy operations such as bulk copy during backup or during restore operation.
                                3. They can alter the size of each of the components manually using ALTER SYSTEM
                                  1. the size is determined by INIT.ORA parameters.++
                                4. data structure
                                  1. Database buffer cache
                                    1. Redo log buffer
                                      1. Shared pool
                                        1. Java pool
                                          1. Large pool (optional)
                                            1. Data dictionary cache
                                              1. Other miscellaneous information
                                            2. PGA (Program Global Area)
                                              1. contains information about bind variables, sort areas, and other aspect of cursor handling
                                                1. each user has its own PGA.
                                                  1. the value upon which the user want to execute the select or update statement cannot be shared
                                                  2. cache
                                                    1. Default Cache
                                                      1. Keep Cache
                                                        1. Recycle Cache
                                                          1. 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
                                                            1. has a limited size
                                                      2. Shared Pool Reserved 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
                                                          3. Process Architecture
                                                            1. categories
                                                              1. Server Process
                                                                1. Background Process
                                                              2. Database writer (DBWn)
                                                                1. writes the contents of buffers to datafiles.
                                                                2. Log Writer (LGWR) – The log writer process
                                                                  1. writes all redo entries that have been copied into the buffer since the last time it wrote
                                                                  2. Checkpoint (CKPT)
                                                                    1. When a checkpoint occurs, Oracle must update the headers of all datafiles to record the details of the checkpoint
                                                                    2. System Monitor (SMON)
                                                                      1. performs crash recovery, if necessary
                                                                        1. responsible for cleaning up temporary segments that are no longer in use
                                                                        Show full summary Hide full summary


                                                                        Certificación Java 8 - Oracle Certified Associate - Tema 1 - Estructura de Clases
                                                                        Miguel Gómez Cuesta
                                                                        Oracle Database Basics - Examen I
                                                                        Ronald Alarcón
                                                                        NORMAS PARA REDACTAR UN ARTÍCULO CIENTÍFICO
                                                                        Motores de bases de datos
                                                                        ROUND & TRUNC [DATES]
                                                                        Adrian Mora
                                                                        Anatomía y fisiología del cuerpo esofágico
                                                                        Shearley Caballero
                                                                        ARTÍCULO CIENTIFICO
                                                                        Yamile Riveros Pardo
                                                                        TEST PGA MODULO 3 UNIDAD II
                                                                        JUAN FONSECA
                                                                        COMPARATIVA BASES DE DATOS 2017
                                                                        David Marín
                                                                        Descripción del Curso
                                                                        Andrés Velásco5802