Nothing Special   »   [go: up one dir, main page]
Scribd Logo
Upload

Welcome to Scribd!

  • 30 views

    001 Architecture

    Uploaded by

    chensuriyan
    This document outlines an Oracle PL/SQL training course. The course will teach participants to write effective PL/SQL programs, create database objects like packages and triggers, and understand Oracle architecture. It will cover Oracle components like the system global area, background processes, physical database files, and logical database structures like tablespaces and segments.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PPT, PDF, TXT or read online from Scribd

    001 Architecture

    Uploaded by

    chensuriyan
    30 views26 pages
    This document outlines an Oracle PL/SQL training course. The course will teach participants to write effective PL/SQL programs, create database objects like packages and triggers, and understand Oracle architecture. It will cover Oracle components like the system global area, background processes, physical database files, and logical database structures like tablespaces and segments.

    Original Description:

    Original Title

    001_Architecture

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    PPT, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document outlines an Oracle PL/SQL training course. The course will teach participants to write effective PL/SQL programs, create database objects like packages and triggers, and understand Oracle architecture. It will cover Oracle components like the system global area, background processes, physical database files, and logical database structures like tablespaces and segments.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PPT, PDF, TXT or read online from Scribd
    Download as ppt, pdf, or txt
    30 views26 pages

    001 Architecture

    Uploaded by

    chensuriyan
    This document outlines an Oracle PL/SQL training course. The course will teach participants to write effective PL/SQL programs, create database objects like packages and triggers, and understand Oracle architecture. It will cover Oracle components like the system global area, background processes, physical database files, and logical database structures like tablespaces and segments.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as PPT, PDF, TXT or read online from Scribd
    Download as ppt, pdf, or txt
    of 26

    Oracle PL/SQL

    Course Objectives

    At the end of course, the participants will be able to:

     Explain the Oracle 9i features, Oracle Server Architecture and its


    components
     Write effective PL/SQL programs
     Write packages, procedures, functions and triggers
     Create Collection types and Objects in Oracle
    Oracle PL/SQL - Contents

     Oracle Architecture
     PL/SQL Basics
     Exception handling
     Cursors
     Procedures and Functions
     Database Triggers
     Objects in Oracle
    Oracle Architecture
    Oracle Architecture - Objectives

    At the end of this session, the participants will be able to:

     Identify the different components of the SGA


     Recognize the importance of background components
     List the different types of files that make up the physical database
     Understand the importance of each type of file
     Comprehend the logical database
    Oracle Architecture - Contents

     System Global Area


     Background Processes
     Instance
     Physical database
     Logical database
    Oracle Server Architecture

    Oracle Server

    Oracle Database Oracle Instance

    Physical Logical • SGA Memory


    • Data Files Structure
    • Table spaces
    • Control Files • Segments • Background
    • Redo Log Files Processes
    • Extents
    • Blocks
    Oracle Server Components

    I N S T A N C E
    Server

    Shared Pool System Global Area


    Library Cache

    Database Buffer Redo log


    Data Dictionary Cache Buffer cache
    Cache

    PMON SMON DBWR LGWR CKPT Others

    Data files Archived Log files


    Server Process
    Control files
    Database
    Parameter file (init.ora)
    PGA
    Redo log files Password file

    User Process

    User
    Oracle Instance

     Is a means to access an Oracle database


     Can open and use only one database at a time
     Consists of SGA memory structure and background processes
    - Allocated in the virtual memory of the computer
    where the oracle server resides
     System Global Area (SGA) stores database information
    - Consists of several memory structures
    - Shared Pool
    - Database Buffer Cache
    - Redo Log Buffer
    Shared Pool

     Stores most recently executed SQL statements and the most recently
    used data from the data dictionary
     The server process uses the area to compile the SQL statement.
     It has two components

    - Library Cache: stores information about the most


    recently used SQL statements
    - The shared SQL area contains
    - The text of SQL statement
    - The parse tree: compiled version of a statement
    - The execution plan
    Shared Pool (contd…)

    - Data Dictionary Cache (dictionary cache/row


    cache): collection of the most recently used
    definitions in the database.

    - Stores information about database files, tables,


    indexes, columns, users, privileges, etc
    Database Buffer Cache

     Stores the most recently used data. The data is read from, and written to
    the data files

     The size of each buffer in the buffer cache is equal to the size of an
    Oracle block
    Redo Log Buffer

     Records changes made to the database using the instance


     The server process records changes in the redo log buffer
     It records the block that is changed, the location of the change, and the
    new value
     The buffer is reused after it is filled, after all the old redo entries are
    recorded in the redo log files
    Background Processes

    Log Writer (LGWR)

     Performs sequential writes from the redo log buffer to the redo log file
    - When a transaction commits
    - When the redo log buffer is one-third full
    - When there is more than a megabyte of changes
    recorded in the redo log buffer
    - Before DBW0 writes modified blocks in the
    database buffer cache to the data files
     Confirms the COMMIT only after the redo is written to disk
    Background Processes (contd…)

    Database Writer (DBW0)

     The server process records changes to rollback and data blocks in the
    buffer cache
     The DBW0 writes the dirty buffers from the database buffer cache to the
    data files.
     It ensures that a sufficient number of free buffers are available in the
    database buffer cache
    Background Processes (contd…)

    Server processes make changes only in the buffer cache, and the DBW0
    defers writing to the data files until
    - A number of dirty buffers reaches the threshold
    value, or
    - A process scans a specified number of blocks
    when scanning for free buffers and cannot find any,
    or
    - A timeout occurs (every three seconds), or
    - A checkpoint occurs
    Background Processes (contd…)

    System Monitor (SMON)

     Recovers the instance when the database is started


     Rolls forward changes in the redo logs
     Rolls back uncommitted transactions
     Combines adjacent areas of free space in the data files
     De-allocates temporary segments used to store data during SQL
    statement processing
    Background Processes (contd…)

    Process Monitor (PMON)

     Cleans up the resources if one of the processes fails


     Includes
    - Rolling back the user’s transaction
    - Releasing all currently held table or row locks
    - Freeing other resources currently reserved by the
    user
    Oracle Database
    Logical Structure: Tablespaces
    •An Oracle Database can be logically grouped into smaller logical
    areas of space known as table spaces
    •A tablespace can belong to only one database at a time
    •Each tablespace consists of one or more OS files (called data files)

    SYSTEM ACCOUNTING SALES


    Tablespace Tablespace Tablespace

    Data Data Data Data


    File File File File
    Segments

    • Space allocated for a specific logical structure within a tablespace


    • Tablespace may consist of one or more segments
    • A segment cannot span tablespaces; however, a segment can span
    multiple data files that belong to the same tablespace
    • Each segment is made up of one or more extents

    Types of Description
    Segments
    Data Stores the data associated with tables
    Index Each index file has an index segment
    Temporary Used to store temporary data during sorting operations

    Rollback To rollback uncommitted transactions for users


    Segments (contd…)
    Extents
    - Space allocated to a segment by extents
    - One or more extents make up a segment
    - Extent is a set of contiguous Oracle blocks
    - Extent must exist in one data file

    Data Blocks
    - At the finest level of granularity, the data is stored in data
    blocks
    - One data block corresponds to one or more OS blocks
    allocated from existing data file
    - Data block size should be a multiple of the OS block size
    to avoid unnecessary I/O
    Segments (contd…)

    Table
    Segment

    8KB 8KB 8KB 8KB 8KB 8KB 8KB 8KB 8KB

    8KB 8KB 8KB 8KB 8KB 8KB 8KB 8KB 8KB

    Data Block 8KB 8KB 8KB 8KB 8KB 8KB 8KB 8KB 8KB

    Extent Extent Extent


    Oracle Database
     Is a collection of data that is treated as a unit. Its purpose is to store and retrieve
    related information
     Its physical structure is the set of OS files in the database
     Consists of three file types
    - Control Files: contain info required to maintain and verify the
    integrity of the database. A database needs at least one control
    file
    - Data Files: Each tablespace in an Oracle database consists of
    one or more files called data files. A data file can belong to only
    one tablespace. Contain the data in the database, including
    tables, indexes, rollback segments and temp. segments
    - Online Redo Logs: Contain a record of changes made to the
    database to enable recovery of the data in case of failures. A
    database requires at least two redo log files
    Other Files

     Parameter File
    Defines the characteristics of an oracle instance

     Password File
    Authenticates privileged database users

     Archived Redo Logs


    Are backups of the online redo logs
    How Oracle Works?

    I N S T A N C E
    Server

    Shared Pool System Global Area


    Library Cache

    Database Buffer Redo log


    Data Dictionary Cache Buffer cache
    Cache

    PMON SMON DBWR LGWR CKPT Others

    Data files Archived Log files


    Server Process
    Control files
    Database
    Parameter file (init.ora)
    PGA
    Redo log files Password file

    User Process

    User
    Summary

    We have discussed,
     The database buffer cache, redo buffer cache and the shared pool
     Oracle server background processes
     The logical database
     The physical database

    You might also like