性能调优工具02–Sql Access Advisor工具的使用

6月 1st, 2014

SAA(Sql Access Advisor)工具的使用

  1. SAA可以做什么:
    1. 在给定的负载上推荐一些物化视图,物化视图日志和索引;
    2. 推荐的索引包括Bitmap索引,基于函数的索引和B-Tree索引;
    3. 推荐如何创建物化视图,以至于可以实现快速刷新和查询重写;
  2. SAA的使用模型:
    1. SQL的来源:
      1. SQL Cache:即当前v$sql视图中记录的sql语句;
      2. User-defined:自定义一个表,记录执行的sql语句和执行sql语句的用户,然后把这个表传给SAA;
      3. STS:使用Sql Tuning Set,主要是从负载中获得;
    2. 给出的建议:
      1. Simultaneously considers index solutions, materialized view solutions, or combinations of both;
      2. Considers storage for creation and maintenance costs;
      3. Does not generate drop recommendations for partial workloads;
      4. Optimizes materialized views for maximum query rewrite usage and fast refresh;
      5. Recommends materialized view logs for fast refresh;
      6. Combines similar indexes into a single index;
      7. Generates recommendations that support multiple workload queries;
    3. 架构图;                                                                                          
    4. 推荐的两种方式:Comprehensive和Limited;
      1. Add new index on table or materialized view;
      2. Drop an unused index;
      3. Modify an existing index by changing the index type;
      4. Modify an existing index by adding columns at the end;
      5. Add a new materialized view;
      6. Drop an unused materialized view;
      7. Add a new materialized view log;
      8. Modify an existing materialized view log to add new columns or clauses;
      9. Comprehensive可以分析1-8所有的推荐;
      10. Limited只能分析1,4,5,7,8推荐;
  3. 使用SAA需要的权限:
    1. ADVISOR的系统权限;
    2. 目标表的SELECT权限,而且这个权限不能从一个角色中获得;
  4. 使用SAA工具:
    1. 执行负载;
    2. 使用EM中图形化界面;
    3. 使用脚本调用DMBS_ADVISOR包;
  5. 使用DMBS_ADVISOR包:
    1. 步骤:
      1. 创建一个任务,并定义参数;
      2. 定义负载;
      3. 生成一些建议;
      4. 查看并应用建议;
    2. SAA的工作流;
    3. 创建一个任务:DBMS_ADVISOR.CREATE_TASK(DBMS_ADVISOR.SQLACCESS_ADVISOR, :task_id, :task_name);
    4. 设置SAA的参数:
      1. 设置任务的参数:DBMS_ADVISOR.SET_TASK_PARAMETER(:task_name, parameter, value);
      2. 设置负载的参数:DBMS_ADVISOR.SET_SQLWKLD_PARAMETER(:workload_name, parameter, value);
    5. 创建模板:DBMS_ADVISOR.CREATE_TASK(DBMS_ADVISOR.SQLACCESS_ADVISOR, :task_id, :task_name, template=>:template_name, is_template=>’TRUE’);
    6. 创建一个负载:DBMS_ADVISOR.CREATE_SQLWKLD(:workload_name);
      1. SQL Tuning Sets:DBMS_ADVISOR.IMPORT_SQLWKLD_STS();
      2. 用户自定义负载:DBMS_ADVISOR.IMPORT_SQLWKLD_USER();
      3. SQL Cache:DBMS_ADVISOR.IMPORT_SQLWKLD_SQLCACHE();
      4. 单条的SQL语句:DBMS_ADVISOR.ADD_SQLWKLD_STATEMENT();
      5. 某个用户下的SQL语句:DBMS_ADVISOR.IMPORT_SQLWKLD_SCHEMA();
    7. 删除负载:DBMS_ADVISOR.DELETE_SQLWKLD(:workload_name);
    8. 任务与负载关联:DBMS_ADVISOR.ADD_SQLWKLD_REF(:task_name, :workload_name);
    9. 生成建议并把建议保存在SAA的资源库:DBMS_ADVISOR.EXECUTE_TASK(:task_name);
      1. 相应的视图为:dba/user_advisor_recommendations;
      2. 或者使用dbms_advisor.get_task_script生成相应的脚本;
    10. SAA的主要建议有:
      1. 创建/删除物化视图;
      2. 创建/删除物化视图日志;
      3. 创建/删除索引;
      4. 收集统计信息;
    11. 生成SQL脚本:
      1. 创建DIRECTORY;
      2. 授权给用户;
      3. 生成脚本;
  6. 如果只是想调优一条SQL语句的话,可以执行快速调优,使用DBMS_ADVISOR.QUICK_TUNE()过程;
  7. 对于物化视图的优化:
    1. DBMS_MVIEW.EXPLAIN_MVIEW:查看使用/不使用物化视图的原因;
    2. DBMS_MVIEW.EXPLAIN_REWRITE:为没有么有使用查询重写,如果使用了,使用的哪个物化视图;
    3. DBMS_ADVISOR.TUNE_MVIEW:生成快速刷新物化视图和生成查询重写的建议;
