简单对比db29.7和ora10g.ppt

2013.04.08,数据库调优,内容提要,性能调优概述 数据库逻辑设计优化 SQL调优 案例 Q & A,性能调优概述,随着数据库在行方的使用不断增长，客户也不断对部门产品的应用提出了高性能要求。数据库性能调优是知识密集型的学科，需要综合考虑各种复杂的因素：数据库缓冲区的大小、索引的创建、语句改写等等。总之，数据库性能调优的目的在于使系统运行得更快。 鉴于目前ORACLE，DB2在行方应用较广，本文将以Oracle(Oracle 10g)，DB2（V9.7）为数据库平台,讲述数据库调优应遵循的一般步骤、针对这两种数据库的优化点以及进行实例分析。,性能调优概述,性能问题的症状 响应时间慢 吞吐量低 资源占用高(CPU、Memory、I/O等),调优是个系统工程 存储 系统 中间件 数据库 应用程序 数据库角度 数据库逻辑设计（分区、索引、表空间。） 数据库物理设计（存储规划） SQL语句,数据库逻辑设计优化,海量数据库逻辑设计优化,索引-创建,索引对于提高SQL读有无可替代作用 提高查询性能 避免全表扫描 减少排序 减少CPU使用及I/O占用,索引-创建,索引创建注意点 为where查询条件、Sort排序(order by、max()、min ()等）、join谓词创建索引 注意组合索引键的顺序，如(a,b), (b,a) 完全不同 不要创建冗余索引，如(a), (a,b) (a)为冗余索引 确保索引被用到,删除未用索引,索引的缺点 牺牲增删改性能 占用存储空间 增加运维负担 删除未用索引,分区设计,表分区原则,对于大表进行分区，将有益于大表操作的性能和大表的数据维护。通常当表的大小超过1.5GB2GB，或对于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 。,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调优