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

SAA(Sql Access Advisor)工具的使用

SAA可以做什么:
1. 在给定的负载上推荐一些物化视图,物化视图日志和索引;
2. 推荐的索引包括Bitmap索引,基于函数的索引和B-Tree索引;
3. 推荐如何创建物化视图,以至于可以实现快速刷新和查询重写;
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推荐;
使用SAA需要的权限:
1. ADVISOR的系统权限;
2. 目标表的SELECT权限,而且这个权限不能从一个角色中获得;
使用SAA工具:
1. 执行负载;
2. 使用EM中图形化界面;
3. 使用脚本调用DMBS_ADVISOR包;
使用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. 生成脚本;
如果只是想调优一条SQL语句的话,可以执行快速调优,使用DBMS_ADVISOR.QUICK_TUNE()过程;
对于物化视图的优化:
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的快速调优 ——————————–

royalwzy的学习笔记