《oracle性能优化》PPT课件

1、ORACLE性能优化,一. Oracle数据库性能优化方法论,WHY WHO WHAT HOW WHEN,-Why tunes？ -Who tunes？ -What to tune？ -How to tune？ -When to tune？,为什么（why）要优化,-系统慢了？ -其实慢只是表象 -距离找到慢的原因可能路还很长,优化什么（what）（需要找到慢的原因）,-是系统的问题？ -是数据库的问题？ -是应用设计的问题？ -是代码编写的问题？ -是架构的问题？,怎样（how）优化,根据what中明确的问题，需要制定出调整策略 这个过程可能需要借助很多工具，如是系统的问题，可能需要借助to

2、pas、vmstat、iostat等；如果是db的问题则可能需要通过awr、ash、addm等,谁（who）来优化,-系统架构师（系统架构的问题，麻烦大了） -系统管理员（os、storage问题） -数据库管理员（db问题） -应用程序设计人员（应用设计问题） -应用程序开发人员（代码书写问题）,什么时候（when）优化,-7*24 高可用性系统 -是否需要停应用 -允许停机的时间 -在可以停机的时间内是否能够顺利完成调整 -调整前的准备工作(是否需要备份db以及oracle_home),关于性能优化的误区,1.你调了哪些参数 2.性能优化是DBA的工作 3.开发阶段无需考虑性能问题 4.优

3、化SQL，就是如何编写SQL 5.多表连接性能太差 6.CPU利用率越低越好 7.大内存能解决性能问题 8.性能分析就是分析底层细节,性能优化过程自顶向下,体验方法论,1.优化工作开始越早越好，其效益也越高 2.投产后才发现的问题有可能是灾难性的 3.再好的硬件也解决不了应用软件设计和开发的问题 4.千万别将性能优化全部寄希望于硬件和系统层面,20/80规则,性能优化过程自底向上,硬件 操作系统 数据库 应用软件 系统架构 业务需求,性能优化中的角色分工,合理运用技术的重要性,二.性能优化分析基本工具的使用,工欲善其事，必先利其器,SQL量化分析和优化工具： EXPLAIN SQL*TRACE

4、 TKPROF AUTO*TRACE AWR ADDM SQL*PROFILING SQL ACCESS ADVISOR SQL TUNING ADVISOR ASH,4种基本的诊断分析工具,SQL语句到底是怎么执行的,最经典的执行计划分析工具-EXPLAIN 可以快速的了解语句的执行过程。 目前几乎所有的开发工具（PLSQL developer、toad等）都有图形化界面，可以直接的分析语句的执行计划。但如银行类的客户不允许使用工具。,如何配套使用SQL*TRACE和TKPROF,最常用的工具-autotrace,打开：set autotrace on 只看执行计划、统计信息： set au

5、totrace traceonly 只看执行计划： set autotrace traceonly explain 只看统计信息： set autotrace statistics 看统计信息：最主要的是看consistent gets和physical gets，分别代表内存消耗和磁盘IO消耗,三.基本索引的使用,索引其实很简单,简单：索引就好比一本书的目录，一张地图，一个写字楼里挂在大堂墙上的公司名录，一个地铁站里的出口指示牌,索引又很复杂,常用的索引,B*树索引 主键 唯一索引 函数索引 复合索引 位图索引 分区索引 全文索引,索引到底长什么样,索引就是这样,索引的高度,只从根块到叶子快

6、遍历所需的块数,索引的聚簇因子(clustering_factor),如果这个值与块数接近，说明表相当有序，得到了很好的组织。在这种情况下，同一个叶子块中的索引条目可能指向同一个数据块上的行。 如果这个值与行数接近，表的次序就可能是非常随机的。在这种情况下，同一个叶子块上的索引条目不太可能指向同一个数据块上的行。,索引未被使用的原因,1.不要轻易的在字段前加函数 2.尽量不要将字段嵌入表达式中 3.避免字符转换 4.索引列的选择性不高 5.索引列值是否可为空（NULL） 6.检查被索引的列或组合索引的首列是否出现在PL/SQL语句的WHERE子句中 7.优化器的选择,复合索引,1. 前缀性(P

7、refixing)复合索引的前缀性是指只有当复合索引的第一个字段出现在SQL语句的谓词条件中时，该索引才会被用到。2. 可选性(Selectivity)Oracle建议复合索引应按字段可选性（即值的多少）的高低进行排列，这是因为，字段值越多，可选性越强，定位的记录就越少，查询效率就越高。,索引监控分析及优化,问题1：索引的IO很高 问题2：如何发现多余的索引 问题3：如何进行索引碎片分析 问题4：索引碎片整理 注意：如果索引碎片超过20%，则oracle认为索引碎片已经非常严重。,四.为应用软件设计更好的性能和可扩展性,SQL语句的执行过程,实现语句共享性,SQL语句共享性主要针对OLTP系统

