Oracle Database 11g: by Arup Nanda
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.)
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.
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.
Click on SQL Advisors, which brings up a screen similar to that below:
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.
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.
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... .
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.
Click Next. This brings up a screen shown below (only top portion shown):
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.)
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.
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.
Click Refresh repeatedly until you see the value under the column Last Run Status change to SUCCEEDED.
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:
This screen indicates that the SQL Access Advisor task is COMPLETED. Now click on the button View Result. The screen is shown below:
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.
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.
On the screen above note the column Recommendation ID. Click on the hyperlink, which brings up the detailed recommendations as shown below:
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";
The script actually builds a new table and then renames it to match the original table.
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:
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.
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:
Now if you click View to examine the recommendations, it brings up a screen as shown below:
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:
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.
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: