Although a DBA could manually tune poorly performing SQL statements, the large number of distinct SQL statements in any real-life production database and the expertise it requires to write good SQL code, design fast data access, and work with explain plans and trace output makes manual tuning quite a daunting task, besides being extremely time consuming.
Oracle Database 10g gave you two excellent performance tuning tools, the SQL Tuning Advisor and the Automatic Database Diagnostic Monitor (ADDM),to facil itate tuning SQL queries.As candidates for tuning,ADDM picks high-load SQL statements that provide the biggest bang for the buck in the form of improved performance.You must look at the ADDM reports and then run the SQL Tuning Advisor to receive recommendations for improving performance.The SQL Tuning Advisor makes recommendations to improve problem SQL queries,due either to a poorly designed query or to a query that wasn’t making use of optimal statistics.The SQL Tuning Advisor’s recommendations might include gathering statistics, adding indexes, creating a SQL profile, or modi fying the SQL statements.
Even when you got recommendations from the SQL Tuning Advisor that saved you the rigors of performing a manual SQL tuning, in Oracle Database 10g you were still in charge of evaluating and then actually implementing therecommen dations made by the SQL Tuning Advisor.In Oracle Database 11g, Oracle goes much further in automating the entire SQL tuning process, as we explain in the next section.
Automation of SQL Tuning in Oracle Database 11g
In Oracle Data base 11g,the new Automatic SQL Tuning Advisor task runs nightly by default. As is the case with the other two automated management tasks—the Optimizer Statistics Gathering and Automatic Segment Advisor tasks—the Automatic SQL Tuning Advisor task is enabled by default when you create a database or migrate an Oracle 10g database to the 11g release.The new automatic task, named the Automatic SQL Tuning Advisor task(sys_ auto_ sql_ tuning_ task), runs automatically every night, as part of the automatic maintenance task framework, looking for ways to speed up the perfor mance of high-load SQL statements by improving their execution plans.
The Automatic SQL Tuning Advisor task does the following things:
In the following sections,we’ll cover how the new automatic SQL tuning feature works in Oracle Database 11g.
Identifying Candidates for SQL Tuning
The Automatic SQL Tuning Advisor job runs every night during the maintenance window of the automated maintenance tasks feature.You don’t need to provide any SQL tuning sets for the job, because the tuning task selects the workload based on its analysis of database performance.The Automatic SQL Tuning Advisor task depends on the AWR for the selection of candidates for further analysis. Automatic SQL tuning pulls SQL from the AWR that was at the top of the list of SQL queries that performed poorly during four different time periods, which it calls buckets. These four buckets are based on weekly, daily, hourly, or top SQL in a single SQL execution based on response time. Automatic SQL tuning combines the top queries from the four buckets into one by assigning weights.
The database considers the following types of queries ineligible for automatic SQL tuning, although you can still tune them (except ad hoc SQL queries) manually using the SQL Tuning Advisor:
Running the Automatic SQL Tuning Advisor
The first step in automatic SQL tuning is to find the bad SQL statements to tune.Once the Automatic SQL Tuning Advisor task generates a list of candidate SQL statements,the advisor orders the candidate SQL list in the order of importance.The SQL Tuning Advisor then tunes each of the candidate SQL statements in the order of importance and recommends SQL profiles to improve theperformance of the SQL statements.
Implementing the Recommendations
If the SQL Tuning Advisor finds an existing SQL profile,it checks the optimizer statistics to ensure they do exist and that they are up-to-date.If not,it lets the gather_stats_task job know about it so it can collect the necessary statistics.
The automatic SQL tuning process may make other recommendations besides the adoption of a SQL profile,such as creating new indexes,refreshing statistics,or even restructuring the SQL state ments.However,you must review and manually implement these other recommendationsyourself,as in Oracle Database 10g.The data base implements only the SQL profiles automatically.
An interesting aspect here is that the database doesn’t automaticallyimplement all SQL profiles that the SQL Tuning Advisor generates during the tuning process.It implements only those SQL profile recommendations that will improve performance by at least threefold.To be more precise, here are the requirements a SQL profile must satisfy before the database automaticallyimplements it:
The reason for the previous set of requirements is to ensure that anycontention caused by the new SQL profile doesn’t make performance actually worse than running the query without the SQL profile.The automatic SQL tuning process tests SQL statements with and without the SQL profile before making the recommendation to accept the new profile.
The automatic SQL tuning process doesn’t modify the offending SQLstatement in any way,and you can reverse its actions any time by removing the new profiles that it implemented.
Managing Automatic SQL Tuning
Managing the automatic SQL tuning feature involves configuring the automatic SQL tuning feature on and off, and second, managing the scheduling of the task during the maintenance window.You also need to configure theparameters of the Automatic SQL Tuning Advisor task.These task parameters include actions such as setting time limits on thetuning tasks, turning SQL profile implementation on and off, and even disabling the testing of new SQL profiles, if the time to implement the changes is a major concern.
Like most components of the advisor framework,you can tune automatic SQL tuning through Enterprise Manager Database Control (or Grid Control) orthrough Oracle-supplied PL/SQL packages.Next we’ll cover how you canmanage automatic SQL tuning through Enterprise Manager Database Control.
Using Database Control
The easiest way to manage most components of the automatic SQLmanagement framework is by using Database Control.Follow these steps to manage automatic SQL tasks through Database Control:
The Automatic SQL Tuning Result Details page
Using the DBMS_AUTO_TASK_ADMIN Package
Use the DBMS_AUTO_TASK_ADMIN package to enable and disable theAutomatic SQL Tuning Advisor task and to control various aspects of executing the SQL Tuning Advisor task. Here’s a summary of the main procedures youcan use to control the execution of the Automatic SQL Tuning Advisor task:
SQL Access Advisor Enhancements
Oracle introduced the SQL Access Advisor in the Oracle Database 10g release to help you determine efficient access structures such as indexes, materialized views, and materialized view logs.The SQL Access Advisor either takes the actual database workload (SQL cache) or derives a hypothetical workload or a SQL tuning set as the basis for making recommendations for better execution of SQL statements.You can run the advisor in limited mode or in comprehensive mode, as was the case with this advisor in Oracle Database 10g.
In Oracle Database 10g,the SQL Access Advisor provided recommendations regarding indexes, materialized views, and materialized view logs.New in Oracle Database 11g, the advisor will also recommend partitions fortables, indexes, and materialized views.The partition enhancements are part of the Partition Advisor, which is inte grated into the SQL Access Advisor.
The SQL Access Advisor now shows you the expected gains of implementing its recommendations.In addition, the advisor also reports intermediate results by breaking up the workload into parts.These intermediate results are called publish points.
In addition to the normal recommendations to create or drop indexes,materialized views,and materialized view logs,you’ll now also get recommendations to partition existing tables and indexes.The advisor may also recommend adding a partitioned index on a table or a materialized view or adding a new partitioned materialized view.In some case, the advisor may recommend replacing the current table partitioning with a more efficient partitioning scheme.
The SQL Access Advisor may recommend range, interval, list, hash, range-hash, and range-list partitioning schemes for base tables and indexes.It mayrecomm end local, range, and hash partitioning schemas for partitioningindexes.
To implement its partition recommendations,the SQL Access Advisor generates an implementation script,which may invoke an external package such as the DBMS_REDEFINITION package, and partitions the base table online.If the table that the advisor is going to partition has any bitmap indexes defined on it, the DBMS_REDEFINITION package may fail.In this case, you must drop the bitmap indexes before the advisor implements its partitioning recommendations and create the indexes after the table partitioning is completed
Here are some key things to note about the partition recommendations:
Accessing Intermediate Results
As you are probably aware, the SQL Access Advisor takes a considerableamount of time to execute, especially when you run it in comprehensivemode.In Oracle Database 11g, you can access the intermediate results while the advisor is still executing.This gives you the opportunity to cut short an advisor run if the intermediate results aren’t appealing to you. Similarly, if the intermediate results show evidence that the recommendations will lead to a significant benefit, you can cut short the advisor run by interrupting itmidstream.
The SQL Access Advisor bases its partitioning recommendations on its analysis of a significant proportion of the workload.Thus, if you interrupt an advisor task early in the game, the advisor may not include any base table partitioning recommendations in the intermediate results.If you decide to interrupt, wait for a substantial portion of the workload to complete so you can get the partitioning recommendations as well.
To accept the intermediate results, you must formally interrupt the advisor task.The advisor will then stop executing the task and mark it as interrupted. Following this, you can either ask the advisor to complete the recommendation process or choose to accept the recommendations and generate scripts for implementing them.
New DBMS_ADVISOR Procedures
Two new procedures in the DBMS_ADVISOR package have a bearing on the SQL Access Advisor.These are the add_sts_ref and copy_sqlwkld_to_sts procedures.
You can use the add_sts_ref procedure to link a SQL Access Advisor task to the SQL tuning set.The advisor task can use this link to access the SQL tuning set for analysis purposes. Here’s the structure of the add_sts_ref procedure:
Here’s an example showing how to use the add_sts_ref procedure to link a task (task1) toa SQL tuning set (sts_1):SQL> exec dbms_advisor.add_sts_ref ("task1','hr', 'sts_1');
Once you create a link between the advisor task and the SQL Tuning Advisor task,the STS can’t be altered or deleted, thus providing a stable view of the workload for the advisor.
The copy_sqlwkld_to_sts procedure transfers the contents of a SQL workload object to an STS.The copy_sqlwkld_to_sts procedure has the following attributes:
The workload_name attribute refers to the source SQL workload,and thests_name attribute points to the target STS into which you’ll be importing the SQL Workload object.
The import_mode attribute can take the value append, new, or replace and determines the handling of the data in the target STS upon the import of the SQL Workload object.Here’s an example showing how to use thecopy_sqlwkload_to_sts procedure:SQL>exec dbms_advisor.copy_sqlwkld_to_sts ('workload_1', 'sts_1','new');
Oracle will create a new STS if the STS you specify doesn’t exist.
Oracle 11g Related Interview Questions
|Oracle 10g Interview Questions||Oracle 9i Interview Questions|
|Oracle 8i Interview Questions||Informatica Interview Questions|
|PL/SQL Interview Questions||Oracle 11g Interview Questions|
|SQL Interview Questions||Oracle apps Interview Questions|
|Sybase Interview Questions||Oracle Apps ERP Interview Questions|
|Oracle 7.3 Interview Questions||Oracle Access Manager Interview Questions|
|Oracle Application Framework Interview Questions||Oracle Apps DBA Interview Questions|
Oracle 11g Related Practice Tests
|Oracle 10g Practice Tests||Oracle 9i Practice Tests|
|Oracle 8i Practice Tests||Informatica Practice Tests|
|PL/SQL Practice Tests||Oracle 11g Practice Tests|
|SQL Practice Tests||Oracle apps Practice Tests|
|Sybase Practice Tests||Oracle Apps ERP Practice Tests|
|Oracle 7.3 Practice Tests|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.