12. Leveraging SQL Advisors to Analyze and Fix SQL Problems

SQL issues formulate the major chunk of an Oracle professional’s troubleshooting activity. Hardly a day goes by in the professional life of an Oracle DBA that SQL issues don’t present a hardy challenge. With the sophistication of technology and the rapid advancement of subsequent versions loaded with new features, an advanced-level Swiss-knife toolset is required to address these issues. Oracle has come a long way in responding to the complex challenge of analyzing, troubleshooting, and fixing various SQL issues with a broad suite of SQL Advisors to address a wide spectrum of problem scenarios.

This chapter’s pointed focus is on the SQL Tuning Advisor—the standard tool of choice for identifying, analyzing, and fixing SQL issues. It also presents a primer and general guidelines on how to leverage the SQL Access Advisor and SQL Repair Advisor. This chapter is a good starting point for intuitive and rapid fixing of SQL issues by covering the SQL tuning, access, and repair advisors. How-to steps to run the SQL Advisors in Oracle Enterprise Manager (OEM) 12c are illustrated throughout this chapter.

Alternative command-line interface steps for running the SQL Tuning Advisor and SQL Access Advisor are also covered in this chapter. A brief overview of the various available SQL performance analyzers is also presented.

OEM 12c Select Database Main Menu Performance SQL Advisors Home

Figure 12.1 A representation of the SQL Advisor links in Advisor Central

Figure 12.2 A representation of the SQL Advisors home page in Advisor Central

The SQL Tuning Advisor is a real time-saver of a tool that can dramatically cut down on tuning SQL by eliminating the need for lengthy, laborious, and complex manual tuning of SQL statements. It can be invoked automatically as part of the Automatic Maintenance Tasks job framework (AUTOTASK) or manually as and when the need arises.

SQL Tuning Advisor performs a deep-level analysis on the underlying SQL statement, the relevant statistics and execution plans, and so on, and generates a whole host of recommendations, including SQL profiles, SQL baselines, new index creation, identification of stale statistics, and rewriting SQL statements for optimization. The reasoning and proposed impact is also provided for each recommendation. Alternative SQL plans are listed along with cost benefits for each proposed recommendation.

SQL Tuning Advisor is the main tool used by AUTOTASK. However, SQL Tuning Advisor run from AUTOTASK does not operate on SQL statements that involve parallel queries, nonrepeatable ad-hoc SQL statements, recursive SQL, and SQL-profiled long-running queries. These types of SQL statements can be processed by running SQL Tuning Advisor manually on demand.

SQL Tuning Advisor can be run on individual SQL statements or SQL tuning sets. SQL Tuning Advisor requires a separate license as part of the Oracle Database Tuning Pack.

OEM 12c Select Database Main Menu Performance SQL SQL Tuning Advisor

Figure 12.3 shows how to easily and intuitively run the SQL Tuning Advisor in OEM 12c on a SQL tuning set—a group of SQL statements. It can be run in either limited or comprehensive mode.

Figure 12.3 A representation of running SQL Tuning Advisor on a SQL tuning set (a set of SQL statements)

The time limit spent by the Advisor on each SQL statement can also be specified. Additionally, the schedule for running the SQL Tuning Advisor can be specified.

Alternatively, the SQL Tuning Advisor can be run from the Top Activity page and an individual SQL page in OEM, as shown in Figures 12.4 and 12.5.

Figure 12.4 Running SQL Tuning Advisor from the Top Activity page in OEM 12c

Figure 12.5 Running SQL Tuning Advisor from an individual SQL page in OEM

Figures 12.6, 12.7, 12.8, 12.9, and 12.10 show some of the sample recommendations along with an example comparison of explain plans. Implementing explain plans in OEM 12c is simply a matter of following the next click on the Implement button. Each recommendation’s impact should be evaluated and measured for benefit analysis before finalizing the implementation.

Figure 12.6 A representation of SQL Tuning Advisor recommendations for stale statistics and creating a SQL plan baseline

Figure 12.7 A representation of SQL Tuning Advisor recommendations for a SQL profile

