11g logo

Oracle Database 11g:
The Top Features for DBAs and Developers

by Arup Nanda Oracle ACE Director

SQL Access Advisor

Get recommendations on improving schema design by partitioning, indexing, and creating materialized views based on actual frequency and type of usage—not data.

See Series TOC

Oracle Database 10g offers an avalanche of helpers—or "advisors"—which help you decide the best course of action. One example is SQL Tuning Advisor, which provides recommendations on query tuning, lengthening the overall optimization process a bit in the process.

But consider this tuning scenario: Say an index will definitely help a query but the query is executed only once. So even though the query will benefit from it, the cost of creating the index will outweigh that benefit. To analyze the scenario in that manner, you would need to know how often the query is accessed and why.

Another advisor—SQL Access Advisor—performs this type of analysis. In addition to analyzing indexes, materialized views, and so on as it does in Oracle Database 10g, in Oracle Database 11g SQL Access Advisor also analyzes tables and queries to identify possible partitioning strategies—a great help when designing optimal schema. In Oracle Database 11g SQL Access Advisor can now provide recommendations with respect to the entire workload, including considering the cost of creation and maintaining access structures.

In this installment you will see how a typical problem is solved by the new SQL Access Advisor. (Note: For the purposes of this demo a single statement will illustrate this functionality; however, Oracle recommends that SQL Access Advisor be used to help tune your entire workload, not just one SQL statement.)

The Problem

Here's a typical problem. The SQL statement below is issued by the application. The query seems resource intensive and slow.

 
select store_id, guest_id, count(1) cnt
from res r, trans t
where r.res_id between 2 and 40
and t.res_id = r.res_id
group by store_id, guest_id
/

This SQL touches two tables, RES and TRANS; the latter is a child table of the former. You have been asked to find solutions to improve query performance—and SQL Access Advisor is the perfect tool for the job.

