简单对比db2-9.7及ora-10g.ppt

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,2013.04.08,数据库调优,内容提要,性能调优概述,数据库逻辑设计优化,SQL,调优,案例,Q&A,性能调优概述,随着数据库在行方的使用不断增长，客户也不断对部门产品的应用提出了高性能要求。数据库性能调优是知识密集型的学科，需要综合考虑各种复杂的因素：数据库缓冲区的大小、索引的创建、语句改写等等。总之，数据库性能调优的目的在于使系统运行得更快。,鉴于目前,ORACLE,，,DB2,在行方应用较广，本文将以,Oracle(Oracle 10g),，,DB2,（,V9.7,）为数据库平台,讲述数据库调优应遵循的一般步骤、针对这两种数据库的优化点以及进行实例分析。,性能调优概述,性能,问题的,症状,响应时间,慢,吞吐量,低,资源,占用高,(CPU,、,Memory,、,I/O,等,),调优是个系统工程,存储,系统,中间件,数据库,应用程序,数据库,角度,数据库逻辑设计（分区、索引、表空间。）,数据库物理设计（存储规划）,SQL,语句,数据库,逻辑设计优化,海量数据库逻辑设计优化,ORACLE 10g,DB2 v9.7,索引,索引,物化视图,物化视图,表分区（范围）,表分区,表分区（,hash,）,数据库分区（,DPF,）,表分区（列表）,多维聚簇（,MDC,）,复合表分区,索引,-,创建,索引,对于提高,SQL,读有无可替代,作用,提高,查询性能,避免全表扫描,减少排序,减少,CPU,使用及,I/O,占用,索引,-,创建,索引创建注意点,为,where,查询条件、,Sort,排序,(order,by,、,max(),、,min,(),等）、,join,谓词创建,索引,注意组合索引键的顺序，如,(a,b),(b,a),完全不同,不要,创建冗余索引，如,(a),(a,b)(a),为冗余索引,确保,索引被用到,ORACLE 10g,DB2 v9.7,使用,Hint,强制走索引,对于不稳定数据，可考虑用,volatile,强制走索引,SQL Access advise,使用,db2advis建议索引,位图索引（,bitmap-index,）,通过,include,语句创建,index-only,索引,删除未用索引,索引,的,缺点,牺牲增删改性能,占用存储空间,增加运维负担,删除未用索引,ORACLE 10g,DB2 v9.7,Last used in indexes,Last used in syscat.indexes,分区设计,表分区原则,对于大表进行分区，将有益于大表操作的性能和大表的数据维护。通常当表的大小超过,1.5GB,2GB,，或对于,OLTP,系统，表的记录超过,1000,万，都应考虑对表进行分区。,基于表的大部分查询应用，只访问表中少量的数据。对于这样表进行分区，可充分利用分区排除无关数据查询的特性。,某些表的数据维护，经常按时间段删除成批的数据，例如按月删除历史数据。对于这样的表需要考虑进行分区，以满足维护的需要。,如果一个表中大部分数据都是只读数据，通过对表进行分区，可将只读数据存储在只读表空间中,对于经常执行并行操作（如,Parallel Insert,Parallel Update,等）的表应考虑进行分区。,分区设计,DB2 v9.7,的分区设计,IBM DB2,的分区特性包括表分区,(Table Partition),、多维聚类,(MDC),和数据库分区特性,(DPF),。具体对比如下表：,分区设计,DB2,表分区设计特性,简要对比,分区设计,DB2,表分区设计特性,事实表特征,分区设计,ORACLE 10g,分区类型,（,1,）范围分区（,range,）；,（,2,）哈希分区（,hash,）；,（,3,）列表分区（,list,）；,（,4,）范围哈希复合分区（,range-hash,）；,（,5,）范围列表复合分区（,range-list,）。,物化视图,视图的物理化,基本原理是将某些耗费资源的聚集、分组或多表联合等复杂操作事先计算出来，并将结果保存到物化视图表中。,优点：当执行某些查询的时候，优化器就会对查询进行重写（,rewrite,）并引导到物化视图表表中查找，由于物化视图表表里包含的是汇总数据，因此能够大大提高复杂查询的执行效率。,缺点：物化视图中的数据需要定时刷新，以和基表中的数据保持一致。,物化,视图,CREATE TABLE TBNAME AS,(SELECT COL,FROM TB),DATA INITIALLY DEFERRED,REFRESH DEFERRED,DB2 v9.7 MQT,（,Materialized Query Table,）,ORACLE 10g MV,（,Materialized View,）,CREATE MATERIALIZED VIEW TBNAME,REFERESH COMPLETE ON DEMAND,ENABLE QUERY REWRITE,AS,SELECT COL FROM TB1,SQL,调优,编写,SQL,语句的一般规范,使用参数化查询，,where col1=?,，减少编译时间,避免对查询条件计算，,where salary*2xx,改为,salary xx/2,只返回需要的行，避免用,select*from t1,尽量使用,exists,而不是用,in,避免笛卡尔乘积，,select*from a,b,函数的效率很高，充分利用,。,SQL,调优,步骤,SQL,优化的具体过程,SQL,优化的八个步骤,第一步，发现问题,第二步，选用合适的追踪工具,第三步，根据执行计划，选用合适优化点,第四步，优化,SQL,语句,第五步，验证,SQL,执行速度第六步，验证优化后,SQL,的正确性，保证优化前后,SQL,起到同样的功能,第七步，再次选用追踪工具查看优化后的,SQL,性能，看能否再次优化,第八步，反复循环至优化到最优,SQL,调优,发现问题,性能问题的症状,响应时间慢,吞吐量低,资源占用高,(CPU,、,Memory,、,I/O,等,),SQL,调优,跟踪工具,追踪消耗资源较多的,SQL,语句,ORACLE 10g AWR,（,Automatic Workload Repository,），自动 负载信息库,ORACLE 10g EM(Enterprise Manager),DB2 v9.7,数据库管理视图信息,DB2,快照（,get snapshot,）,。,SQL,调优,跟踪工具,DB2 v9.7,数据库管理视图信息,查询正在使用绝大多数,CPU,时间的,SQL,语句,SELECT SUBSTR(STMT_TEXT,1,200),TOTAL_USR_CPU_TIME+TOTAL_USR_CPU_TIME_MS/1000000.0 AS USER_CPU,FROM SYSIBMADM.SNAPDYN_SQL,ORDER BY USER_CPU DESC,查找最耗,CPU,的,SQL,语句,SELECT MEMBER,SECTION_TYPE,ToTAL_CPU_TIME/NUM_EXEC_WITH_METRICS AS AVG_CPU_TIME,SUBSTR(STMT_TEXT,1,50),FROM TABLE(MON_GET_PKG_CACHE_STMT(NULL,NULL,NULL,-2)AS T,WHERE T.NUM_EXEC_WITH_METRICS 0,ORDER BY AVG_CPU_TIME desc,查询排序,较多,的,sql,SQL,调优,跟踪工具,SELECT SORT_OVERFLOWS,TOTAL_SORT_TIME,SUBSTR(STMT_TEXT,1,50)AS TEXT,FROM SYSIBMADM.SNAPDYN_SQL,ORDER BY NUM_EXECUTIONS DESC FETCH FIRST 10 ROWS ONLY,查询执行次数、排序次数和访问数据行最多的,SQL,语句,SELECT*FROM SYSIBMADM.TOP_DYNAMIC_SQL ORDER BY NUM_EXECUTIONS;,SELECT*FROM SYSIBMADM.TOP_DYNAMIC_SQL ORDER BY stmt_sorts;,SELECT*FROM sysibmadm.snapdyn_sql ORDER BY rows_read Desc;,。,SQL,调优,跟踪工具,ORACLE 10g AWR,（,Automatic Workload Repository,）,负责收集、处理并维护性能统计信息，用于检查和分析性能问题,(,甚至生成的报告格式都非常接近,),，,AWR,生成的统计数据即可以通过,V$,视图和,DBA_*,数据字典查看，也可以通过脚本来生成相应报表。,优点：自动,+,实时,SQL,调优,执行计划,SQL,语句的执行计划是,SQL,调整的根本,查询执行计划工具：,Aqua Data Studio 7.5-32bit,Toad,。,ORACLE 10g,DB2 v9.7,Pl/sql developer,db2exfmt,或,db2expln,explain plan for SQL,语句,explain plan for SQL,语句,。,。SQL,调优,执行计划,执行计划重要属性,cost,属性的值是一个在数据库内部用来比较各个执行计划所耗费的代价的值，从而使优化器可以选择最好的执行计划。不同语句的,cost,值不具有可比性，只能对同一个语句的不同执行计划的,cost,值进行比较,SQL,调优,执行计划,举例一：,SQL,语句消耗资源较多,（,xx,农商）,SELECT,SMOPERPRIV0_.PRIV_NO,AS,COL_0_0_,FROM,SM_OPERPRIV_TB SMOPERPRIV0_,WHERE,SMOPERPRIV0_.MODULE_NAME=?,AND,(SMOPERPRIV0_.PRIV_NO,IN,(,SELECT,SMROLEPRIV1_.PRIV_NO,FROM,SM_ROLE_PRIV_TB SMROLEPRIV1_,WHERE,SMROLEPRIV1_.ROLE_NO,IN,(,SELECT,SMUSERROLE2_.ROLE_NO,FROM,SM_USER_ROLE_TB SMUSERROLE2_,WHERE,SMUSERROLE2_.USER_NO=?,AND,(,EXISTS,(,SELECT,SMROLETB3_.ROLE_NO,FROM,SM_ROLE_TB SMROLETB3_,WHERE,SMROLETB3_.ROLE_NO=SMUSERROLE2_.ROLE_NO,AND,SMROLETB3_.ROLE_STATE=1),表,SM_OPERPRIV_TB,全表扫描成本最高，说明该表上未建立合适的索引。查看该表发现已经建立主键,SM_OPERPRIV_TB_PK(PRIV_NO),，这里我们可以使用,include,关键字创建唯一性索引,SQL,调优,执行计划,举例二：全表扫描（,xx,农商）,SELECT*FROM FL_FLOW_TB,WHERE LSERIAL_NO=20130320161501266130000001,ORDER BY FLOW_ID,该表目前有,2000,万数据，查询时采用了全表扫描，所以每次执行都需要,30s,左右。需要在流水表,FL_FLOW_TB,上添加索引，索引列选择谓词,LSERIAL,和排序字段,FLOW_ID,，语句如下：,CREATE INDEX,HDUSR.IDX_FL_FLOW_TB_LSERIAL_NO,ON,HDUSR.FL_FLOW_TB(LSERIAL_NO,FLOW_ID),ALLOW REVERSE SCANS,GO,SQL,调优,日常维护,现象,1,：有时候查看,SQL,语句的执行计划，,cost,值不是很高，可是,SQL,执行却特别慢。,数据库表统计信息不准确，造成优化器选择了错误的执行路径。,这时需要重新收集统计信息！,现象,2,：有时候某张表数据量明明很小，就只有几百条记录，可是查询该表速度很慢。,现象,3,：有时候明明建立了索引，而且字段也在查询的谓词之中，可是执行计划中并未使用该索引。,。,SQL,调优,日常,维护,SQL,调优之日常维护内容,表重组,重建索引,重新收集表统计信息,。,ORACLE 10g,DB2 v9.7,表重组,Move/shrink/,表在线重定义,Reorg,收集统计信息,Dbms_stats.gather_table_stats,runstats,重建索引,Alter index rebuild,Drop/create,重新绑定程序,Rebind,。,。,。,SQL,调优,日常维护,案例分析：,某客户表包含了大量数据，后删除了一部分，查看,SQL,语句执行计划，发现,cost,很高。经过,reorg,，,runstats,后，性能改善,平安银行,bos,系统存储过程执行速度慢，由于流水表初始化数据后未及时统计表信息，导致执行计划有误，影响存储过程的执行效率。在初始化表数据后使用,dbms_stats.gather_table_stats,命令收集统计信息,Q&A,感谢！,