• 1. Oracle数据库性能优化实务 第13讲:SQL优化工具主讲人:白鳝华章培训网、[www.hztraining.com]华章培训网版权所有
  • 2. 高手是不用工具的人吗?工具可以帮助人思考 工具可以提高工作效率 工具可以防止信息被忽略 工具避免记一些难记的东西 ...... 结论:工具不是万能的,但是离开工具是万万不能的
  • 3. SQL优化需要哪些工具TOP SQL抓取类工具 会话跟踪类工具 SQL分析类工具 PL/SQL分析类工具 辅助性工具(比如思考树、决策树工具)
  • 4. TOP SQL抓取类工具根据需要抓取 TOP SQL 物理读、逻辑读、执行次数、解析次数、CPU开销、执行时间等 基本都是通过对V$SQLAREA进行分析 抓住系统中最为关键的SQL
  • 5. TOP SQL抓取查找TOP SQL的主要工具 AWR/STATSPACK/ADDM/ASH报告 EM ADDM分析 SQLA(来自METALINK的工具) V$SQL/V$SQLAREA Oracle 9i SQLANALYZER Oracle 9i EM TOP SQL TRCA 第三方工具
  • 6. 会话跟踪类工具SQL_TRACE和10046事件 跟踪某个会话的详细情况 通过TKPROF或者TRCA格式化 其他会话跟踪工具 旁路网络抓包类(比如IMPREVA) 审计类 V$SQLAREA分析类工具
  • 7. SQL分析类工具开销分析 ADDM/awrsqrpt/sprepsql set autotrace 执行计划分析 awrsqrpt/sprepsql explain set autotrace 10046事件 EM ADDM 9i sql analyzer PL/SQL DEVELOPER等第三方工具
  • 8. 设置AWR中的TOP SQL属性DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS ( - retention IN NUMBER DEFAULT NULL, interval IN NUMBER DEFAULT NULL, topnsql IN NUMBER DEFAULT NULL);
  • 9. 在EM里生成AWR报告
  • 10. ASH报告
  • 11. ADDM在SQL优化中的应用ADDMSQL 优化建议SQL Access AdvisorMemorySpacePGA 建议SGASegment 建议Undo 建议Buffer Cache 建议Library Cache 建议PGA
  • 12. 用ADDM优化SQL修改任务参数是否接受建议实施优化建议YesNo提交任务进行分析创建一个优化建议任务AWR
  • 13. 自动SQL优化过程I can do it for you!SQL Tuning AdvisorDBA高开销SQLADDMSQL workload
  • 14. SQL优化建议添加索引重构SQL执行计划优化 (SQL profile)自动优化器SQL Analysis Optimization modeAccess Analysis Optimization modePlan Tuning Optimization modeStatistics Check Optimization mode发现过期或者缺少统计数据综合SQL调优SQL Tuning Advisor
  • 15. SQL访问路径的建议YESYES修改某个物化视图,添加新的列或者其他属性YESYES添加物化视图日志NOYES删除不使用的物化视图YESYES添加物化视图YESYES在索引尾部添加字段NOYES修改索引的类型NOYES删除不使用的索引YESYES索引添加建议PartialFull建议
  • 16. 优化建议的例子
  • 17. 优化建议回顾
  • 18. 课间休息休息一下,后面更精彩
  • 19. 通过案例学习EM优化SQL(1)
  • 20. 通过案例学习使用EM优化SQL(2)SQL Details
  • 21. 通过案例学习使用EM优化SQL(3)老执行计划
  • 22. 通过案例学习使用EM优化SQL(4)
  • 23. 通过案例学习使用EM优化SQL(5)
  • 24. EXPLAINSET STATEMENT_ID = 'text'INTO your plan tableEXPLAIN PLANFOR statement
  • 25. EXPLAIN的例子SQL> EXPLAIN PLAN 2 set statement_id = 'demo01' for 3 select * 4 from products 5 where prod_category = 'Men' 6 and prod_subcategory = 'Jeans - Men'; Explained.
  • 26. 显示EXPLAIN结果SQL> column "Query Plan" format a60 SQL> select id 2 , lpad(' ', 2*level)||operation 3 ||decode(id,0,' Cost = '||position) 4 ||' '||options 5 ||' '||object_name as "Query Plan" 6 from plan_table 7 where statement_id = 'demo01' 8 connect by prior id = parent_id 9 start with id = 0;@?/rdbms/admin/utlxpls.sql @?/rdbms/admin/utlxplp.sql
  • 27. 显示的例子SQL> select id 2 , lpad(' ',2*level)||operation|| 3 decode(id, 0,' Cost = '||position) 4 ||' '||options 5 ||' '||object_name as "Query Plan" 6 from plan_table 7 where statement_id = 'demo01' 8 connect by prior id = parent_id start with id=0; ID Query Plan ----- ----------------------------------------- 0 SELECT STATEMENT Cost = 1 TABLE ACCESS BY INDEX ROWID PRODUCTS 2 AND-EQUAL 3 INDEX RANGE SCAN PRODUCTS_PROD_CAT_IX 4 INDEX RANGE SCAN PRODUCTS_PROD_SUBCAT_IX
  • 28. 阅读执行计划1234TABLE ACCESS (BY INDEX ROWID) classesAND_EQUALINDEX (RANGE_SCAN) Prod category indexINDEX (RANGE_SCAN) Prod subcategory index
  • 29. 使用V$SQL_PLAN查询最近执行的SQL的执行计划 和EXPLAIN的结果很类似: EXPLAIN PLAN估算某个SQL可能的执行计划 V$SQL_PLAN包含真实的执行计划
  • 30. 使用V$SQL_PLANSELECT id , lpad (' ', depth) || operation operation , options , object_name , optimizer , cost FROM V$SQL_PLAN WHERE hash_value = 912244748 AND address = '67D419DC' START WITH id = 0 CONNECT BY ( prior id = parent_id AND prior hash_value = hash_value AND prior child_number = child_number ) ORDER SIBLINGS BY id, position;ID OPERATION OPTIONS OBJECT_NAME OPT COST -- --------------- --------------- ---------------------- --- ----- 0 SELECT STATEMENT CHO 1 TABLE ACCESS BY INDEX ROWID PRODUCTS 2 AND-EQUAL 3 INDEX RANGE SCAN PRODUCTS_PROD_CAT_IX 4 INDEX RANGE SCAN PRODUCTS_PROD_SUBCAT_I
  • 31. DBMS_XPLAN包(10.2)DBMS_XPLAN.DISPLAY_CURSOR( sql_id IN VARCHAR2 DEFAULT NULL, child_number IN NUMBER DEFAULT NULL, format IN VARCHAR2 DEFAULT 'TYPICAL'); 未公开的FORMAT参数‘ADVANCED','PEEKED_BINDS' SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED')); PLAN_TABLE_OUTPUT SQL_ID 148pt8ma9936r, child number 0 select * from dual where dummy=:a Plan hash value: 272002086 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | |* 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 | -------------------------------------------------------------------------- PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- 1 - "DUMMY"[VARCHAR2,1]
  • 32. SQL*PLUS AUTOTRACESET AUTOTRACEOFFONTRACE[ONLY]EXPLAINSTATISTICSSHOW AUTOTRACE
  • 33. AUTOTRACE的例子启用AUTOTRACE跟踪SQL: 不显示执行结果: 只显示执行计划: set autotrace onset autotrace traceonlyset autotrace traceonly explain
  • 34. 只显示SQL执行统计数据set autotrace traceonly statistics select * from dual; Statistics --------------------------------------------------- 0 recursive calls 2 db block gets 1 consistent gets 0 physical reads 0 redo size 367 bytes sent via SQL*Net to client 430 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
  • 35. 课间休息休息一下,后面更精彩
  • 36. PL/SQL分析工具PROFILER用于对PL/SQL对象进行分析 可以分析出PL/SQL对象中每一行的执行情况 有助于发现PL/SQL中的性能瓶颈 ORACLE自带工具
  • 37. 安装PROFILERsqlplus '/as sysdba' sql>@?/rdbms/admin/profload sqlplus scott/tiger sql>@?/rdbms/admin/proftab
  • 38. 使用PROFILERdeclare err number; begin err:=DBMS_PROFILER.START_PROFILER (to_char(sysdate,'dd-Mon-YYYY hh:mi:ss')); --此处执行要测试的PL/SQL存储过程或者程序 err:=DBMS_PROFILER.STOP_PROFILER ; end; / column unit_name format a15 truncate; column occured format 999999 ; column line# format 99999 ; column tot_time format 999.999999 ; select p.unit_name, p.occured, p.tot_time, p.line# line, substr(s.text, 1,75) text from (select u.unit_name, d.TOTAL_OCCUR occured, (d.TOTAL_TIME/1000000000) tot_time, d.line# from plsql_profiler_units u, plsql_profiler_data d where d.RUNID=u.runid and d.UNIT_NUMBER = u.unit_number and d.TOTAL_OCCUR >0 and u.runid= &RUN_ID) p, user_source s where p.unit_name = s.name(+) and p.line# = s.line (+) order by p.unit_name, p.line#;
  • 39. PROFILER的例子模块 执行时间 行号 语句 TESTPLSQL .000350 9 anumber:=0; TESTPLSQL .358233 11 anumber:=anumber+1; TESTPLSQL .358399 12 ainteger:=ainteger+1; TESTPLSQL .363219 13 anumber_10:=anumber_10+1; TESTPLSQL .133521 14 apls_integer:=apls_integer+1; TESTPLSQL .498786 15 abinary:=abinary+1; TESTPLSQL .511632 16 exit when anumber>1500 ;
  • 40. 实验(1)安装PROFILER$ sqlplus '/as sysdba' SQL*Plus: Release 10.2.0.4.0 - Production on Sun Dec 5 19:55:36 2010 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, Oracle Label Security, OLAP, Data Mining and Real Application Testing options SQL> @?/rdbms/admin/profload Package created. Grant succeeded. Synonym created. Library created. Package body created. Testing for correct installation SYS.DBMS_PROFILER successfully loaded. PL/SQL procedure successfully completed.
  • 41. 实验(2)安装PROFILER[oracle@justdb ~]$ sqlplus scott/****** SQL*Plus: Release 10.2.0.4.0 - Production on Sun Dec 5 20:05:36 2010 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, Oracle Label Security, OLAP, Data Mining and Real Application Testing options SQL> @?/rdbms/admin/proftab Table created. Comment created. Table created. Comment created. Table created. Comment created. Sequence created.
  • 42. 实验(3)创建测试环境CREATE SEQUENCE SM_IDSEQ START WITH 1311001 MAXVALUE 10000000 MINVALUE 1 CYCLE CACHE 1000 NOORDER;
  • 43. 实验(4)创建测试环境create or replace procedure testSeq(N integer) is i integer; b integer; v varchar2(20); begin i:=0; v:=to_char(sysdate,'yyyy-mm-dd:hh24:mi:ss'); dbms_output.put_line(v); loop exit when i>N; i:=i+1; select sm_idseq.nextval into b from dual; end loop; v:=to_char(sysdate,'yyyy-mm-dd:hh24:mi:ss'); dbms_output.put_line(v); end; /
  • 44. 实验(5)测试1declare err number; begin err:=DBMS_PROFILER.START_PROFILER ('SEQ CACHE IS 1k '); testseq(20000); err:=DBMS_PROFILER.STOP_PROFILER ; end; /
  • 45. 实验(6)测试结果1column RUN_COMMENT format a40 truncate; select runid, run_date, RUN_COMMENT from plsql_profiler_runs order by runid; column unit_name format a15 truncate; column occured format 999999 ; column line# format 99999 ; column tot_time format 999999.999999 ; select p.unit_name, p.occured, p.tot_time, p.line# line, substr(s.text, 1,75) text from (select u.unit_name, d.TOTAL_OCCUR occured, (d.TOTAL_TIME/1000000000) tot_time, d.line# from plsql_profiler_units u, plsql_profiler_data d where d.RUNID=u.runid and d.UNIT_NUMBER = u.unit_number and d.TOTAL_OCCUR >0 and u.runid= &RUN_ID) p, user_source s where p.unit_name = s.name(+) and p.line# = s.line (+) order by p.unit_name, p.line#;
  • 46. 实验(7)测试结果1 UNIT_NAME OCCURED TOT_TIME LINE TEXT --------------- ------- -------------- ---------- ------------------------- 1 .000027 5 1 .000003 6 TESTSEQ 1 .000002 7 i:=0; TESTSEQ 1 .000067 8 v:=to_char(sysdate,'yyyy-mm-dd:hh24:mi:ss'); TESTSEQ 1 .000022 9 dbms_output.put_line(v); TESTSEQ 20002 .021051 11 exit when i>N; TESTSEQ 20001 .025320 12 i:=i+1; TESTSEQ 20001 .603252 13 select sm_idseq.nextval into b from dual; TESTSEQ 1 .000026 15 v:=to_char(sysdate,'yyyy-mm-dd:hh24:mi:ss'); TESTSEQ 1 .000003 16 dbms_output.put_line(v); TESTSEQ 1 .000003 17 end;
  • 47. 实验(8)准备数据2drop sequence sm_idseq; CREATE SEQUENCE SM_IDSEQ START WITH 1311001 MAXVALUE 10000000 MINVALUE 1 CYCLE CACHE 10000 NOORDER;
  • 48. 实验(9)测试2declare err number; begin err:=DBMS_PROFILER.START_PROFILER ('SEQ CACHE IS 10k '); testseq(20000); err:=DBMS_PROFILER.STOP_PROFILER ; end; /
  • 49. 实验(10)测试结果2UNIT_NAME OCCURED TOT_TIME LINE TEXT --------------- ------- -------------- ---------- ----------------------------- 1 .000057 5 1 .000003 6 TESTSEQ 1 .000002 7 i:=0; TESTSEQ 1 .000030 8 v:=to_char(sysdate,'yyyy-mm-dd:hh24:mi:ss'); TESTSEQ 1 .000022 9 dbms_output.put_line(v); TESTSEQ 20002 .021077 11 exit when i>N; TESTSEQ 20001 .025123 12 i:=i+1; TESTSEQ 20001 .589939 13 select sm_idseq.nextval into b from dual; TESTSEQ 1 .000024 15 v:=to_char(sysdate,'yyyy-mm-dd:hh24:mi:ss'); TESTSEQ 1 .000003 16 dbms_output.put_line(v); TESTSEQ 1 .000002 17 end;
  • 50. 案例分享-PROFILER的特殊用途一个典型的性能优化案例 只有不到10个小时的时间 可能涉及操作系统、存储、数据库、应用等 应用是一个黑匣子 如何进行分析?
  • 51. 案例的启示正确的分析路径 活用工具 走捷径的想法可能导致走更多的路
  • 52. 课间休息休息一下,后面更精彩
  • 53. 用新的方法来优化SQL10g是管理十分方便的数据库 11g进一步提升了数据库优化工具 oracle自带的优化工具十分强大 awrsqrpt.sql Sql Tuning Advisor SQL Performance Analyzer
  • 54. awrsqrpt.sql类似STATSPACK的sprepsql.sql(6级以上SNAP) 在AWR自动采集的基础上 缺省保留7天 建议长期保留 典型的SNAP设置为基线
  • 55. awr SQL报告(1)
  • 56. awr SQL报告(2)
  • 57. awr SQL报告(3)
  • 58. awr SQL报告(4)
  • 59. Sql Tuning Advisor判断是否出现统计数据失效或者缺失 查找更好的执行计划 查找更好的访问路径 重构 SQL 通过EM或者DBMS_SQLTUNE包
  • 60. 使用步骤创建优化任务 执行优化任务 查看建议 确认建议(实施建议)
  • 61. 创建优化任务DBMS_SQLTUNE DECLARE my_task_name VARCHAR2(30); my_sqltext CLOB; BEGIN my_sqltext := 'SELECT * ' || 'FROM sales ' || 'WHERE prod_id = 10 AND ' || 'cust_id = 100 '; my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_text => my_sqltext, user_name => 'SH', scope => 'COMPREHENSIVE', time_limit => 60, task_name => 'TEST_sql_tuning_task', description => 'Sample Task'); END;
  • 62. 执行优化任务Execute dbms_sqltune.Execute_tuning_task (task_name => 'TEST_sql_tuning_task');
  • 63. 查看优化建议set long 65536 set longchunksize 65536 set linesize 100 select dbms_sqltune.report_tuning_task('TEST_sql_tuning_task') from dual;
  • 64. 查看优化建议------------------------------------------------------------------------------- SQL ID : 9bxw71yp99fr6 -------------------------------------------------------------------------------- SQL Text: SELECT * FROM sales WHERE prod_id = 10 AND cust_id = 100 ------------------------------------------------------------------------------- FINDINGS SECTION (5 findings) ------------------------------------------------------------------------------- 1- Statistics Finding --------------------- Index "SH"."SALES_PROMO_BIX" was not analyzed. Recommendation Consider collecting optimizer statistics for this index. execute dbms_stats.gather_index_stats(ownname => 'SH', indname => 'SALES_PROMO_BIX', estimate_percent =>
  • 65. SQL PERFORMANCE ANALYZER(SPA)捕获相关SQL,形成SQL Tuning Set (STS). 修改前评估性能 修改 修改后再次评估性能 性能比较
  • 66. 通过例子学习手工使用SPA(1)-创建STSvar sts_name varchar2(30); exec :sts_name := 'small_sh_sts_4'; exec dbms_sqltune.drop_sqlset(:sts_name); exec dbms_sqltune.create_sqlset(:sts_name, 'small demo workload to test SQLPA'); DECLARE stscur dbms_sqltune.sqlset_cursor; BEGIN OPEN stscur FOR SELECT VALUE(P) FROM TABLE(dbms_sqltune.select_cursor_cache( 'sql_text like ''SELECT /*+ my_query%''', null, null, null, null, null, null, 'ALL')) P; -- populate the sqlset dbms_sqltune.load_sqlset(:sts_name, stscur); end; /
  • 67. var tname varchar2(30); var sname varchar2(30); -- init vars exec :sname := 'small_sh_sts_4'; exec :tname := 'my_sqlpa_demo_task'; -- 1. create a task with a purpose of change impact analysis exec :tname := dbms_sqlpa.create_analysis_task(sqlset_name => :sname, task_name => :tname);通过例子学习手工使用SPA(2)-创建SPA任务
  • 68. 通过例子学习手工使用SPA(3)-执行分析 begin DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name => 'my_sqlpa_demo_task', execution_type => 'TEST EXECUTE', execution_name => 'BEFORECHANGE'); end; /
  • 69. 通过例子学习手工使用SPA(4)-修改比如alter system set optimizer_features_enable='11.1.0.6';
  • 70. begin DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name => 'my_sqlpa_demo_task', execution_type => 'TEST EXECUTE', execution_name => 'AFTERCHANGE'); end; / 通过例子学习手工使用SPA(5)-再次分析
  • 71. begin DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( task_name => 'my_sqlpa_demo_task', execution_type => 'COMPARE PERFORMANCE', execution_name => 'DEMOTASK', execution_params => dbms_advisor.arglist( 'comparison_metric', 'buffer_gets')); end; / 通过例子学习手工使用SPA(6)-分析
  • 72. set long 100000 longchunksize 100000 linesize 200 head off feedback off echo off spool report.html SELECT dbms_sqlpa.report_analysis_task('my_sqlpa_demo_task', 'HTML', 'ALL','ALL') FROM dual; spool off 通过例子学习手工使用SPA(7)-生成报告
  • 73. 报告(1)
  • 74. 报告(2)
  • 75. 报告(3)
  • 76. 在EM中使用SPA图形界面更加方便 组件STS更加灵活 报告更为直观 和其他功能集成
  • 77. 课间休息休息一下,后面更精彩下节预告:SPA的案例 场景:需要调整optimizer_index_cost_adj ,担心调整会对系统整体性能产生不利影响,如何解决?
  • 78. 案例分析阿勒普 南达(Arup Nanda)的一个案例 一个经典的场景 Oracle的CBO不使用索引 不敢调整表分析策略,怕引起其他问题 测试调整optimizer_index_cost_adj可以解决问题 担心这个参数修改的负面影响 如何解决这个问题?
  • 79. 分析思路找出系统中对业务影响最大的重要SQL,组织STS 创建SPA TASK,进行分析 生成报告,比对参数调整前后的差异
  • 80. 操作过程简介(1)第一步:点击“性能”(Performance)选择页
  • 81. 操作过程简介(2)第二步:“查找SQL”(Search sessions):查找需要分析的SQL,创建STS
  • 82. 操作过程简介(3)第三步:选择刚才定义的STS,进行管理(增,删)
  • 83. 操作过程简介(4)第四步: 启动SPA,创建SPA任务
  • 84. 操作过程简介(5)第五步:修改SPA任务参数
  • 85. 操作过程简介(5)说明TASK NANME:任务名 SQL TUNING SET:你准备好的STS PARAMETER CHANGE:你准备做的参数调整,可以设置多个参数调整 EVALUATION:你准备比较的性能指标 SCHEDULE:分析计划,可选择立即执行
  • 86. 操作过程简介(6)第六步:比较报告
  • 87. 操作过程简介(6)续
  • 88. 操作过程简介(6)续
  • 89. 你可能需要的其他SQL优化工具压力测试和基准测试工具:HAMMERORA、ORABM、Swingbench等 SQL语句格式化工具:PL/SQL DEVELOPER、TOAD等 文档编写和PDF文件生成工具:推荐WPS
  • 90. HAMMERORA开源的ORACLE压力测试工具 开源项目主页:http://hammerora.sourceforge.net/ 主要功能: 通过TCL编写测试用例 可以将10046 TRACE自动生成oratcl脚本,通过回放工具进行大规模并发操作模拟 包含一个TPC-C基准测试程序
  • 91. Swingbench开源的oracle压力测试工具 支持Oracle 9i,10g,11g 基于JAVA 项目主页:http://www.dominicgiles.com/ 主要功能 图形化的Oracle压力测试工具 提供TRACE ANALYZER来分析TRACE,生成测试脚本 提供DATA GENERATOR来生成测试数据 提供一系列工具用于测试时的系统监控
  • 92. Swingbench和RAC
  • 93. 关于SQL优化工具的小结就像工具是人手的延展一样,优化工具应该成为DBA大脑的延展 忽视工具的使用会导致事倍功半 平时要注意寻找适合自己的工具 长期使用才能让工具发挥最大功能 用新的工具和方法管理新版本的数据库
  • 94. 下节预告-索引与访问路径介绍ORACLE索引的基本概念 SQL如何访问数据 索引设计的一些基本原则
  • 95. 感谢您对华章培训网的支持!http://www. hztraining.com