You can interact with the advisor either via command line or Oracle Enterprise Manager Database Control, but using the GUI provides somewhat better value by letting you visualize the solution and reducing many tasks to simple pointing and clicking. To solve the problem in the SQL using SQL Access Advisor in Enterprise Manager, follow the steps below.

  1. The first task is, of course, to fire up Enterprise Manager. On the Database Homepage, scroll down to the bottom of the page where you will see several hyperlinks, as shown in the figure below:

     

    Figure 1

  2. From this menu, click on Advisor Central, which brings up a screen similar to that below. Only the top portion of the screen is shown.

     

    Figure 2

  3. Click on SQL Advisors, which brings up a screen similar to that below:

     

    Figure 3

  4. In this screen, you can schedule a SQL Access Advisor session and specify its options. The advisor must gather some SQL statements to work with. The simplest option is to get them from the shared pool, via Current and Recent SQL Activity. Choosing this option allows you to get all SQL statements cached in the shared pool for analysis.

    However, in some cases you may not want all the statements in the shared pool; just a specific set of them. To do so, you need to create a "SQL Tuning Set" on a different screen and then refer to the set name here, in this screen.

    Alternatively, perhaps you want to run a synthetic workload based on a theoretical scenario you anticipate to occur. These types of SQL statements will not be present in the shared pool, as they have not executed yet. Rather, you need to create these statements and store them in a special table. In the third option ( Create a Hypothetical Workload...), you need to supply the name of this table along with the schema name.

    For the purpose of this article, assume you want to take the SQLs from the shared pool. So, choose the first option as shown in the screen, which is default.

  5. However, you may not want all the statements, just some key ones. For instance, you may want to analyze the SQL executed by the user SCOTT, which is the application user. All other users probably issue ad hoc SQL statements and you want to exclude them from your analysis. In that case, click on the "+" sign just before Filter Options as shown in the figure below.

     

    Figure 4

  6. In this screen, enter SCOTT in the text box where it asks to enter the users, and choose the radio button Include only SQL... (default). Similarly, you can exclude some users. For instance, you may want to capture all activity in the database except the users SYS, SYSTEM ,and SYSMAN. You will enter these users in the text box and click the button Exclude all SQL statements... .

  7. You can filter on tables accessed in statements, on Module Ids, Actions, and even specific strings in the SQL statements. The idea is to confirm that only the statements of interest are analyzed. Choosing a small subset of the entire SQL cache makes the analysis faster. In this example, we assumed there is only one statement issued by the user SCOTT. If that is not the case you can put additional filtering conditions to reduce the analyzed set to only one SQL, the one mentioned in the original problem statement.

  8. Click Next. This brings up a screen shown below (only top portion shown):

     

    Figure 5

  9. In this screen you can specify what types of recommendations should be searched. For instance, in this case, we want the advisor to look at potential indexes, materialized views, and partitioning, so check all the boxes next to these terms. For Advisor Mode, you have a choice; the default, Limited Mode, acts on only high-cost SQL statements. It will be faster, of course, and will yield better bang for the buck. For analysis of all SQL, use the Comprehensive Mode. (In this example the choice of modes is irrelevent because you have only one SQL.)

  10. The bottom half of the screen shows advanced options such as how the SQL statements should be prioritized, the tablespaces used, and so on. You can leave the defaults as marked (more on those later).Click Next, which bring up the scheduling screen. Choose Run Immediately and click Next.

  11. Click Submit. This creates a Scheduler job. You can click on the job hyperlink shown in this screen, at the top of the page. The job will be shown as Running.

  12. Click Refresh repeatedly until you see the value under the column Last Run Status change to SUCCEEDED.

  13. Now go back to the Database Homepage and click on Advisor Central as you did in Step 1. Now you will see the SQL Access Advisor row as shown in the figure below:

     

    Figure 6

  14. This screen indicates that the SQL Access Advisor task is COMPLETED. Now click on the button View Result. The screen is shown below:

     

    Figure 7

  15. This screen says it all! SQL Access Advisor analyzed the SQL statement and found some solutions that can improve query performance tenfold. To see what specific recommendations were made, click on the Recommendations tab, which brings up a details screen as shown below.

     

    Figure 8

  16. This screen has a lot of good information, at a slightly higher level. For instance, for the statement with ID = 1 there are two recommended actions, under column Actions. The following column, Action Types, shows the types of actions, indicated by colored squares. From the icon guide just below it, you can see that the actions were on indexes and partitions. Together they improve performance by several orders of magnitude.

    To see exactly what SQL statement can be improved, click on the ID, which brings up the screen below. Of course, this analysis had only one statement so only one showed up here. If you had more than one, you would see them all.

     

    Figure 9

  17. On the screen above note the column Recommendation ID. Click on the hyperlink, which brings up the detailed recommendations as shown below:

     

    Figure 10

  18. The screen offers a very clear description of the solutions. It has two recommendations: to create the table as partitioned and to use an index. Then it finds that the index is already present so it advises to retain the index.

    If you click on PARTITION TABLE under the column Action, you will see the actual script Oracle will generate to make this a partitioned table. Before you click, however, fill in the tablespace name in the text box. This allows SQL Access Advisor to use that tablespace while building this script:

    Rem 
Rem Repartitioning table "SCOTT"."TRANS"
Rem 

SET SERVEROUTPUT ON
SET ECHO ON

