Introduction to Oracle 10g’s New SQLAccess Advisor

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 3^rd
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:TEMPSQLAccess’;

        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