SQL Tuning & its Prerequisites
necessity
Oracle is a Strong Basics of Database
Database. Architecture
Better Performance with SQL Tuning Needs
lower Hardware Expertise
Infrastructure.
Easy to learn.
SQL Architecture salient features
All data is stored in Blocks.
A block is the smallest unit of database storage.
A block can have 2KB to 32KB size (8KB default).
A block stores row data or index data.
Block Header Includes:
• Block Type Information.
• Table Information.
• Row Directory.
We can use PCTFREE or PCTUSE parameters to specify the space size in blocks
Architecture (Cont.…)
PGA (Program Global Area)
SESSION AREA
• Stores session variables, login information,
session status, etc. of each user.
PRIVATE SQL AREA
• Persistent area stores the bind variables.
• Runtime area stores the execution state info.
CURSOR AREA
• Stores the information of cursors.
Shared Pool
DATA DICTIONARY CACHE
• Stores the definitions of the data base objects and their permissions.
RESULT CACHE
• Stores the result of commonly used queries.
• Stores the result of functions.
LIBRARY CACHE
• Stores execution plans.
• Stores procedures, packages, & control structures.
Buffer Cache PGA
Largest memory area of SGA.
Stores the copies of the data blocks.
Read from the disc.
Maintained with a complex algorithm
Buffer
Server Process
Database write process handles the write Cache
operations to the disc.
Stores the index data, too
Why to read into the buffer cache?
Database
Much faster than discs. Disc Writer Process
Stores the most recently used & most used ones.
Redo Log Buffer Oracle Guarantees no Data loss.
A Redo Log Entry is created when a change
(insert, update, delete, create, etc.) occurs.
Redo Log
Server Process
Buffer Redo Log Entry has the changes made to the
database.
They are used for recovery operations.
Redo Log Entries are stored in Redo Log
Redo Log Writer Buffer.
Log Files Process
Redo Log Buffer is a circular Buffer.
Rollback is not done with Redo Log Data.
UNDO
The original data stored into the memory (undo tablespace) is called as undo
data.
Another copy of the data is stored in the buffer cache for the modifications.
Undo data is not modified because:
• Used for rollback operations.
• Used for providing read consistency.
• Used for providing flashback feature.
Blocks> Extents> Segments> Tablespaces
DML (Data Modification Language)
When we run a DML code, the server:
Checks the Shared SQL Area for similar statements to
use.
Checks the Data Dictionary Cache and checks if our
query is valid.
Checks Buffer Cache & Undo Segments for the related
data.
Locks the related blocks.
Makes the change to the blocks in the Buffer Cache.
The changes are applied to the Redo Log Buffer before the Buffer Cache.
The server returns the feedback for the change.
DML (Data Modification Language)
When the user commits:
The server creates a commit record with SCN.
The LGWr process writes redo log entries in the redo
log buffer to the redo log files.
The DBWn writes the dirty blocks to the disc & unlocks
the blocks.
The server returns a feedback about the transaction
completion.
Automatic Memory Management
The size of each memory area is important for the execution performance of your queries.
Oracle manages the memory automatically.
It can manage both SGA and PGA memories.
Leave automatic memory management enabled to prevent out of memory errors.
Automatic Memory Management
Storage: Discs
Control Files: Stores information of the physical structure of the database.
Data Files: Stores Data (Tables, procedures, application data).
Online Redo Log Files: Stores redo log entries.
Archived Redo Log Files: Online redo log files are constantly moved here.
Backup Files: Stores the exact copy of the data files for disaster recovery.
Parameter File: Stores the configure data of the database instance.
Password File: Stores the passwords of the admin users (sysdba, sysoper, sysasm).
Alert Log & Trace Files: Stores log messages and errors occurred in the database.
Logical & Physical DB Structure
Blocks: Smallest units of storage (2KB-32KB).
Extents: Combination of several consecutive data blocks. Used for storing specific
type of info.
Segments: Combination of several extents. Used for storing some big data (tables,
indexes, etc.).
Tablespaces: Combination of many segments. Used for grouping the related data in
one container.
• Temporary Tablespace : Stores the temporary data of a session.
• Permanent Tablespace : Stores the persistent schema objects.
WHAT NEXT?