Automated Tuning

RESOURCE CONSUMING SQL's


Look at the V$SQLSTATS view, which gives information about currently or recently run SQL statements which are consuming most of the System Resources.

You can use the V$SQL_MONITOR view to see real-time statistics of currently running SQL, and see the resource consumption used for a given query based on such statistics as CPU usage, buffer gets, disk reads, and elapsed time of the query.

SQL TRACE


In Oracle Database 11g, the alter session set events command has been enhanced to allow you to trace a process by specifying the process ID (PID), process name (PNAME), or the Oracle Process ID (ORAPID). You can also use an alter system command with the same general syntax as well.

alter session set events 'sql_trace {process : pid = <pid>, pname = <pname>, orapid =
<orapid>} rest of event specification'

AUTOMATED SQL TUNING


The Following are the Automated SQL Tuning Tools which are provided by Oracle at COST

*    Automatic SQL Tuning
*    SQL tuning sets (STS)
*    SQL Tuning Advisor
*    Automatic Database Diagnostic Monitor (ADDM)

Starting with Oracle Database 11g, Automatic SQL Tuning is a preset background database job that by default runs every day. This task examines high resource-consuming statements in the Automatic Workload Repository (AWR). It then invokes the SQL Tuning Advisor and generates tuning advice (if any) for each statement analyzed. As part of automated SQL tuning, you can configure characteristics such as the automatic acceptance of some recommendations such as SQL profiles (see Chapter 12 for details on SQL profiles).

A SQL tuning set (STS) is a database object that contains one or more SQL statements and the associated execution statistics. You can populate a SQL tuning set from multiple sources, such as SQL recorded in the AWR and SQL in memory, or you can provide specific SQL statements. It's critical that you be familiar with SQL tuning sets. This feature is used as an input to several of Oracle's performance tuning and management tools, such as the SQL Tuning Advisor, SQL Plan Management, SQL Access Advisor, and SQL Performance Advisor.

The SQL Tuning Advisor is central to Oracle's Automatic SQL Tuning feature. This tool runs automatically on a periodic basis and generates tuning advice for high resource-consuming SQL statements found in the AWR. You can also run the SQL Tuning Advisor manually and provide as input specific snapshot periods in the AWR, high resource-consuming SQL in memory, or user-provided SQL statements. This tool can be invoked via the DBMS_SQLTUNE package, SQL Developer, or Enterprise Manager.

The Automatic Database Diagnostic Monitor (ADDM) analyzes information in the AWR and provides recommendations on database performance issues including high resource-consuming SQL statements. The main goal of ADDM is to help you reduce the overall time (the DB time metric) spent by the database processing user requests. This tool can be invoked from an Oracle-provided SQL script, the DBMS_ADDM package, or Enterprise Manager.

The Following Query will list you the Auotmatic jobs which are enabled

SELECT client_name, status, consumer_group, window_group
FROM dba_autotask_client
ORDER BY client_name;

AUTOMATED MAINTENANCE JOBS


When you install Oracle Database 11g, by default three automatic maintenance jobs are configured:

*    Automatic SQL Tuning
*    Statistics gathering
*    Segment advice