Figure 12.8 A representation of SQL Tuning Advisor recommendations for a restructure SQL statement

Figure 12.9 A representation of SQL Tuning Advisor recommendations for SQL profiles

Figure 12.10 A representation of SQL Tuning Advisor recommendations for a comparison of original and new explain plans by implementing a SQL profile

Listing 12.1 Creating and Executing a SQL Tuning Advisor Task in SQL*Plus

Click here to view code image

DECLARE
  sqlstmt   CLOB;
  taskname  VARCHAR2(30);
BEGIN
  sqlstmt  :=    'select r.region,s.totalsales from sales s, regions r where
                 s.region_no = r.region_no group by r.region_no';

  taskname :=    DBMS_SQLTUNE.CREATE_TUNING_TASK (
              sql_text    => sqlstmt,
              bind_list   => sql_binds(anydata.ConvertNumber(100)),
              user_name   => 'tfm',
              scope       => 'COMPREHENSIVE',
              time_limit  => 60,
              task_name   => 'totalsales_sql_sta_task',
              description => 'Individual Query - SQL Tuning Advisor Task');
END;
/

BEGIN
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name=>'totalsales_sql_sta_task');
END;
/

SET LONGCHUNKSIZE 1500
SET LINESIZE 250
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'totalsales_sql_sta_task' ) FROM DUAL;

Current and recent SQL activity

SQL tuning set

Hypothetical workload

SQL Access Advisor also provides recommendations for dropping unused indexes. In addition to covering full, refreshable materialized views, SQL Access Advisor can be leveraged to make materialized views fast-refreshable for an optimal implementation. SQL Access Advisor is part of the SQL Tuning Pack licensed option.

OEM 12c Select Database Main Menu Performance SQL SQL Access Advisor

The SQL Access Advisor can be configured to validate and verify the usage of existing access structures or to recommend new access structures, including materialized views, indexes, and partitioning. This is illustrated in Figure 12.11.

Figure 12.11 A representation of running options in SQL Access Advisor in OEM to verify the usage of existing access structures or recommend new access structures

Figure 12.12 shows how to alternatively run the SQL Access Advisor on SQL tuning sets.

Figure 12.12 Running SQL Access Advisor from the SQL Tuning Sets page in OEM

The workload source options can be specified for running the SQL Access Advisor, as illustrated in Figure 12.13. These include Current and Recent Activity, Use an Existing SQL Tuning Set, and Create a Hypothetical Workload from specific schemas and tables.

Figure 12.13 A representation of SQL Access Advisor wizard for workload source runtime options

Specific SQL Access structure recommendations can be specified for running the SQL Access Advisor, as illustrated in Figure 12.14. These include materialized views, indexes, and partitioning. The Advisor Mode can also be specified as Limited or Comprehensive.

Figure 12.14 A representation of SQL Access Advisor wizard for structures runtime options

Figure 12.18 shows the overall end results of running the SQL Access Advisor on a SQL tuning set gathered from current SQL cache activity along with a projected improvement in performance. Figures 12.15, 12.16, 12.17, and 12.18 illustrate that drilling down into the individual recommendations gives you a detailed view for the specific recommendations, the projected potential for improvement of performance, and the exact steps for implementation.

Figure 12.15 A representation of SQL Access Advisor results with potential for improvement

Figure 12.16 A representation of example recommendation in SQL Access Advisor for a partition table

Figure 12.17 Example recommendation in SQL Access Advisor to create an index

Figure 12.18 Example recommendation in SQL Access Advisor to create a materialized view

Listing 12.2 Creating and Executing a SQL Access Advisor Task in SQL*Plus

Click here to view code image

DECLARE
        taskname varchar2(30)         := 'SQL_ACCESS_9940';
        task_desc varchar2(256)     := 'SQL Access Advisor';
        task_or_template varchar2(30) := 'SQLACCESS_EMTASK';
        task_id number         := 0;
        num_found number;
        sts_name varchar2(256)     := 'STS_CUSTOM_9940';
        sts_owner varchar2(30)     := 'SYS';
