Performance tuning can be a daunting task in high-activity applications, especially when changes have the potential to further reduce the overall performance of a database environment. Thousands of SQL statements can be generated over a very short amount of time, and working through these during performance tuning operations can be a complicated feat requiring days of investigation and testing. The new SQL tuning set (STS) management capabilities in DBM allow a streamlined experience in the administration of STSs located in your database. Key features include:
- View all SQL tuning sets in your database along with statistics for each
- Create new or delete existing STSs
- Load SQL statements into existing STSs
- Drill down into a SQL tuning set for SQL-level stati
What are SQL tuning sets?
SQL tuning sets provide a convenient medium for large-scale performance tuning in databases. They are a single object located within the database, comprised of SQL statements along with their metadata. Specifically, they contain the following data:
- Set of SQL statements
- SQL execution context – such as schema, application module name and action, bind value, and environment for the SQL compilation
- Basic execution statistics including elapsed time, CPU time, buffer gets, disk reads, rows processed, cursor fetches, number of executions, Optimizer costs, and command type
- Associated execution plans and row source statistics for each SQL statement
The true potential of STSs is conveyed when utilized as inputs for their tuning counterparts – advisors, namely, SQL Tuning Advisor, SQL Access Advisor, and SQL Performance Advisor. Having a single object containing multiple SQL statements allows consistent, large-scale tuning operations that can be exported into other database environments. For example, improve performance without impacting your production database by the following workflow:
- Capture a production workload as a single STS
- Export the workload from production and import into a test environment
- Run performance-tuning operations in the test environment
- Implement the positive outcomes of tuning into your production environment
SQL tuning sets can also be utilized to find the most resource-intensive operations on your database. By filtering and sorting SQL statements by resource consumption, you can triage performance tuning operations to focus on the highest return on tuning investments.
What can I do with STSs in Database Management?
After navigating to the Database Management resource homepage, you will find the SQL tuning set feature in the “Resources” panel. Within the SQL tuning set console, a table displays the SQL tuning sets located in the database which the monitoring user has access to view. Along with the STS name, additional details are provided including the status of the STS operation, owner, number of SQL statements, creation time, and last modified time. Filter the STSs by searching based on name, owner, or description and sort the output based on the attributes of the SQL tuning set itself. A three-dot menu enables launching a “Load SQLs” pop-out workflow or deletion of the STS from the database. Drill down into the SQL tuning set to view the details of the SQL statements comprising the set.
SQL Tuning Set can be generated from various sources, such as Automatic Workload Repository (AWR) or the cursor cache. For example, to quickly and easily store as many statements as possible in the STS, capture from the cursor cache is suitable, but if only the top statements are to be considered, one can limit the capture to AWR. When loading additional SQL statements there are several methods to choose:
- Incremental cursor cache – Allows continuous collection of SQL statements from the cursor cache to populate the STS. Administrators can define capture time, repeat interval, capture options (merge, insert, update), and capture mode (replace or accumulate statistics).
- Current cursor cache – Allow collection of current shared SQL area. Options for capture include result percentage and result limit based on sort, load option (insert, update, merge), and sort results by query statistics (elapsed time, CPU time, buffer gets, Optimizer cost, reads, or writes).
- AWR snapshots – Allows collection from workload repository based on starting and ending snapshots. Capabilities for capture are similar to the current cursor cache option.
All methods include a filter option to limit statements based on SQL attributes, such as SELECT statements only, action, user, module, etc.
Drilling down into a specific SQL tuning set is possible to view SQL statements comprising the STS. A filter allows for quickly narrowing the scope of the SQLs found in the STS based upon the predicate filter (i.e. sql_text like ‘SELECT%’), and sorting will quickly bring the most important statements to the top based on run statistics. Delete SQL statements from the STS based on the filtered data to prune any further actions utilizing the STS, such as running the SQL tuning advisor with the STS as input. Load additional SQL statements into the STS with the “Load SQLs” workflow previously described.
What environments have STS support in OCI DBM?
- Available in all OCI commercial regions for databases version 11.2.0.4 and later
- ExaDB-D
- Base DB
- External database (on-premise)
- Coming soon to Autonomous databases
- From database versions 12.2 and later it is supported at both the CDB and PDB levels
In summary
The new administration capabilities of SQL tuning sets in OCI’s Database Management service further minimize the effort required to effectively tune SQL statements in high-activity environments. Capture problematic SQL statements in an STS to quickly tune utilizing the SQL Tuning Advisor or transport to another test database for worry-free tuning tasks
To learn more about DBM capabilities, visit:
Get started with a hands-on workshop