Oracle SQL Access Advisor 说明

一.说明

1.1 相关内容

在如下官方文档中提到了一些优化工具的说明：

Monitoring and Tuning the Database

http://docs.oracle.com/cd/E11882_01/server.112/e10897/montune.htm

Using Advisors to Optimize DatabasePerformance

Oracle Databaseincludes a set ofadvisorsto help you manage and tune your database.This section contains background information about these advisors andinstructions for their use. The following topics are covered:

(1)AboutAdvisors

(2)Aboutthe SQL Advisors

(3)Aboutthe Automatic SQL Tuning Advisor

(4)Configuringthe Automatic SQL Tuning Advisor

(5)ViewingAutomatic SQL Tuning Results

(6)Runningthe SQL Tuning Advisor

(7)Runningthe SQL Access Advisor

(8)OptimizingMemory Usage with the Memory Advisors

其中的部分内容之前也有整理，参考：

如何用 SQL Tuning Advisor (STA) 优化SQL语句

http://blog.csdn.net/tianlesoftware/article/details/5630888

Oracle ADDM 自动诊断监视工具 介绍

http://blog.csdn.net/tianlesoftware/article/details/5630942

Oracle ASH(Active SessionHistory) 说明

http://blog.csdn.net/tianlesoftware/article/details/6448765

Oracle AWR 介绍

http://blog.csdn.net/tianlesoftware/article/details/4682300

这里我们看一下SQLAccess Advisor的说明：

The SQL AccessAdvisor tunes a schema to a given SQL workload. For example, the SQL AccessAdvisor can provide recommendations for creating indexes, materialized views,or partitioned tables for a given workload.

有关在OEM中配置SQL Access Advisor的方法参考：

Monitoring and Tuning the Database

http://docs.oracle.com/cd/E11882_01/server.112/e10897/montune.htm

1.2 SQL Access Advisor 概述

在另篇里有SQLAccess Advisor 的详细说明：

SQL AccessAdvisor

http://docs.oracle.com/cd/E11882_01/server.112/e16638/advisor.htm

Materialized views, partitions, and indexes are essential when tuning a database to achieveoptimum performance for complex, data-intensive queries.SQL AccessAdvisor helps you achieve your performance goals by recommending the proper setof materialized views, materialized view logs, partitions, and indexes for agiven workload.

--数据库对负责查询的优化的本质是对物化视图，分区和索引的优化，而SQL Access Advisor 可以帮助我们实现优化，其可以提供物化视图，分区和索引的推荐值。

Understanding and using these structures is essential when optimizing SQL as they can resultin significant performance improvements in data retrieval. The advantages,however, do not come without a cost. Creation and maintenance of these objectscan be time consuming, and space requirements can be significant. In particular,partitioning of an unpartitioned base table is a complex operation that must beplanned carefully.

--在优化SQL时，理解和使用这些结构可以大幅的提高数据检索的性能，但是这也是有代价的，创建和维护这些对象也需要消耗时间，空间，特别是对非分区表进行分区是一个复杂的操作，需要慎重考虑。

SQL AccessAdvisor index recommendations include bitmap, function-based, and B-treeindexes. A bitmap index offers a reduced response time for many types of ad hocqueries and reduced storage requirements compared to other indexing techniques.B-tree indexes are most commonly used in a data warehouse to index unique ornear-unique keys.

--SQL Access Advisor 索引的推荐包括bitmap，function-based，B-tree 索引。

SQL AccessAdvisor materialized view recommendations include fast refreshable and fullrefreshable MVs, for either general rewrite or exact text match rewrite.

--SQL Access Advisor 物化视图推荐包括fast refreshable和fullrefreshable。

SQL Access Advisor,using theTUNE_MVIEWprocedure, also recommends how to optimizematerialized views so that they can be fast refreshable and take advantage ofgeneral query rewrite.

--SQL Access Advisor 使用TUNE_MVIEW过程来建议如何优化物化视图。

