下载文档原格式
OracleSQL性能优化及案例分析标题：Oracle SQL性能优化及案例分析一、引言Oracle数据库作为全球最受欢迎的数据库之一，其性能优化问题一直是用户和开发者的焦点。
尤其是在处理大量数据或复杂查询时，性能问题可能会严重影响应用程序的响应时间和用户体验。
因此，对Oracle SQL进行性能优化及案例分析显得尤为重要。
二、Oracle SQL性能优化1、索引优化索引是提高Oracle SQL查询性能的重要工具。
通过创建合适的索引，可以大大减少查询所需的时间，提高数据库的响应速度。
然而，过多的索引可能会导致额外的存储空间和插入、更新、删除的性能损失。
因此，需要根据实际应用的需求，合理地选择需要索引的字段。
2、查询优化编写高效的SQL查询语句也是提高Oracle SQL性能的关键。
这包括选择正确的查询语句、避免在查询中使用复杂的子查询、使用连接（JOIN）代替子查询等。
还可以使用Oracle SQL Profiler来分析和优化查询语句的性能。
3、数据库参数优化Oracle数据库有许多参数可以影响SQL性能，如内存缓冲区、磁盘I/O参数等。
根据实际应用的需求和硬件环境，对这些参数进行合理的调整，可以提高Oracle SQL的性能。
三、案例分析1、案例一：索引优化问题描述：在一个电商系统中，用户在搜索产品时，使用全文本搜索功能时经常出现延迟。
解决方案：通过分析用户搜索的习惯和需求，对产品表的名称和描述字段创建全文索引。
同时，调整Oracle的全文搜索参数以提高搜索效率。
2、案例二：查询优化问题描述：在一个银行系统中，客户查询自己的贷款信息时，查询时间过长。
解决方案：通过使用Oracle SQL Profiler分析查询语句，发现查询中存在复杂的子查询。
将子查询改为连接（JOIN）方式，减少了查询时间。
3、案例三：数据库参数优化问题描述：在一个大型电商系统中，用户在访问高峰期经常遇到响应时间过长的问题。
ORACLE优化SQL语句，提高效率我们要做到不但会写SQL，还要做到写出性能优良的SQL语句。
（1）选择最有效率的表名顺序(只在基于规则的优化器中有效)：Oracle的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving table)将被最先处理，在FROM子句中包含多个表的情况下,您必须选择记录条数最少的表作为基础表。
假如有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表。
（2）WHERE子句中的连接顺序：Oracle采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些能够过滤掉最大数量记录的条件必须写在WHERE子句的末尾。
（3）SELECT子句中避免使用‘*’：Oracle在解析的过程中, 会将‘*’依次转换成任何的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。
（4）减少访问数据库的次数：Oracle在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等。
（5）在SQL*Plus , SQL*Forms和Pro*C中重新配置ARRAYSIZE参数, 能够增加每次数据库访问的检索数据量 ,建议值为200。
（6）使用DECODE函数来减少处理时间：使用DECODE函数能够避免重复扫描相同记录或重复连接相同的表。
（7）整合简单,无关联的数据库访问：假如您有几个简单的数据库查询语句,您能够把他们整合到一个查询中(即使他们之间没有关系)。
（8）删除重复记录：最高效的删除重复记录方法 ( 因为使用了ROWID)例子：DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);（9）用TRUNCATE替代DELETE：当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放能够被恢复的信息. 假如您没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息。
ORACLESQL语句优化SQL Select语句完整的执行顺序1、from子句组装来自不同数据源的数据；2、where子句基于指定的条件对记录行进行筛选；3> group by子句将数据划分为多个分组；4、使用聚集函数进行计算；5、使用having 了-句筛选分组；6、计算所有的表达式；7、使用order by对结果集进行排序。
说明：—8) SELECT (9) DISTINCT (11) <T0P_spec辻ication> <select_list>—(1) FROM <left_table>—(3) <join_type> JOIN <right_table>—(2) ON <join_condition>—(4) WHERE <where_condit ion>—(5) GROUP BY <group_by_list>—(6) WITH {CUBE ROLLUP}—(7) HAVING <having_condition>~(10) ORDER BY <order_by_list>Oracle SQL性能优化技巧1、选用适合的ORACLE优化器2、访问Table的方式3、共享SQL语句4、选择最有效率的表名顺序(只在基于规则的优化器中有效)5、 WHERE子句中的连接顺序6、SELECT子句中避免使用'* '7、减少访问数据库的次数8、使用DECODE函数来减少处理时间9、整合简单，无关联的数据库访问10、删除重复记录11、用 TRUNCATE 替代 DELETE12、尽量多使用COMMIT13、计算记录条数14、用Where子句替换HAVING子句13、减少对表的查询16、通过内部函数提高SQL效率17、使用表的别名(Alias)18、用 EXISTS 替代 IN19、用 NOT EXISTS 替代 \0T IN20、用表连接替换EXISTS21、用 EXISTS 替换 DISTINCT1.选用适合的ORACLE优化器ORACLE的优化器共有3种a、 RULE (基于规则)b、 COST (基于成本)c、 CHOOSE (选择性)设置缺省的优化器，可以通过对init. ora文件中OPTIMIZER.MODE参数的各种声明，如 RULE, COST, CHOOSE, ALL_ROWS, FIRST_ROWS。
Oracle sql 优化一．培训目地数据库参数进行优化所获得地性能提升全部加起来只占数据库应用系统性能提升地40%左右,其余60%地系统性能提升全部来自对应用程序地优化.许多优化专家甚至认为对应用程序地优化可以得到80%地系统性能提升.因此可以肯定,通过优化应用程序来对数据库系统进行优化能获得更大地收益.对应用程序地优化通常可分为两个方面: 源代码地优化和SQL语句地优化.由于涉及到对程序逻辑地改变,源代码地优化在时间成本和风险上代价很高(尤其是对正在使用中地系统进行优化) .另一方面,源代码地优化对数据库系统性能地提升收效有限,因为应用程序对数据库地操作最终要表现为SQL语句对数据库地操作.对SQL语句进行优化有以下一些直接原因：1. SQL语句是对数据库(数据) 进行操作地惟一途径,应用程序地执行最终要归结为SQL语句地执行,SQL语句地效率对数据库系统地性能起到了决定性地作用.2. SQL语句消耗了70%～90%地数据库资源.3. SQL语句独立于程序设计逻辑,对SQL语句进行优化不会影响程序逻辑,相对于对程序源代码地优化,对SQL语句地优化在时间成本和风险上地代价都很低.4. SQL语句可以有不同地写法,不同地写法在性能上地差异可能很大.5. SQL语句易学,难精通.SQL语句地性能往往同实际运行系统地数据库结构、记录数量等有关,不存在普遍适用地规律来提升性能.二.优化数据库地思想：1、关键字段建立索引.2、使用存储过程,它使SQL变得更加灵活和高效.3、备份数据库和清除垃圾数据.4、SQL语句语法地优化.5、清理删除日志.三．SQL语句优化地原则：3.1 ORACLE地优化器优化器有时也被称为查询优化器,这是因为查询是影响数据库性能最主要地部分,不要以为只有SELECT语句是查询.实际上,带有任何WHERE条件地 DML(INSERT、UPDATE、DELETE)语句中都包含查询要求,在后面地文章中,当说到查询时,不一定只是指SELECT语句,也有可能指DML语句中地查询部分.优化器是所有关系数据库引擎中地最神秘、最富挑战性地部件之一,从性能地角度看也是最重要地部分,它性能地高低直接关系到数据库性能地好坏.我们知道,SQL语句同其它语言(如C语言)地语句不一样,它是非过程化(non-procedural)地语句,即当你要取数据时,不需要告诉数据库通过何种途径去取数据,如到底是通过索引取数据,还是应该将表中地每行数据都取出来,然后再通过一一比较地方式取数据(即全表扫描),这是由数据库地优化器决定地,这就是非过程化地含义,也就是说,如何取数据是由优化器决定,而不是应用开发者通过编程决定.在处理SQL地SELECT、UPDATE、 INSERT或DELETE语句时,Oracle 必须访问语句所涉及地数据,Oracle地优化器部分用来决定访问数据地有效路径,使得语句执行所需地I/O和处理时间最小.为了实现一个查询,内核必须为每个查询定制一个查询策略,或为取出符合条件地数据生成一个执行计划(execution plan).典型地,对于同一个查询,可能有几个执行计划都符合要求,都能得到符合条件地数据.例如,参与连接地表可以有多种不同地连接方法,这取决于连接条件和优化器采用地连接方法.为了在多个执行计划中选择最优地执行计划,优化器必须使用一些实际地指标来衡量每个执行计划使用地资源(I/0次数、 CPU等),这些资源也就是我们所说地代价(cost).如果一个执行计划使用地资源多,我们就说使用执行计划地代价大.以执行计划地代价大小作为衡量标准,优化器选择代价最小地执行计划作为真正执行该查询地执行计划,并抛弃其它地执行计划.在ORACLE地发展过程中,一共开发过2种类型地优化器：基于规则地优化器和基于代价地优化器.这2种优化器地不同之处关键在于：取得代价地方法与衡量代价地大小不同.现对每种优化器做一下简单地介绍：基于规则地优化器 -- Rule Based (Heuristic) Optimization(简称RBO)：在ORACLE7之前,主要是使用基于规则地优化器.ORACLE在基于规则地优化器中采用启发式地方法(Heuristic Approach)或规则(Rules)来生成执行计划.例如,如果一个查询地where条件(where clause)包含一个谓词(predicate,其实就是一个判断条件,如”=”, “>”, ”<”等),而且该谓词上引用地列上有有效索引,那么优化器将使用索引访问这个表,而不考虑其它因素,如表中数据地多少、表中数据地易变性、索引地可选择性等.此时数据库中没有关于表与索引数据地统计性描述,如表中有多上行,每行地可选择性等.优化器也不考虑实例参数,如multi block i/o、可用排序内存地大小等,所以优化器有时就选择了次优化地计划作为真正地执行计划,导致系统性能不高.如,对于select * from emp where deptno = 10。
oracle sql 优化技巧（实用版3篇）目录（篇1）1.Oracle SQL 简介2.优化技巧2.1 减少访问数据库次数2.2 选择最有效率的表名顺序2.3 避免使用 SELECT2.4 利用 DECODE 函数2.5 设置 ARRAYSIZE 参数2.6 使用 TRUNCATE 替代 DELETE2.7 多使用 COMMIT 命令2.8 合理使用索引正文（篇1）Oracle SQL 是一款广泛应用于各类大、中、小微机环境的高效、可靠的关系数据库管理系统。
为了提高 Oracle SQL 的性能，本文将为您介绍一些优化技巧。
首先，减少访问数据库的次数是最基本的优化方法。
Oracle 在内部执行了许多工作，如解析 SQL 语句、估算索引的利用率、读数据块等，这些都会大量耗费 Oracle 数据库的运行。
因此，尽量减少访问数据库的次数，可以有效提高系统性能。
其次，选择最有效率的表名顺序也可以明显提升 Oracle 的性能。
Oracle 解析器是按照从右到左的顺序处理 FROM 子句中的表名，因此，合理安排表名顺序，可以减少解析时间，提高查询效率。
在执行 SELECT 子句时，应尽量避免使用，因为 Oracle 在解析的过程中，会将依次转换成列名，这是通过查询数据字典完成的，耗费时间较长。
DECODE 函数也是一个很好的优化工具，它可以避免重复扫描相同记录，或者重复连接相同的表，提高查询效率。
在 SQLPlus 和 SQLForms 以及 ProC 中，可以重新设置 ARRAYSIZE 参数。
该参数可以明显增加每次数据库访问时的检索数据量，从而提高系统性能。
建议将该参数设置为 200。
当需要删除数据时，尽量使用 TRUNCATE 语句替代 DELETE 语句。
执行 TRUNCATE 命令时，回滚段不会存放任何可被恢复的信息，所有数据不能被恢复。
因此，TRUNCATE 命令执行时间短，且资源消耗少。
在使用 Oracle 时，尽量多使用 COMMIT 命令。
【接确保Oracle数据库sql语句高效执行的优化总结(一)】2.4 10053和10046事件2.4.1 10053事件我们在查看一条SQL的执行计划的时候，只能看到CBO 最终告诉我们的执行计划结果，但是不知道CBO 是根据什么来做的。
如果遇到了执行计划失真，如：一个SQL语句，很明显oracle应该使用索引，但是执行计划却没有使用索引。
无法进行分析判断。
10053事件就提供了这样的功能。
它产生的trace文件提供了Oracle如何选择执行计划，为什么会得到这样的执行计划信息。
对于10053事件的trace文件，我们只能直接阅读原始的trace文件，不能使用tkprof工具来处理，tkprof工具只能用来处理sql_trace 和10046事件产生的trace文件。
10053事件有两个级别：Level2：2级是1级的一个子集，它包含以下内容：Column statisticsSingle Access PathsJoin CostsTable Joins ConsideredJoin Methods Considered (NL/MS/HA)Level1：1级比2级更详细，它包含2级的所有内容，在加如下内容：Parameters used by the optimizerIndex statistics启用10053事件：关闭10053事件：说明：（1）sqlplus中打开autotrace看到的执行计划实际上是用explain plan 命令得到的，explain plan 命令不会进行bind peeking。
应该通过v$sql_plan查看SQL的真实的执行计划。
（2）10053只对CBO有效，而且如果一个sql语句已经解析过，就不会产生新的trace信息。
2.4.2 10046事件10046 事件主要用来跟踪SQL语句，它并不是ORACLE 官方提供给用户的命令，在官方文档上也找不到事件的说明信息。
Database : Oracle Database: SQL Advance & Tuning亚信联创管理学院聂耿青目录•Oracle数据库体系结构与SQL开发环境简介•数据查询语言DQL及其优化•SQL优化原则与工具简介•SQL优化方法与技巧•数据操纵语言DML及其优化•数据定义语言DDL数据查询语言DQL及其优化Main Structures and AccessPaths St tAccess Paths 1.Full Table Scan2Rowid ScanStructures Tables 2.Rowid Scan 3.Sample Table Scan4.Index Scan (Unique)5.Index Scan (Range)6.Index Scan (Full)7I d S (F t F ll)7.Index Scan (Fast Full)8.Index Scan (Skip)9.Index Scan (Index Join)Indexes()ing Bitmap Indexesbining Bitmap IndexesFull Table Scan–Performs multiblock readsPerforms multiblock reads (here DB_FILE_MULTIBLOCK_READ_COUNT = 4)–Reads all formatted blocks below the high-water mark HWM–May filter rows –Faster than indexrange scans for large amount of data B B B B B B B B B ...range scans for large amount of dataselect * from emp where ename='King';---------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|---------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 37 | 3 (0)||* 1 | TABLE ACCESS FULL | EMP | 1 | 37 | 3 (0)|---------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 -filter("ENAME"='King')Full Table Scans: Use CasesNo suitable index–No suitable index–Low selectivity filters (or no filters)–Small tableSmall table–High degree of parallelismFull table scan hint: FULL (<table name>)–Full table scan hint:FULL(<table name>)•在较大的表上不建议使用全表扫描，除非取出在较大的表上不建议使用全表扫描除非取出数据的比较多，超过总量的5% --10%，或你想使用并行查询功能时使用并行查询功能时。
通过ROWID的表存取(Table Access by ROWID或rowid lookup)：OOOOOOFFFBBBBBBRRR 取单行最快•Rowid OOOOOOFFFBBBBBBRRR•占用10个字节（32bit+10bit rfile#+22bit+16bit）•其中，O是对象ID，F是文件ID，B是块ID，R是行ID ,•select rowid, e.*•from employees e•/*64进制•A-Z <==> 0 -25 (26)•a-z <==> 26 -51 (26)()•0-9 <==> 52 -61 (10)•+/ <==> 62 -63 (2)*/Indexes: Overview•Index storage techniques:–B*-tree indexes: The default and the most commonB*t i d Th d f lt d th t•Normal•Function based: Precomputed value of a function or expressionFunction based:Precomputed value of a function or expression•Index-organized table (IOT)•Bitmap indexes•Cluster indexes: Defined specifically for cluster–Index attributes:•Key compression•Reverse keyAscending descending•Ascending, descending–Domain indexes: Specific to an application or cartridge推荐使用索引的两个原因•提高从数据表中检索少量的数据集的速度。
提高从数据表中检索少量的数据集的速度•预先排序结果集，这样SQL的order by等子句就不会引发内部排序了。
•用索引优化SQLIndex Unique ScanIndex Unique Scan•index UNIQUE ScanPK_EMP•通过唯一索引查找一个数值经常返回单个ROWIDcreate unique index PK_EMP on EMP(empno)select * from emp where empno = 9999;l t*f h9999--------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost| --------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 37 | 1| | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 1| | 2 | INDEX UNIQUE SCAN| PK_EMP | 1 | | 0| |2|INDEX UNIQUE SCAN|PK EMP|1||0| --------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 -access("EMPNO"=9999)2("EMPNO"9999)Index Range Scan Index Range SCAN g •Index Range SCAN I_DEPTNO•使用index rang scan 的3种情况：•(a) 在唯一索引列上使用了range 操作符(> < <> >= <= between)create index I DEPTNO on EMP(deptno);•(b) 在组合索引上，只使用部分列进行查询，导致查询出多行•(c) 对非唯一索引列或函数索引上进行的任何查询。
create index I_DEPTNO on EMP(deptno);select /*+ INDEX(EMP I_DEPTNO) */ *from emp where deptno = 10 and sal > 1000;---------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost ---------------------------------------------------------------------| 0 | SELECT STATEMENT | | 3 | 261 | 2| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 261 | 2| 2 | INDEX RANGE SCAN | I_DEPTNO | 3 | | 1---------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 -filter("SAL">1000)2 -access("DEPTNO"=10)Index Range Scan: Descending g g•Index Range SCAN IDX create index IDX on EMP(deptno);Index Range SCAN IDXcreate index IDX on EMP(deptno);select * from emp where deptno>20 order by deptno desc ;---------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost |---------------------------------------------------------------------| 0 | SELECT STATEMENT | | 6 | 522 | 2|| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 6 | 522 | 2|| 2 | INDEX RANGE SCAN DESCENDING | IDX | 6 | | 1|---------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 -access("DEPTNO">20)index_desc提示index_desc提示要求在范围扫描中使用降序索引在使用内置函数计算字段的最大值时索引。
