Execute the tuning task

Execute dbms_sqltune.Execute_tuning_task (task_name => 'sql_tuning_task_test1');

3.1 You can check the status of the task using following query.

select status from dba_advisor_log where task_name='sql_tuning_task_test1'; 4. Now view the Recommendation

set linesize 100 set long 1000

set longchunksize 1000

SQL> select dbms_sqltune.report_tuning_task('sql_tuning_task_test1') from dual; DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TASK_TEST1')

--- GENERAL INFORMATION SECTION

--- Tuning Task Name : sql_tuning_task_test1

Scope : COMPREHENSIVE Time Limit(seconds): 60

Completion Status : COMPLETED Started at : 06/22/2006 15:33:13 Completed at : 06/22/2006 15:33:14

--- SQL ID : ad1437c24nqpn

SQL Text: SELECT * from EMP where empno = 1200

--- FINDINGS SECTION (1 finding)

--- 1- Statistics Finding

---

Table "SCOTT"."EMP" was not analyzed. Recommendation

---

Consider collecting optimizer statistics for this table.

execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname => 'EMP', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

Based on this information, you can decide what actions are necessary to tune the SQL.

Automatic Workload Repository (AWR)

One of the nice features of Oracle 10g that I really like is the Automatic Workload Repository (AWR). This new tool is kind of replacement of STATSPACK. AWR takes snapshots of the system every 60 minutes. You can also create manual snapshots like in statspack. At the end this tool give you feature to generate txt or HTML (I like it) report.

The article below will explain how to create manual snapshots.

You can manually create snapshots with the CREATE_SNAPSHOT procedure if you want to capture statistics at times different than those of the automatically generated snapshots.

BEGIN

DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT (); END;

/

You can also drop a range of snapshots using the DROP_SNAPSHOT_RANGE procedure. To view a list of the snapshot Ids along with database Ids, check the DBA_HIST_SNAPSHOT view. For example, you can drop the following range of snapshots:

BEGIN

DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 1, high_snap_id => 10 dbid => 131045908);

END; /

If you like, you can also adjust the interval and retention of snapshot generation for a specified database id, but note that this can affect the precision of the Oracle diagnostic tools.

The INTERVAL setting affects how often in minutes that snapshots are automatically generated. The RETENTION setting affects how long in minutes that snapshots are stored in the workload repository. To adjust the settings, use the MODIFY_SNAPSHOT_SETTINGS procedure. For example:

BEGIN

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 14400, interval => 15, dbid => 131045908);

END; /

In this example, the retention period is specified as 14400 minutes (10 days) and the interval between each snapshot is specified as 15 minutes. You can also specify NULL to preserved existing value. If you don’t specify optional database identifier then local database is used.is 3310949047. If you do not specify a value for dbid, the local database identifier is used as the default value. You can check the current settings for your database instance with the DBA_HIST_WR_CONTROL view.

SQL> col RETENTION format a20 SQL> col SNAP_INTERVAL format a30 SQL> set linesize 120

SQL> select * from DBA_HIST_WR_CONTROL; DBID SNAP_INTERVAL RETENTION

--- --- --- 131045908 +00000 01:00:00.0 +00007 00:00:00.0

In above example, snapshot interval is 1 hr and retention period is 7 days. Running the awrrpt.sql Report

To run an AWR report, a user must be granted the DBA role. You can view the AWR reports with Oracle Enterprise Manager or by running the following SQL scripts:

- The awrrpt.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids .

- The awrrpti.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids for a specified database and instance.

For example:

To generate a text report for a range of snapshot ids, run the awrrpt.sql script at the SQL prompt:

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

Reading Statspack

In Oracle, Performance Tuning is based on the following formula: Response Time = Service Time + Wait Time

Where

 Service Time is time spent on the CPU

 Wait Time is the sum of time spent on Wait Events i.e. non-idle time spent waiting for an event to complete or for a resource to become available.

Service Time is comprised of time spent on the CPU for Parsing, Recursive CPU usage (for PLSQL and recursive SQL) and CPU used for execution of SQL statements (CPU Other).

The above components of Service Time can be found from the following statistics: � Service Time from CPU used by this session

� CPU Parse from parse time cpu

� CPU Recursive from recursive cpu usage

From these, CPU Other can be calculated as follows:

CPU other = CPU used by this session - parse time CPU - recursive CPU usage

Many performance-tuning tools (including Statspack) produce a list of the top wait events. For example, Statspack s report contains the "Top 5 Wait Events" section.� (Pre-Oracle9i Release 2).

It is a common mistake to start dealing with Wait Events first and not taking in consideration the corresponding response time. So always compare the time consumed by the top wait events to the 'CPU used by this session' and identify the biggest consumers.

Here is an example where CPU Other was found to be a significant component of total Response Time even though the report shows direct path read as top wait event: