This Article will make you learn how Oracle 11g Sql Access Advisor helps in 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.
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. Oracle DBA have been asked to find solutions to improve query performance—and SQL Access Advisor is the perfect tool for the job.
Oracle DBA 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 Oracle DBA 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.
- The first task is, of course, to fire up Enterprise Manager. On the Database Homepage, scroll down to the bottom of the page where Oracle DBA will see several hyperlinks, as shown in the figure 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, Oracle DBA 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 Oracle DBA to get all SQL statements cached in the shared pool for analysis.
However, in some cases Oracle DBA may not want all the statements in the shared pool; just a specific set of them. To do so, Oracle DBA need to create a "SQL Tuning Set" on a different screen and then refer to the set name here, in this screen.
Alternatively, perhaps Oracle DBA 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, Oracle DBA 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... .
- Oracle DBA 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 Oracle DBA 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 Oracle DBA 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 Repartitioning table "SCOTT"."TRANS"
SET SERVEROUTPUT ON
SET ECHO ON
Rem Creating new partitioned table
CREATE TABLE "SCOTT"."TRANS1"
( "TRANS_ID" NUMBER,
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
PARTITION BY RANGE ("RES_ID") INTERVAL( 3000) ( PARTITION VALUES LESS THAN (3000)
dbms_stats.gather_table_stats('"SCOTT"', '"TRANS1"', NULL, dbms_stats.auto_sample_size);
Rem Copying constraints to new partitioned table
ALTER TABLE "SCOTT"."TRANS1" MODIFY ("TRANS_ID" NOT NULL ENABLE);
Rem Copying referential constraints to new partitioned table
ALTER TABLE "SCOTT"."TRANS1" ADD CONSTRAINT "FK_TRANS_011" FOREIGN KEY ("RES_ID")
REFERENCES "SCOTT"."RES" ("RES_ID") ENABLE;
Rem Populating new partitioned table with data from original table
INSERT /*+ APPEND */ INTO "SCOTT"."TRANS1"
SELECT * FROM "SCOTT"."TRANS";
Rem Renaming tables to give new partitioned table the original table name
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.
- 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.
- 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.