oracle数据库性能优化
1. 拜访Table的形式
ORACLE 采纳两种拜访表中记录的形式:
a.全表扫描 全表扫描就是程序地拜访表中每条记录. ORACLE采纳一次读入多个数据块(database block)的形式优化全表扫描. b. 通过ROWID拜访表 你能够采纳基于ROWID的拜访形式状况,进步拜访表的效率, , ROWID蕴含了表中记录的物理地位信息..ORACLE采纳索引(INDEX)实现了数据和存放数据的物理地位(ROWID)之间的分割. 通常索引提供了快速访问ROWID的办法,因而那些基于索引列的查问就能够失去性能上的进步. 2. 共享Sql语句
为了不反复解析雷同的SQL语句,在第一次解析之后, ORACLE将SQL语句寄存在内存中.这块位于零碎全局区域SGA(system global area)的共享池(shared buffer pool)中的内存能够被所有的数据库用户共享. 因而,当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同, ORACLE就能很快取得曾经被解析的语句以及最好的执行门路. ORACLE的这个性能大大地进步了SQL的执行性能并节俭了内存的应用.
惋惜的是ORACLE只对简略的表提供高速缓冲(cache buffering) ,这个性能并不适用于多表连贯查问.
数据库管理员必须在init.ora中为这个区域设置适合的参数,当这个内存区域越大,就能够保留更多的语句,当然被共享的可能性也就越大了.
当你向ORACLE 提交一个SQL语句,ORACLE会首先在这块内存中查找雷同的语句.
这里须要注明的是,ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语句必须完全相同(包含空格,换行等).
共享的语句必须满足三个条件:
A. 字符级的比拟:
以后被执行的语句和共享池中的语句必须完全相同.
例如:
SELECT * FROM EMP; 和下列每一个都不同 SELECT * from EMP; Select * From Emp; SELECT * FROM EMP; 3. (*)抉择最有效率的表名程序(只在基于规定的优化器中无效)
ORACLE的解析器依照从右到左的程序解决FROM子句中的表名,因而FROM子句中写在最初的表(根底表 driving table)将被最先解决. 在FROM子句中蕴含多个表的状况下,你必须抉择记录条数起码的表作为根底表.当ORACLE解决多个表时, 会使用排序及合并的形式连贯它们.首先,扫描第一个表(FROM子句中最初的那个表)并对记录进行排序,而后扫描第二个表(FROM子句中最初第二个表),最初将所有从第二个表中检索出的记录与第一个表中适合记录进行合并.
例如:
表 TAB1 16,384 条记录 表 TAB2 1 条记录 抉择TAB2作为根底表 (最好的办法) select count(*) from tab1,tab2 执行工夫0.96秒 抉择TAB1作为根底表 (不佳的办法) select count(*) from tab2,tab1 执行工夫26.09秒 如果有3个以上的表连贯查问, 那就须要抉择穿插表(intersection table)作为根底表, 穿插表是指那个被其余表所援用的表.
例如:
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
4 WHERE子句中的连贯程序．
ORACLE采纳自下而上的程序解析WHERE子句,依据这个原理,表之间的连贯必须写在其余WHERE条件之前, 那些能够过滤掉最大数量记录的条件必须写在WHERE子句的开端.
例如:
(低效,执行工夫156.3秒) SELECT … FROM EMP E WHERE SAL > 50000 AND JOB = ‘MANAGER' AND 25 < (SELECT COUNT() FROM EMP WHERE MGR=E.EMPNO); **(高效,执行工夫10.6秒) SELECT … FROM EMP E WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO) AND SAL > 50000 AND JOB = ‘MANAGER';
5. SELECT子句中防止应用‘*’
当你想在SELECT子句中列出所有的COLUMN时,应用动静SQL列援用 ‘' 是一个不便的办法.可怜的是,这是一个十分低效的办法. 实际上,ORACLE在解析的过程中, 会将'' 顺次转换成所有的列名, 这个工作是通过查问数据字典实现的, 这意味着将消耗更多的工夫.
6. 缩小拜访数据库的次数
当执行每条SQL语句时, ORACLE在外部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等等. 由此可见, 缩小拜访数据库的次数 , 就能实际上缩小ORACLE的工作量.
例如,
以下有2种办法能够检索出雇员号等于0342或0291的职员. 办法1 (低效) SELECT EMP_NAME , SALARY , GRADE FROM EMP WHERE EMP_NO = 342; SELECT EMP_NAME , SALARY , GRADE FROM EMP WHERE EMP_NO = 291; 办法2 (高效) SELECT A.EMP_NAME , A.SALARY , A.GRADE, B.EMP_NAME , B.SALARY , B.GRADE FROM EMP A,EMP B WHERE A.EMP_NO = 342 AND B.EMP_NO = 291; OrSELECT EMP_NAME , SALARY , GRADE FROM EMP WHERE EMP_NO = 342 or EMP_NO = 291;
7. 应用DECODE函数来缩小解决工夫
应用DECODE函数能够防止反复扫描雷同记录或反复连贯雷同的表.
例如:
SELECT COUNT(*)，SUM(SAL) FROMEMP WHERE DEPT_NO = 0020 AND ENAME LIKE‘SMITH%'; SELECT COUNT(*)，SUM(SAL) FROMEMP WHERE DEPT_NO = 0030 AND ENAME LIKE‘SMITH%'; 你能够用DECODE函数高效地失去雷同后果 SELECT COUNT(DECODE(DEPT_NO,0020,'X',NULL)) D0020_COUNT, COUNT(DECODE(DEPT_NO,0030,'X',NULL)) D0030_COUNT, SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL, SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL FROM EMP WHERE ENAME LIKE ‘SMITH%'; 相似的,DECODE函数也能够使用于GROUP BY 和ORDER BY子句中.
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时, 回滚段不再寄存任何可被复原的信息.当命令运行后,数据不能被复原.因而很少的资源被调用,执行工夫也会很短.
10. 尽量多应用COMMIT
只有有可能,在程序中尽量多应用COMMIT, 这样程序的性能失去进步,需要也会因为COMMIT所开释的资源而缩小:
COMMIT所开释的资源:
a. 回滚段上用于复原数据的信息. b. 被程序语句取得的锁 c. redo log buffer 中的空间 d. ORACLE为治理上述3种资源中的外部破费 在应用COMMIT时必须要留神到事务的完整性,事实中效率和事务完整性往往是鱼和熊掌不可得兼
11. 用EXISTS代替IN
在许多基于根底表的查问中,为了满足一个条件,往往须要对另一个表进行联接.在这种状况下, 应用EXISTS(或NOT EXISTS)通常将进步查问的效率.
低效: SELECT * FROM EMP (根底表) WHERE EMPNO > 0 AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB') 高效: SELECT * FROM EMP (根底表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB')
置信绝大多数人会应用第一种格局，因为它比拟容易编写，而实际上第二种格局要远比第一种格局的效率高。在Oracle中能够简直将所有的IN操作符子查问改写为应用EXISTS的子查问。
第二种格局中，子查问以‘select 'X'开始。使用EXISTS子句不论子查问从表中抽取什么数据它只查看where子句。这样优化器就不用遍历整个表而仅依据索引就可实现工作（这里假设在where语句中应用的列存在索引）。绝对于IN子句来说，EXISTS应用相连子查问，结构起来要比IN子查问艰难一些。
通过应用EXIST，Oracle零碎会首先查看主查问，而后运行子查问直到它找到第一个匹配项，这就节俭了工夫。Oracle零碎在执行IN子查问时，首先执行子查问，并将取得的后果列表寄存在在一个加了索引的长期表中。在执行子查问之前，零碎先将主查问挂起，待子查问执行结束，寄存在长期表中当前再执行主查问。这也就是应用EXISTS比应用IN通常查问速度快的起因。
12. 用NOT EXISTS代替NOT IN
在子查问中,NOT IN子句将执行一个外部的排序和合并. 无论在哪种状况下,NOT IN都是最低效的 (因为它对子查问中的表执行了一个全表遍历). 为了防止应用NOT IN ,咱们能够把它改写成外连贯(Outer Joins)或NOT EXISTS.
例如:
SELECT … FROM EMP WHERE DEPT_NO NOT IN (SELECT DEPT_NO FROM DEPT WHERE DEPT_CAT='A'); 为了提高效率.改写为: (外连贯: 高效) SELECT …. FROM EMP A,DEPT B WHERE A.DEPT_NO = B.DEPT(+) AND B.DEPT_NO IS NULL AND B.DEPT_CAT(+) = ‘A' (NOT EXISTS: 最高效) SELECT …. FROM EMP E WHERE NOT EXISTS (SELECT ‘X' FROM DEPT D WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT = ‘A');
13. 用表连贯替换EXISTS
通常来说 , 采纳表连贯的形式比EXISTS更有效率 SELECT ENAME FROM EMP E WHERE EXISTS (SELECT ‘X' FROM DEPT WHERE DEPT_NO = E.DEPT_NO AND DEPT_CAT = ‘A'); (更高效) SELECT ENAME FROM DEPT D,EMP E WHERE E.DEPT_NO = D.DEPT_NO AND DEPT_CAT = ‘A' ;
14. 用EXISTS替换DISTINCT
当提交一个蕴含一对多表信息(比方部门表和雇员表)的查问时,防止在SELECT子句中应用DISTINCT. 个别能够思考用EXIST替换
例如:
低效: SELECT DISTINCT D.DEPT_NO,D.DEPT_NAME FROM DEPT D,EMP E WHERE D.DEPT_NO = E.DEPT_NO 高效: SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT ‘X' FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO); EXISTS 使查问更为迅速,因为RDBMS外围模块将在子查问的条件一旦满足后,立即返回后果.