Performance Tuning using SQL Access Advisor(SAA) using Oracle11g

Performance Tuning & SQL Access Advisor:

When Oracle Database 10g released, it offers lots of features for performance tuning. One example is SQL Tuning Advisor, which provides recommendations on query tuning. "Advisors" — which help you decide the best course of action.

One scenario: 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.

Find recommendations on improving schema design by partitioning, indexing, and creating materialized views based on your current workload and type of usage - not using any data.

About SQL Access Advisor:

This is an enterprise feature in Oracle database to get recommendations for performance improvements. Materialized views, partitions, and indexes are essential when tuning a database to achieve optimum performance for complex, data-intensive queries.

SQL Access Advisor helps you achieve your performance goals by recommending the proper set of materialized views, materialized view logs, partitions, and indexes for a given workload. Understanding and using these structures is essential when optimizing SQL as they can result in significant performance improvements in data retrieval. The advantages, however, do not come without a cost. Creation and maintenance of these objects can be time consuming, and space requirements can be significant. In particular, partitioning of an unpartitioned base table is a complex operation that must be planned carefully.

SQL Access Advisor recommend partitioning on an existing unpartitioned base table to improve performance. Furthermore, it may recommend new indexes and materialized views that are themselves partitioned. While creating new partitioned indexes and materialized view is no different from the unpartitioned case, partitioning existing base tables should be executed with care. This is especially true when indexes, views, constraints, or triggers are defined on the table.

Overview of Using SQL Access Advisor:

An easy way to use SQL Access Advisor is to invoke its wizard, which is available in Enterprise Manager from the Advisor Central page. If you prefer to use SQL Access Advisor through the DBMS_ADVISOR package, then this section describes the basic components and the sequence in which you must call the procedures.

This section describes the four steps in generating a set of recommendations:

• Create a task
• Define the workload
• Generate the recommendations
• View and implement the recommendations

All the above tasks are easy to handle though Oracle Enterprise Manager rather than Manual.

Here are the steps to complete the task:

Using Oracle OEM (Enterprise Manager) it is easy to set, manage and execute SQL Access advisor. Here are the steps to set task for sql access advisor.

OEM Home Page → Advisor Central → SQL Advisor → Advisor Central → SQL Access Advisor → Choose Verify use of access structures (indexes, materialized views, partitioning, etc) only options → Select Continue → Choose Current and Recent SQL Activity option to choose from current work load → Next → Choose Indexes / Materialized views / Partitioning with scope ( limited) → Next → Schedule your task details → Next → Submit → See the Advisory Central all scheduled windows/ tasks → Choose your task to view recommendation details

Fig_1: Home screen when you logged on to the database ( sys user)

                                                                    Fig_1(Home Page)

Fig_2 : When Advisor central selected from Home screen of after log on.

                                                                   Fig_2 ( Advisor Central Window)

Fig_3 : When selected SQL Access Advisor from Advisory window:

                                                                Fig_3 (SQL Advisor's Window)

Fig_4 : After Selecting SQL Access Advisor from SQL Access advisor window:

                                                                 Fig_4 ( SQL Access Advisor )

Fig_5 : Starting of SQL Access advisor screen:

                                                                 Fig_5 ( First phase of SQL access advisor  )
Note : 
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.

Fig_6 : Second phase of SQL Accees advisor window:

                                                              Fig_6 (Access recommendation window )

Fig : 7 : Third window to set task details:

                            Fig_7 ( Window to set SQL Acccess advisor task details including filter options )


Fig:8 :  Fourth / Submit window to schedule SQL Access Advisor task:

                                                               Fig_8 ( Submit Window)


Fig : 9 : SQL Advisor main window (with status created)

                                              Fig_9 (  SQL Advisor main window to view task details)


Fig:10 : SQL Advisor main window (with status running)

                             Fig_10 (SQL Advisor main window (with status running)

Fig:11: from v$session when Task is executing:

Fig:12 : Task selected see the status

                                       Fig_12 ( SQL advisor status window after run )

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.

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.

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

1. 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";

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).

User level grants for SQL access advisor:

Assume APP is the user to run SQL access advisor.


SQL> grant all on dbms_advisor to app;
SQL> grant advisor to app;
SQL> grant ADMINISTER SQL TUNING SET to app;


SQL Access Advisor Examples-using DBMS_ADVISOR

SQL Access Advisor Examples are as follows:

Example1: Steps to Implement Advisor recommendations

Step1: Create a sample table:

SQL> create table test_saa as select * from all_objects;

Step2: gather table statistics:

SQL> exec dbms_stats.gather_table_stats  (APP, 'TEST_SAA');

Step3: Create Advisor Task using dbms_advisor.quick_tune:

SQL>
declare
    v_sql varchar2(200) := 'select object_name from test_adv where object_id = 1523';
    v_tuning_task varchar2(200) := 'tune_task_advisor_gourang';
    v_tune_result clob;
begin
    dbms_advisor.quick_tune ( dbms_advisor.sqlaccess_advisor , v_tuning_task, v_sql );
    dbms_output.put_line ('Quick Tune Completed');
end;
/

Step4:  Sql’s to check the status:

SQL> select * from user_advisor_templates;
SQL> select * from user_advisor_journal;
SQL> select * from user_advisor_recommendations
where task_name = 'tune_task_advisor_gouranga';
SQL> select * from user_advisor_actions
where task_name = 'tune_task_advisor_gouranga';
SQL> SELECT description, advisor_name, created, status,
recommendation_count, source, how_created
FROM user_advisor_tasks WHERE task_name = 'tune_task_advisor_gouranga';

Step5: To see the advisor recommendations execute below SQL

SELECT DBMS_ADVISOR.get_task_script
('tune_task_advisor_gouranga') AS script
FROM dual;

Output from above sql statement:
--------------------------------------------------

Rem  SQL Access Advisor: Version 10.2.0.4.0 - Production
Rem 
Rem  Username:        APP
Rem  Task:            tune_task_advisor_gouranga
Rem  Execution date:  27/10/2014 19:09
Rem 

CREATE MATERIALIZED VIEW LOG ON
    "APP"."TEST_ASS"
    WITH ROWID ;

CREATE MATERIALIZED VIEW "APP"."MV$$_88110000"
    REFRESH FAST WITH ROWID
    ENABLE QUERY REWRITE
    AS SELECT "APP"."TEST_ASS"."OBJECT_NAME" M1
FROM APP.TEST_ASS
WHERE (APP.TEST_ASS.OBJECT_ID  = 2599);

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

---------------------------------------------------------------------------------------
I hope this document will help to understand SAA ( SQL Access advisor). Post comments.