Oracle performance tuning

When SQL statements are fired in the Oracle database, a part called the optimizer will determine the most efficient execution path of the statement after considering many factors related to the objects referenced and the conditions specified in the query. In the old days a rule based optimizer was used, but since Oracle 8i most of the time a cost based optimizer is used.
For the Cost Based Optimizer to work efficiently, Oracle needs to have right statistics. There is a seperate document that descripts how to create statistics for Oracle tables/columns.Tracing SQLTo trace SQL statements you can also use TKProfSince Oracle 10g Oracle ADDM can be used to look at executed SQL statements.In SQL*Plus statements can be traced by using the command 'set autotrace on;'.Tuning SQLIf SQL statemnts are not performing you can do many things about it. Some solutions can be: • Add a hint to the statement to influence the optimizer. • Add indexes • Rewrite the SQL
Oracle 10 automated performance tuning features.Oracle 10g provides some automated features:Automatic Workload Repository (AWR)The Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. This data is both in memory and stored in the database. The gathered data can be displayed in both reports and views. The statistics collected and processed by AWR include: • Object statistics that determine both access and usage statistics of database segments • Time model statistics based on time usage for activities, displayed in the V$SYS_TIME_MODEL and V$SESS_TIME_MODEL views • Some of the system and session statistics collected in the V$SYSSTAT and V$SESSTAT views • SQL statements that are producing the highest load on the system, based on criteria such as elapsed time and CPU time • Active Session History (ASH) statistics, representing the history of recent sessions activity • AWR automatically generates snapshots of the performance data once every hour and collects the statistics in the workload repository. You can also manually create snapshots, but this is usually not necessary. The data in the snapshot interval is then analyzed by the Automatic Database Diagnostic Monitor (ADDM).
Automatic Database Diagnostic MonitorAutomatic Database Diagnostic Monitor (ADDM) analyzes the information collected by the AWR for possible performance problems with the Oracle database. An ADDM analysis is performed every time an AWR snapshot is taken and the results are saved in the database. You can view the results of the analysis using Oracle Enterprise Manager or by viewing a report in a SQL*Plus session. In most cases, ADDM output should be the first place that a DBA looks when notified of a performance problem. Automatic database diagnostic monitoring is enabled by default and is controlled by the STATISTICS_LEVEL initialization parameter. The STATISTICS_LEVEL parameter should be set to the TYPICAL or ALL to enable the automatic database diagnostic monitoring. The default setting is TYPICAL. Setting STATISTICS_LEVEL to BASIC disables many Oracle features, including ADDM, and is strongly discouraged. The primary interface for diagnostic monitoring is the Oracle Enterprise Manager Database Control. On the Database Home page, ADDM findings for the last analysis period are displayed under Diagnostic Summary. ADDM can also be invoked in SQL*Plus. This can be done by running the $ORACLE_HOME/rdbms/admin/addmrpt.sql script. SQL Tuning AdvisorSQL Tuning Advisor allows a quick and efficient technique for optimizing SQL statements without modifying any statements. Automatic SQL Tuning capabilities are exposed through a server utility called the SQL Tuning Advisor. The SQL Tuning Advisor takes one or more SQL statements as an input and invokes the Automatic Tuning Optimizer to perform SQL tuning on the statements. The output of the SQL Tuning Advisor is in the form of an advice or recommendations, along with a rationale for each recommendation and its expected benefit. The recommendation relates to collection of statistics on objects, creation of new indexes, restructuring of the SQL statement, or creation of SQL Profile. A user can choose to accept the recommendation to complete the tuning of the SQL statements. The recommended interface for the SQL Tuning Advisor is the Oracle Enterprise Manager. The SQL Tuning Advisor may be used to tune a single or multiple SQL statements. When tuning multiple SQL statements, Oracle Enterprise Manager will automatically create a SQL Tuning Set (STS) from a user-defined set of SQL statements. An STS is a database object that stores SQL statements along with their execution context. While the recommended interface for the SQL Tuning Advisor is the Oracle Enterprise Manager Database Control, the advisor can be administered with procedures in the DBMS_SQLTUNE package. To use the APIs, the user must be granted specific privileges. Running SQL Tuning Advisor using DBMS_SQLTUNE package is a multi-step process:1. Create a SQL Tuning Set (if tuning multiple SQL statements)2. Create a SQL tuning task3. Execute a SQL tuning task4. Display the results of a SQL tuning task5. Implement recommendations as appropriate
Sample code: -- Tuning task created for specific a statement from the AWR.DECLARE l_sql_tune_task_id VARCHAR2(100);BEGIN l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( begin_snap => 743, end_snap => 804, sql_id => '2udx7yrn9gcf2', scope => DBMS_SQLTUNE.scope_comprehensive, time_limit => 60, task_name => 'Edwin_AWR_tuning_task', description => 'Test Tuning task for select distinct .'); DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' l_sql_tune_task_id);END;/
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'Edwin_AWR_tuning_task');
SET LONG 10000;SET PAGESIZE 1000SET LINESIZE 200SELECT DBMS_SQLTUNE.report_tuning_task('Edwin_AWR_tuning_task') AS recommendations FROM dual;SET PAGESIZE 24

