SQL Tuning Sets

Introducing SQL Tuning Sets
A SQL Tuning Set is a database object that groups and stores one or more SQL statements. In addition to the text of the statement, a SQL Tuning Set also stores the following items to facilitate the SQL monitoring and tuning process:

• Execution context - You can loosely describe a SQL statement's execution context as the environment that the SQL statement executes in. It consists of the user's schema, application program that executes it, a listing of bind variables and the cursor compliation enviroment.
• Performance statistics such as CPU and elapsed times, buffer gets, disk reads, rows processed, cursor fetches, number of executions, optimizer cost and command type.
• Execution plans and row source statistics.

Later in this blog, I'll show you how to create SQL Tuning Sets using a couple of different workload captures from Enterprise Manager as input. The source of the SQL statements can be the Automatic Workload Repository (AWR), the database's SQL cursor cache (from the shared pool) or a user-defined set of SQL statements contained in a workload table.

SQL Tuning Set and the SQL Advisors
SQL Tuning Sets can be used as input to the SQL Access Advisor and the SQL Tuning Advisor (topic of next blog). That's the key benefit of using SQL Tuning Sets. You create the tuning sets from a selected workload and store them as a named object. You can then review and tune them at any time. In addition, 10G allows you to rank the statements according to several different criteria.

You'll see in one of the examples below that I'm capturing statements that are currently running in the database. Capturing the current contents of the SQL cursor area is of great benefit to administrators. When notified of a performance problem, DBAs are now able to access the contents of the SQL cursor area to capture the workload currently running in the database.

The SQL cursor cache is dynamic and fluid by nature. Since this memory construct is finite in size, old statements must be flushed out at times to make room for new statements coming in for parsing and execution. So what you think should be in there, may not be. Being able to capture and store the contents of the SQL cursor area as well as using historical information from snapshots allows us to tune statements that executed in the past. Now, we finally have an answer to "Hey, my program ran long two days ago... Can you fix it?"

Transporting SQL Tuning Sets
I also like the new 10G R2 feature that allows DBAs to transfer SQL Tuning Sets from one database to another. Administrators can use the DBMS_SQLTUNE package to transfer poorly perfoming SQL statements to test systems where they can be safely analyzed and tuned.

This will prevent DBAs from having to perform extended diagnostics on SQL statements in their production environments. In addition, once a statement is tuned, it must be re-executed to determine if the tuning changes accomplished their intended results. If the results don't match expectations, another round of tuning and monitoring is warranted. Transporting the statements in bulk to another database facilitates the monitoring and testing of multiple SQL statements.

Editing SQL Tuning Sets
SQL Tuning Sets can be modified using DBMS_SQLTUNE. The statement below removes all statements that have executed less than 10 times. Check the Oracle documentation for a complete listing of the criteria that can be used to modify the contents of SQL Tuning Sets.

BEGIN
DBMS_SQLTUNE.DELETE_SQLSET(
sqlset_name => 'sqlcursor_dump_072905_0700',
basic_filter => 'executions < 10');
END;

Demo
Let's continue our discussion with a quick demo of how to create SQL Tuning Sets using 10G Grid Control R1.

One way (out of many) that we can access the SQL Tuning Set home page is from the target database's administration home page. You can also access the SQL Tuning Set home page from the Top SQL, SQL Tuning Advisor, SQL Access Advisor and ADDM tools.

Clicking on the link I just described takes me to the SQL Tuning Set home page. The home page allows us to create and administer stored SQL Tuning Sets. We can search for existing tuning sets by name or create a tuning set by choosing from one of the following input options: spot SQL, period SQL, snapshots and preserved snapshot sets.

I clicked on the drop down menu selection titled "spot SQL" and clicked create. 10G Grid Control now displays the Top SQL Spot SQL page. The Top SQL page can also be displayed by navigating to it from the target database's performance home page. The Spot SQL page provides a slider that allows me to choose from 5 minute windows. After dragging the window to the desired 5 minute time slice, 10G Grid Control displays the SQL for that time period on the lower left side of the panel. The tool allows me to drill down to view the text of the statement and also displays the statement's CPU and Wait times.