8、，因此减少语句的重复parse次数，是保证OLTP应用性能的重要方面。 （1）应用级绑定化处理 （2）系统级bind化处理cursor_sharing参数（建议不要这么做） 最完美的是应用级绑定化处理； 系统级如果将参数设置成similar或者force会导致所有应用的常量也会被转化成变量处理。,五.如何提高排序、表连接性能,如何提高排序性能,1.尽量将需要排序的数据装载在内存中，减少餐盘IO次数 2.能不排序就不排序（利用索引）废话 3.了解union（排序，去重）和union all（不排序，不去重） 4.了解group by（不保证结果排序） 5.了解order by（当然排序） 6.D

9、istinct（排序再去重）,表连接类型,嵌套循环连接-OLTP系统 最常用的表连接技术 排序合并连接-OLAP系统 两个表先按照连接字段进行排序，再将两个表的排序结果进行顺序匹配，将合并结果反给客户。 适用于大表与大表 哈希连接-OLAP系统 适用于大表与大表,驱动表和被驱动表,多表连接优化的基本思路,OLTP应用的表连接优化,子查询好不好-不好,原则上等同于多表连接，多此一举 子查询书写方式导致SQL语句冗长，可读性下降 也是最重要的，子查询方式可能会强制oracle优化器选择错误的执行路径，导致整个语句的性能急剧下降 建议：能不写子查询就不写，而是直接编写多表连接操作,到底是使用in还是

10、exists,六.应用综合优化及总结,导致数据库性能问题的常见原因,不合理的大表全表扫描 语句共享性不好,何谓全表扫描,全表扫描非常容易理解，就是不合理的消耗大量资源的数据访问方式，解决该问题的最简单策略就是合理的设计和使用索引。 真的这么简单吗？,全表扫描与数据增长的关系,误解： 数据量越来越大，响应速度越来越慢，很正常嘛。 数据量越来越大，赶紧增加CPU、内存进行扩容吧。 XX系统能运行个3-5年就不错了。,全表扫描与数据增长的关系,导致性能问题的其他原因,1.频繁的数据库连接操作 2.存储部署不合理 3.不合理的参数设置 4.过量数据的排序操作 5.优化器和统计信息问题 6.大量递归的S

11、QL语句 7.Redo设计不合理 *8.低质量的SQL语句,频繁的数据库连接操作,这种错误主要发生在传统的client-server两层模式中，大量并发用户频繁地发起login、logoff数据库操作，消耗了大量数据库系统资源。在目前基本采用应用服务器和中间件服务器的架构下，这类问题不太普遍了。,存储部署不合理,由于存储部署不合理而导致的IO效率低下，例如磁盘部署不均衡、条带化技术不合理等。在需要进行海量数据处理的数据仓库系统中，存储部署将非常关键。采用oracle最新的自动存储管理技术，以及更好的条带化技术，是解决此类问题的有效方式。,不合理的参数设置,系统参数一定要调，还要合理的调，但是调

12、好了并不一定能解决问题。调坏了，则可能带来灾难性的后果。,过量数据的排序操作,能不排序就不排序。另外，过量数据的排序操作，特别是基本磁盘的排序操作，与事务设计、缺乏索引、优化器的选择等均有关系。,优化器及统计信息问题,客户经常会遇到这种情况：在开发环境运行的好好的，怎么到了生产环境就不行了呢？这种情况很大程度是因为生产环境没有及时采集统计信息，导致oracle优化器不了解最新的数据和应用情况，而错误的选择了非优化的执行路径。解决方式：及时采集统计信息，保证基于统计信息的CBO优化器的高质量运行。,大量递归的SQL语句,系统产生由sys用户执行的大量递归SQL语句，也将极大的消耗系统的资源。这类

13、语句尤以大量空间管理SQL语句为普遍现象，例如大量扩展快的分配操作。这类问题一般在大批量数据处理中比较普遍。解决方式：在进行大批量数据处理前，主动进行存储空间的分配等。,Redo log设计不合理,Redo log文件设计的太小，出发频繁的检查点操作，导致内存和IO操作频繁，加重系统负担。 Redo log文件组太少，则可能使归档操作无法赶上数据库的日志产生速度。,低质量的SQL语句,1:如何建表，建立什么样的表 2:如何建立索引，建立什么样的索引复合索引 3:分区表及分区索引的创建及使用 4:如何提高排序及表的连接性能 5:绑定变量问题,七. Oracle分区技术及应用,分区技术内容,什么是