In addition, SQLAccess Advisor can recommend partitioning on an existing unpartitioned basetable to improve performance. Furthermore, it may recommend new indexes andmaterialized views that are themselves partitioned. While creating newpartitioned indexes and materialized view is no different from theunpartitioned case, partitioning existing base tables should be executed withcare. This is especially true when indexes, views, constraints, or triggers aredefined on the table. See"SpecialConsiderations when Script Includes Partitioning Recommendations"fora list of issues involving base table partitioning for performing this taskonline.

-- SQL Access Advisor 可以建议在非分区表上使用分区来提高性能。此外可能建议在表上（分区和非分区表）创建新的索引和物化视图。

You can run SQLAccess Advisor from Oracle Enterprise Manager (accessible from the AdvisorCentral page) using SQL Access Advisor Wizard or by invoking theDBMS_ADVISORpackage.TheDBMS_ADVISORpackage consists of a collection of analysis and advisoryfunctions and procedures callable from any PL/SQL program.

--也可以从OEM中调用DBMS_ADVISOR包来运行SQL Access Advisor。

Figure18-1illustrates how SQL Access Advisor recommends access structuresfor a given workload obtained from a user-defined table or the SQL cache. If aworkload is not provided, then it can generate and use a hypothetical workloadalso, provided the user schema contains dimensions defined by theCREATEDIMENSIONkeyword.

--下图给出了SQL Access Advisor 访问用户表或者SQL 缓存中的一个架构图，如果没有提供workload,那么它会假设一个workload。

Using SQL Access Advisor in EnterpriseManager or API, you can do the following:

--在EM中使用SQLAccess Advisor或者API，可以实现如下功能：

（1）Recommend materialized views andindexes based on collected, user-supplied, or hypothetical workloadinformation.

（2）Recommend partitioning of tables,indexes, and materialized views.

（3）Mark, update, and removerecommendations.

In addition, you can use SQL Access AdvisorAPI to do the following:

--使用API还可以实现：

（1）Perform a quick tune using a singleSQL statement.

（2）Show how to make a materializedview fast refreshable.

（3）Show how to change a materializedview so that general query rewrite is possible.

To make recommendations, SQL Access Advisor relies on structural statistics about tableand index cardinalities of dimension level columns,JOINKEYcolumns,and fact table key columns. You can gather either exact or estimated statisticswith theDBMS_STATSpackage.Because gathering statistics is time-consuming and full statistical accuracy isnot required, it is generally preferable to estimate statistics. Withoutgathering statistics on a given table, queries referencing this table aremarked as invalid in the workload, resulting in no recommendations being madefor those queries. It is also recommended that all existing indexes andmaterialized views have been analyzed. SeeOracleDatabase PL/SQL Packages and Types Referencefor more informationabout theDBMS_STATSpackage.

--为了实现建议的功能，SQL Access Advisor 依赖于结构的统计信息，如表和索引的基数。可以使用DBMS_STATS包来收集统计信息，因为收集统计统计信息需要消耗时间，索引不需要精确的收集，一般都是收集大概的统计信息。 如果查询的表上没有统计信息，那么查询时，在workload里的表就会标记为无效，那么就不能提供相关的优化建议。同时，对于已经存在的索引和物化视图也是建议进行分析。

1.3 SQL Access Advisor 使用概述

An easy way touse SQL Access Advisor is to invoke its wizard, which is available inEnterprise Manager from the Advisor Central page. If you prefer to use SQLAccess Advisor through theDBMS_ADVISORpackage, then this sectiondescribes the basic components and the sequence in which you must call theprocedures.

--使用SQL Access Advisor 最简单的方法是在OEM中调用向导，当然也可以使用DBMS_ADVISOR 包，具体的调用方法有如下4步。

Createa task

Definethe workload

Generatethe recommendations

Viewand implement the recommendations

1.3.1Create a task

Anadvisortaskis a container in the data dictionary that stores the inputs to andthe results of an intelligent advisor analysis run. All information relating tothe recommendation operation, including the results, resides in the task.

--Advisor task 是数据字典里的一个容器，其用来存储输入和advisor 分析的结果。 所有的于recommendation 操作相关的信息和结果都存储在task里。

Before SQLAccess Advisor can makerecommendations, you mustcreate a task using either of the following:

