This section provides information on tuning, its functionality, and is structured so a user can follow the information provided to fully tune their enterprise in terms of more efficient query paths at the SQL statement level of individual data sources.
Tuner has three parts:
- Query rewrites and quick fixes
- Alternative execution plans generated via optimizer directives
- Analysis of Query showing:
- Indexes used, not used, missing (suggested to create)
- Graphic display of query
The SQL tuner takes a query and adds database optimizer directives to change the execution path of the query. A list of all the unique execution paths is generated with all duplicates eliminated from the list. The final list of alternative paths can be executed. Any path that takes more than 150% of the base case will be canceled because we are only interested on paths that could be faster than the base case so no need to waste time and resources continuing to run cases that are slower than the original. After the cases have been executed they can be sorted in order of elapsed time. If a better path is found then those optimizer directives can be included in the original query to achieve optimal response time.
You can save the entire content of a tuning job for later analysis or for sharing with other users. This section contains the following topics:
- Understanding the tuner interface
- Tuning SQL statements
- Using platform-specific features
- Additional tuning commands
- Configuring tuning
- Examples of transformations and SQL query rewrites
- DBMS hints