About Tuning SQL Statements

A SQL statement specifies the data you want Oracle Database to retrieve.

Execution plan(which the optimizer is responsible for generating): The access method for physically executing a SQL statement

The optimizer determines whether it is more efficient to read all data in the table, called a full table scan, or use an index. It compares the cost of all possible approaches and chooses the approach with the least cost.

The query optimizer can also help you tune SQL statements. By using SQL Tuning Advisor and SQL Access Advisor, you can run the query optimizer in advisory mode to examine a SQL statement or set of statements and determine how to improve their efficiency. SQL Tuning Advisor and SQL Access Advisor can make various recommendations, such as the following:

• Creating SQL profiles

• Restructuring SQL statements

• Creating additional indexes or materialized views

• Refreshing optimizer statistics

12.1 Tuning SQL Statements Using SQL Tuning Advisor

You can use SQL Tuning Advisor to tune one or more SQL statements. When tuning multiple statements, SQL Tuning Advisor does not recognize interdependencies between the SQL statements. Instead, SQL Tuning Advisor provides a convenient way to obtain tuning advice for a large number of SQL statements.

Oracle Database can generate SQL tuning reports automatically. Automatic SQL Tuning runs during system maintenance windows as an automated maintenance task, searching for ways to improve the execution plans of high-load SQL statements. A maintenance window is a contiguous time interval during which automated maintenance tasks are run.

12.1.1 Tuning SQL Manually Using SQL Tuning Advisor

Automatic Database Diagnostic Monitor (ADDM) automatically identifies high-load SQL statements. If ADDM identifies such statements, then click Schedule/Run SQL Tuning Advisor on the Recommendation Detail page to run SQL Tuning Advisor.

To tune SQL statements manually using SQL Tuning Advisor:

1. Access the Database Home page. --->
2. Performance menu,select Advisors Home --->

3. In the Advisors section, click SQL Advisors. The SQL Advisors page appears. -->

4. In the SQL Tuning Advisor section, click SQL Tuning Advisor. --->

5. In the Name field, enter a name for the SQL tuning task. ---->

 12.1.2 Viewing Automatic SQL Tuning Results

By analyzing data in the Automatic Workload Repository (AWR), the database can identify routine maintenance tasks. The automated maintenance tasks infrastructure (known as AutoTask) schedules these tasks to run in maintenance windows.

Maintenance windows are Oracle Scheduler time intervals that belong to the window group named MAINTENANCE_WINDOW_GROUP. By default, one window is scheduled for each day of the week. You can customize attributes of these maintenance windows, including start and end times, frequency, and days of the week.

By default, AutoTask runs the following automated maintenance tasks in all maintenance windows:

• Optimizer Statistics Collection

• Segment Advisor

• SQL Tuning Advisor

To view automatic SQL tuning results:

Access the Database Home page.-->From the Performance page, select Advisors Home. --->

Under Advisors, click SQL Advisors. --->Under SQL Tuning Advisor, click Automatic SQL Tuning Results.

---->In the Time Period list, select All and then click Go. 

---->Optionally, in the Task Status section, click Configure to change the attributes of the Automatic SQL Tuning task.

--->  In the Task Activity Summary section, leave All selected for the Time Period and then click View Report.

---->  Under Recommendations, select a SQL statement and then click View Recommendations.

The Recommendations for SQL ID page appears.

12.2 Managing SQL Tuning Sets

A SQL tuning set is a database object that includes one or more SQL statements and their execution statistics and context. You can use the set as an input for advisors such as SQL Tuning Advisor, SQL Access Advisor, and SQL Performance Analyzer. You can load SQL statements into a SQL tuning set from different SQL sources, such as AWR, the cursor cache, or high-load SQL statements that you identify.

A SQL tuning set includes the following:

• A set of SQL statements

• Associated execution context such as:

  • User schema

  • Application module name and action

  • List of bind values

  • Cursor compilation environment

• Associated basic execution statistics such as:

  • Elapsed time and CPU time

  • Buffer gets

  • Disk reads

  • Rows processed

  • Cursor fetches

  • Number of executions and number of complete executions

  • Optimizer cost

  • Command type

• Associated execution plans and row source statistics for each SQL statement (optional)

12.2.1 Creating a SQL Tuning Set

To create a SQL tuning set:

1. Specify the initial options for the SQL tuning set
2. Select the load method to use for collecting and loading SQL statements into the SQL tuning set,
3. Specify the filter options for the SQL tuning set
4. Schedule and submit a job to collect the SQL statements and load them into the SQL tuning set

12.2.1.1 Creating a SQL Tuning Set: Options

• To specify options for creating a SQL tuning set:
• Access the Database Home page.
• From the Performance menu, select SQL, and then SQL Tuning Sets.
• Click Create.
• Enter the following details:

1. In SQL Tuning Set Name, enter a name for the SQL tuning set.
2. In Owner, enter the owner of the SQL tuning set.
3. In Description, enter a description of the SQL tuning set.

• Optionally, to create an empty SQL tuning set and add SQL statements to it at a later time, complete the following steps: