Get help on SQL tuning from the ultimate authority: the Oracle Database itself! Make a query behave using SQL Profiles and learn how to use ADDM to solve common performance problems quickly and easily.
It has been a quiet day so far: no major problems in the database, no fires to put out. You are almost relaxed; it's a perfect day for catching up on important to-do tasks such as tuning RMAN tuning parameters or multiple block sizes.Suddenly, a developer appears at your cubicle. His SQL query is taking a long time to run. Could you please, he asks, tune the query ASAP so it will "behave"? Perhaps you relaxed too soon. Your original agenda was to spend some time making strategic decisions that will make your database better, faster, and more securesuch as ensuring the database is recoverable, enhancing underlying technology, or researching security updates. Instead, you will spend another day focusing on tactical activities such as SQL tuning, leaving little or no time for a strategic agenda.
As a strategic DBA, you want to free yourself from mundane chores and focus more on thought-provoking areas. Wouldn't it be nice to have an assistant DBA do them for you? With Oracle Database 10g, you have one in the form of Automatic Database Diagnostic Monitor (ADDM), a sort of robotic DBA that tirelessly trolls through database performance statistics to identify bottlenecks, analyze SQL statements, and consequently offer various types of suggestions to improve performance, often in conjunction with other "advisors" such as the SQL Tuning Advisor. In this installment, you will get an overview of how this process works. Automatic Database Diagnostic Monitor (ADDM) In Week 6, you learned about Automatic Workload Repository (AWR), which collects detailed performance-related metrics from the database at regular intervals, known as snapshots. After each snapshot is taken, ADDM is invoked to thoroughly analyze the data and metrics deriving from the difference between snapshots, and then recommend necessary actions. As I mentioned earlier, after finding a problem, ADDM might in turn call other advisors (such as the SQL Tuning Advisor) to offer recommendations for improvement. Instead of explaining this feature in words, let me show you exactly how it works. Suppose you are trying to diagnose an unexplained performance problem. In the example in our introduction, you knew which SQL statements needed to be tuned, or at least that SQL statements were the problem. In real life, however, you probably will not have such helpful information. To perform a diagnosis in 10g, you would choose the snapshots in the relevant interval for further drill-down analysis. In Enterprise Manager 10g, from the Database home page, you would choose "Advisor Central" and then click on the "ADDM" link, which brings up a page similar to Figure 1.
As you might expect, this "thinking" consumes resources such as CPU; hence the SQL Tuning Advisor works on SQL statements during a Tuning Mode, which can be run during off-peak times. This mode is indicated by setting the SCOPE and TIME parameters in the function while creating the tuning task. It's a good practice to run Tuning Mode during a low-activity period in the database so that regular users are relatively unaffected, leaving analysis for later. The concept is best explained through an example. Take the case of the query that the developer brought to your attention, shown below.
select account_no from accounts where old_account_no = 11This statement is not difficult to tune but for the sake of easier illustration, assume it is. There are two ways to fire up the advisor: using Enterprise Manager or plain command line. First, let's see how to use it in command line. We invoke the advisor by calling the supplied package dbms_sqltune.
declare l_task_id varchar2(20); l_sql varchar2(2000); begin l_sql := 'select account_no from accounts where old_account_no = 11'; dbms_sqltune.drop_tuning_task ('FOLIO_COUNT'); l_task_id := dbms_sqltune.create_tuning_task ( sql_text => l_sql, user_name => 'ARUP', scope => 'COMPREHENSIVE', time_limit => 120, task_name => 'FOLIO_COUNT' ); dbms_sqltune.execute_tuning_task ('FOLIO_COUNT'); end; /This package creates and executes a tuning task named FOLIO_COUNT. Next, you will need to see the results of the execution of the task (that is, see the recommendations).
set serveroutput on size 999999 set long 999999 select dbms_sqltune.report_tuning_task ('FOLIO_COUNT') from dual;The output is shown is Listing 2. Look at these recommendations carefully; the advisor says you can improve performance by creating an index on the column OLD_ACCOUNT_NO. Even better, the advisor calculated the cost of the query if the index were created, making the potential savings more definable and concrete.
Of course, considering the simplicity of this example, you would have reached the conclusion via manual examination as well. However, imagine how useful this tool would be for more complex queries where a manual examination may not be possible or is impractical.Intermediate-Level Tuning: Query Restructuring Suppose the query is a little bit more complex:
select account_no from accounts a where account_name = 'HARRY' and sub_account_name not in ( select account_name from accounts where account_no = a.old_account_no and status is not null);The advisor recommends the following:
1- Restructure SQL finding (see plan 1 in explain plans section) ---------------------------------------------------------------- The optimizer could not unnest the subquery at line ID 1 of the execution plan. Recommendation -------------- Consider replacing "NOT IN" with "NOT EXISTS" or ensure that columns used on both sides of the "NOT IN" operator are declared "NOT NULL" by adding either "NOT NULL" constraints or "IS NOT NULL" predicates. Rationale --------- A "FILTER" operation can be very expensive because it evaluates the subquery for each row in the parent query. The subquery, when unnested can drastically improve the execution time because the "FILTER" operation is converted into a join. Be aware that "NOT IN" and "NOT EXISTS" might produce different results for "NULL" values.This time the advisor did not recommend any structural changes such as indexes, but rather intelligently guessed the right way to tune a query by replacing NOT IN with NOT EXISTS. ecause the two constructs are similar but not identical, the advisor gives the rationale for the change and leaves the decision to the DBA or application developer to decide whether this recommendation is valid for the environment.
Advanced Tuning: SQL ProfilesAs you may know, the optimizer decides on a query execution plan by examining the statistics present on the objects referenced in the query and then calculating the least-cost method. If a query involves more than one table, which is typical, the optimizer calculates the least-cost option by examining the statistics of all the referenced objectsbut it does not know the relationship among them.
For example, assume that an account with status DELINQUENT will have less than $1,000 as balance. A query that joins the tables ACCOUNTS and BALANCES will report fewer rows if the predicate has a clause filtering for DELINQUENT only. The optimizer does not know this complex relationshipbut the advisor does; it "assembles" this relationship from the data and stores it in the form of a SQL Profile. With access to the SQL Profile, the optimizer not only knows the data distribution of tables, but also the data correlations among them. This additional information allows the optimizer to generate a superior execution plan, thereby resulting in a well-tuned query.SQL Profiles obviate the need for tuning SQL statements by manually adding query hints to the code. Consequently, the SQL Tuning Advisor makes it possible to tune packaged applications without modifying codea tremendous benefit. The main point here is that unlike objects statistics, a SQL Profile is mapped to a query, not an object or objects. Another query involving the same two tablesACCOUNTS and BALANCESmay have a different profile. Using this metadata information on the query, Oracle can improve performance. If a profile can be created, it is done during the SQL Tuning Advisor session, where the advisor generates the profile and recommends that you "Accept" it. Unless a profile is accepted, it's not tied to a statement. You can accept the profile at any time by issuing a statement such as the following:
begin dbms_sqltune.accept_sql_profile ( task_name => 'FOLIO_COUNT', name => 'FOLIO_COUNT_PROFILE' description => 'Folio Count Profile', category => 'FOLIO_COUNT'); end;
This command ties the profile named FOLIO_COUNT_PROFILE generated earlier by the advisor to the statement associated with the tuning task named FOLIO_COUNT described in the earlier example. (Note that although only the advisor, not the DBA, can create a SQL Profile, only you can decide when to use it.)
You can see created SQL Profiles in the dictionary view DBA_SQL_PROFILES. The column SQL_TEXT shows the SQL statement the profile was assigned to; the column STATUS indicates if the profile is enabled. (Even if it is already tied to a statement, the profile must be enabled in order to affect the execution plan.)
Using ADDM and SQL Tuning AdvisorIn addition to the three cases described above, SQL Tuning Advisor also identifies any objects with missing statistics referenced in the query. Thus, the advisor performs four distinct types of tasks:
A few weeks ago ( Week 13) you were introduced to the revamped Enterprise Manager interface. Here's how you would use it to diagnose and tune SQL: From the Database home page, click on the link "Advisor Central" at the bottom of the screen, which launches the page containing all the advisors. Next, click on "SQL Tuning Advisor" at the top of the screen as shown in Figure 4.
Next Week: Scheduler