Oracle优化常用概念

Oracle优化常用概念解析常见概念•CBO/RBO•表连接方式•执行计划•统计信息和柱状图•索引•分区表•AWR•表扫描方式•hintCBO/RBO•什么是CBO/RBO，工作原理是什么•应该使用哪种模式•如何查看是哪种模式•如何修改优化器模式什么是CBO/RBO，工作原理是什么•CBO：cost-basedoptimizer，基于成本的优化器•RBO：rule-basedoptimizer，基于规则的优化器•CBO需要使用统计信息，据此计算最佳的执行计划；而RBO根据oracle设定好的规则生成执行计划。由于不能窥视到表中数据，RBO往往不能得到合理的执行计划应该使用哪种模式•Oracle10g的CBO已经很成熟，推荐使用。Oracle公司也将使用CBO代替RBO，而且RBO对开发人员的要求比较高，因此，CBO是不二选择。如何查看是哪种模式•showparameteroptimizer_mode•optimizer_mode的取值包括：rule：RBOchoose：有统计信息时是CBO，否则是RBO，但使用use_hash,all_rows等提示时都是CBOall_rows,first_rows：CBO如何修改优化器模式•altersystemsetoptimizer_mode=all_rowsscope=both;scope的值：both、spfile、memory，默认是both表连接方式•有哪几种连接方式•每种方式的特点、工作原理有哪几种连接方式•hashjoin•nestedloop•mergejoin每种方式的特点、工作原理•nestedloop：使用条件：任何连接优点：当有高选择性索引或进行限制性搜索时效率比较高，能够快速返回第一次的搜索结果缺点：当索引丢失或者查询条件限制不够时，效率很低；当表的纪录数多时，效率低。•sortmerge：使用条件：主要用于不等价连接，但不包括；相关资源：内存、临时空间优点：当缺乏索引或者索引条件模糊时，排序合并连接比嵌套循环有效。缺点：所有的表都需要排序。它为最优化的吞吐量而设计，并且在结果没有全部找到前不返回数据。•hashjoin：使用条件：仅用于等价连接；相关资源：内存、临时空间优点：当缺乏索引或者索引条件模糊时，哈希连接连接比嵌套循环有效。通常比排序合并连接快。在数据仓库环境下，如果表的纪录数多，效率高。缺点：为建立哈希表，需要大量内存。第一次的结果返回较慢。执行计划•什么是执行计划•如何生成执行计划•如何看懂执行计划什么是执行计划•Oracle执行每一条SQL语句，都必须经过Oracle优化器的评估，选择最佳的执行路径，包括索引的使用、表的访问、表之间的连接等等。如何生成执行计划•Pl/sql中按F5，最简单快捷的方式•Explainplan•Sqltrace，最准确•Sql/plusautotrace，包含更多信息如何看懂执行计划•没有捷径，多看执行计划，网上查一下具体含义•计划开个专题讨论统计信息和柱状图•为什么要收集统计信息•统计信息包括哪些内容•如何查看是否存在统计信息•如何收集统计信息•什么是柱状图•为什么要做柱状图•如何生成柱状图为什么要收集统计信息•cbo模式下计算cost所需要的信息，统计信息越准确，oracle生成的执行计划越高效，所以要定期收集统计信息统计信息包括哪些内容•表：行数、块数、空块数、块的平均剩余空间等•表的列：唯一值数、null值数等•索引：级数、叶子块数、唯一值数等如何查看是否存在统计信息•User_tab_statistics（user_tables也存在相应列）•User_ind_statistics（user_indexes也存在相应列）•User_tab_col_statistics如何收集统计信息•analyzetabletb_namecompute|estimatestatistics;该方式用于向后兼容•execdbms_stats.gather_*oracle建议使用的方式什么是柱状图•Oracle的柱状图和web页面展示的柱状图属同一个概念，只是已数据的形式存在而没有直观展示出来而已。每个柱被称为bucket（桶）。柱状图包含了列上的数据分布，大致可理解为列上每个值的记录数，即数据倾斜度。为什么要做柱状图•Oracle在选择索引时会检查索引的效率从而确定是否使用索引，而柱状图正是提供了这种信息。如果不做柱状图，oracle就确定不了索引的效率，会倾向于使用索引，可能反而不如做全表扫描来得快如何生成柱状图•analyzetabletb_namecompute|estimatestatisticsforallindexedcolumns;针对索引列•analyzetabletb_namecompute|estimatestatisticsforcolumnscol_namesizen;针对某一列•analyzetabletb_namecompute|estimatestatisticsforallcolumns;针对表所有列•使用dbms_stats包提供的过程，如：dbms_stats.gather_table_stats(ownname='',tabname='',method_opt='forallcolumns');索引•创建索引的目的•索引为什么会加快查询•有哪些类型的索引•索引的限制创建索引的目的•从根本来讲就是为了加快查询速度•Oracle出于对效率的考虑，某些约束会关联到索引，从而可以更快的检查约束，主键约束就是一个典型的例子。索引为什么会加快查询•索引的结构使得搜索索引的键值很快。oracle索引的结构类似于平衡二叉树，但每个非叶子节点存储了多个值，降低了树的高度。•每个索引键都存储了索引列和rowid，通过rowid访问表是最快的方式。Rowid相当于指针。•如果取表中少量数据，先索引搜索再通过rowid访问表显然代价比全表扫描小，但如果取表中大量数据，索引搜索几乎就成了多余的一步，因此，全表扫描更好，走索引反而效率低。这就是索引的效率问题，也是做柱状图的目的。有哪些类型的索引•唯一索引、非唯一索引：关键字unique•函数索引•反转索引：关键字reverse用于平衡索引树，使之不会朝一侧倾斜•位图索引：关键字bitmap列的基数比较小时非常适合，但dml操作的代价很高，不适合oltp系统索引的限制•不等（、!=、^=）、isnull操作符•对索引列使用函数或者索引列存在隐身转换•索引效率不高，适合做全表扫描•错误的统计信息•数据倾斜而未做柱状图，或使用了绑定变量而oracle未窥视到•使用hint强制做fullscan•以’%’开头的like操作符•并行查询•小表，oracle认为不需要走索引分区表•有哪些类型的分区表•索引的创建有哪些类型的分区表•range•hash•list•complex索引的创建•globalorlocal？建议使用local索引AWR•什么是awr•如何生成awr•Awr包含哪些内容什么是awr•全称automaticworkloadrepository（自动工作负荷存储）。对于运行中的数据库，oracle会定时对运行状态抓取快照，包括内存的占用、磁盘的读写、语句执行情况等信息。Awr保存了时间最长的快照信息，默认是7天。与awr对应的概念还有addm和ash，是时间较短的快照。如何生成awr•登陆到数据库服务器，执行脚本awrrpt•Pl/sql和sql/plus中也可以。独家抓取脚本，无须登陆服务器，使用更方便，每license10元，收费公道，童叟无欺，欢迎订购使用表扫描•表扫描原理•如何查看hwm•如何降低hwm表扫描原理•从水线（hwm）向下扫描•hwm（hightwatermark）：oracle使用的块的最大高度，或者说是使用到的最大块号如何查看hwm•查询user_tables.blocks，需要先对表进行分析•其他一些左道旁门的方法如何降低hwm•altertabletb_nameshrinkspace;该方法需要前提altertabletb_nameenablerowmovement;•altertabletb_namemove;该方法需要在move完后重建索引alterindexind_namerebuild;•truncatehint•使用hint的两种方式•有哪些hint使用hint的两种方式•--+，需要注意该方式会注释掉其后同行的内容•/*+*/有哪些hint•use_nl、use_hash、use_merge表连接方式•leading表连接顺序•parallel并行查询•full、index全表扫描、索引扫描•all_rows、first_rows、choose、rule优化器模式•Append、noappend直接路径插入，慎用语句慢怎么办？•检查语句书写是否规范、业务逻辑是否可调•一句话，死盯执行计划，语句慢一般是走了错误的执行计划。以下原因都可能是罪魁祸首：1、优化器选择了RBO2、统计信息缺失或错误3、有hint进行了错误的强制4、未做柱状图，oracle窥视不到值分布5、索引建的不合理6、隐式转换导致索引使用不上（经常会遇到）7、某些时候oracle判断不了数据多少，选择了错误的表连接方式动态性能视图和数据字典视图•动态性能视图：v$开头•数据字典视图：dba_*、all_*、user_*动态性能视图•v$session,v$session_wait,v$session_event,•v$sql•v$parameter数据字典视图•dba_data_files•user_tables•user_indexes•user_objects•user_source•user_segments：对象实际占用空间，非实际使用空间•user_users,user_errors,user_constraints,user_tablespaces,user_recyclebin,•user_part_tables,user_part_indexes,user_tab_partitions,user_ind_partitions,•user_jobs,user_dependencies,user_mviews,user_histograms,user_arguments,谢谢！•未完，待续。。。。。。