Introduction to Oracle 10g's New SQLAccess Advisor

August 13, 2004

If you have ever had a difficult time tuning applications because you can never find the SQL or code behind these applications, Oracle 10g's new SQLAccess Advisor, a new tool from Oracle, is a must.

Have you have ever had a 3rd party application or internal application that you just never could get your hands on to tune? Now through the use of Oracle's new SQLAccess Advisor you can capture the SQL from within the SQL cache and have this advisor help you decide the best plan of attack to tune this elusive SQL. The goal of the SQLAccess Advisor is to investigate a workload and suggest new indexing strategies and possible materialized views to give you the added boost in performance required.

The SQLAccess Advisor is part of Oracle 10g's new ADDM architecture that allows for the "automatic" tuning of SQL by evaluating structures and workload to determine if the index structures are adequate or if new ones are needed. Its' purpose is to improve the access to the data you are requesting through the SQL calls you tell it are important, and then to make recommendations to speed up performance and access to your data.

Using SQLAccess against cached SQL

Below you will find eight distinct steps that are required to setup and extract SQL from the cache and produce recommendations from the SQLAccess engine. The individual steps should be run from within the same SQL*Plus session, as there are steps in the process that rely upon earlier steps.

/* 00  Run a workload through the system */

The purpose of this script is for the workload or SQL that has been issued within the system at any point in time, to be captured and evaluated through the SQLAccess Advisor. If you currently have a system that is being used you may be able to skip this step. If you have an idle system, you should conjure up some SQL that you are interested in and execute it through the system. After the SQL is completely executed, you can proceed to the others steps. For this article, I used the SCOTT/TIGER tables, removed the indexes, loaded them a lot more data and then issued various DML operations against them.

/* 01 - Setup variables */
        VARIABLE taskid     NUMBER;
        VARIABLE taskname   VARCHAR2(255);
        VARIABLE wrkldname  VARCHAR2(255);
        VARIABLE stmts_in   NUMBER;
        VARIABLE stmts_lost NUMBER;
        EXECUTE  :taskname  := 'TASKNAME1';
        EXECUTE  :wrkldname := 'WRKLDNAME1'

These variables should be self-explanatory. The only variables you may wish to concern yourself with for this particular article are the TASKNAME and WRKLDNAME variables. These are defined as unique names for a task and workload, which will be explained latter, and you can change them if you wish to maintain multiple tasks or workloads for the SQLAccess Advisor.

/* 02 - Delete the last created task and workload*/
        EXECUTE DBMS_ADVISOR.DELETE_TASK (:taskname);
        EXECUTE DBMS_ADVISOR.DELETE_SQLWKLD(:wrkldname);

This step is purely cleanup. Its only purpose is to remove the task and workload from the Advisory repository so that we may reuse the name. The only requirement here is that you cannot delete a workload if any tasks are linked to the workload, explained further in this script.

/* 03 - Create the task */
        EXECUTE DBMS_ADVISOR.CREATE_TASK-
        ('SQL Access Advisor', :taskid, :taskname);

A task must be created and is necessary for holding all information relating to the recommendations that the SQLAccess Advisor creates. This procedure takes in two variables. The task is generated by the call but you must supply the task name.

/* 04 - Create the workload */
        EXECUTE DBMS_ADVISOR.CREATE_SQLWKLD(:wrkldname);

The workload contains all the SQL that you would like the SQLAccess Advisor to analyze and produce recommendations on. This workload can be loaded by a few different mechanisms and for this article, we will be calling another procedure that loads it from the SQL cache. It is very important to note that if you truly want to use SQLAccess Advisor properly you must make sure that all SQL statements that are related to an application or true workload are presented. It is only through the full disclosure of all SQL statements to the SQLAccess Advisor that SQLAccess Advisor can assist you in giving a real recommendation. If the Advisor does not come in contact with all SQL, it is possible for it to produce inadequate recommendations. This is only logical and it is your responsibility to provide the Advisor with all the information you can get about an application.

/* 05 - Load the Workload from Cache */
        EXECUTE DBMS_ADVISOR.IMPORT_SQLWKLD_SQLCACHE-
        (:wrkldname,'APPEND',2,:stmts_in,:stmts_lost);

As stated previously, we are going to populate our workload with the cached SQL in our system. We can easily do this by calling the IMPORT_SQLWKLD_SQLCACHE procedure.

/* 06 - Create link between the workload and task */
        EXECUTE DBMS_ADVISOR.ADD_SQLWKLD_REF(:taskname, :wrkldname);

