For application developers and database administrators, application tuning is an area of essential importance, and a considerable amount of time is invested in developing this critical function.
A poorly adaptable commercial application can potentially affect not only some users but also the entire business operation. For this reason, companies invest a significant amount of resources to ensure the seamless execution of essential applications for their businesses.
For this reason, Oracle Tuning Pack offers an extremely cost-effective and easy-to-use solution that automates the entire application tuning process. SQL performance improvements are achieved through SQL Advisors, which seamlessly integrate with Enterprise Manager Database Control and Grid Control, together providing a complete solution for automating the complex and lengthy task of application tuning.
How does it work?
SQL Tuning Advisor
Manual SQL tuning is a process that presents many challenges. It requires expertise in many areas, consumes a great deal of time, and demands an intimate knowledge of schema structures and the data usage model for the application.
All of these factors make manual SQL tuning a challenging task that requires many resources and is very costly for the company. SQL Tuning Advisor is Oracle's answer to the obstacles and challenges of manual SQL tuning.
It automates the SQL tuning process by fully exploring all possible ways to optimize an SQL statement. The analysis and tuning are carried out through the highly enhanced query optimizer of the database engine.
SQL Tuning Advisor performs four types of analyses:
- Statistical Analysis: The query optimizer must update object statistics to generate good execution plans. In this analysis, Oracle identifies objects with outdated or missing statistics and suggests recommendations to remediate the problem.
- SQL Profiling: This feature, introduced in Oracle Database 10g, revolutionizes the approach to SQL tuning. Traditional SQL tuning involves manual manipulation of application code using optimizer hints. SQL Profiling eliminates the need for this manual process and tunes SQL statements without requiring any changes to application code. This capability to adapt SQL without changing application code also helps resolve the issue of tuning packaged applications. Users of packaged applications no longer need to log a bug with the application vendor and wait several weeks for a fixed code to tune the statement. With SQL Profiling, the tuning process is automatic and immediate.
- Access Path Analysis: Indexes can greatly enhance the performance of an SQL statement by reducing the need for a full table scan. Effective indexing is, therefore, a common tuning technique. In this analysis, new indexes that could significantly improve query performance are identified and recommended.
- SQL Structure Analysis: Problems with the structure of SQL statements can lead to poor performance. These could be syntactical, semantic, or design issues concerning the statement. In this analysis, relevant suggestions are made to restructure the selected SQL statements for better performance.
The results of this analysis are presented in the form of recommendations, along with logic for each recommendation and its expected performance benefit. Recommendations relate to object statistics collection, creating new indexes, restructuring SQL statements, or creating an SQL Profile. A user can choose to accept the recommendation to complete the tuning of SQL statements.
SQL Tuning Advisor
Secondly, Automatic SQL Tuning Advisor also runs automatically. In this mode, the advisor runs automatically during system maintenance windows as a maintenance task. During each execution, the advisor selects high-load SQL queries in the system and generates recommendations on how to tune them.
Automatic SQL Tuning Advisor can be configured to automatically implement SQL Profile recommendations. If you allow automatic implementation, the advisor will create SQL Profiles only for those SQL statements where performance improvements could at least triple.
Other types of recommendations, such as creating new indexes, updating optimizer statistics, or restructuring SQL, can only be implemented manually. DML statements are not considered for tuning performed by Automatic SQL Tuning Advisor.
You can view a summary of the automatic SQL tuning results over a specific period (such as the last seven days) and see a detailed report of the recommendations made for all processed statements.
Implementations can be selectively executed through a manual process. You can also view the recommendations that were automatically implemented.
SQL Tuning Sets
Thirdly, SQL Advisors are designed to accept input from various SQL sources, such as the Automatic Database Diagnostic Monitor (ADDM), Automatic Workload Repository (AWR), cursor cache, and user-defined custom SQL. This allows for the tuning of almost any SQL statements that would be of interest to the user.
SQL statements from these incoming sources are typically loaded first into a new object called a SQL Tuning Set (STS), which is then presented to the advisor as input. A SQL Tuning Set is a new database object used to capture SQL workload information.
This includes:
- One or more SQL statements
- Related execution context, such as user schema, required value lists, etc.
- Related basic execution statistics, such as elapsed time, CPU time, etc.
SQL Access Advisor
Finally, the design of the database schema can have a significant impact on the overall performance of applications. SQL Access Advisor provides comprehensive guidance on how to optimize schema design to maximize application performance. Together, SQL Access and SQL Tuning Advisor offer a complete solution for database application tuning.
These two advisors automate all currently practiced manual tuning techniques and form the cornerstone of Oracle's automatic SQL tuning solution. SQL Access Advisor accepts input from all possible sources of interest, such as cursor cache, Automatic Workload Repository (AWR), any user-defined workload, and even generates a hypothetical workload if a schema contains key primary/secondary dimensions or relationships.
It thoroughly analyzes the entire workload and offers recommendations to create new partitions or indexes; if necessary, it removes any unused indexes, creates new materialized views, and maintains materialized view logs.
Determining the optimal indexing and partitioning strategy for a particular workload is a complicated process that requires expertise and time. SQL Access Advisor takes into account the cost of inserting/updating/deleting operations, in addition to queries on the workload, and makes appropriate recommendations, along with a measurable estimate of the expected performance gain, as well as the scripts needed to implement the recommendations.
In summary…
Oracle Tuning Pack also provides the ability to reorganize objects. Effectively managing the space usage of your tablespaces by eliminating wasted space is not only a good space management practice but also improves performance by reducing unnecessary disk I/Os. Reorganization is used to:
- Rebuild fragmented indexes and tables
- Reassign objects to other tablespaces
- Recreate objects with optimal storage attributes Oracle Tuning Pack 11g offers a wizard that can perform reorganization at the tablespace and schema levels and provides the option for online and offline reorganization.
- The wizard also offers an impact analysis report, as well as a review script containing the exact operations that will be performed. This helps users to understand precisely the implications of the operations before implementing them.
EN 
ES