I selected two SQL statements to be used as input to the tuning set by clicking on the check box (far lower left side of screen). I then clicked the button titled "Create Tuning Set" to begin the creation process. 10G Grid Control displays the Create SQL Tuning Set page. The reason why the statements don't match in number (2 selected vs 3 shown) and SQL IDs is because I went back and chose a timeslice that had statements that consumed more resources than my first selection.We can accept the name that 10G Grid Control recommends or we can choose our own. In addition, we can add a description to help us better identify the grouped SQL statements.

Every time 10G Grid Control displays a SQL statement's SQL ID, it shows it as a link that allows us to drill down to the SQL statement's details. Clicking on the SQL ID link sends us to the SQL statement details page. This page shows the text of the SQL statement and its associated execution plan. 10G Grid Control provides a link to tune the statement immediately using the SQL Tuning Advisor and tabs that display in-depth performance statistics and historical tuning and execution information.

I clicked the back button to return to the Create SQL Tuning Set page and clicked OK to create the SQL Tuning Set. 10G Grid Control returns me to the SQL Tuning Set home page. Now that we have stored a SQL Tuning Set in the repository, 10G Grid Control provides navigation buttons to allow me to view or delete SQL Tuning Sets as well as activate the advisors.

Creating a SQL Tuning Set using Period SQL is pretty much the same as its Spot SQL counterpart. From the SQL Tuning Set home page, I clicked on the drop down menu selection titled "period SQL" and clicked create. 10G Grid Control displayed the Top SQL Period SQL Page. I performed virtually the same steps as I did when I was using the Top SQL Spot SQL Page. I used the slider to select the time slice, reviewed the SQL statements, selected the ones I wanted by clicking on the check box (far lower left on screenshot) and clicked on the button titled "Create Tuning Set" to begin the creation process.

Let's begin to conclude our demo by creating a SQL Tuning Set using an AWR snapshot as input. To learn more about AWR snapshots, please review my blog titled "Advisory Framework - 10G Common Manageability Infrastructure". I navigate to this page by clicking on on the link provided on the target database's performance home page. The link is titled "Snapshots" and is displayed on the lower right hand side of the page.

I clicked on the Snapshot link and 10G Grid Control displays the Snapshot Actions home page. I activated the Actions drop down selection menu. The menu allows me to select from several different actions. I'll discuss all of these selections in-depth in my upcoming blog on 10G Grid Control Snapshot Administration. In this case, I chose the "Create SQL Tuning Set" option to create a new SQL Tuning Set. The page also allows me to specify the starting time period. After I select the beginning time period, 10G Grid Control displays the specify ending time period page. I then click OK to create the SQL Tuning Set. Very simple.

Now that I have my SQL Tuning sets created I can use them as input to the SQL Advisors. In addition, I can navigate to the SQL Tuning Set home page to evaluate individual SQL statements. This is accomplished by first selecting a SQL Tuning Set, clicking on the statement's SQL ID drilldown link which will takes me to the SQL statement details page.

As stated earlier in this blog, the SQL details page allows me view the text of the SQL statement and its associated execution plan. It also provides links to to tune the statement immediately using the SQL Tuning Advisor and displays tabs that allow me to navigate to screens displaying in-depth performance statistics and historical tuning and execution information.

This is how we tune the majority of statements here. Using SQL Tuning Sets doesn't automatically force you into using the 10G Grid Control SQL advisors. We often copy statements from the SQL details page and paste them into text editors. We then rewrite the SQL or add hints to improve performance. Remember that the SQL Tuning Advisor, which will be covered in my next blog, will automatically create alternative access paths for you. Some times they work better, some times they don't. Manually specifying hints allows me more granular ontrol over the access paths. But that's better covered in my next blog.

Next Up
How to use the 10G Grid Control SQL Tuning Advisor.