——————————– 使用SAA工具 ——————————–
— 1.执行负载;
CONNECT / AS SYSDBA;
ALTER USER sh IDENTIFIED BY oracle ACCOUNT UNLOCK;
GRANT DBA TO sh;
ALTER SYSTEM FLUSH shared_pool;
ALTER SYSTEM FLUSH buffer_cache;
CONNECT sh/oracle;
SET autotrace traceonly stat;

SELECT c.cust_last_name, SUM(s.amount_sold) AS dollars, SUM(s.quantity_sold) AS quantity
FROM sales s, customers c, products p
WHERE c.cust_id = s.cust_id AND
s.prod_id = p.prod_id AND
c.cust_state_province IN (‘Dublin’, ‘Galway’)
GROUP BY c.cust_last_name;

SELECT c.cust_id, SUM(amount_sold) AS dollar_sales
FROM sales s, customers c
WHERE s.cust_id = c.cust_id
GROUP BY c.cust_id;

SELECT SUM(unit_cost) FROM costs GROUP BY prod_id;
SELECT * FROM customers WHERE cust_postal_code = ‘83786’;

— 2.使用EM中的SAA工具;
1.Databaes Instance->Advisor Central->SQL Access Advisor;
2.选择SQL语句的来源:选择从内存中的SQL语句;
3.选择推荐的选项:生成索引和物化视图,并使用综合模式;
4.系统创建了一个作业,指定立即执行;
5.查看作业已完成;
6.然后查看和采用相应的建议;
— 3.使用脚本调用DBMS_ADVISOR包;
1.定义变量;
VARIABLE v_task_id NUMBER;
VARIABLE v_task_name VARCHAR2(255);
VARIABLE v_wkld_name VARCHAR2(255);
2.指定任务的名称和负载的名称;
EXECUTE :v_task_name := ‘my_task’;
EXECUTE :v_wkld_name := ‘my_sql_wkld’;
3.删除之前的任务和负载;
BEGIN
dbms_advisor.delete_sqlwkld_ref(:v_task_name, :v_wkld_name);
dbms_advisor.delete_sqlwkld(:v_wkld_name);
dbms_advisor.delete_task(:v_task_name);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
4.创建一个任务;
EXECUTE DBMS_ADVISOR.CREATE_TASK(DBMS_ADVISOR.SQLACCESS_ADVISOR, :v_task_id, :v_task_name);

5.查看当天前的优化任务;
SELECT * FROM user_advisor_tasks;

6.设置任务的参数,生成所有建议,并使用综合模式;
EXECUTE dbms_advisor.set_task_parameter(:v_task_name, ‘EXECUTION_TYPE’, ‘FULL’);
EXECUTE dbms_advisor.set_task_parameter(:v_task_name, ‘MODE’, ‘COMPREHENSIVE’);

7.创建一个负载;
EXECUTE dbms_advisor.create_sqlwkld(:v_wkld_name);
8.把任务与负载关联;
EXECUTE dbms_advisor.add_sqlwkld_ref(:v_task_name, :v_wkld_name);

9.查看任务与负载的映射关系;
SELECT * FROM user_advisor_sqla_wk_map;

10.导入负载,使用SQL Cache的模式;
VARIABLE v_saved_stmts NUMBER;
VARIABLE v_failed_stmts NUMBER;
EXECUTE DBMS_ADVISOR.IMPORT_SQLWKLD_SQLCACHE(:v_wkld_name, ‘APPEND’, 2, :v_saved_stmts, :v_failed_stmts);

11.查看负载中的SQL语句;
SELECT * FROM user_advisor_sqlw_stmts;
12.执行任务,生成优化建议;
EXECUTE DBMS_ADVISOR.EXECUTE_TASK(:v_task_name);
13.查看优化建议;
SELECT * FROM user_advisor_recommendations;
14.生成优化建议的脚本;
直接查看:SELECT DBMS_ADVISOR.GET_TASK_SCRIPT(:v_task_name) FROM dual;
或者创建目录,生成建议的脚本;
CONNECT / AS SYSDBA;
DROP DIRECTORY ADVISOR_RESULTS;
CREATE OR REPLACE DIRECTORY ADVISOR_RESULTS AS ‘/home/oracle’;
GRANT READ, WRITE ON DIRECTORY ADVISOR_RESULTS TO sh;
CONNECT SH/ORACLE
EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:v_task_name), ‘ADVISOR_RESULTS’, ‘advscript.sql’);
——————————– 使用SAA工具 ——————————–
——————————– SAA的快速调优 ——————————–
1.定义变量;
CONNECT sh/oracle;
VARIABLE v_task_name VARCHAR2(30);
EXECUTE :v_task_name := ‘quick_task’;
2.执行快速调优,只能执行一条SQL语句,默认是在当前用户下执行;
EXECUTE dbms_advisor.quick_tune(dbms_advisor.sqlaccess_advisor, :v_task_name, ‘SELECT AVG(amount_sold) FROM sh.sales WHERE promo_id=10’);
3.查看优化建议结果;
SELECT DBMS_ADVISOR.GET_TASK_SCRIPT(:v_task_name) FROM dual;
——————————– SAA的快速调优 ——————————–
标签:
本文的评论功能被关闭了.