Oracle SQL性能优化方法研究

1、 4/4Oracle SQL性能优化方法研究 Oracle SQL性能优化方法探讨 Oracle性能优化方法(SQL篇) (1) 1综述 (2) 2表分区的应用 (2) 3访问Table的方式 (3) 4共享SQL语句 (3) 5选择最有效率的表名顺序 (5) 6WHERE子句中的连接顺序 (6) 7SELECT子句中幸免使用* (6) 8减少访问数据库的次数 (6) 9使用DECODE函数来减少处理时刻 (7) 10整合简单,无关联的数据库访问 (8) 11删除重复记录 (8) 12用TRUNCATE替代DELETE (9) 13尽量多使用COMMIT (9) 14计算记录条数 (9) 15

2、用Where子句替换HAVING子句 (9) 16减少对表的查询 (10) 17通过内部函数提高SQL效率 (11) 18使用表的不名(Alias) (12) 19用EXISTS替代IN (12) 20用NOT EXISTS替代NOT IN (13) 21识不低效执行的SQL语句 (13) 22使用TKPROF 工具来查询SQL性能状态 (14) 23用EXPLAIN PLAN 分析SQL语句 (14) 24实时批量的处理 (16) 1综述 ORACLE数据库的性能调整是个重要，却又有难度的话题，如何有效地进行调整，需要通过反反复复的过程。在数据库建立时，就能依照顾用的需要合理设计分配表空间以

3、及存储参数、内存使用初始化参数，对以后的数据库性能有专门大的益处，建立好后，又需要在应用中不断进行应用程序的优化和调整，这需要在大量的实践工作中不断地积存经验，从而更好地进行数据库的调优。 数据库性能调优的方法 调整内存 调整I/O 调整资源的争用问题 调整操作系统参数 调整数据库的设计 调整应用程序 本文针对应用程序的调整，来讲明对数据库性能如何进行优化。 2表分区的应用 关于海量数据的表，能够考虑建立分区以提高操作效率。建 立分区一般以关键字为分区的标志，也能够以其他字段作为分区的标志，但效率不如关键字高。建立分区的语句在建表时能够进行讲明： create table TABLENAME(

4、) partition by range (PutOutNo) (partition PART1 values lessthan (200312319999) partition PART2 values lessthan (200412319999) 。 如此，在进行大部分数据查询，数据更新和数据插入时，Oracle自动推断操作应该在哪个分区进行，幸免了整表操作，提高了执行的效率 3访问Table的方式 ORACLE 采纳两种访问表中记录的方式: 全表扫描 全表扫描确实是顺序地访问表中每条记录. ORACLE采纳一次读入多个数据块(database block)的方式优化全表扫描. 通过RO

5、WID访问表 能够采纳基于ROWID的访问方式情况,提高访问表的效率, , ROWID包含了表中记录的物理位置信息.ORACLE采纳索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系. 通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就能够得到性能上的提高. 4共享SQL语句 为了不重复解析相同的SQL语句,在第一次解析之后, ORACLE将SQL语句存放在内存中.这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存能够被所有的数据库用户共享. 因此,当执行一个SQL语句(有时被称为一个

6、游标)时,假如它和之前的执行过 的语句完全相同, ORACLE就能专门快获得差不多被解析的语句以及最好的执行路径. ORACLE的那个功能大大地提高了SQL的执行性能并节约了内存的使用.但是ORACLE只对简单的表提供高速缓冲(cache buffering) ,那个功能并不适用于多表连接查询. 数据库治理员必须在init.ora中为那个区域设置合适的参数,当那个内存区域越大,就能够保留更多的语句,因此被共享的可能性也就越大了. 当向ORACLE 提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句.那个地点需要注明的是,ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语

7、句必须完全相同(包括空格,换行等). 共享的语句必须满足三个条件: 字符级的比较: 当前被执行的语句和共享池中的语句必须完全相同.例如: SELECT * FROM EMP; 和下列每一个都不同 SELECT * from EMP; Select * From Emp; SELECT * FROM EMP; 两个语句所指的对象必须完全相同: 例如: 用户对象名如何访问 Jack sal_limit private synonym Work_city public synonym Plant_detail public synonym Jill sal_limit private synonym

8、 Work_city public synonym Plant_detail table owner 下列SQL语句不能在这两个用户之间共享. select max(sal_cap) from sal_limit; 缘故 每个用户都有一个private synonym - sal_limit , 它们是不同的对象 下列SQL语句能在这两个用户之间共享. select count(*) from work_city where sdesc like NEW%; 缘故：两个用户访问相同的对象public synonym - work_city 下列SQL语句不能在这两个用户之间共享. select

9、 a.sdesc,b.location from work_city a , plant_detail b where a.city_id = b.city_id 缘故：用户jack 通过private synonym访问plant_detail 而jill 是表的所有者,对象不同. 两个SQL语句中必须使用相同的名字的绑定变量(bind variables) 例如：第一组的两个SQL语句是相同的(能够共享),而第二组中的两个语句是不同的(即使在运行时,赋于不同的绑定变量相同的值) 1. select pin , name from people where pin = :blk1.pin;

10、select pin , name from people where pin = :blk1.pin; 2. select pin , name from people where pin = :blk1.ot_ind; select pin , name from people where pin = :blk1.ov_ind; 5选择最有效率的表名顺序 ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处

11、理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并. 例如: 表 TAB1 16,384 条记录,表 TAB2 1 条记录 选择TAB2作为基础表 (最好的方法) select count(*) from tab1,tab2 选择TAB2作为基础表 (不佳的方法) select count(*) from tab2,tab1 假如有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表,

12、 交叉表是指那个被其他 表所引用的表. 例如: EMP表描述了LOCATION表和CATEGORY表的交集. SELECT * FROM LOCATION L , CATEGORY C, EMP E WHERE E.EMP_NO BETWEEN 1000 AND 2000 AND E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN 将比下列SQL更有效率 SELECT * FROM EMP E , LOCATION L , CATEGORY C WHERE E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN AND E.EMP_NO BETWEEN 1000 AND 2000 6WHERE子句中的连接顺序 ORACLE采纳自下而上的顺