oracle优化-SQL优化

Oracle优化—SQL优化（Somanyopensourceprojects.WhynotOpenyourDocuments?）持续更新：：35720263Oracle优化—SQL优化1、数据库、数据表、数据表数据库、数据表、数据表I/O优化原则数据库规划原则最大可重用化数据库重大问题时，通过备份和恢复机制最大程度上恢复数据最小磁盘争用数据库文件平均分布在不同的磁盘上，避免多用户访问时争用同一磁盘各种数据的合理分布将数据库中的各种数据按特性（如基表和变化表、大数据和常规数据）存储在不同的文件中。数据表规划原则数据定义精确化满足要求的情况下，选择占用资源最少的数据类型以提高DBMS的I/O性能表的抽象化通过将具有共性的表合并，将其特性以标识字段表示表的范式化设计表时，满足1NF（原子性）、2NF（键相关性）、3NF（无函数相关性），保证表的结构无冗余数据表I/O优化原则变化表和基表的分离将变化表（递增性很强，如单据表、销售记录表）和基表（很少变化，如学生资源、班组资料等）分开存放到不同的数据文件中大数据和常规数据的分离将表的long、lob等大数据字段和其他常规类型字段分开存放，保证Oracle优化—SQL优化（Somanyopensourceprojects.WhynotOpenyourDocuments?）持续更新：：35720263常规数据的查询高效性索引数据和表数据的分离将索引和表分离，减少磁盘争用；并提高索引的查询效率2、选用适合的ORACLE优化器优化器RBO/CBOOracle的优化器有两种优化方式,即基于规则的优化方式(Rule-BasedOptimization，简称为RBO)和基于代价的优化方式(Cost-BasedOptimization，简称为CBO)，在Oracle8及以后的版本,Oracle强列推荐用CBO的方式。RBO方式：优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的，当一个where子句中的一列有索引时去走索引。CBO方式：它是看语句的代价(Cost),这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小、有少行、每行的长度等信息。这些统计信息起初在库内是没有的，是做analyze命令后才出现的，很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因些应及时更新这些信息。注意：走索引不一定就是优的，比如一个表只有两行数据，一次IO就可以完成全表的检索,而此时走索引时则需要两次IO,这时全表扫描(fulltablescan)是最好Oracle优化模式包括：rule,choose,first_rows,all_rows，设置缺省的优化器,可以通过对init.ora文件中Optimizer_mode参数的各种声明Rule：基于规则的方式，不推荐用户使用RBO这种优化模式Choose：这个是Oracle的默认值。当一个表或索引有统计信息，则走CBO的方式，如果表或索引没有统计信息，表又不是特别的小，而且相应的列有索引时，那么就走索引，走RBO的方式Oracle优化—SQL优化（Somanyopensourceprojects.WhynotOpenyourDocuments?）持续更新：：35720263first_rows：该选项的作用在于寻找能够在最短的时间内返回结果集的第一行的执行计划。设置为这种CBO模式以后，SQL语句返回结果的速度会尽可能的快，而不管系统全部的查询是否会耗时较长或者耗系统资源过多。由于利用索引会使查询速度加快，所以first_rows优化模式会在全表扫描上进行索引扫描。这种优化模式一般适合于一些OLTP（联机事务处理系统）系统，满足用户能够在较短时间内看到较小查询结果集的要求。all_rows：优化器将寻找能够在最短的时间内完成语句的执行计划。设置为这种CBO模式以后，将保证消耗的所有计算资源最小，尽管有时查询结束以后没有结果返回。all_rows的优化模式更倾向于全表扫描，而不是全索引扫描和利用索引排序，因此这种优化模式适合于数据查看实时性不是那么强的数据仓库、决策支持系统和面向批处理的数据库（batch-orienteddatabases）等。3、访问Table的方式ORACLE采用三种访问表中记录的方式:全表扫描、通过ROWID访问表、索引扫描全表扫描（fulltablescan）全表扫描就是顺序地访问表中每条记录.ORACLE采用一次读入多个数据块(databaseblock)的方式优化全表扫描.通过ROWID访问表（tableaccessbyROWID）行的ROWID指出了该行所在的数据文件、数据块以及行在该块中的位置，所以通过ROWID来存取数据（表中记录的物理位置信息）可以快速定位到目标数据上，是Oracle存取单行数据的最快方法。为了通过ROWID存取表，Oracle首先要获取被选择行的ROWID，或者从语句的WHERE子句中得到，或者通过表的一个或多个索引的索引扫描得到。Oracle然后以得到的ROWID为依据定位每个被选择的行。Oracle优化—SQL优化（Somanyopensourceprojects.WhynotOpenyourDocuments?）持续更新：：35720263索引扫描（Indexscan）我们先通过index查找到数据对应的rowid值(对于非唯一索引可能返回多个rowid值)，然后根据rowid直接从表中得到具体的数据，这种查找方式称为索引扫描或索引查找(indexlookup)。一个rowid唯一的表示一行数据，该行对应的数据块是通过一次i/o得到的，在此情况下该次i/o只会读取一个数据库块。在索引中，除了存储每个索引的值外，索引还存储具有此值的行对应的ROWID值。索引扫描可以由2步组成：(1)扫描索引得到对应的rowid值。(2)通过找到的rowid从表中读出具体的数据。4、SQL语句性能诊断、执行计划什么是优化优化是选择最有效的执行计划来执行SQL语句的过程，这是在处理任何数据的语句（SELECT,INSERT,UPDATE或DELETE）中的一个重要步骤。对Oracle来说，执行这样的语句有许多不同的方法，譬如说，将随着以什么顺序访问哪些表或索引的不同而不同。所使用的执行计划可以决定语句能执行得有多快。Oracle中称之为优化器（Optimizer）的组件用来选择这种它认为最有效的执行计划。什么是执行计划在运行一条DML语句时，oracle可能需要执行多个步骤。例如，从数据库物理文件中获取数据行，或将数据整理为提交语句的用户所需的形式。Oracle运行一条语句的所有步骤被告称为语句的执行计划（executionplan）。执行计划中包括语句所需数据所在数据表的访问方式，以及数据表的访问顺序（即关联顺序）。如果查看执行计划EXPLAINPLANSQL*PLUSautotracefeatureOracle优化—SQL优化（Somanyopensourceprojects.WhynotOpenyourDocuments?）持续更新：：35720263EXPLAINPLAN1、解释执行计划执行文件：rdbms/admin/utlxplan.sqlExplainplanforselect语句2、查看执行计划的信息1)、查询plan_tableselect*fromplan_table;2)、使用脚本utlxpls.sql3)、使用包：dbms_xplanselect*fromtable(dbms_xplan.display);说明：产生的计划需要按照从里到外,从上到下的次序解读分析的结果.EXPLAINPLAN分析的结果是用缩进的格式排列的,最内部的操作将被最先解读,如果两个操作处于同一层中,带有最小操作号的将被首先执行如：SELECT*FROMdept,empWHEREemp.deptno=dept.deptno14rowsselected.ExecutionPlan----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE10NESTEDLOOPS21TABLEACCESS(FULL)OF'EMP'31TABLEACCESS(BYINDEXROWID)OF'DEPT'Oracle优化—SQL优化（Somanyopensourceprojects.WhynotOpenyourDocuments?）持续更新：：3572026343INDEX(UNIQUESCAN)OF'PK_DEPT'(UNIQUE)通过以上分析,可以得出实际的执行步骤是:1.TABLEACCESS(FULL)OF'EMP'2.INDEX(UNIQUESCAN)OF'PK_DEPT'(UNIQUE)3.TABLEACCESS(BYINDEXROWID)OF'DEPT'4.NESTEDLOOPS(JOINING1AND3)SQL*PLUSautotracefeature使用autotrace语法：setautotrace{off|on|traceonly|explain|statistics}SETAUTOTRACEOFF此为默认值，即关闭AutotraceSETAUTOTRACEONEXPLAIN只显示执行计划SETAUTOTRACEONSTATISTICS只显示执行的统计信息SETAUTOTRACEON包含2,3两项内容SETAUTOTRACETRACEONLY与ON相似，但不显示语句的执行结果。Autotrace执行计划的各列的涵义ID_PLUS_EXP：每一步骤的行号PARENT_ID_PLUS_EXP：每一步的Parent的级别号PLAN_PLUS_EXP：实际的每步OBJECT_NODE_PLUS_EXP：Dblink或并行查询时才会用到AUTOTRACEStatistics常用列解释dbblockgets：从buffercache中读取的block的数量consistentgets：从buffercache中读取的undo数据的block的数量physicalreads：从磁盘读取的block的数量Oracle优化—SQL优化（Somanyopensourceprojects.WhynotOpenyourDocuments?）持续更新：：35720263redosizeDML：生成的redo的大小sorts(memory)：在内存执行的排序量sorts(disk)：在磁盘上执行的排序量5、用索引提高效率索引是表的一个概念部分,用来提高检索数据的效率.实际上,ORACLE使用了一个复杂的自平衡B-tree结构.通常,通过索引查询数据比全表扫描要快.当ORACLE找出执行查询和Update语句的最佳路径时,ORACLE优化器将使用索引.同样在联结多个表时使用索引也可以提高效率.另一个使用索引的好处是,它提供了主键(primarykey)的唯一性验证.除了那些LONG或LONGRAW数据类型,你可以索引几乎所有的列.通常,在大型表中使用索引特别有效.当然,你也会发现,在扫描小表时,使用索引同样能提高效率.虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的