Wednesday , July 26 2017
Home / Oracle DBA / LOGICAL STRUCTURES OF DATABASE

LOGICAL STRUCTURES OF DATABASE

LOGICAL STRUCTURES OF DATABASE
  1. The following are the logical structures of database and will be helpful in easy manageability of the database
    1. TABLESPACE – an alias name to a group of datafiles (or) group of segments (or) a space where tables reside
    2. SEGMENT – group of extents (or) object that occupies space
    3. EXTENT – group of oracle data blocks (or) memory unit allocated to the object
    4. ORACLE DATA BLOCK – basic unit of data storage (or) group of operating system blocks
  1. The following tablespaces are mandatory to exist in 10g database
    1. SYSTEM – stores base tables (dictionary information)
    2. SYSAUX – auxiliary tablespace to SYSTEM which also stores base tables required for reporting purpose
    3. TEMP – used for performing sort operations
    4. UNDO – used to store before images helpful for rollback of transaction or instance recovery
Note: Oracle 9i should have all the above tablespaces except SYSAUX. SYSAUX is introduced in 10g to avoid burden on SYSTEM tablespace.
LOGICAL STRUCTURES OF DATABASE
DML STATEMENT PROCESSING
  1. Server process performs parsing in library cache by taking the statement information. Optimizer will generate the best execution plan based on time & cost.
  1. By following the execution plan statement will get executed in PGA.
  1. After execution, Server process will search for the data in LRU list. If exists, it will copy undo block to LRU list. If data is not found, then it will copy both data block and undo block to LRU list.
  1. From there those blocks will be copied to PGA where modifications will be done by which redo entries will be generated in PGA which are copied to redolog buffer cache by server process.
Note: A single atomic change happened to the database is called redo entry or redo record or change vector. E.g.: if 100 rows are modified, then we will have 200 redo entries.
  1. Modifications is done by copying previous image from data block to undo block and new value will be inserted into data block thus making both the blocks DIRTY.
  1. The dirty blocks will be moved to write list from where DBWRn will write them to corresponding datafiles. But before DBWRn writes, LGWR writes the content of log buffer cache to redolog files.
Note : LGWR writing before DBWRn is called WRITE-AHEAD protocol.
DDL STATEMENT PROCESSING
  1. DDL statement processing is same as DML processing as internally all DDL are DML statements to the base tables.
  1. For every DDL statement, base tables will get modified with update/delete/insert statements. Because of this reason, in case of DDL also undo will be generated.
Read More:
Creating a Physical Standby Database – Data Gaurd
Partitioned Tables and Indexes
Performance Tuning using SQL Tuning Advisor

Comments

comments

Check Also

DATA BASE LINKS

DATABASE LINKS: What are the db links? create a link from one database to another …

Leave a Reply

Your email address will not be published. Required fields are marked *