"If I were
two-faced, would I be wearing this one?" - Abraham Lincoln
first then play the video:
Space Allocation on the fly
Oracle Performance Tuning
The Oracle Performance
Tuning Case Study consists of the numbers of the most common ways to
detect an Oracle performance problem.
In your organization, you
are expected at least know how to: Change memory allocation on the
fly; size the Shared Pool; size the Buffer Cache; Measure the Buffer
Cache Hit Ratio; Monitor and re-size the Redo Log buffer; Detect
Database I/O problems; Optimize Sort Operations; Diagnose Contentions
for Latches; Tune rollback or undo segments; Monitor and Detect Lock
Contentions; Optimize a SQL statement; and Use Diagnostic and Tuning
"The covers of
this book are too far apart." - Ambrose Bierce (1842-1914)
Intro to Oracle Database
In this section, you will
learn about the Oracle Architectural components such as Log Writer, DB
Writer, etc (Background Processes), SGA, Buffer Cache, Shared Pool,
etc (Memory Layout - Memory Buffer), Datafiles, Controlfiles, etc
(Physical Oracle Layout). All these components, running together play
an important part in the Oracle Architecture.
Oracle Memory Components
All components such as
Shared Pool (Library Cache, Dictionary Cache), Buffer Cache, Online
Redo Log file, Large Pool, Java Pool as well as a few other items are
referred to as the System Global Area (SGA). And the place stores
information like bind variable values, sort areas, cursor handling,
etc for a specific user is called Program Global Area (PGA). The PGA
is used to store only real values in place of bind variables for
executing SQL statements. The combination of these two memories
structure while they are running is called Oracle Instance.
All components such as Log
Writer (LGWR), DB Writer (DBWR), Checkpoint (CKPT), Recovery Process (RECO),
Lock Process (LCKn), Archive Process (ARCH), System Monitor (SMON),
and Program Monitor (PMON) are referred to as an Oracle Background
All components such as
Server Parameter File (SPFILE), Parameter File (PFILE the INIT.ORA
file), Controlfiles, Datafiles, Password File, Archives and Online
Redo Log files are referred to as an Oracle Database Components.
All these Oracle components
running together allow users to read, write, and modify data in an
Now, the following are
brief job descriptions for above components.
The Shared Pool contains
the Library Cache and the Dictionary Cache as well as a few other
items, which are not in the scope of this section. The Library Cache
holds all usersí SQL statements, Functions, Procedures, and
Packages. It stores parsed SQL statement with its execution plan for
reuse. The Dictionary Cache, sometimes also referred to as the Row
Cache, holds the Oracle repository data information such as tables,
indexes, and columns definitions, usernames, passwords, synonyms,
views, procedures, functions, packages, and privileges information.
The Buffer Cache holds
usersí data. Users query their data while they are in the Buffer
Cache. If userís request is not in the Buffer Cache then server
process has to bring it from disk. The smallest unit in the buffer
cache is an Oracle block. The buffer cache can be increased or
decreased by granule unit. The smallest Granule Unit is 4Meg if the
SGA size is less than 128Meg and the smallest Granule Unit become
16Meg is the SGA size is more than 128Meg.
Redo Log Buffer
The Redo Log Buffer holds
usersí entries such as INSERT, UPDATE, DELETE, etc (DML) and CREATE
TABLE, DROP TABLE (DDL). The Redo Entries are information that will be
used to reconstruct, or redo, changes made to a database. The Log
Writer writes the entries into the Online Redo Log files when a COMMIT
occurs, every 3 seconds, or when one third of the Redo Log Buffer is
full. That will guarantee a database recovery to a point of failure if
an Oracle database failure occurred.
The Large Pool holds
information about the Recovery Manager (RMAN) utility when RMAN is
running. If you use the Multi-threaded Server (MTS) process, you may
allocate the Oracle Memory structure such that you can get advantage
of using Large Pool instead of the Shared Pool. Notice that when you
use dedicated servers, user session information is housed in the PGA.
The Multi-threaded Server
process will be used when a user send his/her request by using a
shared server. A userís request will be assigned to a dispatcher
based on the availability of dispatchers. Then the dispatcher will
send or receive request from an assigned shared server.
When you are running
dedicated servers then the session information can be stored inside
the process global area (PGA). The UGA is the user global area,
which holds session-based information. When you are
running shared servers then the session information can be stored
inside the user global area (UGA) and when your session does some
sorting, some of the memory allocated for sorting - specifically the
amount defined by parameter sort_area_retained_size -
comes from the SGA and the rest (up to sort_area_size)
comes from the PGA (Snnn). This is because the
sort_area_retained_size may have to be held open as the
pipeline to return results to the front-end, so it has to be located
where the session can find it again as the session migrate from server
to server. On the other hand, the sort_area_size is a
complete throwaway, and by locating it in the PGA, Oracle can make
best use of available memory without soaking the SGA. To avoid
sessions grabbing too much memory in the SGA when running MTS/shared
server, you can set the private_sga value in the resource_limit
for the user. This ensures that any particularly greedy SQL that (for
example) demands multiple allocations of sort_area_retained_size
will crash rather than flushing and exhausting the SGA.
Log Writer Background
The LGWRís job is to
write the redo userís entries from the Redo Log Buffer when the
buffer exceeds one third of the Redo Log Buffer, every 3 seconds, or
when a user executes the commit SQL statement.
DB Writer Background
The DBWRís job is to
write all the blocks that were marked as dirty block to the Oracle
database on disks (datafiles) whenever the checkpoint process signals
it. Notice that when the Online Redo Log files are filled the
checkpoint process will signals a DBWR to write all the dirty block
into the Oracle database.
The Checkpoint signals DB
writers to write all dirty blocks into the disk. The Checkpoint will
occurred either by a specific defined time, size of the Online Redo
Log file used by DBA, or when an Online Redo log file will be switched
from on log file to another. The following are the parameters that
will be used by a DBA to adjust time or interval of how frequently its
checkpoint should occur on its database.
3600 # every one hour
# number of Operating System blocks
Recovery Background Process
The RECO will be used only
if you have a distributed database. You use this process to recover a
database if a failure occurs due to physical server problem or
Lock Background Process (LCKn)
The LCKn background process
will be used if you have multiple instances accessing to only one
database. An example of that is a Parallel Server or a Real
Archive Background Process
This background process
archives the Online Redo Log file when you are manually or
automatically switching an Online Redo Log file. An example of
manually switching is: ALTER SYSTEM SWITCH LOGFILE or ALTER SYSTEM
ARCHIVE LOG CURRENT.
System Monitor Background
When you start your
database, the SMON will make sure that all datafiles, controlfiles,
and log files are synchronized before opening a database. If they are
no, it will perform an instance recovery. It will check the last SCN
that was performed against the datafiles. If it finds that there are
transactions that were not applied against the datafile, then it will
recover or synchronize the datafile from either the Online Redo Log
files or the Archive Log files. The smaller Online Redo log files will
bring a faster database recovery.
Program Monitor Background
A user may be disconnected
either by canceling its session or by communication link. In either
act, the PMON will start and perform an action to clean the reminding
memory allocation that was assigned to the user.
Parameter File (PFILE -
You can read or change this
file. The file contains all Oracle parameters file to configure a
database instance. In this file, you can reset and change the Buffer
Cache size, Shared Pool size, Redo Log Buffer size, etc. You use this
file to increase or decrease the size of System Global Area (SGA). You
also can change the location of your control files, mode of a database
such as archivelog mode or noarchivelog mode, and many other parameter
options that you will learn them in the course of this book.
Server Parameter File
This file is in binary
format and you cannot read this file. You should create the Server
Parameter file (CREATE SPFILE FROM PFILE) and startup your database
using the spfile file, if you want to change database parameters
dynamically. There are some few parameters that you still need to
shutdown and startup the database, if you want to make the parameter
in effect. You will learn all about these parameters in the course of
You cannot read this file
and it is in a binary format. If you want to see the content of
control file or the layout of your database, you should use the ALTER
DATABASE BACKUP CONTROLFILE TO TRACE statement. It writes a
trace file into the %ORACLE_BASE<db-name>
directory. It contains information the structure of your database
layout, database name, last System Change Number (SCN) number, your
database mode (archivelog mode or noarchivelog mode), maximum number
of log files, maximum number of log members, maximum number of
instances, maximum of number of datafiles, the location of the
database Online Redo Log files, and backup information.
All the Oracle data
information will be stored in the Oracle datafiles. A datafile is one
of the physical layout components of a database. A tablespace (logical
database layout) contains one or more datafiles (physical database
layout). You may have one or more extents in a datafile. An extent is
a collection of blocks. A block is a smallest unit in an Oracle. A
tablespace is a collection of segments. Think of a segment like an
object in an Oracle database. A Segment is a collection of Oracle
To use the password file
you should set the REMOTE_LOGIN_PASSWORD parameter to exclusive or
shared mode in the Parameter File (Example: REMOTE_LOGIN_PASSWORD=EXCLUSIVE).
A password file is an external Oracle file and to create it you should
run the ORAPWD utility from operating system.
MS-DOS> ORAPWD FILE=%ORACLE_HOME\b0
orapw<sid>.pwd \par PASSWORD=mypass
The ENTRIES parameter
specifying the number of user entries allowed for the password file.
Now, the DBA can be connected to the database as a user with sysdba
privileges as shown here:
SQL> connect / as sysdba
Online Redo Log files
The Online Redo Log files
hold the Redo Entries. You should have at least two or more Redo Log
Groups. Each group may have more than one member. It is a good
practice to multiplex Online Redo Log members. The Redo Entries are
information that will be used to reconstruct, or redo, changes made to
a database. The Log Writer writes the entries into the Online Redo Log
files when a COMMIT occurs, every 3 seconds, or when one third of the
Redo Log Buffer is full. That will guarantee a database recovery to a
point of failure if an Oracle database failure occurred.
Archive Online Redo Log
When an Online Redo Log
File fills out, the checkpoint will force DBWR to write into the
Oracle datafiles and also the archive process copies the log file to
an archive destination directory. That will guarantee a database
recovery to a point of failure if an Oracle database failure occurred.
Assuming that you have a
user is updating a record from her SQLPLUS. The following SQL
statement is her SQL transaction:
SQL> UPDATE emp
SET sal = 1000 WHERE empno = 100;
Let us see what would be
happen when oracle process it.
The user will type the
above SQL statement and press enter key. This user either is connect
to the database by dedicated server or shared server (MTS). If the
user is using multi-threaded servers then her request will be given to
a dispatcher and the dispatcher will give the request to shared
server. If the user is using dedicated server then the dedicated
server will be all hers. Now, her user process is talking to shared or
Now, the userís SQL
statement will be parsed and assigned an executed plan to be compiled
in the Library Cache in the Shared Pool. In order the SQL statement be
compiled, Oracle need to make sure its table and columns are valid and
the user did not violated any security information. It goes to the
Dictionary Cache known as Raw Cache to get all necessary information
about the table. If there was no syntax problem and its table and
columns were valid, then the SQL statement will be parsed successfully
and the execution plan will be perform.
Now, there is no problem.
The Server process fetches the record. If the data or record is in the
Buffer Cache then an update process will be applied to it and the
block will be marked as dirty block. Notice that before the user save
the update, the before block images are in the UNDO segment. When the
user executes commit statement or more than one third of the Redo Log
buffer have filled out, then LGWR writes the userís entries from the
redo log buffer to the Online Redo Log files. Still the block may not
been stored in the database.
In the case that the record
is not in the buffer cache, the server process read the block
containing the record from the datafile (disk) and places it into the
Now, the checkpoint process
will be activated based on the LOG_CHECKPOINT_INTERVAL,
LOG_CHECKPOINT_TIMEOUT parameters, or may be due to a log switch. This
action will force DBWR or CKPT to write all dirty block in the
"Too many pieces
of music finish too long after the end." - Igor Stravinsky
Q: What are the Oracle
Q: What are the Oracle
Q: What is the Server
Q: What is the Parameter
Q: How do you use the
Q: What is the System
Global Area (SGA)?
Q: What is the Shared Pool
Q: What is the Buffer Cache
Q: What does the Buffer
Cache hold in SGA?
Q: What are the differences
between the Library Cache and Dictionary Cache?
Q: What is the Redo Log
Buffer in SGA?
Q: Describe the Large Pool
component in SGA?
Q: Describe the
Multi-threaded Server process?
Q: What are PGA and UGA?
Q: Describe the log writer
background process (LGWR)?
Q: How often LGWR writes
userís entries to the Online Redo Log Buffer files?
Q: Describe the Checkpoint
Q: How do you automatically
force the Oracle to perform a checkpoint?
Q: What is the Recovery
Q: What is the Lock
Q: How does the Archive
Q: How do you configure
your database to do an automatic archiving?
Q: What is the System
Q: Describe the Program
Monitor Process Job?
Q: What are the differences
between the SPFILE and PFILE startup?
Q: What is the controlfile?
Q: How do you backup your
Q: What does a controlfile
Q: Describe the password
Q: How do you create a
Q: Describe the Online Redo