BEGIN
Create the SQL Access Advisor Task dbms_advisor.create_task(DBMS_ADVISOR.SQLACCESS_ADVISOR,
task_id,taskname,task_desc,task_or_template);

-- Reset the SQL Access Advisor Task
dbms_advisor.reset_task(taskname);

-- Delete Previous STS Workload Task Link
select count(*) into num_found from user_advisor_sqla_wk_map where
task_name = taskname and workload_name = sts_name;
IF num_found > 0 THEN
dbms_advisor.delete_sts_ref(taskname, sts_owner, sts_name);
END IF;

-- Link STS Workload to Task
dbms_advisor.add_sts_ref(taskname,sts_owner, sts_name);

-- Set the STS Workload Parameters
dbms_advisor.set_task_parameter(taskname,'VALID_ACTION_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'VALID_MODULE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'SQL_LIMIT',DBMS_ADVISOR.ADVISOR_UNLIMITED);
dbms_advisor.set_task_parameter(taskname,'VALID_USERNAME_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'VALID_TABLE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'INVALID_TABLE_LIST',
DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'INVALID_ACTION_LIST',
DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'INVALID_USERNAME_LIST',
DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'INVALID_MODULE_LIST',
DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'VALID_SQLSTRING_LIST',
DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'INVALID_SQLSTRING_LIST','"@!"');
/* Set Task Parameters */
dbms_advisor.set_task_parameter(taskname,'ANALYSIS_SCOPE','ALL');
dbms_advisor.set_task_parameter(taskname,'RANKING_MEASURE','PRIORITY,OPTIMIZER_COST');
dbms_advisor.set_task_parameter(taskname,'DEF_PARTITION_TABLESPACE',
DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'TIME_LIMIT',10000);
dbms_advisor.set_task_parameter(taskname,'MODE','COMPREHENSIVE');
dbms_advisor.set_task_parameter(taskname,'STORAGE_CHANGE',DBMS_ADVISOR.ADVISOR_UNLIMITED);
dbms_advisor.set_task_parameter(taskname,'DML_VOLATILITY','TRUE');
dbms_advisor.set_task_parameter(taskname,'WORKLOAD_SCOPE','PARTIAL');
dbms_advisor.set_task_parameter(taskname,'DEF_INDEX_TABLESPACE',
DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'DEF_INDEX_OWNER',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'DEF_MVIEW_TABLESPACE',
DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'DEF_MVIEW_OWNER',DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'DEF_MVLOG_TABLESPACE',
DBMS_ADVISOR.ADVISOR_UNUSED);
dbms_advisor.set_task_parameter(taskname,'CREATION_COST','TRUE');
dbms_advisor.set_task_parameter(taskname,'JOURNALING','4');
dbms_advisor.set_task_parameter(taskname,'DAYS_TO_EXPIRE','30');

-- Execute the SQL Access Advisor Task
dbms_advisor.execute_task(taskname);
END;

Figure 12.19 illustrates how SQL Repair Advisor can be launched from the Support Workbench link from the SQL Advisor homepage in OEM 12c by the following menu hierarchy:

OEM 12c Select Database Main Menu Performance Advisors Home SQL Advisors SQL Repair Advisor

Figure 12.19 A representation of running SQL Repair Advisor from the Advisor Central page in OEM 12c

Figure 12.20 shows how SQL Repair Advisor can be invoked from the Problem Details page from Support Workbench.

Figure 12.20 A representation of running SQL Repair Advisor from the Support Workbench Problem Details page in OEM 12c

SQL Performance Analyzer can prove to be a very effective tool for proactively predicting, forecasting, and avoiding SQL performance issues. SQL Performance Analyzer is part of the Real Application Testing license option.

Figure 12.21 shows the launch link in Advisor Central in OEM 12c. Figure 12.22 shows the various workflow options available for the SQL Performance Analyzer in OEM 12c.

Figure 12.21 A representation of running SQL Performance Analyzer from the Advisor Central page in OEM 12c

Figure 12.22 Workflow options in SQL Performance Analyzer