共计 5005 个字符，预计需要花费 13 分钟才能阅读完成。
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;
Or
SELECT 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 外围模块将在子查问的条件一旦满足后, 立即返回后果.
