Oracle
performance tuning is very complex, and where there are some generic steps for
performance tuning, the world is not always that simple. Here are the
steps for a top-down Oracle performance tuning approach:
Server & network tuning:
This is always the first step, as not amount of tuning will help a poor server environment.
Instance tuning:
Tuning the Oracle SGA is the next step, and all of the Oracle initialization parameters must be reviewed to ensure that the database has been properly configured for it's workload. In some cases, a database may have a bi-modal workload (online vs. batch) and the instance parms are adjusted as-needed during this step
Object tuning:
This step of performance tuning looks at the setting for Oracle tables and indexes. Table and index settings such as PCTFREE, PCTUSED, and FREELISTS can have a dramatic impact on Oracle performance.
SQL tuning:
This is last step in tuning, and the most time-consuming tuning operation because there can be many thousands of individual SQL statements that access the Oracle database. If you have carefully optimized the workload as a whole from step 2, there you will only need to tune "outlier" SQL statements. Within this step, there are sub-steps:
Server & network tuning:
This is always the first step, as not amount of tuning will help a poor server environment.
Instance tuning:
Tuning the Oracle SGA is the next step, and all of the Oracle initialization parameters must be reviewed to ensure that the database has been properly configured for it's workload. In some cases, a database may have a bi-modal workload (online vs. batch) and the instance parms are adjusted as-needed during this step
Object tuning:
This step of performance tuning looks at the setting for Oracle tables and indexes. Table and index settings such as PCTFREE, PCTUSED, and FREELISTS can have a dramatic impact on Oracle performance.
SQL tuning:
This is last step in tuning, and the most time-consuming tuning operation because there can be many thousands of individual SQL statements that access the Oracle database. If you have carefully optimized the workload as a whole from step 2, there you will only need to tune "outlier" SQL statements. Within this step, there are sub-steps:
Remove
unnecessary large-table full-table scans: In
this tuning step you evaluate the SQL based on the number of rows returned by
the query. Standard b-tree indexes can be added to tables, and bitmapped and
function-based indexes can also eliminate full-table scans.
Cache small-table full-table scans: In this step we ensure that a
dedicated data buffer is available for the rows.
Verify optimal index usage: This step is critical because you may have
"missing" indexes in your database, causing excessive I/O.
Materialize your aggregations and summaries for static tables: One
features of the Oracle SQLAccess advisor is recommendations for new indexes and suggestions for
materialized views.
We begin by looking carefully at the database server for any problems that might
exist within the CPU, RAM, or disk configurations. No amount of tuning is going
to help an Oracle database when the server it is running on is short on
resources.
Once we've completed the tuning of the Oracle server, we can look at the global parameters that affect the Oracle database (the Oracle instance). When looking at the Oracle database, we take a look at the database as a whole, paying careful attention to the Oracle initialization parameters that govern the configuration of the System Global Area (SGA) and the overall behavior of the database.
Once the database server and the Oracle instance have been tuned, we can begin the work of examining the individual Oracle tables and indexes within the database. At this phase, we look at the settings that can govern the behavior of a table and determine how well the settings accommodate the needs of the individual database.
When the Oracle objects are tuned, we proceed with tuning the individual SQL queries that are issued against the Oracle database. This is often one of the most challenging areas of Oracle turning because there can be many thousands of SQL statements issued against a highly active Oracle database. The person tuning the Oracle SQL has to identify the most frequently used SQL statements and apply the tools necessary to tune each statement for the optimal execution plan
Once we've completed the tuning of the Oracle server, we can look at the global parameters that affect the Oracle database (the Oracle instance). When looking at the Oracle database, we take a look at the database as a whole, paying careful attention to the Oracle initialization parameters that govern the configuration of the System Global Area (SGA) and the overall behavior of the database.
Once the database server and the Oracle instance have been tuned, we can begin the work of examining the individual Oracle tables and indexes within the database. At this phase, we look at the settings that can govern the behavior of a table and determine how well the settings accommodate the needs of the individual database.
When the Oracle objects are tuned, we proceed with tuning the individual SQL queries that are issued against the Oracle database. This is often one of the most challenging areas of Oracle turning because there can be many thousands of SQL statements issued against a highly active Oracle database. The person tuning the Oracle SQL has to identify the most frequently used SQL statements and apply the tools necessary to tune each statement for the optimal execution plan
