Automated SQL Tuning - Displaying Automatic SQL Tuning Job Details

Problem
You have an Oracle Database 11g environment and want to determine if the Automatic SQL Tuning job is enabled and regularly running. If the job is enabled, you want to display other aspects, such as when it starts and how long it executes.

Solution
Use the following query to determine if any Automatic SQL Tuning jobs are enabled:

SELECT client_name, status, consumer_group, window_group

FROM dba_autotask_client

ORDER BY client_name;

The following output shows that there are three enabled automatic jobs running, one of which is the SQL Tuning Advisor:

CLIENT_NAME                       STATUS            CONSUMER_GROUP         WINDOW_GROUP

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

sql tuning advisor               ENABLED    ORA$AUTOTASK_SQL_GROUP       ORA$AT_WGRP_SQ

auto space advisor               ENABLED  ORA$AUTOTASK_SPACE_GROUP       ORA$AT_WGRP_SA

auto optimizer stats collection  ENABLED  ORA$AUTOTASK_STATS_GROUP       ORA$AT_WGRP_OS

Run the following query to view the last several times the Automatic SQL Tuning Advisor job has run:

SELECT task_name, status, TO_CHAR(execution_end,'DD-MON-YY HH24:MI')

FROM dba_advisor_executions

WHERE task_name='SYS_AUTO_SQL_TUNING_TASK'

ORDER BY execution_end;

Here is some sample output:

TASK_NAME                          STATUS TO_CHAR(EXECUTION_END

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

SYS_AUTO_SQL_TUNING_TASK        COMPLETED       30-APR-11 06:00

SYS_AUTO_SQL_TUNING_TASK        COMPLETED       01-MAY-11 06:02

How It Works
When you create a database in Oracle Database 11g or higher, Oracle automatically implements three automatic maintenance jobs:

• Automatic SQL Tuning Advisor
• Automatic Segment Advisor
• Automatic Optimizer Statistics Collection

These tasks are automatically configured to run in maintenance windows. A maintenance window is a specified time and duration for the task to run. You can view the maintenance window details with this query:

SELECT window_name,TO_CHAR(window_next_time,'DD-MON-YY HH24:MI:SS')

,sql_tune_advisor, optimizer_stats, segment_advisor

FROM dba_autotask_window_clients;

WINDOW_NAME      TO_CHAR(WINDOW_NEXT_TIME SQL_TUNE OPTIMIZE SEGMENT_

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

THURSDAY_WINDOW        28-APR-11 22:00:00  ENABLED  ENABLED  ENABLED

FRIDAY_WINDOW          29-APR-11 22:00:00  ENABLED  ENABLED  ENABLED

SATURDAY_WINDOW        30-APR-11 06:00:00  ENABLED  ENABLED  ENABLED

SUNDAY_WINDOW          01-MAY-11 06:00:00  ENABLED  ENABLED  ENABLED

There are several data dictionary views related to the automatically scheduled jobs. See Table 11-1 for descriptions of these views.

Automated SQL Tuning

Prior to Oracle Database 11g, accurately identifying poorly performing SQL queries and recommending solutions was mainly the purview of veteran SQL tuners. Typically one had to know how to identify high-resource SQL statements and bottlenecks, generate and interpret execution plans, extract data from the dynamic performance views, understand wait events and statistics, and then collate this knowledge to produce good SQL queries. As you’ll see in this chapter, the Oracle SQL tuning paradigm
has shifted a bit.

With the advent of automated SQL tuning features, anybody from novice to expert can generate and recommend solutions for SQL performance problems. This opens the door for new ways to address problematic SQL. For example, imagine your boss coming to you each morning with tuning recommendations and asking what the plan is to implement enhancements. This is different.

The automated SQL tuning feature is not a panacea for SQL performance angst. If you are an expert SQL tuner, there’s no need to fear your skills are obsolete or your job is lost. There will always be a need to verify recommendations and successfully implement solutions. A human is still required to review the automated SQL tuning output and confirm the worthiness of fixes.

Still, there’s been a change in the way SQL performance problems can be identified and solutions can be recommended. Some old-school folks may disagree and argue that you can’t allow just anybody to generate SQL tuning advice. Regardless, Oracle has made these automated tools accessible and usable by the general population (for a fee). Therefore you need to understand the underpinnings of these features and how to use them.

This chapter focuses on the following automated SQL tuning tools:

• 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

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(@?/rdbms/admin/addmrpt.sql), the DBMS_ADDM package, or Enterprise Manager.

Please take a long look at Figure 11-1. This diagram demonstrates how the various automated tools interact and in what scenarios you would use a particular feature. Refer back to this diagram as you work through the recipes in this chapter.
Particularly notice that you can easily use SQL statements found in the AWR or SQL currently in memory as input for various Oracle tuning tools. This allows you to systematically identify and use high-resource SQL statements as the target for various performance tuning activities.

Minimizing System Contention - Understanding Response Time

Problem
You want to understand what database response time is, and its relationship with wait time.

Solution
The most crucial performance indicator in a database is response time. Response time is the time it takes to get a response from the database for a query that a client sends to the database. Response time is simply the sum of two components:

response time(DB Time) = processing time + wait time

The foregoing relationship is also frequently represented as R=S + W, where R is the response time, S the service time, and W stands for the wait time. The processing time component is the actual time spent by the database processing the request. Wait time, on the other hand, is time actually wasted by the database—it’s the time the database spends waiting for resources such as a lock on a table's rows, library cache latch, or any of the numerous resources that a query needs to complete its processing. Oracle has hundreds of official wait events, a dozen or so of which are crucial to troubleshooting slow-running queries.

Do You Have a Wait Problem?
It’s easy to find out the percentage of time a database has spent waiting for resources instead of actually executing. Issue the following query to find out the relative percentages of wait times and actual CPU processing in the database:

SQL> select metric_name, value, metric_unit from v$sysmetric

where metric_name in ('Database CPU Time Ratio',

'Database Wait Time Ratio') and

intsize_csec =

(select max(INTSIZE_CSEC) from V$SYSMETRIC);

METRIC_NAME                     VALUE             METRIC_UNIT

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

Database Wait Time Ratio    11.371689          % Wait/DB_Time

Database CPU Time Ratio     87.831890           % Cpu/DB_Time

SQL>

If the query shows a very high value for the Database Wait Time Ratio, or if the Database Wait Time Ratio is much greater than the Database CPU Time Ratio, the database is spending more time waiting than processing and you must dig deeper into the Oracle wait events to identify the specific wait events causing this.

Getting ASH(Active Session History) Information from the Data Dictionary

Problem
You want to see what ASH information is kept in Oracle’s data dictionary.

Solution
There are a couple of data dictionary views you can use to get ASH information. The first,V$ACTIVE_SESSION_HISTORY, can be used to get information on current or recent sessions within your database. The second, DBA_HIST_ACTIVE_SESS_HISTORY, is used to store older, historical ASH information. If you wanted to see all the events and their total wait time for activity within the past 15 minutes in your database, you could issue the following query:

SELECT s.event, sum(s.wait_time + s.time_waited) total_wait

FROM v$active_session_history s

WHERE s.sample_time between sysdate-1/24/4 AND sysdate

GROUP BY s.event

ORDER BY 2 desc;

EVENT                                                            TOTAL_WAIT

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

                                                                 20002600

db file scattered read                                           15649078

read by other session                                            9859503

db file sequential read                                          443298

direct path read temp                                            156463

direct path write temp                                           139984

log file parallel write                                          49469

db file parallel write                                           21207

log file sync                                                    11793

SGA: allocation forcing component growth                         11711

control file parallel write                                      4421

control file sequential read                                     2122

SQL*Net more data from client                                    395

SQL*Net more data to client                                      66

If you wanted to get more session-specific information, and wanted to see the top 5 sessions that were using the most CPU resources within the last 15 minutes, you could issue the following query:

column username format a12

column module format a30

SELECT * FROM

(

SELECT s.username, s.module, s.sid, s.serial#, count(*)

FROM v$active_session_history h, v$session s

WHERE h.session_id = s.sid

AND h.session_serial# = s.serial#

AND session_state= 'ON CPU' AND

sample_time > sysdate - interval '15' minute

GROUP BY s.username, s.module, s.sid, s.serial#

ORDER BY count(*) desc

)

where rownum <= 5;

USERNAME                         MODULE        SID    SERIAL#   COUNT(*)

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

SYS                      DBMS_SCHEDULER        536          9         43

APPLOAD            etl1@app1 (TNS V1-V3)       1074       3588        16

APPLOAD            etl1@app1 (TNS V1-V3)       1001       4004        12

APPLOAD            etl1@app1 (TNS V1-V3)        968        108         5

DBSNMP          emagent@ora1 (TNS V1-V3)        524          3         2

The SESSION_STATE column has two valid values, ON CPU and WAITING, which denote whether a session is active or is waiting for resources. If you wanted to see the sessions that are waiting for resources, you could issue the same query as previously, with a SESSION_STATE of WAITING. If you wanted to see the most heavily used database objects for a given sample period, you could join V$ACTIVE_SESSION_HISTORY to the DBA_OBJECTS view to get that information. In the following example, we are getting a list of the top 5 database objects in use, along with the event associated with
that database object, over the past 15 minutes:

SELECT * FROM

(

SELECT o.object_name, o.object_type, s.event,

SUM(s.wait_time + s.time_waited) total_waited

FROM v$active_session_history s, dba_objects o

WHERE s.sample_time between sysdate - 1/24/4 and sysdate

AND s.current_obj# = o.object_id

GROUP BY o.object_name, o.object_type, s.event

ORDER BY 4 desc

)

WHERE rownum <= 5;

OBJECT_NAME                      OBJECT_TYPE                     EVENT TOTAL_WAITED

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

WRI$_ALERT_OUTSTANDING                 TABLE Streams AQ: enqueue block    110070196

                                                      ed on low memory

APP_ETL_IDX1                           INDEX     read by other session     65248777

APP_SOURCE_INFO              TABLE PARTITION    db file scattered read     33801035

EMPPART_PK_I                 INDEX PARTITION     read by other session     28077262

APP_ORDSTAT                  TABLE PARTITION    db file scattered read     15569867

How It Works
The DBA_HIST_ACTIVE_SESS_HISTORY view can give you historical information on sessions that have aged out of the V$ACTIVE_SESSION_HISTORY view. Let’s say you had a day when performance was particularly bad on your database. You could zero in on historical session information for a given time frame, provided it is still held within the DBA_HIST_ACTIVE_SESS_HISTORY view. For instance, if you wanted to get the users that were consuming the most resources for a given day when performance was poor, you could issue the following query:

SELECT * FROM

(

SELECT u.username, h.module, h.session_id sid,

h.session_serial# serial#, count(*)

FROM dba_hist_active_sess_history h, dba_users u

WHERE h.user_id = u.user_id

AND session_state= 'ON CPU'

AND (sample_time between to_date('2011-05-15:00:00:00','yyyy-mm-dd:hh24:mi:ss')

AND to_date('2011-05-15:23:59:59','yyyy-mm-dd:hh24:mi:ss'))

AND u.username != 'SYS'

GROUP BY u.username, h.module, h.session_id, h.session_serial#

ORDER BY count(*) desc

)

where rownum <= 5;

USERNAME                             MODULE        SID    SERIAL#   COUNT(*)

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

APPLOAD1              etl1@app1 (TNS V1-V3)       1047        317       1105

APPLOAD1              etl1@app1 (TNS V1-V3)       1054        468        659

APPLOAD1              etl1@app1 (TNS V1-V3)       1000        909        387

STG                 oracle@ora1 (TNS V1-V3)        962       1707        353

APPLOAD1              etl1@app1 (TNS V1-V3)        837      64412        328

To then zero in on the database objects, you could issue the following query for the same time frame:

SELECT * FROM

(

SELECT o.object_name, o.object_type, s.event,

SUM(s.wait_time + s.time_waited) total_waited

FROM dba_hist_active_sess_history s, dba_objects o

WHERE s.sample_time

between to_date('2011-05-15:00:00:00','yyyy-mm-dd:hh24:mi:ss')

AND to_date('2011-05-15:23:59:59','yyyy-mm-dd:hh24:mi:ss')

AND s.current_obj# = o.object_id

GROUP BY o.object_name, o.object_type, s.event

ORDER BY 4 desc

)

WHERE rownum <= 5;

OBJECT_NAME                      OBJECT_TYPE                     EVENT TOTAL_WAITED

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

EMPPART                      TABLE PARTITION  PX Deq Credit: send blkd   8196703427

APPLOAD_PROCESS_STATUS                 TABLE    db file scattered read    628675085

APPLOAD_PROCESS_STATUS                 TABLE     read by other session    408577335

APP_SOURCE_INFO              TABLE PARTITION    db file scattered read    288479849

APP_QUALITY_INFO             TABLE PARTITION    Datapump dump file I/O    192290534

Manually Getting Active Session Information

Problem
You need to do performance analysis on sessions that run too frequently or are too short to be available on available AWR snapshots. The AWR snapshots are not taken often enough to capture the information that you need.

Solution
You can use the Oracle Active Session History (ASH) information in order to get real-time or near real time session information. While the AWR information is very useful, it is bound by the reporting periods, which are by default run every hour on your database. The ASH information has active session information, and is sampled every second from V$SESSION, and can show more real-time or near real time session information to assist in doing performance analysis on your database. There are a few ways to get active session information from the database:

• Running the canned ASH report
• Running an ASH report from within Enterprise Manager
• Getting ASH information from the data dictionary

The easiest method to get information on active sessions is to run the ashrpt.sql script, which is similar in nature to the awrrpt.sql script that is run when generating an AWR report. When you run the ashrpt.sql script, it asks you for the following:

• Report type (text or HTML)
• Begin time for report (defaults to current time minus 15 minutes)
• End time for report (defaults to current time)
• Report name

There are many sections to the ASH report. See Table 4-1 for a brief description of each section. See the following snippet from many of the sections of the ASH report. Some sections have been shortened for brevity.

How It Works

Retrieving ASH information is necessary if you need to get session information more current than you can retrieve from the AWR report. Again, AWR information is generated only hourly by default. ASH information is gathered every second from V$SESSION, and stores the most useful session information to help gauge database performance at any given moment.

The ASH information is stored within a circular buffer in the SGA. Oracle documentation states that the buffer size is calculated as follows:

Max [Min [ #CPUs * 2 MB, 5% of Shared Pool Size, 30MB ], 1MB ]

The amount of time that the information is stored within the data dictionary depends on the activity within your database. You may need to view the DBA_HIST_ACTIVE_SESS_HISTORY historical view in order to get the ASH information you need if your database is very active. For an example of querying the DBA_HIST_ACTIVE_SESS_HISTORY view, see Recipe 4-13. To quickly see how much data is held in your historical view, you could simply get the earliest SAMPLE_TIME from the DBA_HIST_ACTIVE_SESS_HISTORY view:

SELECT min(sample_time) FROM dba_hist_active_sess_history;
MIN(SAMPLE_TIME)
---------------------------------------------------------------------------
20-MAR-11 11.00.27.433 PM

The MMON background process, which manages the AWR hourly snapshots, also flushes ASH information to the historical view at the same time. If there is heavy activity on the database, and the buffer fills between the hourly AWR snapshots, the MMNL background process will wake up and flush the ASH data to the historical view.

The V$ACTIVE_SESSION_HISTORY and DBA_HIST_ACTIVE_SESS_HISTORY views contain much more detailed information than just the samples shown within this recipe, and you can drill down and get much more information at the session level, if desired, including information regarding actual SQL statements, the SQL operations, blocking session information, and file I/O information.

Quickly Analyzing AWR Output

Problem
You have generated an AWR report, and want to quickly interpret key portions of the report to determine if there are performance issues for your database.

Solution
The AWR report, like its predecessors Statspack and UTLBSTAT/UTLESTAT for earlier versions of Oracle, has a multitude of statistics to help you determine how your database is functioning and
performing. There are many sections of the report. The first three places on the report to gauge how your database is performing are as follows:

1. DB Time
2. Instance Efficiency
3. Top 5 Timed Events

The first section displayed on the report shows a summary of the snapshot window for your report, as well as a brief look at the elapsed time, which represents the snapshot window, and the DB time, which represents activity on your database. If the DB time exceeds the elapsed time, it denotes a busy database. If it is a lot higher than the elapsed time, it may mean that some sessions are waiting for resources. While not specific, it can give you a quick view to see if your overall database is busy and possibly overtaxed. We can see from the following example of this section that this is a very busy database by comparing the elapsed time to the DB time:

Snap Id Snap Time Sessions Curs/Sess

---

Begin Snap: 18033 11-Jun-11 00:00:43 59 2.3
End Snap: 18039 11-Jun-11 06:00:22 69 2.4
Elapsed: 359.66 (mins)
DB Time: 7,713.90 (mins)

The instance efficiency section gives you a very quick view to determine if things are running adequately on your database. Generally, most percentages within this section should be above 90%. The Parse CPU to Parse Elapsd metric shows how much time the CPU is spending parsing SQL statements.

The lower this metric is, the better. In the following example, it is about 2%, which is very low. If this metric ever gets to 5%, it may mean investigation is warranted to determine why the CPU is spending
this much time simply parsing SQL statements.

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.64 Redo NoWait %: 99.99
Buffer Hit %: 91.88 In-memory Sort %: 99.87
Library Hit %: 98.92 Soft Parse %: 94.30
Execute to Parse %: 93.70 Latch Hit %: 99.89
Parse CPU to Parse Elapsd %: 2.10 % Non-Parse CPU: 99.75

The third place to get a quick glance at your database performance is the Top 5 Timed Events section. This section gives you a quick look at exactly where the highest amount of resources are being consumed within your database for the snapshot period. Based on these results, it may show you that there is an inordinate amount of time spent performing full-table scans, or getting data across a network database link. The following example shows that the highest amount of resources is being used performing index scans (noted by “db file sequential read”). We can see there is significant time on “local write wait”, “enq: CF – contention”, and “free buffer waits”, which gives us a quick view of what possible contention and wait events are for our database, and gives us immediate direction for investigation and analysis.

Top 5 Timed Foreground Events Avg
~~~~~~~~~~~~~~~~~~~~~~~~~ wait % DB
Event Waits Time (s) (ms) Time Wait Class

---

db file sequential read 3,653,606 96,468 26 20.8 User I/O
local write wait 94,358 67,996 721 14.7 User I/O
enq: CF - contention 18,621 46,944 2521 10.1 Other
free buffer waits 3,627,548 38,249 11 8.3 Configurat
db file scattered read 2,677,267 32,400 12 7.0 User I/O

How It Works
After looking at the DB Time, Instance Efficiency, and Top 5 Timed Events sections, if you want to look in more detail at the sections of a given AWR report, refer to Recipe 7-17 in Chapter 7 for more information. Because the sheer volume of information in the AWR report is so daunting, it is strongly recommended to create baselines that represent a normal processing window. Then, AWR snapshots can be compared to the baselines, and metrics that may just look like a number on a given AWR report will stand out when a particular metric is significantly above or below a normal range.