--在使用SQL Access Advisor 之前需要使用如下方法创建一个task：

（1）The wizard inOracle Enterprise Manager or theDBMS_ADVISOR.QUICK_TUNEprocedure,which creates the task automatically。

（2）TheDBMS_ADVISOR.CREATE_TASKprocedure

You can controlwhat a task does by defining parameters for the task using theDBMS_ADVISOR.SET_TASK_PARAMETERprocedure.

--可以使用DBMS_ADVISOR 过程来设置task的参数。

1.3.2 Definethe workload

Aworkloadconsistsof one or more SQL statements, plus statistics and attributes that fullydescribe each statement. Afull workloadcontains all SQL statementsfrom a target business application. A partial workloadcontains a subsetof SQL statements. The difference is that for full workloads SQL Access Advisormay recommend dropping unused materialized views and indexes.

--Wordload 包含一个或者多个SQL 语句，及其统计信息和每个语句的属性。Full workload 包含目标应用中的所有SQL 语句，Partialworkload包含SQL statements的一个子集。

You cannot useSQL Access Advisor without a workload. A workload may contain a variety ofstatements. SQL Access Advisor ranks the entries according to a specificstatistic, business importance, or combination of the two, which enables theadvisor to process the most important SQL statements first.

--只有创建了workload 才可以使用SQL Access Advisor。 Workload里包含了各种SQL语句，SQL Access Advisor 根据统计信息,重要性或者2者的结合对这些SQL进行排序，越重要的SQL 会被advisor 优先处理。

SQL AccessAdvisor may require particular attributes to be present in a valid workload.Although the advisor can perform analysis when items are missing, the qualityof the recommendations may be lower. For example, SQL Access Advisor requires aworkload to contain a SQL query and the user who ran the query, with otherattributes as optional. However, if the workload also contains I/O and CPUdata, then SQL Access Advisor can better evaluate statement efficiency.

--SQL Access Advisor 需要指定特别的属性到一个有效的workload，虽然在没有指定的情况下也可以运行，但是此时的recommendations的质量就会低很多。

The databasestores a workload as a SQL tuning set. You can access the workload with theDBMS_SQLTUNEpackageand share it among many Advisor tasks. Because the workload is independent, youmust link it to a task using theDBMS_ADVISOR.ADD_STS_REFprocedure.After this link has been established, you cannot delete or modify the workloaduntil all advisor tasks have removed their dependency on the workload. Aworkload reference is removed when a user deletes a parent advisor task ormanually removes the workload reference from the task by using theDBMS_ADVISOR.DELETE_STS_REFprocedure.

--数据库已SQL tuning set的方式存储workload，所以我们可以使用DBMS_SQLTUNE包来访问workload，并将workload 于advisor tasks 分享。因为workload是独立的，所以我们必须使用DBMS_ADVISOR.ADD_STS_REF过长将workloadlink到task上。 Link 建立之后，我们不能删除或修改workload，知道所有的advisortask 从关联的workload中移除。

当用户删除一个parent advisor task 或者使用DBMS_ADVISOR.DELETE_STS_REF过程手工的移除workload相关的task，此时workload reference 也会移除。

You can create ahypothetical workload from a schema by analyzing dimensions and constraints.For best results, provide a workload as a SQL tuning set. TheDBMS_SQLTUNEpackageprovides several helper functions that can create SQL tuning sets from commonworkload sources, such as the SQL cache, a user-defined workload stored in atable, and a hypothetical workload.

--我们可以使用analyze dimensions 和constraint 从schema中创建一个虚拟的workload。为了更准确的结构，已SQL tuning set 的方式提供一个workload，DBMS_SQLTUNE 包提供了一些帮助函数，其可以从common workload source中创建SQL tuning sets，如SQLcache，用户自定义存储在table里的workload，虚拟机的workload等。

At the time therecommendations are generated, you can apply a filter to the workload torestrict what is analyzed. This restriction provides the ability to generatedifferent sets of recommendations based on different workload scenarios. SQLAccess Advisor parameters control the recommendation process and customizationof the workload. These parameters control various aspects of the process, suchas the type of recommendation required and the naming conventions for what itrecommends.

