 |
|
Oracle Tips by Burleson |
Oracle Database Instance Tuning
Overview
This tutorial covers the all-important area of
Oracle instance tuning. The management of any Oracle database
involves understanding and manipulating the 250 global instance
parameters that are provided within Oracle 10g.
While it is beyond the scope of this course to
cover all 250 of these parameters, the students will examine the top
20 Oracle parameters and understand how their settings can affect the
performance of the entire Oracle database.
This tutorial will examine the Oracle data
buffer pools and the student will understand how the allocation of RAM
data buffers can dramatically reduce disk I/O for their Oracle
environment.
We will also take a look at tuning the Oracle
shared pool and understand how the size of the shared pool has direct
impact on Oracle's library cache and the ability of Oracle to parse
and execute SQL statements in an efficient manner. We also examine
the tuning of the Oracle large pool, and the tuning of the database
writer processes within the Oracle environment.
This tutorial also examines the tuning of the
Oracle library cache, and emphasizes how certain parameters can affect
the performance of SQL statements within the Oracle library cache. We
also take a look at often overlooked but important areas of Oracle
sorting and understand how the settings for PGA aggregate target, and
sort area size, affect the propensity of Oracle to perform disk vs.
in-memory RAM sorts.
This tutorial wraps up with an overview of the
dynamic SGA features of Oracle 10g, and the student will understand
how they can run “alter system” commands to change the configuration
all of the Oracle database systems while the system continues to run.
Upon completion of this tutorial, you will be
able to:
* Explain the main instance tuning parameters
* Understand the process of tuning the data
buffer pools
* Describe tuning the shared pool
* Know when to tune the large pool
* List techniques for tuning the Oracle
database writers
* Explain the tuning of the library cache
* Describe techniques for tuning Oracle
sorting
* Explain methods for tuning the Oracle 10g
SGA
* Burleson, Chapter 9.
Chapter 9: Tuning the
Oracle Database Instance
An Overview of the Oracle Database Instance
Tuning the Oracle Data Buffers
Trend Reports of the Data Buffer Hit Ratio
with STATSPACK
Tuning the Oracle database Writer Processes
Tuning the Shared Pool
Tuning the Library Cache
Tuning the Dictionary Cache
Tuning Oracle Sorting
Tuning the Rollback Segments
Conclusion
* Oracle tuning – examine the Oracle database
instance -
http://www.dba-oracle.com/art_tuning_instance.htm
* Oracle tuning – boost performance by
tweaking global parameters -
http://www.dba-oracle.com/art_tuning4.htm
Optional Reading
* “The Dynamic SGA Features of Oracle 10g” –
Donald Burleson -
http://www.dba-oracle.com/art_dbazine_9i_sga.htm
For more details, see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology.
|