14、分区? 分区的好处? 如何实施分区? 如何评估分区的效果?,Oracle的分区技术基本原理,2003,2004,2005,2006,分而治之,分区概述,大数据对象 (表, 索引)被分成小物理段 当分区表建立时，记录基于分区字段值被存储到相应分区。 分区字段值可以修改。(row movement enabled) 分区可以存储在不同的表空间 分区可以有不同的物理存储参数 分区支持IOT表，对象表，LOB字段，varrays等,分区技术的效益和目标,性能 Select和DML操作只访问指定分区 并行DML操作 可管理性：数据删除，数据备份 历史数据清除 提高备份性能 指定分区的数据维护操作 可用性

15、 将故障局限在分区中 缩短恢复时间 分区目标优先级 高性能 数据维护能力-实施难度 高可用性（故障屏蔽能力）,分区方法,分区方法: 范围 Hash 列表 组合,Rangepartitioning,Hashpartitioning,范围分区特点,最早、最经典的分区算法 Range分区通过对分区字段值的范围进行分区 Range分区特别适合于按时间周期进行数据的存储。日、周、月、年等。 数据管理能力强 数据迁移 数据备份 数据交换 范围分区的数据可能不均匀 范围分区与记录值相关，实施难度和可维护性相对较差,Hash分区例,create table CUSTOMERS (. column defini

16、tions .) pctfree 0 nologging storage ( initial 40m next 40m pctincrease 0 ) partition by hash(customer_no) partitions 8 store in (cust_data01,cust_data02) create table CUSTOMERS (. column definitions .) pctfree 0 nologging storage ( initial 40m next 40m pctincrease 0 ) partition by hash(customer_no)

17、 (partition cust_p01 tablespace cust_data01 ,partition cust_p02 tablespace cust_data02 ,partition cust_p03 tablespace cust_data03 ,partition cust_p04 tablespace cust_data04 ,partition cust_p05 tablespace cust_data05 ,partition cust_p06 tablespace cust_data06 ,partition cust_p07 tablespace cust_data0

18、7 ,partition cust_p08 tablespace cust_data08),Hash分区特点,基于分区字段的HASH值，自动将记录插入到指定分区。 分区数一般是2的幂 易于实施 总体性能最佳 适合于静态数据 HASH分区适合于数据的均匀存储 HASH分区特别适合于PDML和partition-wise joins。 支持 (hash) local indexes 9i 不支持 (hash) global indexes 10g 支持(hash) global indexes HASH分区 数据管理能力弱 HASH分区对数据值无法控制,列表分区例,create table add

19、resses (. column definitions .) pctfree 0 nologging storage ( initial 40m next 40m pctincrease 0 ) partition by list(city_name) (partition addr_p01 values (WELLINGTON) tablespace addr_data01 ,partition addr_p02 values (CHRISTCHURCH) tablespace addr_data02 ,partition addr_p03 values (DUNEDIN,INVERCAR

20、GILL) tablespace addr_data03 ,partition addr_p04 values (AUCKLAND) tablespace addr_data04 ,partition addr_p05 values (HAMILTON,ROTORUA,TAURANGA) tablespace addr_data05),列表分区特点,List分区通过对分区字段的离散值进行分区。 List分区是不排序的，而且分区之间没有关联关系 List分区适合于对数据离散值进行控制。 List分区只支持单个字段。 List分区具有与范围分区相似的优缺点 数据管理能力强 List分区的数据可能不

21、均匀 List分区与记录值相关，实施难度和可维护性相对较差,不同的分区索引,Global Partitioned Index,绍兴,杭州,温州,Local partitioned index,分区索引,分区表索引的分类： Local Prefixed index Local Non-prefiexed index Global Prefixed index Non Partition Index Global索引的分区不同与表分区 Local索引的分区与表分区相同 An index is prefixed if it is partitioned on a left prefix of the