--在生成recommendations时，可以应用filter 来过滤workload，从而限制其analyze。 这个限制提供了根据不同的workload来生成不同recommendations的能力。 SQL Access Advisor 参数控制recommendation进程和定制workload。 这些参数控制各方面的进程。

To set theseparameters, use theSET_TASK_PARAMETERprocedure. Parameters arepersistent in that they remain set for the life span of the task. When aparameter value is set using SET_TASK_PARAMETER, it does not change until youmake another call to this procedure.

--可以使用SET_TASK_PARAMETER 过程来设置这些参数。

1.3.3Generatethe recommendations

After a taskexists and a workload is linked to the task and theappropriate parameters are set, you can generate recommendations using theDBMS_ADVISOR.EXECUTE_TASKprocedure.These recommendations are stored in SQL Access Advisor Repository.

--创建了task，并将workload link到task之后，在设置好相关的参数，就可以使用DBMS_ADVISOR.EXECUTE_TASK 过程来生成recommendations。 这个recommendations存储在SQL Access Advisor Repository里。

Therecommendation process generates several recommendations. Each recommendationspecifies one or more actions. For example, a recommendation could be to createseveral materialized view logs, create a materialized view, and then analyze itto gather statistics.

--recommendations 进程生成recommendations，每个recommendation指定一个或者多个actions。 例如：一个recommendation可以创建一些物化视图日志，创建物化视图，和收集统计信息。

A task recommendation can range from a simple suggestion to a complex solution thatrequires partitioning a set of existing base tables and implementing a set ofdatabase objects such as indexes, materialized views, and materialized viewlogs. When an advisor task is executed, SQL Access Advisor carefully analyzescollected data and user-adjusted task parameters. It then forms a structuredrecommendation that the user can view and implement.

--Task recommendation 是一个范围，从简单的建议到复杂的解决方案。当advisortask 执行时，SQL Access Advisor 会仔细分析收集数据和用户定义的参数。

1.3.4Viewand implement the recommendations

You can view therecommendations from SQL Access Advisor in either of the following ways:

--可以使用如下2种方法来查看recommendation的内容：

（1）Using thecatalog views

（2）Generating ascript using theDBMS_ADVISOR.GET_TASK_SCRIPTprocedure

In EnterpriseManager, you may display the recommendations after SQL Access Advisor processhas completed. See"ViewingRecommendations"for a description of using the catalog views toview the recommendations. See"GeneratingSQL Scripts"to see how to create a script.

--在OEM中，在SQL Access Advisor 进程处理完毕后会自动显示recommendation。

You need notaccept all recommendations. You can mark the ones to be included in therecommendation script. However, when base table partitioning is recommended,some recommendations depend on others. For example, you cannot implement alocal index if you do not also implement the partitioning recommendation on theindex base table.

The final stepis then implementing the recommendations and verifying that query performancehas improved.

1.3.5 SQLAccess Advisor Repository

All theinformation needed and generated by SQL Access Advisor resides in the Advisorrepository, which is a part of the database dictionary. The benefits of usingthe repository are that it:

--Advisor 生成的所有信息都存放在Advisor repository中，其是数据字典的一部分，使用repository有如下好处：

(1)Collects a complete workloadfor SQL Access Advisor.

(2)Supports historical data.

(3)Is managed by the server.

1.3.6 使用SQLAccess Advisor需要的权限

You must have theADVISORprivilege tomanage or use SQL Access Advisor. When processing a workload, SQL AccessAdvisor attempts to validate each statement to identify table and columnreferences. The database achieves validation by processing each statement as ifit were being executed by the statement's original user.

--必须需要有ADVISOR权限

If the user doesnot haveSELECTprivileges to a particular table, then SQL AccessAdvisor bypasses the statement referencing the table. This behavior can causemany statements to be excluded from analysis. If SQL Access Advisor excludesall statements in a workload, then the workload is invalid. SQL Access Advisorreturns the following message:

QSM-00774, thereare no SQL statements to process for task TASK_NAME

--必须需要有指定表的select 的权限，否则会报QSM-774错误。

To avoid missingcritical workload queries, the current database user must haveSELECTprivilegeson the tables targeted for materialized view analysis. For these tables, theseSELECTprivilegescannot be obtained through a role.

Additionally,you must have theADMINISTER SQL TUNING SETprivilege to create andmanage workloads in SQL tuning set objects. To run the Advisor on SQL tuningsets owned by other users, you must have the ADMINISTER ANY SQL TUNING SETprivilege.

--还需要ADMINISTER SQL TUNING SET的权限来创建和管理workload。

二.手工生成SQLAccess Advisor 示例

From:

http://www.oracle-base.com/articles/10g/SQLAccessAdvisor10g.php

2.1 DBMS_ADVISOR

TheDBMS_ADVISORpackagecan be used to create and execute any advisor tasks, including SQL AccessAdvisor tasks. The following example shows how it is used to create, executeand display a typical SQL Access Advisor script for the current workload.

--DBMS_ADVISOR 包可以用来创建和执行advisor 任务。

DECLARE

l_taskname VARCHAR2(30):= 'test_sql_access_task';

l_task_desc VARCHAR2(128) := 'Test SQL Access Task';

l_wkld_name VARCHAR2(30) := 'test_work_load';

l_saved_rows NUMBER := 0;

l_failed_rows NUMBER := 0;

l_num_found NUMBER;

BEGIN

-- Create an SQLAccess Advisor task.

DBMS_ADVISOR.create_task (

advisor_name => DBMS_ADVISOR.sqlaccess_advisor,

task_name => l_taskname,

task_desc => l_task_desc);

-- Reset the task.

DBMS_ADVISOR.reset_task(task_name => l_taskname);

-- Create a workload.

SELECT COUNT(*)

INTO l_num_found

FROM user_advisor_sqlw_sum

WHERE workload_name =l_wkld_name;

IFl_num_found = 0 THEN

DBMS_ADVISOR.create_sqlwkld(workload_name => l_wkld_name);

ENDIF;

-- Link the workload to the task.

SELECT count(*)

INTO l_num_found

FROM user_advisor_sqla_wk_map

WHERE task_name = l_taskname

AND workload_name =l_wkld_name;

IFl_num_found = 0 THEN

DBMS_ADVISOR.add_sqlwkld_ref(

task_name => l_taskname,

workload_name => l_wkld_name);

ENDIF;

-- Set workload parameters.

