How do you use Oracle’s SQL Tuning Advisor and SQL Access Advisor to optimize complex SQL statements and improve overall database performance?

Oracle has several tools to tune SQL statements and improve overall database performance. SQL Tuning Advisor and SQL Access Advisor are two such tools offered by Oracle. These tools use different techniques to diagnose and optimize SQL statements. The SQL Tuning Advisor is a tool that identifies SQL statements that are consuming excessive database resources, such as high CPU, long execution time, excessive I/O or contention. The advisor then recommends changes to the SQL statement or underlying database structure to improve performance. SQL Access Advisor is a tool that recommends indexes and partitioning strategies for tables accessed by SQL statements. In the following sections, we discuss these tools in more detail and provide examples of how to use them.

# SQL Tuning Advisor:

The SQL Tuning Advisor is an automated tool that assists in the tuning of SQL statements by identifying performance problems and providing recommendations on how to tune the SQL statement. It analyzes the SQL statement and builds a description of the statement, including all the objects it references, the predicates and joins, and the execution plan. The advisor then uses this information to recommend changes to the SQL statement, such as adding or removing indexes, modifying the SQL statement, or tuning the database structure.

To use the SQL Tuning Advisor, the user submits a SQL statement for analysis, and the tool generates a report containing recommendations for improving the statement’s performance. The advisor uses several methods to tune the SQL statement, including the following:

## 1. Cost-based analysis:

The cost-based analysis is used to optimize the SQL statement by choosing the most efficient execution plan. The advisor evaluates the execution plans generated by the optimizer, identifies the most expensive operations, and suggests alternative plans to improve performance.

## 2. Access path analysis:

The advisor analyzes the access paths used by the optimizer to fetch data from the tables. It identifies the access paths that are inefficient and makes recommendations to choose the most efficient access paths.

## 3. Behavior analysis:

The advisor analyzes the behavior of the SQL statement, including the number of rows returned, the number of executions, the data types used, and the SQL features used. It then determines whether the SQL statement is behaving correctly and suggests changes to improve performance.

Here is an example of how to use the SQL Tuning Advisor:

-- Create a SQL Tuning Task for a specific SQL statement
DECLARE
  l_sql_tune_task_id   VARCHAR2(100);
BEGIN
  l_sql_tune_task_id :=
      DBMS_SQLTUNE.CREATE_TUNING_TASK (
          sql_text => 'SELECT d.department_name, l.city
                        FROM departments d, locations l
                        WHERE d.location_id = l.location_id
                        AND d.department_name LIKE ''%SALES%''',
          bind_list => '',
          user_name => 'SCOTT',
          scope     => DBMS_SQLTUNE.SCOPE_COMPUTE_AND_ADVICE,
          time_limit=> 1800,
          task_name =>'tune_sales_query'
          );

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

-- Display the results of the SQL Tuning Advisor
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tune_sales_query') AS recommendations
FROM dual;

# SQL Access Advisor:

The SQL Access Advisor is a tool that recommends indexes and partitioning strategies for tables accessed by SQL statements. It analyzes the SQL workload and the database schema to determine the tables and indexes that require tuning. The advisor provides recommendations on index creation, index modification or removal, partitioning, and subpartitioning.

To use the SQL Access Advisor, the user submits a SQL workload to the tool, and the tool generates a recommendation report for index creation or modification. The SQL workload contains SQL statements and their corresponding execution statistics, including the number of executions, the number of rows returned, and the execution time. The advisor uses this information to recommend the appropriate indexes.

Here is an example of how to use the SQL Access Advisor:

-- Create a task to analyze a SQL workload
DECLARE
   task_name VARCHAR2(30);
BEGIN

   task_name := dbms_advisor.create_task (
                  advisor_name => 'SQLAccessAdvisor',
                  task_name    => 'tune_sales_workload',
                  description  => 'Recommend indexes for the sales workload');

   dbms_advisor.create_object (
      task_name     => task_name,
      object_type   => 'SQL',
      object_name   => 'SELECT d.department_name, l.city
                        FROM departments d, locations l
                        WHERE d.location_id = l.location_id
                        AND d.department_name LIKE ''%SALES%''',
      attr1         => DBMS_ADVISOR.SQL_WORKLOAD,
      attr2         => 'SALES_WORKLOAD',
      attr3         => NULL);

   dbms_advisor.execute_task(task_name);
END;
/

-- View the recommendations
SELECT dbms_advisor.get_task_report('tune_sales_workload') AS recommendations
FROM dual;

In summary, SQL Tuning Advisor and SQL Access Advisor are two powerful tools in Oracle that provide recommendations for SQL statement tuning and index creation. These tools can significantly improve the performance of complex SQL statements and databases.