22、 index columns. 分区表上的非分区索引等同于Global索引,分区索引,Global索引必须是范围分区 - 9i之前 Global索引可以是HASH分区 - 10g新特性 Global索引不支持Bitmap索引 Unique索引必须是prefixed，或者包含分区字段 Local索引（non-prefixed, non-unique）可以不包含分区字段,分区索引举例,create index cust_idx1 on customers(customer_name) global partition by range (customer_name) (partition cust

23、_p01 values less than (H) tablespace cust_index01 ,partition cust_p02 values less than (N) tablespace cust_index02 ,partition cust_p03 values less than (T) tablespace cust_index03 ,partition cust_p04 values less than (MAXVALUE) tablespace cust_index04) create index cust_idx2 on customers(customer_no

24、) local (partition cust_idx_p01 tablespace cust_index01 ,partition cust_idx_p02 tablespace cust_index02 ,partition cust_idx_p03 tablespace cust_index03 ,partition cust_idx_p04 tablespace cust_index04 ,partition cust_idx_p05 tablespace cust_index05 ,partition cust_idx_p06 tablespace cust_index06 ,par

25、tition cust_idx_p07 tablespace cust_index07 ,partition cust_idx_p08 tablespace cust_index08) create index cust_idx3 on customers(customer_type) local;,分区表索引的使用,OLTP系统中的建议 Global和unique local index性能优于nonunique local index Local index提供了更好的可用性 数据仓库系统中的建议 Local index更适合于数据装载和分区维护 在大量数据统计时，能充分利用Local i

26、ndex并行查询能力 在性能、高可用性和可管理性之间进行平衡,分区索引选择策略,八.统计信息采集与性能优化,RBO,RBO（Rule Based Optimizer,基于规则的优化器），他的执行非常简单，就是在优化器里面嵌入15中规则，执行SQL语句符合哪种规则，就按照规则定制出相应的SQL执行计划。由于他是一种过时呆板的优化器，在10g以后的版本中已经被踢出掉了。,CBO,CBO（Cost Based Optimizer,基于代价的优化器），他的思路是让Oracle获取所有的执行计划的相关信息，通过这些信息做计算分析，最后得出一个代价最小的执行计划作为最终的执行计划。,CBO优化器有两种可选

27、的运行模式,FIRST_ROWS(n) ALL_ROWS,什么是统计信息,统计信息主要是描述数据库中表，索引的大小，规模，数据分布状况等的一类信息。比如，表的行数，块数，平均每行的大小，索引的leaf blocks，索引字段的行数，不同值的大小等，都属于统计信息.,相关视图,DBA_TABLES DBA_INDEXES DBA_TAB_COL_STATISTICS DBA_TAB_HISTOGRAMS 等,如何搜集统计信息,统计信息搜集也是有多种方法，推荐大家使用DBMS_STATS 表来进行统计信息搜集及进行一般的统计信息维护工作。DBMS-STATS 包，主要提供了搜集，删除，导出，导入，

28、修改统计信息的方法，分别对应于gather系列，delete系列，export 系列，import系列，set系列的子过程。一般可能主要是使用统计信息的搜集，以及导出导入这样的功能。,自动收集统计信息,Oracle10g中，在安装Oracle的时候，就默认创建了一个名为GATHER_STATS_JOB的job来自动收集优化器统计信息。这个job收集数据库中所有对象的统计信息。默认的情况下这个job是周一到周五每天晚上10点到第二天早上6点以及整个周末来收集统计信息。,关闭自动收集统计信息,在某些情况下，我们想关闭自动收集统计信息那么我们可以利用如下方法: BEGIN DBMS_SCHEDULE

29、R.DISABLE(GATHER_STATS_JOB); END; /,何时该手动收集统计信息,白天经常被delete,或者truncated之后又rebuild的表(经常变化的表) 批量操作之后有10%或者以上的数据被更改的表(批量处理的表) 对于经常变化的表，可以将其统计信息设置为null，当ORACLE遇到一个表没有统计信息，ORACLE会动态采样以便为查询优化器收集必要的统计信息。,锁住/解锁统计信息, LOCK_SCHEMA_STATS LOCK_TABLE_STATS UNLOCK_SCHEMA_STATS UNLOCK_TABLE_STATS,收集统计信息的策略,通常情况下，我们

30、会将ORACLE自动收集统计信息功能给关闭，我们会采用手动的方式给数据库收集统计信息。至于收集统计信息的策略需要根据系统来确定。下面说说几种常见的情况： 如果你系统中的表的数据是增量(有规律)的增加，也就是说你几乎不做任何的批量处理操作，比如批量删除，批量加载操作。对于这样的表收集统计信息是非常简单的。你可以通过查看DBA_TAB_MODIFICATIONS视图来观察表的变化情况，观察表中数据量的变化是否超过了10%,并且记录下天数。这样你就可以每隔这样的时间间隔对其收集一次统计信息。你可以用CRONTAB,或者JOB调用GATHER_SCHEMA_STATS或者GATHER_TABLE_ST

