用DBMS_ADVISOR.SQLACCESS_ADVISOR创建SQL Access Advisor访问优化建议

        使用OEM方式来创建SQL Access Advisor访问优化建议，已经是四五年的事了，下面就来写写怎样使用DBMS_ADVISOR.SQLACCESS_ADVISOR来创建SQL Access Advisor访问优化建议吧。

1、SQL Access Advisor从三个方面提供优化建议

1.1、索引优化建议

    SQL Access Advisor可以建议通过创建bitmap，function-based，B-tree 三种类型索引的改进SQL执行性能。

1.2、物化视图优化建议

    SQL Access Advisor可以建议通过创建fast refreshable和fullrefreshable刷新方式的物化视图的方式改进SQL执行性能。

1.3、分区优化建议

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

2、SQL Access Advisor的依赖信息

   SQL Access Advisor依赖于表的统计信息。

   本文作者“踩点”做过反复测试，在下文中用到的litest.litest_8表的统计信息删除后，偿试生成优化建议，SQL AccessAdvisor是不会生成优化建议的；重新对litest.litest_8表收集统计信息，SQL Access Advisor即可生成优化建议。

3、创建SQL Access Advisor优化任务及获取建议内容（两种方式任选一种）

3.1 创建优化任务，方式一：

DECLARE

  task_name VARCHAR2(200);

BEGIN

  task_name := 'LI_TASK_9';

  DBMS_ADVISOR.QUICK_TUNE(DBMS_ADVISOR.SQLACCESS_ADVISOR, task_name,

'select * from litest.litest_8 where created>sysdate-50 and created<sysdate-30 and object_id=79420');

END;

/

3.1 创建优化任务，方式二（方式二比方式一更简单直接）：

SQL> execute DBMS_ADVISOR.QUICK_TUNE(DBMS_ADVISOR.SQLACCESS_ADVISOR,'LI_TASK_9','select * from litest.litest_8 where created>sysdate-50 and created<sysdate-30 and object_id=79420');

  两种方式都要注意以下两点：

  （1）、如果SQL文本中本身带有单引号（'）的，需要替换成双引号（'’）;

  （2）、不支持SQL语句中含有SYS和SYSTEM用户下的表对象

3.2 查看上面优化任务创建是否成功以及其基本情况

SQL>select a.owner,a.task_id,a.task_name,execution_start,a.status_message,b.commandfromdba_advisor_log a,dba_advisor_actions bwhere a.task_id=b.task_idand b.task_name='LI_TASK_9'

owner	task_id	task_name	execution_start	status_message	command
SYS	852	LI_TASK_9	2013/9/24 23:55	访问指导执行完毕	CREATE INDEX

    如果表上缺少统计信息或SQL Access Adviso认为无优化建议，将不会生成TASK

3.3 查看使用优化建议前后的资源成本对比信息

SQL> SELECT sql_id, precost 优化前cost, postcost 优化后cost,(precost/postcost) cost提升倍数，decode(PRIORITY,1,'高',2,'中',3,'低') 重要性 FROM dba_advisor_sqla_wk_stmts WHERE task_name='LI_TASK_9';

sql_id	优化前cost	优化后cost	cost提升倍数	重要性
5rr7tx64r1pcs	14165	5	2833	中

3.4 查看建议方案全部内容

3.4.1 创建一个directory目录存放生成的建议方案内容文件（如果已存有可用directory，此步可跳过）

SQL> create directory EXPDP_DIR as '/dba/soft'

3.4.2 生成建议方案内容文件

SQL> exec DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT('LI_TASK_9'),'EXPDP_DIR', 'LI_TASK_9.sql');

3.4.3 查看建议方案文件内容

$cd /dba/soft

$vi LI_TASK_9.sql

Rem  SQL Access Advisor: Version 11.2.0.3.0 - Production

Rem

Rem  Username:        SYS

Rem  Task:            LI_TASK_9

Rem  Execution date:

Rem

CREATE INDEX "LITEST"."LITEST_8_IDX$$_03660000"

    ON "LITEST"."LITEST_8"

    ("OBJECT_ID","CREATED")

    COMPUTE STATISTICS;

  上面的SQL Access Advisor 给出了明确的创建索引的语句，建议在LIST_8表上创建OBJECT_ID+ CREATED字段的联合索引

4、删除SQL Access Advisor优化任务

SQL> exec DBMS_ADVISOR.DELETE_TASK('LI_TASK_9');

      本文由“踩点”所作，转发请说明出处。谢谢！