In order for a recommendation to be produced, a task needs to be linked to a workload. This section of the script will link your defined task and workload together. After this step, you can always delete a task but as stated before, a workload cannot be deleted unless no task is associated with it.

/* 07 - Execute the task */
        EXECUTE DBMS_ADVISOR.EXECUTE_TASK(:taskname);

This section of the script actually executes a task and its associated workload and produces recommendations and actionable items to help you create additional structures to aid in performance.

/* 08 - Generate script */
        CREATE DIRECTORY SQLACCESSDIR as 'C:\TEMP\SQLAccess';
        GRANT READ,WRITE ON DIRECTORY SQLACCESSDIR TO PUBLIC;
        EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT-
        (:taskname),'SQLACCESSDIR', 'SQLAccess_Results.sql');

After the execution of the task has completed, you can now generate a script that will allow you to view the results of the recommendation. You do this by first setting up a directory through the CREATE DIRECTORY command and granting access to that directory. If you already have a DIRECTORY you can use, or you execute this script multiple times, you will want to comment out the CREATE and GRANT commands. The combination of GET_TASK_SCRIPT and CREATE_FILE will pull the recommendations from the Advisory repository and create the script file you provide. In this case, my script is called SQLAccess_Results.sql and you should change this to be descriptive of the task and workload you are executing. The output of this call can be seen in Listing 1. You can either execute this script in its entirety or by cutting and pasting through SQL*Plus.

Listing 1.
Recommendations from SQLAccess

Rem  SQL Access Advisor: Version 10.1.0.1 - Production
Rem  
Rem  Username:        SYS
Rem  Task:            TASKNAME1
Rem  Execution date:  03/08/2004 09:12
Rem  

set feedback 1
set linesize 80
set trimspool on
set tab off
set pagesize 60

whenever sqlerror CONTINUE

CREATE MATERIALIZED VIEW LOG ON
    "SCOTT"."EMP"
    WITH ROWID ;

CREATE MATERIALIZED VIEW LOG ON
    "SCOTT"."DEPT"
    WITH ROWID ;

CREATE MATERIALIZED VIEW "SYS"."MV$$_01D50000"
    REFRESH FAST WITH ROWID
    ENABLE QUERY REWRITE
    AS SELECT SCOTT.EMP.ROWID C1, SCOTT.DEPT.ROWID C2, "SCOTT"."DEPT"."DNAME" M1, "SCOTT"."EMP"."EMPNO"
       M2, "SCOTT"."EMP"."ENAME" M3 FROM SCOTT.EMP, SCOTT.DEPT WHERE SCOTT.DEPT.DEPTNO
       = SCOTT.EMP.DEPTNO;

begin
  dbms_stats.gather_table_stats('"SYS"','"MV$$_01D50000"',NULL,dbms_stats.auto_sample_size);
end;
/

CREATE MATERIALIZED VIEW "SYS"."MV$$_01D50002"
    REFRESH FAST WITH ROWID
    ENABLE QUERY REWRITE
    AS SELECT "SCOTT"."DEPT"."DEPTNO" M1, "SCOTT"."DEPT"."DNAME" M2 FROM SCOTT.DEPT
       WHERE (SCOTT.DEPT.DEPTNO = 30);

begin
  dbms_stats.gather_table_stats('"SYS"','"MV$$_01D50002"',NULL,dbms_stats.auto_sample_size);
end;
/

CREATE INDEX "SCOTT"."_IDX$$_01D50009"
    ON "SCOTT"."EMP"
    ("DEPTNO")
    COMPUTE STATISTICS;

whenever sqlerror EXIT SQL.SQLCODE

begin
  dbms_advisor.mark_recommendation('TASKNAME1',1,'IMPLEMENTED');
  dbms_advisor.mark_recommendation('TASKNAME1',2,'IMPLEMENTED');
  dbms_advisor.mark_recommendation('TASKNAME1',3,'IMPLEMENTED');
end;
/

One of the major drawbacks in tuning database systems has been our inability as DBAs to tune for a set of related SQL statements, a workload. By giving us the ability to define what the workload of our system is and actually have Oracle help in telling us what some of the structures should be for improved performance, we will surly end up with better systems. Oracles SQLAccess Advisor is just one piece of the ADDM architecture that gets us there and I have only scraped the tip here in this introductory article.

» See All Articles by Columnist James Koopmann








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers