Oracle performance tuning can include speed performance, memory usage, cpu, general i/o or reads and writes specifically. Sometimes it is the Oracle database that needs to be fine tuned or the application using it but it always reducing resource usage by the database.
When tuning Oracle systems, consider individual susbsystems and application modules rather than looking at the system as a whole. Gather specific information about the performance problem such as time of day or the people using it so you have a general frame of reference as to where to begin.
Oracle Performance Tuning Tools
Look at the database you are using, Oracle 10g and upwards have more tools available than earlier versions. The general tools available are:
- explain plan / DBMS_XPLAN package
- SQL trace
- DBMS_STATS, DBMS_SPM and DBMS_OUTLN packages
- Automatic Workload Repository (AWR)
- Automatic Database Diagnostic Monitor (ADDM)
Explain Plan / DBMS_XPLAN
Often times, this is the only tool required for Oracle performance tuning as most problems are due to SQL statements that are not using the optimal path to access the data. If you have a SQL statement that you suspect could be improved, before changing it you need to know what it’s current execution plan is. Precede a statement with a predicted execution plan that the statement would use if it were to be run into the plan table from where it can be retrieved by calling DBMS_XPLAN.DISPLAY. The predicted execution plan may not be entirely accurate but you can determine the actual execution plan of the statement after it has been run by using DBMS_XPLAN.DISPLAY_CURSOR.
SQL Trace is an Oracle database facility that provides individual performance information about SQL statements either at the instance or session level. Information gathered about SQL statements include:
- Parse, execute, and fetch counts
- CPU and elapsed times
- Physical reads and logical reads
- Number of rows processed
- Misses on the library cache
- Username under which each parse occurred
- Each commit and rollback
- Wait event data for each SQL statement, and a summary for each trace file
TKPROF is a program that is run against Oracle trace files to format them and generate a readable output. It can also display the actual execution plan of a statement and create a SQL script that stores statistics in the database.
The DBMS_STATS package provides a number of routines for generating and manipulating statistics on tables and indexes in the database. These statistics are then used by the Oracle cost-based optimizer when determining the optimal execution plan for SQL statements.
The DBMX_SPM package allows you to manage SQL execution plans to ensure that the plan and the performance of the corresponding SQL statements are unchanged regardless of any changes to the system.
This package enables the management of stored outlines and data for an execution plan for a specific SQL statement. It enables the Oracle optimizer to re-create an execution plan equivalent to the original plan. In future releases of Oracle this will be deprecated in favour of SQL plan management (DBMS_SPM).
AWR helps with Oracle performance tuning by collecting, processing and maintaining performance information which can be displayed in reports and/or views.
ADDM analyzes the data collected by AWR to identify any performance issues and provides recommendations to correct problems.
Oracle ADF training courses can be taken online for both the introduction and advanced course curriculums. Firebox writes and delivers ADF courses live.