Rem 
Rem Creating new partitioned table
Rem 
CREATE TABLE "SCOTT"."TRANS1" 
(    "TRANS_ID" NUMBER, 
    "RES_ID" NUMBER, 
    "TRANS_DATE" DATE, 
    "AMT" NUMBER, 
    "STORE_ID" NUMBER(3,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "USERS" 
PARTITION BY RANGE ("RES_ID") INTERVAL( 3000) ( PARTITION VALUES LESS THAN (3000)
);

begin
dbms_stats.gather_table_stats('"SCOTT"', '"TRANS1"', NULL, dbms_stats.auto_sample_size);
end;
/

Rem 
Rem Copying constraints to new partitioned table
Rem 
ALTER TABLE "SCOTT"."TRANS1" MODIFY ("TRANS_ID" NOT NULL ENABLE);

Rem 
Rem Copying referential constraints to new partitioned table
Rem 
ALTER TABLE "SCOTT"."TRANS1" ADD CONSTRAINT "FK_TRANS_011" FOREIGN KEY ("RES_ID")
     REFERENCES "SCOTT"."RES" ("RES_ID") ENABLE;

Rem 
Rem Populating new partitioned table with data from original table
Rem 
INSERT /*+ APPEND */ INTO "SCOTT"."TRANS1"
SELECT * FROM "SCOTT"."TRANS";
COMMIT;

Rem 
Rem Renaming tables to give new partitioned table the original table name
Rem 
ALTER TABLE "SCOTT"."TRANS" RENAME TO "TRANS11";
ALTER TABLE "SCOTT"."TRANS1" RENAME TO "TRANS";
  19.  

  20. The script actually builds a new table and then renames it to match the original table.

  21. The final tab, Details, show some interesting details on the task. Although they are not important for your analysis, they can offer valuable clues about how the advisor arrived at those conclusions, which can help your own thinking process. The screen has two parts, the first part being Workload and Task Options, shown below.

     

    Figure 11
  22.  
  23. The lower half of the screen shows a sort of run-log of the task. Sometimes the advisor cannot process all SQL statements. If some SQL statements are discarded, it shows up here, against the Invalid SQL String: Statements discarded count. If you are wondering how why only a few of the several SQL statements were analyzed, here lies the reason.

     

    Figure 12

Advanced Options

In Step 10 above, I made a reference to advanced settings. Let's see what these settings do.

Click on the plus sign just left of Advanced Options, which brings up a screen as shown below:

 

Figure 13


This screen allows you to enter the name of the tablespace where the index will be created, which schema it will be created on, and so on. For partitioning advice, you can specify the tablespace the partitions will go to and so on.

Perhaps the most important element is the checkbox Consider access structures creation costs recommendations. If you check this box, SQL Access Advisor will take into consideration the cost of the creation of the index itself. For example, should 10 new indexes potentially be involved, the associated cost may lead SQL Access Advisor to not advise their creation.

You can also specify the maximum size of the index in this screen.

Differences vs. SQL Tuning Advisor

In the introduction I only briefly described how this tool differs from SQL Tuning Advisor, so let's go into more detail here. A quick demo will best explain the differences.

From the SQL Advisors screen, choose SQL Tuning Advisor and run it. After it is completed, here is part of the screen that shows the results:

 

Figure 14


Now if you click View to examine the recommendations, it brings up a screen as shown below:

 

Figure 15


Examine the recommendation carefully: it is to create an index on TRANS on the column RES_ID. SQL Access Advisor, however, did not make that specific recommendation. Instead it recommended to partition the table, the reason being: Based on the access patterns and the data available, SQL Access Advisor determined that partitioning is more efficient than building an index on the column. This is a more "real world" recommendation than that provided by SQL Tuning Advisor.

The recommendations made by SQL Tuning Advisor map to one of four objectives:

  • Gather statistics for objects that have missing or stale statistics
  • Account for any data skew, complex predicates, or stale statistics by the optimizer
  • Restructure the SQL for optimizing performance
  • Make new index recommendations

These recommendations are with respect to only a single statement, not the entire workload. Therefore SQL Tuning Advisor should be used on high-load or business critical queries occasionally. Note that this advisor only recommends indexes that offer significant improvement in performance, compared to SQL Access Advisor, which may have more lenient standards. And of course, the former does not have a partition advisor in it.


Use Cases

SQL Access Advisor is useful for tuning schemas, not just queries. As a best practice, you can use this strategy in developing an effective SQL tuning plan:

  1. Search for high-cost SQL statements, or better yet, evaluate the entire workload.
  2. Put suspect statements into a SQL Tuning Set.
  3. Analyze them using both SQL Tuning Advisor and SQL Access Advisor.
  4. Get the results of the analysis; note the recommendations.
  5. Plug the recommendations into SQL Performance Analyzer (see this installment).
  6. Examine the before-and-after changes in SQL Performance Analyzer and arrive at the best solution.
  7. Repeat the tasks until you get the optimal schema design.
  8. When you have the best schema design, you may want to lock the plan using SQL Plan Management baselines (described in this installment).


Back to Series TOC