Create statistics

Create statisticsSince Oracle 8i the Cost Based Optimizer (CBO) is the preferred optimizer for Oracle.In order to make good use of the CBO, you need to create statistics for the data in the database. There are several options to create statistics.
Analyze command The ANALYZE command is available for all versions of Oracle, however to obtain faster and better statistics use the procedures supplied - in 7.3.4 and 8.0 DBMS_UTILITY.ANALYZE_SCHEMA, and in 8i and above - DBMS_STATS.GATHER_SCHEMA_STATS The analyze table can be used to create statistics for 1 table, index or cluster. Syntax:ANALYZE table tableName {computeestimatedelete) statistics optionsANALYZE table indexName {computeestimatedelete) statistics optionsANALYZE cluster clusterName {computeestimatedelete) statistics optionsCode examples
ANALYZE table scott compute statistics;
ANALYZE table scott estimate statistics sample 25 percent;
ANALYZE table scott estimate statistics sample 1000 rows;
analyze index sc_idx compute statistics;
analyze index sc_idx validate structure;
DBMS_UTILITY.ANALYZE_SCHEMA With DBMS_UTILITY.ANALYZE_SCHEMA you can gather all the statistics for all the tables, clusters and indexes of a schema. Code examples
exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','COMPUTE');
exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE', estimate_rows =>
1000);
exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE', estimate_percent
=> 25);
exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','DELETE');
Note: It's also possible to analyze the whole database with the DBMS_UTILITY.ANALYZE_DATABASE('COMPUTE'); command.
DBMS_STATS.GATHER_SCHEMA_STATS From Oracle 8i the DBMS_STATS package is the preferred method Oracle list a number of benefits to using it including parallel execution, long term storage of statistics and transfer of statistics between servers. Once again, it follows a similar format to the other methods: Syntax:exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname,estimate_percent, block_sample , method_opt,degree,granularity,cascade,stattab, statid,options,statown ,no_invalidate, gather_temp,gather_fixed); Code examples:
exec
DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT',DBMS_STATS.AUTO_SAMPLE_SIZE);
exec
DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'SCOTT'
,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);
EXEC DBMS_STATS.gather_schema_stats(ownname => 'SCOTT',
estimate_percent => 25);
EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES');
EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK');
exec DBMS_STATS.DELETE_SCHEMA_STATS('SCOTT');
Note: It's also possible to gather statistics for the whole database with the DBMS_STATS.gather_database_stats; command.
Transfering statistics between database. It can be very handy to use production statistics on your development database, so that you can forecast the optimizer behavior.You can do this the following way:1. Create the statistics table.exec DBMS_STATS.CREATE_STAT_TABLE(ownname =>'SCHEMA_NAME' ,stat_tab => 'STATS_TABLE' , tblspace => 'STATS_TABLESPACE');Example:exec DBMS_STATS.CREATE_STAT_TABLE(ownname =>'SYSTEM',stat_tab => 'STATS_TABLE');2. Export statistics to statistics tableEXEC DBMS_STATS.EXPORT_SCHEMA_STATS('ORIGINAL_SCHEMA' ,'STATS_TABLE',NULL,'SYSTEM');3. Import statistics into the data dictionary.exec DBMS_STATS.IMPORT_SCHEMA_STATS('NEW_SCHEMA','STATS_TABLE',NULL,'SYSTEM');4. Drop the statistics table.exec DBMS_STATS.DROP_STAT_TABLE('SYSTEM','STATS_TABLE');

Opatch Utility: The OPatch utility is located in the /OPatch directory. You can run it with various commands and options. The following command shows the syntax for the OPatch utility:

/opatch [-options]
OPatch Commands

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

napply: Installs n number of patches (hence napply). Refer to "napply Command" for more information.

auto: Applies Oracle Clusterware patches. Refer to "auto Command" for more information.

lsinventory: Lists what is currently installed on the system. Refer to "lsinventory Command" for more information.

query: Queries a given patch for specific details. Refer to "query Command" for more information.

rollback: Removes an interim patch. Refer to "rollback Command" for more information.

nrollback: Removes n number of patches (hence nrollback). Refer to "nrollback Command" for more information.

version: Prints the current version of the patch tool. Refer to "version Command" for more information.