DBMS_ADVISOR.set_sqlwkld_parameter(l_wkld_name, 'ACTION_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);

DBMS_ADVISOR.set_sqlwkld_parameter(l_wkld_name,'MODULE_LIST', DBMS_ADVISOR.ADVISOR_UNUSED);

DBMS_ADVISOR.set_sqlwkld_parameter(l_wkld_name, 'SQL_LIMIT',DBMS_ADVISOR.ADVISOR_UNLIMITED);

DBMS_ADVISOR.set_sqlwkld_parameter(l_wkld_name, 'ORDER_LIST', 'PRIORITY,OPTIMIZER_COST');

DBMS_ADVISOR.set_sqlwkld_parameter(l_wkld_name, 'USERNAME_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);

DBMS_ADVISOR.set_sqlwkld_parameter(l_wkld_name, 'VALID_TABLE_LIST',DBMS_ADVISOR.ADVISOR_UNUSED);

DBMS_ADVISOR.import_sqlwkld_sqlcache(l_wkld_name, 'REPLACE', 2,l_saved_rows, l_failed_rows);

-- Set task parameters.

DBMS_ADVISOR.set_task_parameter(l_taskname, '_MARK_IMPLEMENTATION','FALSE');

DBMS_ADVISOR.set_task_parameter(l_taskname, 'EXECUTION_TYPE','INDEX_ONLY');

DBMS_ADVISOR.set_task_parameter(l_taskname, 'MODE', 'COMPREHENSIVE');

DBMS_ADVISOR.set_task_parameter(l_taskname, 'STORAGE_CHANGE',DBMS_ADVISOR.ADVISOR_UNLIMITED);

DBMS_ADVISOR.set_task_parameter(l_taskname, 'DML_VOLATILITY', 'TRUE');

DBMS_ADVISOR.set_task_parameter(l_taskname, 'ORDER_LIST','PRIORITY,OPTIMIZER_COST');

DBMS_ADVISOR.set_task_parameter(l_taskname, 'WORKLOAD_SCOPE','PARTIAL');

DBMS_ADVISOR.set_task_parameter(l_taskname, 'DEF_INDEX_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);

DBMS_ADVISOR.set_task_parameter(l_taskname, 'DEF_INDEX_OWNER',DBMS_ADVISOR.ADVISOR_UNUSED);

DBMS_ADVISOR.set_task_parameter(l_taskname, 'DEF_MVIEW_TABLESPACE',DBMS_ADVISOR.ADVISOR_UNUSED);

DBMS_ADVISOR.set_task_parameter(l_taskname, 'DEF_MVIEW_OWNER', DBMS_ADVISOR.ADVISOR_UNUSED);

-- Execute the task.

DBMS_ADVISOR.execute_task(task_name => l_taskname);

END;

/

-- Display the resultingscript.

SET LONG 100000

SET PAGESIZE 50000

SELECT DBMS_ADVISOR.get_task_script('test_sql_access_task') AS script FROMdual;

SET PAGESIZE 24

The value for theSET LONGcommandshould be adjusted to allow the whole script to be displayed.

在我测试环境上的输入结果如下：

PL/SQL procedure successfully completed.

SCRIPT

--------------------------------------------------------------------------------

Rem SQL AccessAdvisor: Version 10.2.0.4.0 - Production

Rem

Rem Username: SYS

Rem Task: test_sql_access_task

Rem Executiondate: 31/01/2012 21:50

Rem

CREATE BITMAP INDEX "QSOA"."DATA_OA_MESSAGE_IDX$$_167F0001"

ON"QSOA"."DATA_OA_MESSAGE"

("MESS_TYPE")

COMPUTESTATISTICS;

CREATE INDEX"ZHAOKA"."CFG_GAME_AREA_S_IDX$$_167F0004"

ON "ZHAOKA"."CFG_GAME_AREA_SERVER"

("AREA_ID","AREA_NAME","SERVER_ID","SERVER_NAME")

COMPUTESTATISTICS;

….

2.2 Quick Tune

If you just wantto tune an individual statement you can use theQUICK_TUNEprocedureas follows.

--如果仅仅是调整一个独立的语句，可以使用QUICK_TUNE过程：

BEGIN

DBMS_ADVISOR.quick_tune(

advisor_name => DBMS_ADVISOR.SQLACCESS_ADVISOR,

task_name =>'emp_quick_tune',

attr1 => 'SELECT e.*FROM emp e WHERE UPPER(e.ename) = ''SMITH''');

END;

/

Any recommendations can then be displayed using the previous query with the correcttask name specified.

查询输出结果和之前的一样，使用:

Select DBMS_ADVISOR.get_task_script(‘emp_quick_tune’) fromdual;

2.3 Related Views

The followingviews can be used to display the SQL Access Advisor output without usingEnterprise Manager or theget_task_scriptfunction:

--可以使用以下视图来查看advisor的输出：

(1)DBA_ADVISOR_TASKS：Basic information about existingtasks.

(2)DBA_ADVISOR_LOG：Status information about existingtasks.

(3)DBA_ADVISOR_FINDINGS： Findings identified for anexisting task.

(4)DBA_ADVISOR_RECOMMENDATIONS： Recommendations for the problemsidentified by an existing task.

更多的示例参考官网：

SQL Access Advisor

http://docs.oracle.com/cd/E11882_01/server.112/e16638/advisor.htm

-------------------------------------------------------------------------------------------------------

版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!

Blog: http://blog.csdn.net/tianlesoftware

Weibo: http://weibo.com/tianlesoftware

Email: [e-mail]

Skype: tianlesoftware

-------加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请----

DBA1 群：62697716(满); DBA2 群：62697977(满)DBA3 群：62697850(满)

DBA 超级群：63306533(满); DBA4 群：83829929 DBA5群： 142216823

DBA6 群：158654907 DBA7 群：172855474 DBA总群：104207940