Databases Reference
In-Depth Information
•
You may want to determine if the advice the tool recommends is worth the cost of an extra
license.
You may eventually find yourself in a shop that uses this tool, so you should know how to
operate and manage the recommendations.
In the examples in this chapter, we focus on showing you how to use features via SQL and built-in
PL/SQL packages. While we do show some screenshots from Enterprise Manager, we don't focus on the
graphical tool usage. You should be able to use SQL and PL/SQL regardless of whether Enterprise
Manager is installed. Furthermore, the manual approach allows you to understand each piece of the
process and will help you to diagnose issues when problems arise.
The first section of this chapter deals with the Automatic SQL Tuning feature. You'll be shown how
to determine if and when the automated job is running and how to modify its characteristics. The
middle section of this chapter focuses on how to create and manage SQL tuning sets. Lastly, you'll learn
how to manually run the SQL Tuning Advisor to generate indexing recommendations for SQL
statements.
•
Automatic SQL Tuning Job
When you create a database in Oracle Database 11
g
or higher, there is an automatic SQL tuning job that
routinely runs the SQL Tuning Advisor for you and generates advice on how to improve performance.
This advice can be in the form of creating indexes, restructuring SQL, creating a SQL profile, and so
forth. This architecture is depicted in Figure 9-1.
Figure 9-1. Architecture of the automatic SQL tuning job
