Performance Tuning

PERFORMANCE TUNING

Tools Available for Oracle Performance Tuning

• Explain plan / DBMS_XPLAN package:- When faced with an SQL statement that you suspect could be improved, before changing it you need to know what it's current execution plan is.
• SQL trace: - SQL trace is an Oracle database facility that provides individual performance information about SQL statements either at the instance or session level.

ALTER SESSION SET sql_trace=TRUE;

EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, 

serial#=>1234, sql_trace=>TRUE);

Trcsess:- Activating trace on multiple sessions means that 

trace information is spread throughout many trace files. 

For this reason Oracle 10g introduced the trcsess utility, 

which allows trace information from multiple trace files to 

be identified and consolidated into a single trace file.

To Identify Current Trace File:- 

SELECT value FROM   v$diag_info WHERE name = 'Default Trace File';

VALUE

-------------------------------------------------------------------------

c:\app\administrator\diag\rdbms\primedb\primedb\trace\primedb_ora_2564.trc

• TKPROF:- TKPROF is a program that is run against Oracle trace files to format them and generate a readable output.
• DBMS_STATS:- 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.
• DBMS_SPM : - This package enables you to manage SQL execution plans to ensure that the plan and therefore the performance of the corresponding SQL statements are unchanged irrespective of any changes to the system such as more data, a new version of the Oracle database, or new applications.
• DBMS_OUTLN packages: - This package enables the management of stored outlines (stored data for an an execution plan for a specific SQL statement) in the Oracle database. 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).
• Automatic Workload Repository (AWR): - Introduced with Oracle 10g, AWR helps with Oracle performance tuning by collecting, processing and maintaining performance information which can be displayed in reports and/or views.
• Automatic Database Diagnostic Monitor (ADDM): - This analyzes the data collected by AWR to identify any performance issues and provides recommendations to correct problems.

PERFORMANCE TUNING ADVISORS:-

ADDM – AUTOMATIC DATABASE DIAGNOSTIC MONITOR

ADDM makes it possible for the Oracle Database to diagnose its own performance and determine how any identified problems can be resolved. See "Performance Self-Diagnostics: Automatic Database Diagnostics Monitor" and "Diagnosing Performance Problems".

MEMORY ADVISOR – By default, Oracle automatically tunes physical memory allocation for optimal performance. The Memory Advisor gives graphical analysis of SGA and PGA settings, which you can use for what-if planning. See "Using the Memory Advisor".

SEGMENT ADVISOR -

SQL ACCESS ADVISOR Use this advisor to tune schema to a given SQL workload. For example, the SQL Access Advisor can provide recommendations for creating indexes and materialized views for a given workload. See "Using the SQL Access Advisor".

SQL TUNING ADVISOR This advisor analyzes SQL statements and makes recommendations for improving performance. See "Using the SQL Tuning Advisor".

UNDO MANAGEMENT

MTTR ADVISOR