31、ATS过程来收集统计信息。 对于经常批量操作的表，那么表的统计信息就必须在批量操作之后对其收集统计信息。 对于分区表，通常只有一个分区被修改，这种情况下可以只收集单独分区的统计信息，不过收集整个表的统计信息还是非常有必要的。 最后我会给出两个脚本，判断该表是否需要收集统计信息。,个人的几点理解,统计信息默认是存放在数据字典表中的，也只有数据字典中的统计信息，才会影响到CBO。 DBMS_STATS 提供的CREATE_STAT_TABLE 过程，只是生成一个用户自定义的特定格式的表，用来存放统计信息罢了，这个表中的统计信息是不会影响到统计信息的。 GATHER 系列过程中，如果指定statta

32、b，statid，statown 参数（也可以不指定），则是搜集的统计信息除了更新到数据字典外，还在statown 用户下的stattab 表中存放一份，标示为 statid; EXPORT和IMPORT 系列的过程中,stattab,statid,statown 参数不能为空，分别表示把数据字典中的当前统计信息导出到用户自定义的表中，以及把用户表中的统计信息导入到数据字典中，很明显可以看出，这里的导入操作和上面GATHER 操作会改变统计信息，可能会引起执行执行计划的改变，因此要慎重操作。 每次统计信息搜集前，将旧的统计信息备份起来是很有必要的；特别是保留一份或多份系统在稳定时期的统计信息也

33、是很有必要的。 多长时间搜集一次统计信息，对于统计信息如何备份和保留，搜集统计信息时如何选择合适的采样，并行，直方图设置等都比较重要，需要设计一个较好的统计信息搜集策略。,统计信息包括下面几类,表统计：包括记录数、block数和记录平均长度。 列统计：列中不同值的数量（NVD）、空值的数量和数据分布（HISTOGRAM）。 索引统计：索引叶块的数量、索引的层数和聚集因子（CLUSTERING FACTOR）。 系统统计：I/O性能和利用率和CPU性能和利用率。,DBMS_STATS包中用于收集统计信息的过程,dbms_stats.gather_table_stats 收集表、列和索引的统计信息

34、；dbms_stats.gather_schema_stats 收集SCHEMA下所有对象的统计信息；dbms_stats.gather_index_stats 收集索引的统计信息；dbms_stats.gather_system_stats 收集系统统计信息。dbms_stats.delete_table_stats 删除表的统计信息dbms_stats.export_table_stats 输出表的统计信息dbms_stats.create_state_tabledbms_stats.set_table_stats 设置 表的统计dbms_stats.auto_sample_size db

35、ms_stats.gather_database_stats：收集数据库中所有对象的统计信息；,九.感悟性能优化分析的高级工具,动态性能视图,V$SYSSTAT V$SESSION V$SESSTAT V$SGASTATV$FILESTAT V$UNDOSTAT V$ROLLSTAT V$WAITSTAT V$LOCK V$LATCH V$SQL V$SQLAREA V$SQLTEXTV$PROCESS V$LIBRARYCACHE V$ROWCACHE等等。,等待事件,竞争即等待，关注等待事件：v$system_event / v$session_event / v$session_wait

36、 有助于我们发现系统的性能瓶颈。比如：从v$system_event中，我们能发现系统问题；从v$session_event中发现会话问题； 从v$session_wait的参数中可以找到竞争对象。,statspack,Statspack输出包含丰富的信息，如数据库和实例名称；获取快照的时间；当前高速缓存的大小；负载概览；实例效率百分比；前五个等待事件；等待事件的完整列表；共享池中SQL语句的信息；实例活动统计；表空间和文件I/O；缓冲区统计信息；回退段或还原段统计信息；字典高速缓存统计信息；库高速缓存统计；SGA统计；Init.ora参数的启动值等。,ASH,Active Session H

37、istory（ASH）-活动会话历史，每秒钟自动从内存中抓取样例的活动会话信息. 通过ashrpt.sql可以产生ash分析报告，发现某个时段的TOP (Top Events/ Top SQL/ Top Sessions/ Top Objects/Files/Latches)通过活动会话历史信息，可以追溯到性能问题的根源，找出什么资源在竞争？向下追溯到哪个程序带来了竞争？以及哪个SQL带来了竞争？,AWR,Automatic Workload Repository-自动负载信息库 (AWR)，功能基本与statspact,ADDM,Automatic Database Diagnostic Monitor -自动数据库诊断监控(ADDM) 。,sql tuning advisor/sql access advisor,SQL Tuning 这是一条简单易记的规则，但是实际的执行效果还须检验.,避免在索引列上使用IS NULL