截图
简介
这是oracle优化ppt，包括了内容概述，优化基础知识，性能调整综述，有效的应用设计，SQL语句的处理过程，Oracle的优化器，Oracle的执行计划，注意事项等内容，欢迎点击下载。
oracle优化ppt是由红软PPT免费下载网推荐的一款课件PPT类型的PowerPoint.
ORACLE培训 --SQL性能优化
内容概述
课程主要讨论：
SQL语句执行的过程、ORACLE优化器
，表之间的关联，如何得到SQL执行计划，如何分析执
行计划等内容，从而由浅到深的方式了解SQL优化的过
程，使大家逐步掌握SQL优化。
目录
优化基础知识
性能调整综述
有效的应用设计
SQL语句的处理过程
Oracle的优化器
Oracle的执行计划
注意事项
一、优化基础知识
概述
性能管理
性能问题
调整的方法
SQL优化机制
应用的调整
SQL语句的处理过程
共享SQL区域
SQL语句处理的阶段
共享游标
SQL编码标准
Oracle 优化器介绍
SQL Tunning Tips
优化Tools
性能管理
SQL 优化衡量指标
调优领域
调整的方法
不同调整产生相应性能收益
调整的角色
SQL语句优化是提高性能的重要环节
开发人员不能只注重功能的实现，不管性能如何
开发人员不能把Oracle当成一个黑盒子，必须了解其结构、处理SQL和数据的方法
必需遵守既定的开发规范
未经过SQL语句优化的模块不要上线
SQL语句优化的过程
定位有问题的语句
检查执行计划
检查执行过程中优化器的统计信息
分析相关表的记录数、索引情况
改写SQL语句、使用HINT、调整索引、表分析
有些SQL语句不具备优化的可能，需要优化处理方式
达到最佳执行计划
什么是好的SQL语句？
尽量简单，模块化
易读、易维护
节省资源
内存
CPU
扫描的数据块要少
少排序
不造成死锁
SQL共享原理
ORACLE将执行过的SQL语句存放在内存的共享池(shared
buffer pool)中，可以被所有的数据库用户共享。
当你执行一个SQL语句(有时被称为一个游标)时，
如果它和之前的执行过的语句完全相同，
ORACLE就能很快获得已经被解析的语句以及最好的 执行路径. 这个功能大大地提高了
SQL的执行性能并节省了内存的使用。
SQL共享原理
SQL共享的三个条件
当前被执行的语句和共享池中的语句必须完全相同 （包括大小写、空格、换行等）
两个语句所指的对象必须完全相同 （同义词与表是不同的对象）
两个SQL语句中必须使用相同的名字的绑定变量(bind variables)
共享SQL语句
SQL语句的处理过程
Sql 处理过程
SQL PARSE与共享SQL语句
当一个Oracle实例接收一条sql后
1、Create a Cursor 创建游标
2、Parse the Statement 分析语句
3、Describe Results of a Query 描述查询的结果集
4、Define Output of a Query 定义查询的输出数据
5、Bind Any Variables 绑定变量
6、Parallelize the Statement 并行执行语句
7、Run the Statement 运行语句
8、Fetch Rows of a Query 取查询出来的行
9、Close the Cursor 关闭游标
为什么要bind variables?
字符级的比较:
SELECT * FROM USER_FILES
WHERE USER_NO = ‘10001234’;
与
SELECT * FROM USER_FILES
WHERE USER_NO = ‘10004321’;
检查：
select name，executions
from v$db_object_cache
where name like 'select * from user_files%'
什么叫做重编译问题
什么叫做重编译？
下面这个语句每执行一次就需要在SHARE POOL 硬解析一
次，一百万用户就是一百万次，消耗CPU和内存，如果业务
量大，很可能导致宕库……
如果绑定变量，则只需要硬解析一次，重复调用即可
select * from dConMsg
where contract_no = 32013484095139
绑定变量解决重编译问题
未使用绑定变量的语句
sprintf(sqlstr， "insert into scott.test1 (num1， num2) values
(%d，%d)"，n_var1， n_var2);
EXEC SQL EXECUTE IMMEDIATE :sqlstr ;
EXEC SQL COMMIT;
使用绑定变量的语句
strcpy(sqlstr， "insert into test (num1， num2) values (:v1， :v2)");
EXEC SQL PREPARE sql_stmt FROM :sqlstr;
EXEC SQL EXECUTE sql_stmt USING :n_var1， :n_var2;
EXEC SQL COMMIT;
绑定变量的注意事项
注意：
1、不要使用数据库级的变量绑定参数cursor_sharing来强
制绑定，无论其值为 force 还是similar
2、有些带> < 的语句绑定变量后可能导致优化器无法正确
使用索引
ORACLE 优化器模式 概述
访问数据表的方式
数据库性能
影响数据库系统性能的要素：
主机CPU，RAM，存储系统;
OS参数配置，ORACLE参数配置;
应用方面：数据库设计及SQL编程的质量
一个性能优秀的应用系统需要：
良好的硬件配置；正确合理的数据库及中间件参数配置；合理的数据库设计；良好的sql编程；运行期的性能优化
SQL Tunning 的重点
SQL: insert， update， delete， select;
主要关注的是select
关注的是：如何用最小的硬件资源消耗、最少的响应时间定位数据位置
SQL优化的一般性原则
目标：
减少服务器资源消耗（主要是磁盘IO）；
设计方面：
尽量依赖oracle的优化器，并为其提供条件；
合适的索引，索引的双重效应，列的选择性；
编码方面：
利用索引，避免大表FULL TABLE SCAN；
合理使用临时表；
避免写过于复杂的sql，不一定非要一个sql解决问题；
在不影响业务的前提下减小事务的粒度；
优化概括
课程Oracle数据库SQL语句优化的总体策略。以这些
优化策略为指导，通过经验总结，我们可以不断地丰富优
化方案，进而指导我们进行应用系统的数据库性能优化。
以下枚举几则被证明行之有效的优化方案：
● 创建表的时候。应尽量建立主键，尽量根据实际需要调整数据表的PCTFREE和PCTUSED参数；大数据表删除，用truncate table代替delete。
● 合理使用索引，在OLTP应用中一张表的索引不要太多。数据重复量大的列不要建立二叉树索引，可以采用位图索引；组合索引的列顺序尽量与查询条件列顺序保持一致；对于数据操作频繁的表，索引需要定期重建，以减少失效的索引和碎片。
优化概括
● 查询尽量用确定的列名，少用*号。select count(key)from
tab where key> 0性能优于select count(*)from tab；
尽量少嵌套子查询，这种查询会消耗大量的CPU资源；对于有比较多
or运算的查询，建议分成多个查询，用union all联结起来；多表查询
的查询语句中，选择最有效率的表名顺序。Oracle解析器对表解析从
右到左，所以记录少的表放在右边。
● 尽量多用commit语句提交事务，可以及时释放资源、解
锁、释放日志空间、减少管理花费；在频繁的、性能要求比较高的
数据操作中，尽量避免远程访问，如数据库链等，访问频繁的表可
以常驻内存：alter table．．．cache；
● 在Oracle中动态执行SQL，尽量用execute方式，不用
dbms_sql包。
** SQL Tunning Tips **
sql 语句的编写原则和优化
sql 语句的编写原则和优化
Tunning Tip的各个方面
1.不要让Oracle做得太多；
2.给优化器更明确的命令；
3.减少访问次数；
4.细节上的影响；
1.不要让Oracle做得太多
避免复杂的多表关联
select …
from user_files uf， df_money_files dm，
cw_charge_record cc
where
uf.user_no = dm.user_no
and dm.user_no = cc.user_no
and ……
and not exists(select …)
???
很难优化，随着数据量的增加性能的风险很大。
避免使用 ‘ * ‘
当你想在SELECT子句中列出所有的COLUMN时，使用动态
SQL列引用 ‘*’ 是一个方便的方法.不幸的是，这是一个非常低
效的方法. 实际上，ORACLE在解析的过程中， 会将’*’ 依次转
换成所有的列名， 这个工作是通过查询数据字典完成的， 这意
味着将耗费更多的时间；
只提取你所要使用的列；
使用别名能够加快解析速度；
避免使用耗费资源的操作
带有DISTINCT，UNION，MINUS，INTERSECT，ORDER BY的
SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能.
DISTINCT需要一次排序操作， 而其他的至少需要执行两次
排序.
例如，一个UNION查询，其中每个查询都带有GROUP BY子句
， GROUP BY会触发嵌入排序(NESTED SORT) ; 这样， 每个
查询需要执行一次排序， 然后在执行UNION时， 又一个唯一
排序(SORT UNIQUE)操作被执行而且它只能在前面的嵌入
排序结束后才能开始执行. 嵌入的排序的深度会大大影响查
询的效率.
通常， 带有UNION， MINUS ， INTERSECT的SQL语句都可以
用其他方式重写.
用EXISTS替换DISTINCT
例如:
低效:
SELECT DISTINCT DEPT_NO，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);
用UNION-ALL 替换UNION ( if possible)
当SQL语句需要UNION两个查询结果集合时，这两个结果集合会以
UNION-ALL的方式被合并， 然后在输出最终结果前进行排序.
举例:
低效：
SELECT ACCT_NUM， BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = ’31-DEC-95’
UNION
SELECT ACCT_NUM， BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = ’31-DEC-95’
高效:
SELECT ACCT_NUM， BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = ’31-DEC-95’
UNION ALL
SELECT ACCT_NUM， BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = ’31-DEC-95’
2. 给优化器更明确的命令
自动选择索引
如果表中有两个以上（包括两个）索引，其中有一个唯一性
索引，而其他是非唯一性．
在这种情况下，ORACLE将使用唯一性索引而完全忽略非唯
一性索引．
举例:
SELECT ENAME
FROM EMP
WHERE EMPNO = 2326
AND DEPTNO = 20 ;
这里，只有EMPNO上的索引是唯一性的，所以EMPNO索
引将用来检索记录．
TABLE ACCESS BY ROWID ON EMP
INDEX UNIQUE SCAN ON EMP_NO_IDX
至少要包含组合索引的第一列
如果索引是建立在多个列上， 只有在它的第一个列(leading
column)被where子句引用时，优化器才会选择使用该索引.
SQL> create table multiindexusage ( inda number ， indb number ， descr varchar2(10));
Table created.
SQL> create index multindex on multiindexusage(inda，indb);
Index created.
SQL> set autotrace traceonly
SQL> select * from multiindexusage where inda = 1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'MULTIINDEXUSAGE'
2 1 INDEX (RANGE SCAN) OF 'MULTINDEX' (NON-UNIQUE)
SQL> select * from multiindexusage where indb = 1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'MULTIINDEXUSAGE'
很明显， 当仅引用索引的第二个列时，优化器使用了全表扫描而忽略了索引
避免在索引列上使用函数
WHERE子句中，如果索引列是函数的一部分．优化器将不
使用索引而使用全表扫描．
举例:
低效：
SELECT …
FROM DEPT
WHERE SAL * 12 > 25000;
高效:
SELECT …
FROM DEPT
WHERE SAL > 25000/12;
避免使用前置通配符
WHERE子句中， 如果索引列所对应的值的第一个字符由通
配符(WILDCARD)开始， 索引将不被采用.
SELECT USER_NO，USER_NAME，ADDRESS
FROM USER_FILES
WHERE USER_NO LIKE '%109204421';
在这种情况下，ORACLE将使用全表扫描.
避免在索引列上使用NOT
通常，我们要避免在索引列上使用NOT， NOT会产生在和在
索引列上使用函数相同的影响. 当ORACLE”遇到”NOT，他就
会停止使用索引转而执行全表扫描.
举例:
低效: (这里，不使用索引)
SELECT …
FROM DEPT
WHERE DEPT_CODE NOT = 0;
高效: (这里，使用了索引)
SELECT …
FROM DEPT
WHERE DEPT_CODE > 0;
避免在索引列上使用 IS NULL和IS NOT NULL
避免在索引中使用任何可以为空的列，ORACLE将无法使用该
索引 ．对于单列索引，如果列包含空值，索引中将不存在此记
录. 对于复合索引，如果每个列都为空，索引中同样不存在此
记录. 如果至少有一个列不为空，则记录存在于索引中．
如果唯一性索引建立在表的A列和B列上， 并且表中存在一条记
录的A，B值为(123，null) ， ORACLE将不接受下一条具有相同
A，B值（123，null）的记录(插入). 然而如果所有的索引列都为
空，ORACLE将认为整个键值为空而空不等于空. 因此你可以
插入1000条具有相同键值的记录，当然它们都是空!
因为空值不存在于索引列中，所以WHERE子句中对索引列进行
空值比较将使ORACLE停用该索引.
任何在where子句中使用is null或is not null的语句优化器是
不允许使用索引的。
避免出现索引列自动转换
当比较不同数据类型的数据时， ORACLE自动对列进行简单
的类型转换.
假设EMP_TYPE是一个字符类型的索引列.
SELECT USER_NO，USER_NAME，ADDRESS
FROM USER_FILES
WHERE USER_NO = 109204421
这个语句被ORACLE转换为:
SELECT USER_NO，USER_NAME，ADDRESS
FROM USER_FILES
WHERE TO_NUMBER(USER_NO) = 109204421
因为内部发生的类型转换， 这个索引将不会被用到!
在查询时尽量少用格式转换
如用 WHERE a.order_no = b.order_no
不用
WHERE TO_NUMBER (substr(a.order_no， instr(b.order_no， '.') - 1)
= TO_NUMBER (substr(a.order_no， instr(b.order_no， '.') - 1)
3.减少访问次数
减少访问数据库的次数
当执行每条SQL语句时， ORACLE在内部执行了许多工作:
解析SQL语句， 估算索引的利用率， 绑定变量 ， 读数据块等等.
由此可见， 减少访问数据库的次数 ， 就能实际上减少
ORACLE的工作量.
类比，工程实施
使用DECODE来减少处理时间
例如:
SELECT COUNT(*)，SUM(SAL)
FROM EMP
WHERE DEPT_NO = 0020
AND ENAME LIKE ‘SMITH%’;
SELECT COUNT(*)，SUM(SAL)
FROM EMP
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%’;
减少对表的查询
在含有子查询的SQL语句中，要特别注意减少对表的查询.
例如:
低效
SELECT TAB_NAME
FROM TABLES
WHERE TAB_NAME = ( SELECT TAB_NAME
FROM TAB_COLUMNS
WHERE VERSION = 604)
AND DB_VER= ( SELECT DB_VER
FROM TAB_COLUMNS
WHERE VERSION = 604)
高效
SELECT TAB_NAME
FROM TABLES
WHERE (TAB_NAME，DB_VER)
= ( SELECT TAB_NAME，DB_VER)
FROM TAB_COLUMNS
WHERE VERSION = 604)
4. 细节上的影响
WHERE子句中的连接顺序
ORACLE采用自下而上的顺序解析WHERE子句，根据这个原
理， 当在WHERE子句中有多个表联接时，WHERE子句中排
在最后的表应当是返回行数可能最少的表，有过滤条件的子
句应放在WHERE子句中的最后。
如：设从emp表查到的数据比较少或该表的过滤条件比较确定，能大大缩小查询范围，则将最具有选择性部分放在WHERE子句中的最后：
select * from emp e，dept d
where d.deptno >10 and e.deptno =30 ;
如果dept表返回的记录数较多的话，上面的查询语句会比下面的查询语句响应快得多。
select * from emp e，dept d
where e.deptno =30 and d.deptno >10 ;
WHERE子句 ——函数、表达式使用
最好不要在WHERE子句中使用函或表达式，如果要使用的话，最好统一使用相同的表达式或函数，这样便于以后使用合理的索引。
Order by语句
ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制，也可以将函数加入列中（象联接或者附加等）。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。
仔细检查order by语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by子句中使用表达式。
联接列
对于有联接的列，即使最后的联接值为一个静态值，优化器
是不会使用索引的。
select * from employss where first_name||''||last_name ='Beill Cliton';
系统优化器对基于last_name创建的索引没有使用。
当采用下面这种SQL语句的编写，Oracle系统就可以采用基
于last_name创建的索引。 select * from employee where first_name ='Beill' and last_name ='Cliton';
带通配符（%）的like语句
通配符（%）在搜寻词首出现，Oracle系统不使用
last_name的索引。
select * from employee where last_name like '%cliton%';
在很多情况下可能无法避免这种情况，但是一定要心中有底
，通配符如此使用会降低查询速度。然而当通配符出现在字
符串其他位置时，优化器就能利用索引。在下面的查询中索
引得到了使用：
select * from employee where last_name like 'c%';
用Where子句替换HAVING子句
避免使用HAVING子句， HAVING 只会在检索出所有记录之后才对结果
集进行过滤. 这个处理需要排序，总计等操作. 如果能通过WHERE子句限
制记录的数目，那就能减少这方面的开销.
例如:
低效:
SELECT REGION，AVG(LOG_SIZE)
FROM LOCATION
GROUP BY REGION
HAVING REGION REGION != ‘SYDNEY’
AND REGION != ‘PERTH’
高效
SELECT REGION，AVG(LOG_SIZE)
FROM LOCATION
WHERE REGION REGION != ‘SYDNEY’
AND REGION != ‘PERTH’
GROUP BY REGION
顺序
WHERE > GROUP > HAVING
用NOT EXISTS 替代 NOT IN
在子查询中，NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况
下，NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历).
使用NOT EXISTS 子句可以有效地利用索引。尽可能使用NOT EXISTS
来代替NOT IN，尽管二者都使用了NOT（不能使用索引而降低速度），
NOT EXISTS要比NOT IN查询效率更高。
例如:
语句1
SELECT dname， deptno FROM dept WHERE
deptno NOT IN (SELECT deptno FROM emp);
语句2
SELECT dname， deptno FROM dept WHERE
NOT EXISTS
(SELECT deptno FROM emp WHERE dept.deptno = emp.deptno);
2要比1的执行性能好很多。
因为1中对emp进行了full table scan，这是很浪费时间的操作。而且1中没有用到emp的index， 因为没有where子句。而2中的语句对emp进行的是缩小范围的查询。
用索引提高效率
索引是表的一个概念部分，用来提高检索数据的效率，ORACLE使
用了一个复杂的自平衡B-tree结构. 通常，通过索引查询数据比全表
扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时，
ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以
提高效率. 另一个使用索引的好处是，它提供了主键(primary key)
的唯一性验证。
通常， 在大型表中使用索引特别有效. 当然，你也会发现， 在扫描小
表时，使用索引同样能提高效率. 虽然使用索引能得到查询效率的提
高，但是我们也必须注意到它的代价. 索引需要空间来存储，也需要
定期维护， 每当有记录在表中增减或索引列被修改时， 索引本身也
会被修改. 这意味着每条记录的INSERT ， DELETE ， UPDATE将为
此多付出4 ， 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理，
那些不必要的索引反而会使查询反应时间变慢.。定期的重构索引
是有必要的。
避免在索引列上使用计算
WHERE子句中，如果索引列是函数的一部分．优化器将不
使用索引而使用全表扫描．
低效：
SELECT … FROM DEPT WHERE SAL * 12 > 25000;
高效:
SELECT … FROM DEPT WHERE SAL > 25000/12;
用>= 替代 >
如果DEPTNO上有一个索引。
高效:
SELECT *
FROM EMP
WHERE DEPTNO >=4
低效:
SELECT *
FROM EMP
WHERE DEPTNO >3
通过使用>=、<=等，避免使用NOT命令
例子：
select * from employee where salary <> 3000;
对这个查询，可以改写为不使用NOT：
select * from employee where salary<3000 or salary>3000;
虽然这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种查询允许Oracle对salary列使用索引，而第一种查询则不能使用索引。
如果有其它办法，不要使用子查询。
外部联接"+"的用法
外部联接"+"按其在"="的左边或右边分左联接和右联接。若不带"+"运算符的表中的一个行不直接匹配于带"+"预算符的表中的任何行，则前者的行与后者中的一个空行相匹配并被返回。利用外部联接"+"，可以替代效率十分低下的 not in 运算，大大提高运行速度。例如，下面这条命令执行起来很慢：
select a.empno from emp a where a.empno not in
(select empno from emp1 where job='SALE');
利用外部联接，改写命令如下:
select a.empno from emp a ，emp1 b
where a.empno=b.empno(+)
and b.empno is null
and b.job='SALE';
这样运行速度明显提高.
尽量多使用COMMIT
事务是消耗资源的，大事务还容易引起死锁
COMMIT所释放的资源:
回滚段上用于恢复数据的信息.
被程序语句获得的锁
redo log buffer 中的空间
ORACLE为管理上述3种资源中的内部花费
用TRUNCATE替代DELETE
当删除表中的记录时，在通常情况下， 回滚段(rollback
segments ) 用来存放可以被恢复的信息. 如果你没有
COMMIT事务，ORACLE会将数据恢复到删除之前的状态(准
确地说是恢复到执行删除命令之前的状况)
而当运用TRUNCATE时， 回滚段不再存放任何可被恢复的
信息.当命令运行后，数据不能被恢复.因此很少的资源被调用，
执行时间也会很短.
计算记录条数
和一般的观点相反， count(*) 比count(1)稍快 ， 当然如果可
以通过索引检索，对索引列的计数仍旧是最快的.
例如
COUNT(EMPNO)
字符型字段的引号
比如有的表PHONE_NO字段是CHAR型，而且创建有索引，
但在WHERE条件中忘记了加引号，就不会用到索引。
WHERE PHONE_NO=‘13920202022’
WHERE PHONE_NO=13920202022
优化EXPORT和IMPORT
使用较大的BUFFER(比如10MB ， 10，240，000)可以提高
EXPORT和IMPORT的速度;
ORACLE将尽可能地获取你所指定的内存大小，即使在内存
不满足，也不会报错.这个值至少要和表中最大的列相当，否则
列值会被截断;
** 优化 Tools **
SQL 语句的执行步骤
语法分析 ，分析语句的语法是否符合规范，衡量语句中各表达式的意义。
语义分析 ，检查语句中涉及的所有数据库对象是否存在，且用户有相应的权限。
视图转换，将涉及视图的查询语句转换为相应的对基表查询语句。
表达式转换， 将复杂的 SQL 表达式转换为较简单的等效连接表达式。
选择优化器，不同的优化器一般产生不同的“执行计划”
选择连接方式， ORACLE 有三种连接方式，对多表连接 ORACLE 可选择适当的连接方式。
选择连接顺序， 对多表连接 ORACLE 选择哪一对表先连接，选择这两表中哪个表做为源数据表。
选择数据的搜索路径， 根据以上条件选择合适的数据搜索路径，如是选用全表搜索还是利用索引或是其他的方式。
运行“执行计划”
优化器与执行计划
Oracle在执行一个SQL之前，首先要分析一下语句的执行计
划，然后再按执行计划去执行。分析语句的执行计划的工作
是由优化器(Optimizer)来完成的
Oracle的优化器共有两种的优化方式，即基于规则的优化方
式(Rule-Based Optimization，简称为RBO)和基于代价的优
化方式(Cost-Based Optimization，简称为CBO)。
A、RBO方式：优化器在分析SQL语句时，所遵循的是Oracle内部预定
的一些规则。比如我们常见的，当一个where子句中的一列有索引时去走
索引。
B、CBO方式：是看语句的代价(Cost)了，这里的代价主要指Cpu和内存
。优化器在判断是否用这种方式时，主要参照的是表及索引的统计信息，
很多的时侯过期统计信息会令优化器做出一个错误的执行计划在
Oracle8及以后的版本，Oracle推荐用CBO的方式。
在Oracle10g中，取消了RBO的支持。
优化器与执行计划
Rule:即走基于规则的方式
Choose:默认的情况下Oracle用的便是这种方式。当一个表或或索引有
统计信息，则走CBO的方式，如果表或索引没统计信息，表又不是特别的小，
而且相应的列有索引时，那么就走索引，走RBO的方式
First Rows:它与Choose方式是类似的，所不同的是当一个表有统计信息
时，它将是以最快的方式返回查询的最先的几行，从总体上减少了响应时
间
All Rows:all_rows是oracle优化器默认的模式，它将选择一种在最短时
间内返回所有数据的执行计划，它将基于整体成本的考虑.
first_rows_n:first_rows_n是根据成本而不是基于硬编码的规则来选择
执行计划.n可以是1，10，100，1000或者直接用first_rows(n) hint指定任意
正数.这里的n是我们想获取结果集的前n条记录，这种需求在很多分页语
句的需求中会碰到.
用EXPLAIN PLAN 分析SQL语句
EXPLAIN PLAN 是一个很好的分析SQL语句的工具，它甚至
可以在不执行SQL的情况下分析语句. 通过分析，我们就可以
知道ORACLE是怎么样连接表，使用什么方式扫描表(索引扫
描或全表扫描)以及使用到的索引名称.
你需要按照从里到外，从上到下的次序解读分析的结果.
EXPLAIN PLAN分析的结果是用缩进的格式排列的， 最内部
的操作将被最先解读， 如果两个操作处于同一层中，带有最小
操作号的将被首先执行.
NESTED LOOP是少数不按照上述规则处理的操作， 正确的
执行路径是检查对NESTED LOOP提供数据的操作，其中操
作号最小的将被最先处理.
Autotrace 解读
使用TKPROF 工具
SQL trace 工具收集正在执行的SQL的性能状态数据并记录到
一个跟踪文件中. 这个跟踪文件提供了许多有用的信息，例如解
析次数.执行次数，CPU使用时间等.这些数据将可以用来优化你
的系统.
设置SQL TRACE在会话级别: 有效
ALTER SESSION SET SQL_TRACE TRUE
设置SQL TRACE 在整个数据库有效， 你必须将SQL_TRACE
参数在init.ora中设为TRUE， USER_DUMP_DEST参数说明了
生成跟踪文件的目录
再使用TKPROF对TRACE文件进行分析
分析结果更加准确、清楚
在SQLPLUS 配置AUTOTRACE
在SQLPLUS 配置AUTOTRACE
1、 首先创建PLUSTRACE角色并且赋给public：
Sql> @$ORACLE_HOME/sqlplus/admin/plustrce.sql
2、 赋权限给用户
Sql> grant plustrace to public（预赋权的用户名）;
3、以SYSTEM用户创建PLAN_TABLE表
Sql> @$ORACLE_HOME/rdbms/admin/utlxplan.sql
Sql> create public synonym plan_table for plan_table;
Sql> grant all on plan_table to public;
在每个用户下设置AUTOTRACE可显示其执行计划。
优化器与执行计划
SQL> select ename，dname from emp， dept where emp.deptno=dept.deptno and dept.dname in ('ACCOUNTING'，'RESEARCH'，'SALES'，'OPERATIONS');
Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 TABLE ACCESS (FULL) OF 'EMP' 3 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' 4 3 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
最起码要解决全表扫描问题
改变where条件的次序一般没有用
目录
优化基础知识
性能调整综述
有效的应用设计
SQL语句的处理过程
Oracle的优化器
Oracle的执行计划
注意事项
性能调整综述
谁来调整系统?
什么时候调整?
建立有效调整的目标
在设计和开发时的调整
谁来调整系统
应用设计人员必须传达应用系统的设计，使得每个人都清楚应用中的数据流动.
应用开发人员必须传达他们选择的实现策略，使得语句调整的过程中能快速、容易地识别有问题的应用模块和可疑的SQL语句.
数据库管理人员必须仔细地监控系统活动并提供它们的资料，使得异常的系统性能可被快速得识别和纠正.
硬件/软件管理人员必须传达系统的硬件、软件配置并提供它们的资料，使得相关人员能有效地设计和管理系统。
谁来调整系统
与系统涉及的每个人都在调整过程中起某些作用，当上面提及的那些人员传达了系统的特性并提供了它们的资料，调整就能相对的容易和更快一些。
事实上的结果是：数据库管理员对调整负有全部或主要的责任。但是，数据库管理员很少有合适的系统方面的资料，而且，在很多情况下，数据库管理员往往是在实施阶段才介入数据库，这就给调整工作带来许多负面的影响，因为在设计阶段的缺陷是不能通过DBA的调整而得以解决，而设计阶段的缺陷往往对数据库性能造成极大的影响。
在真正成熟的开发环境下，开发人员作为纯代码编写人员时，对性能的影响最小，此时大部分的工作应由应用设计人员完成，而且数据库管理员往往在前期的需求管理阶段就介入，为设计人员提供必要的技术支持。
调整并不是数据库管理员的专利，相反大部分应该是设计人员和开发人员的工作，这就需要设计人员和开发人员具体必要的数据库知识，这样才能组成一个高效的团队，然而事实上往往并非如此。
什么时候调整系统
多数人认为当用户感觉性能差时才进行调整，这对调整过程中使用某些最有效的调整策略来说往往是太迟了。此时，如果你不愿意重新设计应用的话，你只能通过重新分配内存(调整SGA)和调整I/O的办法或多或少地提高性能。Oracle提供了许多特性，这些特性只有应用到正确地设计的系统中时才能够很大地提高性能。
应用设计人员需要在设计阶段设置应用的性能期望值。然后在设计和开发期间，应用设计人员应考虑哪些Oracle 特性可以对系统有好处，并使用这些特性。
通过良好的系统设计，你就可以在应用的生命周期中消除性能调整的代价和挫折。下图说明在应用的生命周期中调整的相对代价和收益，最有效的调整 时间是在设计阶段。在设计期间的调整能以最低的代价给你最大的收益。
什么时候调整系统
图: 在应用生命周期中调整的代价
什么时候调整系统
图: 在应用生命周期中调整的收益
调整的目标
不管正在设计或维护系统，应该建立专门的性能目标，它使你知道何时要作调整。调整你的系统的最有效方法如下：
当设计系统时考虑性能
调整操作系统的硬件和软件
识别性能瓶颈
确定问题的原因
采取纠正的动作
当你设计系统时，制定专门的目标；例如，响应时间小于３秒。当应
用不能满足此目标时，识别造成变慢的瓶颈（例如，I/O竞争），确
定原因，采取纠正动作。在开发期间，你应测试应用研究，确定在采
取应用之前是否满足设计的性能目标。
调整的目标
调整通常是一系列开销。一旦确定了瓶颈，可能要牺牲一些其它方面的指标来达到所要的结果。例如，如果I/O有问题，你可能需要更多内存或磁盘。如果不可能买，你可能要限制系统的并发性，来获取所需的性能。如果你已经明确地定义了性能的目标，那用什么来交换高性能的决策就变的很容易的，因为已经确定了哪些方面是最重要的，如果我的目标为高性能，可能牺牲一些空间资源。
随着应用的越来越庞大，硬件性能的提高，全面的调整应用逐渐变成代价高昂的行为，在这样情况下，要取得最大的投入/效率之比，较好的办法是调整应用的关键部分，使其达到比较高的性能，这样从总体上来说，整个系统的性能也是比较高的。这也就是有名的20/80原则，调整应用的20%(关键部分)，能解决80%的问题。
SQL 调整的目标
去掉不必要的大型表的全表扫描。
缓存小型表的全表扫描。
校验优化索引的使用。
检验优化的连接技术。
以上目标任务将占据SQL调整90%以上的工作。
在设计和开发时调整
良好设计的系统可防止在应用生命周期中产生性能问题。
系统设计人员和应用开发人员必须了解Oracle的查询处理机制以便写出高效的SQL语句。
“有效的应用设计”讨论了你的系统中各种可用的配置，以及每种配置更适合哪种类型的应用。
“优化器”讨论了Oracle的查询优化器，以及如何写语句以获取最快的结果。
在设计和开发时调整
当设计你的系统时，使用下列优化性能的准则：
- 消除客户机／服务器应用中不必要的网络传输。使用存储过程。
- 使用适合你系统的Oracle服务器选件（例如，并行查询或分布式数据库）。
- 除非你的应用有特殊的需要，否则使用缺省的Oracle锁。
- 利用数据库记住应用模块，以便能以每个模块为基础来追踪性能。
- 选择你的数据块的最佳大小。 -- 原则上来说大一些的性能较好。
- 分布你的数据，使得一个节点使用的数据本地存贮在该节点中。
目录
优化基础知识
性能调整综述
有效的应用设计
SQL语句的处理过程
Oracle的优化器
Oracle的执行计划
注意事项
有效的应用设计
将最常用的应用分为2种类型：联机事务处理类型(OLTP)，决策支持系统(DSS)。
联机事务处理(OLTP)
该类型的应用是高吞吐量，插入、更新、删除操作比较多的系统，这些系统以不断增长的大容量数据为特征，它们提供给成百用户同时存取，典型的OLTP系统是订票系统，银行的业务系统，订单系统。OTLP的主要目标是可用性、速度、并发性和可恢复性。
当设计这类系统时，必须确保大量的并发用户不能干扰系统的性能。还需要避免使用过量的索引与cluster 表，因为这些结构会使插入和更新操作变慢。
有效的应用设计
将最常用的应用分为2种类型：联机事务处理类型(OLTP)，决策支持系统(DSS)。
决策支持(DSS)
该类型的应用将大量信息进行提取形成报告，协助决策者作出正确的判断。典型情况是：决策支持系统将OLTP应用收集的大量数据进行查询。典型的应用为客户行为分析系统(超市，保险等)。
决策支持的关键目标是速度、精确性和可用性。
该种类型的设计往往与OLTP设计的理念背道而驰，一般建议使用数据冗余、大量索引、cluster table、并行查询等。
近年来，该类型的应用逐渐与OLAP、数据仓库紧密的联系在一起，形成的一个新的应用方向。
目录
优化基础知识
性能调整综述
有效的应用设计
SQL语句的处理过程
Oracle的优化器
Oracle的执行计划
注意事项
SQL语句的处理过程
1 查询语句处理
2 DML语句处理(insert， update， delete)
3 DDL 语句处理(create .. ， drop .. ， alter .. ， )
4 事务控制(commit， rollback)
SQL语句执行过程
如图：
列出了处理和运行一个sql语句的需要各个重要阶段。在某些情况下，Oracle运行sql的过程可能与下面列出的各个阶段的顺序有所不同。如DEFINE阶段可能在FETCH阶段之前，这主要依赖你如何书写代码
DML 语句的处理
假设你使用Pro*C程序来为指定部门的所有职员增加工资。程序已经连到正确的用户，你可以在你的程序中嵌入如下的SQL语句：
EXEC SQL UPDATE employees
SET salary = 1.10 * salary
WHERE department_id = :var_department_id;
var_department_id是程序变量，里面包含部门号，我们要
修改该部门的职员的工资。当这个SQL语句执行时，使用该
变量的值。
DML 语句的处理
每种类型的语句都需要如下阶段：
• 第1步: Create a Cursor 创建游标
• 第2步: Parse the Statement 分析语句
• 第5步: Bind Any Variables 绑定变量
• 第7步: Run the Statement 运行语句
• 第9步: Close the Cursor 关闭游标
如果使用了并行功能，还会包含下面这个阶段：
• 第6步: Parallelize the Statement 并行执行语句
如果是查询语句，则需要几个额外的步骤，如图所示：
• 第3步: Describe Results of a Query 描述查询的结果集
• 第4步: Define Output of a Query 定义查询的输出数据
• 第8步: Fetch Rows of a Query 取查询出来的行
以上语句处理步骤解释
第1步: 创建游标(Create a Cursor)
由程序接口调用创建一个游标（cursor）。任何SQL语句都会创建它，特别在运行DML语句时，都是自动创建游标的，不需要开发人员干预。多数应用中，游标的创建是自动的。然而，在预编译程序(pro*c)中游标的创建，可能是隐含的，也可能显式的创建。在存储过程中也是这样的。
第2步:分析语句(Parse the Statement)
在语法分析期间，SQL语句从用户进程传送到Oracle，SQL语句经语法分析后，SQL语句本身与分析的信息都被装入到共享SQL区。在该阶段中，可以解决许多类型的错误。
以上语句处理步骤解释
语法分析分别执行下列操作：
翻译SQL语句，验证它是合法的语句，即书写正确
实现数据字典的查找，以验证是否符合表和列的定义
在所要求的对象上获取语法分析锁，使得在语句的语法分析过程中不改变这些对象的定义
验证为存取所涉及的模式对象所需的权限是否满足
决定此语句最佳的执行计划
将它装入共享SQL区
对分布的语句来说，把语句的全部或部分路由到包含所涉及数据的远程节点
* 以上任何一步出错误，都将导致语句报错，中止执行。
以上语句处理步骤解释
只有在共享池中不存在等价SQL语句的情况下，才对SQL语句作语法分析。在这种情况下，数据库内核重新为该语句分配新的共享SQL区，并对语句进行语法分析。进行语法分析需要耗费较多的资源，所以要尽量避免进行语法分析，这是优化的技巧之一。
语法分析阶段包含了不管此语句将执行多少次，而只需分析一次的处理要求。Oracle只对每个SQL语句翻译一次，在以后再次执行该语句时，只要该语句还在共享SQL区中，就可以避免对该语句重新进行语法分析，也就是此时可以直接使用其对应的执行计划对数据进行存取。这主要是通过绑定变量(bind variable)实现的，也就是我们常说的共享SQL，后面会给出共享SQL的概念。
虽然语法分析验证了SQL语句的正确性，但语法分析只能识别在SQL语句执行之前所能发现的错误(如书写错误、权限不足等)。因此，有些错误通过语法分析是抓不到的。例如，在数据转换中的错误或在数据中的错（如企图在主键中插入重复的值）以及死锁等均是只有在语句执行阶段期间才能遇到和报告的错误或情况。
查询 语句的处理
查询与其它类型的SQL语句不同，因为在成功执行后作为结
果将返回数据。
第3步: 描述查询结果(Describe Results of a Query)
描述阶段只有在查询结果的各个列是未知时才需要；例如，当查询由用户交互地输入需要输出的列名。在这种情况要用描述阶段来决定查询结果的特征（数据类型，长度和名字）。
第4步: 定义查询的输出数据(Define Output of a Query)
在查询的定义阶段，你指定与查询出的列值对应的接收变量的位置、大小和数据类型，这样我们通过接收变量就可以得到查询结果。如果必要的话，Oracle会自动实现数据类型的转换。这是将接收变量的类型与对应的列类型相比较决定的。
查询 语句的处理
第5步: 绑定变量(Bind Any Variables)
Oracle知道了SQL语句的意思，但仍没有足够的信息用于执行该语句。Oracle 需要得到在语句中列出的所有变量的值。在该例中，Oracle需要得到对department_id列进行限定的值。得到这个值的过程就叫绑定变量(binding variables)
此过程称之为将变量值捆绑进来。程序必须指出可以找到该数值的变量名（该变量被称为捆绑变量，变量名实质上是一个内存地址，相当于指针）。应用的最终用户可能并没有发觉他们正在指定捆绑变量，因为Oracle 的程序可能只是简单地指示他们输入新的值，其实这一切都在程序中自动做了。
因为你指定了变量名，在你再次执行之前无须重新捆绑变量。你可以改变绑定变量的值，而Oracle在每次执行时，仅仅使用内存地址来查找此值。
如果Oracle 需要实现自动数据类型转换的话（除非它们是隐含的或缺省的），你还必须对每个值指定数据类型和长度。关于这些信息可以参考oracle的相关文档，如Oracle Call Interface Programmer's Guide
查询 语句的处理
第6步: 并行执行语句(Parallelize the Statement )
ORACLE 可以在SELECTs， INSERTs， UPDATEs， MERGEs， DELETEs语句中执行相应并行查询操作，对某些DDL操作，如创建索引、用子查询创建表、在分区表上的操作，可以执行并行操作。并行化可导致多个服务器进程(oracle server processes)为同一个SQL语句工作，使该SQL语句可以快速完成，但是会耗费更多的资源，所以除非很有必要，否则不要使用并行查询。
第7步: 执行语句(Run the Statement)
此时，Oracle拥有所有需要的信息与资源，可以真正运行SQL语句了。如果该语句为SELECT查询或INSERT语句，则不需要锁定任何行，因没有数据需要被改变。如果语句为UPDATE或DELETE语句，则该语句影响的所有行都被锁定，防止该用户提交或回滚之前，别的用户对这些数据进行修改。这保证了数据的一致性。
对于某些语句，你可以指定执行的次数，这称为批处理(array processing)。指定执行N次，则绑定变量与定义变量被定义为大小为N的数组的开始位置，这种方法可以减少网络开销，也是优化的技巧之一。
查询 语句的处理
第8步: 取出查询的行(Fetch Rows of a Query)
在fetch阶段，行数据被取出来，每个后续的存取操作检索结果集中的下一行数据，直到最后一行被取出来。上面提到过，批量的fetch是优化的技巧之一。
第9步: 关闭游标(Close the Cursor)
SQL语句处理的最后一个阶段就是关闭游标。
DDL 语句的处理
DDL语句的执行不同与DML语句和查询语句的执行，这是因为DDL语句执行成功后需要对数据字典数据进行修改。对于DDL语句，语句的分析阶段包括：分析、查找数据字典信息和执行。
事务管理语句、会话管理语句、系统管理语句只有分析与执行阶段，为了重新执行该语句，会重新分析与执行该语句。
事务控制
必须定义事务，这样在一个逻辑单元中的所有工作可以同时被提交或回滚，保证了数据的一致性。一个事务应该由逻辑单元中的所有必须部分组成，不应该多一个，也不应该少一个。
在事务开始和结束的这段时间内，所有被引用表中的数据都应该在一致的状态(或可以被回溯到一致的状态)
事务应该只包含可以对数据进行一致更改(one consistent change to the data)的SQL语句
Eg:在两个帐号之间的转帐(这是一个事务或逻辑工作单元)，应该包含从一个帐号中借钱(由一个SQL完成)，然后将借的钱存入另一个帐号(由另一个SQL完成)。这2个操作作为一个逻辑单元，应该同时成功或同时失败。其它不相关的操作，如向一个帐户中存钱，不应该包含在这个转帐事务中。
在设计应用时，除需要决定哪种类型的操作组成一个事务外，还需要决定使用BEGIN_DISCRETE_TRANSACTIO存储过程是否对提高小的、非分布式的事务的性能有作用。
目录
优化基础知识
性能调整综述
有效的应用设计
SQL语句的处理过程
Oracle的优化器
Oracle的执行计划
注意事项
Oracle 优化器
2 种类型的优化器：
基于规则的优化器
基于代价的优化器。
不同之处：取得代价的方法与衡量代价的大小不同。
基于规则的优化器 -- Rule Based (Heuristic) Optimization(简称RBO)
基于规则的优化器中采用启发式的方法(Heuristic Approach)或规则(Rules)来生成执行计划。例如，如果一个查询的where条件(where clause)包含一个谓词(predicate，其实就是一个判断条件，如”=”， “>”， ”<”等)，而且该谓词上引用的列上有有效索引，那么优化器将使用索引访问这个表，而不考虑其它因素，如表中数据的多少、表中数据的易变性、索引的可选择性等。此时数据库中没有关于表与索引数据的统计性描述，如表中有多上行，每行的可选择性等。优化器也不考虑实例参数，如multi block i/o、可用排序内存的大小等，所以优化器有时就选择了次优化的计划作为真正的执行计划，导致系统性能不高。
如，对于 select * from emp where deptno = 10;
如果是使用基于规则的优化器，而且deptno列上有有效的索引，则会通过deptno列上的索引来访问emp表。在绝大多数情况下，这是比较高效的，但是在一些特殊情况下，使用索引访问也有比较低效的时候，现举例说明：
基于规则的优化器 -- Rule Based (Heuristic) Optimization(简称RBO)
1) emp表比较小，该表的数据只存放在几个数据块中。此时使用全表扫描比使用索引访问emp表反而要好。因为表比较小，极有可能数据全在内存中，所以此时做全表扫描是最快的。而如果使用索引扫描，需要先从索引中找到符合条件记录的rowid，然后再一一根据这些rowid从emp中将数据取出来，在这种条件下，效率就会比全表扫描的效率要差一些。
2) emp表比较大时，而且deptno = 10条件能查询出表中大部分的数据如(50%)。如该表共有4000万行数据，共放在有500000个数据块中，每个数据块为8k，则该表共有约4G，则这么多的数据不可能全放在内存中，绝大多数需要放在硬盘上。此时如果该查询通过索引查询，则是你梦魇的开始。db_file_multiblock_read_count参数的值200。如果采用全表扫描，则需要500000/db_file_multiblock_read_count=500000/200=2500次I/O。但是如果采用索引扫描，假设deptno列上的索引都已经cache到内存中，所以可以将访问索引的开销忽略不计。因为要读出4000万x 50% = 2000万数据，假设在读这2000万数据时，有99.9%的命中率，则还是需要20000次I/O，比上面的全表扫描需要的2500次多多了，所以在这种情况下，用索引扫描反而性能会差很多。在这样的情况下，用全表扫描的时间是固定的，但是用索引扫描的时间会随着选出数据的增多使查询时间相应的延长。
基于代价的优化器 -- Cost Based Optimization(简称CBO)
Oracle把一个代价引擎(Cost Engine)集成到数据库内核中，用来估计每个执行计划需要的代价，该代价将每个执行计划所耗费的资源进行量化，从而CBO可以根据这个代价选择出最优的执行计划。
查询耗费的资源可以被分成3个基本组成部分：I/O代价、CPU代价、network代价。
I/O代价是将数据从磁盘读入内存所需的代价。访问数据包括将数据文件中数据块的内容读入到SGA的数据高速缓存中，在一般情况下，该代价是处理一个查询所需要的最主要代价，所以我们在优化时，一个基本原则就是降低查询所产生的I/O总次数。
CPU代价是处理在内存中数据所需要的代价，如一旦数据被读入内存，则我们在识别出我们需要的数据后，在这些数据上执行排序(sort)或连接(join)操作，这需要耗费CPU资源。
对于需要访问跨节点(即通常说的服务器)数据库上数据的查询来说，存在network代价，用来量化传输操作耗费的资源。查询远程表的查询或执行分布式连接的查询会在network代价方面花费比较大。
判断当前数据库使用何种优化器
由optimizer_mode初始化参数决定的。该参数可能的取值为：first_rows_[1 | 10 | 100 | 1000] | first_rows | all_rows | choose | rule。
具体解释如下：
RULE为使用RBO优化器。
CHOOSE则是根据实际情况，如果数据字典中包含被引用的表的统计数据，即引用的对象已经被分析，则就使用CBO优化器，否则为RBO优化器。
ALL_ROWS为CBO优化器使用的第一种具体的优化方法，是以数据的吞吐量为主要目标，以便可以使用最少的资源完成语句。
FIRST_ROWS为优化器使用的第二种具体的优化方法，是以数据的响应时间为主要目标，以便快速查询出开始的几行数据。
FIRST_ROWS_[1 | 10 | 100 | 1000] 为优化器使用的第三种具体的优化方法，让优化器选择一个能够把响应时间减到最小的查询执行计划，以迅速产生查询结果的前 n 行。该参数为ORACLE 9I新引入的。
查看命令：show parameter OPTIMIZER_MODE
目录
优化基础知识
性能调整综述
有效的应用设计
SQL语句的处理过程
Oracle的优化器
Oracle的执行计划
注意事项
内容提纲
Oracle的执行计划
优化定义
概念分析
执行计划分析
优化器选择
访问数据存取方法
表间连接
如何产生执行计划
如何分析执行计划
如何干预执行计划
综合案例分析
优化定义
什么是优化：
优化是选择最有效的执行计划来执行SQL语句的过程，这是在处理任何数据的语句（SELECT，INSERT，UPDATE或DELETE）中的一个重要步骤。对Oracle来说，执行这样的语句有许多不同的方法，譬如说，将随着以什么顺序访问哪些表或索引的不同而不同。所使用的执行计划可以决定语句能执行得有多快。Oracle中称之为优化器（Optimizer）的组件用来选择这种它认为最有效的执行计划。
内容提纲
Oracle的执行计划
优化定义
概念分析
执行计划分析
优化器选择
访问数据存取方法
表间连接
如何产生执行计划
如何分析执行计划
如何干预执行计划
综合案例分析
概念分析
共享SQL语句：
为了不重复解析相同的SQL语句(因为解析操作比较费资源，会导致性能下降)，在第一次解析之后，ORACLE将SQL语句及解析后得到的执行计划存放在内存中。这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享。因此，当你执行一个SQL语句(有时被称为一个游标)时，如果该语句和之前的执行过的某一语句完全相同，并且之前执行的该语句与其执行计划仍然在内存中存在，则ORACLE就不需要再进行分析，直接得到该语句的执行路径。ORACLE的这个功能大大地提高了SQL的执行性能并大大节省了内存的使用。使用这个功能的关键是将执行过的语句尽可能放到内存中，所以这要求有大的共享池(通过设置shared buffer pool参数值)和尽可能的使用绑定变量的方法执行SQL语句。
概念分析
Rowid的概念：
rowid是一个伪列，既然是伪列，那么这个列就不是用户定义，而是系统自己给加上的。对每个表都有一个rowid的伪列，但是表中并不物理存储ROWID列的值。不过你可以像使用其它列那样使用它，但是不能删除该列，也不能对该列的值进行修改、插入。一旦一行数据插入数据库，则rowid在该行的生命周期内是唯一的，即即使该行产生行迁移，行的rowid也不会改变。
概念分析
为什么使用Rowid：
rowid对访问一个表中的给定的行提供了最快的访问方法，通过ROWID可以直接定位到相应的数据块上，然后将其读到内存。我们创建一个索引时，该索引不但存储索引列的值，而且也存储索引值所对应的行的ROWID，这样我们通过索引快速找到相应行的ROWID后，通过该ROWID，就可以迅速将数据查询出来。这也就是我们使用索引查询时，速度比较快的原因。
在ORACLE8以前的版本中，ROWID由FILE 、BLOCK、ROW NUMBER构成。随着oracle8中对象概念的扩展，ROWID发生了变化，ROWID由OBJECT、FILE、BLOCK、ROW NUMBER构成。利用DBMS_ROWID可以将rowid分解成上述的各部分，也可以将上述的各部分组成一个有效的rowid。
概念分析
Recursive SQL概念 ：
为了执行用户发出的一个sql语句，Oracle必须执行一些额外的语句，我们将这些额外的语句称之为‘recursive calls’或‘recursive SQL statements’。如当一个DDL语句发出后，ORACLE总是隐含的发出一些recursive SQL语句，来修改数据字典信息，以便用户可以成功的执行该DDL语句。当需要的数据字典信息没有在共享内存中时，经常会发生Recursive calls，这些Recursive calls会将数据字典信息从硬盘读入内存中。用户不必关心这些recursive SQL语句的执行情况，在需要的时候，ORACLE会自动的在内部执行这些语句。当然DML语句与SELECT都可能引起recursive SQL。简单的说，我们可以将触发器视为recursive SQL。
概念分析
Row Source(行源) ：
用在查询中，由上一操作返回的符合条件的行的集合，即可以是表的全部行数据的集合；也可以是表的部分行数据的集合；也可以为对上2个row source进行连接操作(如join连接)后得到的行数据集合。
Predicate(谓词) ：
一个查询中的WHERE限制条件。
概念分析
Driving Table(驱动表)：
该表又称为外层表(OUTER TABLE)。这个概念用于嵌套与HASH连接中。如果该row source返回较多的行数据，则对所有的后续操作有负面影响。注意此处虽然翻译为驱动表，但实际上翻译为驱动行源(driving row source)更为确切。一般说来，是应用查询的限制条件后，返回较少行源的表作为驱动表，所以如果一个大表在WHERE条件有限制条件(如等值限制)，则该大表作为驱动表也是合适的，所以并不是只有较小的表可以作为驱动表，正确说法应该为应用查询的限制条件后，返回较少行源的表作为驱动表。在执行计划中，应该为靠上的那个row source，后面会给出具体说明。在我们后面的描述中，一般将该表称为连接操作的row source 1。
概念分析
Probed Table(被探查表)：
该表又称为内层表(INNER TABLE)。在我们从驱动表中得到具体一行的数据后，在该表中寻找符合连接条件的行。所以该表应当为大表(实际上应该为返回较大row source的表)且相应的列上应该有索引。在我们后面的描述中，一般将该表称为连接操作的row source 2。
概念分析
组合索引(concatenated index)：
由多个列构成的索引，如create index idx_emp on emp(col1， col2， col3， ……)，则我们称idx_emp索引为组合索引。在组合索引中有一个重要的概念：引导列(leading column)，在上面的例子中，col1列为引导列。当我们进行查询时可以使用”where col1 = ? ”，也可以使用”where col1 = ? and col2 = ?”，这样的限制条件都会使用索引，但是”where col2 = ? ”查询就不会使用该索引。所以限制条件中包含先导列时，该限制条件才会使用该组合索引。
概念分析
可选择性(selectivity)：
比较一下列中唯一键的数量和表中的行数，就可以判断该列的可选择性。如果该列的”唯一键的数量/表中的行数”的比值越接近1，则该列的可选择性越高，该列就越适合创建索引，同样索引的可选择性也越高。在可选择性高的列上进行查询时，返回的数据就较少，比较适合使用索引查询。
内容提纲
Oracle的执行计划
优化定义
概念分析
执行计划分析
优化器选择
访问数据存取方法
表间连接
如何产生执行计划
如何分析执行计划
如何干预执行计划
综合案例分析
执行计划分析
为了执行语句，Oracle可能必须实现许多步骤。这些步骤中的每一步可能是从数据库中物理检索数据行，或者用某种方法准备数据行，供发出语句的用户使用。Oracle用来执行语句的这些步骤的组合被称之为执行计划。
执行计划是SQL优化中最为复杂也是最为关键的部分，只有知道了ORACLE在内部到底是如何执行该SQL语句后，才能知道优化器选择的执行计划是否为最优的。执行计划对于DBA来说，就象财务报表对于财务人员一样重要。所以我们面临的问题主要是：
1. 如何得到执行计划；
2. 如何分析执行计划;
从而找出影响性能的主要问题。
执行计划分析
举例，如何得到执行计划：
显示下面SQL语句的执行计划。
SELECT ename， job， sal， dname
FROM emp， dept
WHERE emp.deptno = derpt.deptno
AND NOT EXISTS
( SELECT *
FROM salgrade
WHERE emp.sal BETWEEN losal AND hisal );
此语句查询薪水不在任何建议薪水范围内的所有雇员的
名字，工作，薪水和部门名。
执行计划分析
如图显示了上例执行计划的图形表示：
执行计划的步骤
第３步和第６步分别的从EMP表和SALGRADE表读所有行。
第５步在PK_DEPTNO索引中查找由步骤３返回的每个DEPTNO值。它找出与DEPT表中相关联的那些行的ROWID。
第４步从DEPT表中检索出ROWID为第５步返回的那些行。
由黑色字框指出的步骤在行源上操作，如做2表之间的关联，排序，或过滤等操作。
第２步实现嵌套的循环操作(相当于C语句中的嵌套循环)，接收从第３步和第４步来的行源，把来自第３步源的每一行与它第４步中相应的行连接在一起，返回结果行到第１步。
第１步完成一个过滤器操作。它接收来自第２步和第６步的行源，消除掉第２步中来的，在第６步有相应行的那些行，并将来自第２步的剩下的行返回给发出语句的用户或应用。
实现执行计划步骤的顺序
执行计划中的步骤不是按照它们编号的顺序来实现的：Oracle首先实现上图树结构图形里作为叶子出现的那些步骤(例如步骤3、5、6)。由每一步返回的行称为它下一步骤的行源。然后Oracle实现父步骤。
Oracle以下列顺序实现这些步骤：
首先，Oracle实现步骤３，并一行一行地将结果行返回给第２步。
对第３步返回的每一行，Oracle实现这些步骤：
Oracle实现步骤５，并将结果ROWID返回给第４步。
Oracle实现步骤４，并将结果行返回给第２步。
Oracle实现步骤２，将接受来自第３步的一行和来自第４步的一行，并返回给第１步一行。
Oracle实现步骤６，如果有结果行的话，将它返回给第１步。
Oracle实现步骤１，如果从步骤６返回行，Oracle将来自第２步的行返回给发出SQL语句的用户。
内容提纲
Oracle的执行计划
优化定义
概念分析
执行计划分析
优化器选择
访问数据存取方法
表间连接
如何产生执行计划
如何分析执行计划
如何干预执行计划
综合案例分析
优化器选择
对于以上的操作过程，可以使用first_rows作为优化目
标以便于实现快速响应用户的请求。
有些父步骤在它们被实现之前需要来自子步骤的所有行
。对这样的父步骤，直到所有行从子步骤返回之前
Oracle不能实现该父步骤。这样的父步骤包括排序，排
序一合并的连接，组功能和总计。对于这样的操作，可
以用all_rows作为优化目标，使该中类型的操作耗费的
资源最少。
有时语句执行时，并不是象上面说的那样一步一步有先有后的进行，而是可能并行运行，如在实际环境中，3、5、4步可能并行运行，以便取得更好的效率。从上面的树型图上，是很难看出各个操作执行的先后顺序，而通过ORACLE生成的另一种形式的执行计划，则可以很容易的看出哪个操作先执行，哪个后执行，这样的执行计划是我们真正需要的，后面会给出详细说明。现在先来看一些预备知识。
内容提纲
Oracle的执行计划
优化定义
概念分析
执行计划分析
优化器选择
访问数据存取方法
表间连接
如何产生执行计划
如何分析执行计划
如何干预执行计划
综合案例分析
访问路径(方法) -- access path
全表扫描
通过ROWID的表存取 （Table Access by ROWID或rowid lookup）
索引扫描 （Index Scan或index lookup）
索引扫描 （Index Scan或index lookup）
索引扫描 （Index Scan或index lookup）
索引扫描 （Index Scan或index lookup）
索引扫描 （Index Scan或index lookup）
索引扫描 （Index Scan或index lookup）
内容提纲
Oracle的执行计划
优化定义
概念分析
执行计划分析
优化器选择
访问数据存取方法
表间连接
如何产生执行计划
如何分析执行计划
如何干预执行计划
综合案例分析
表间连接
表间连接
表间连接
排序 - - 合并连接 (Sort Merge Join (SMJ) )
排序 - - 合并连接 (Sort Merge Join (SMJ) )
嵌套循环 (Nested Loops (NL) )
嵌套循环 (Nested Loops (NL) )
嵌套循环 (Nested Loops (NL) )
嵌套循环 (Nested Loops (NL) )
哈希连接 (Hash Join， HJ)
笛卡儿乘积(Cartesian Product)
总 结
总 结
内容提纲
Oracle的执行计划
优化定义
概念分析
执行计划分析
优化器选择
访问数据存取方法
表间连接
如何产生执行计划
如何分析执行计划
如何干预执行计划
综合案例分析
如何产生执行计划
如何产生执行计划
如何产生执行计划
如何产生执行计划
内容提纲
Oracle的执行计划
优化定义
概念分析
执行计划分析
优化器选择
访问数据存取方法
表间连接
如何产生执行计划
如何分析执行计划
如何干预执行计划
综合案例分析
如何分析执行计划
如何分析执行计划
如何分析执行计划
如何分析执行计划
如何分析执行计划
如何分析执行计划
如何分析执行计划
如何分析执行计划
如何分析执行计划
内容提纲
Oracle的执行计划
优化定义
概念分析
执行计划分析
优化器选择
访问数据存取方法
表间连接
如何产生执行计划
如何分析执行计划
如何干预执行计划
综合案例分析
如何干预执行计划
如何干预执行计划
如何干预执行计划
如何干预执行计划
如何干预执行计划
如何干预执行计划
如何干预执行计划
如何干预执行计划
内容提纲
Oracle的执行计划
优化定义
概念分析
执行计划分析
优化器选择
访问数据存取方法
表间连接
如何产生执行计划
如何分析执行计划
如何干预执行计划
综合案例分析
综合案例分析
总 结
总 结
目录
优化基础知识
性能调整综述
有效的应用设计
SQL语句的处理过程
Oracle的优化器
Oracle的执行计划
注意事项
注意事项
注意事项
注意事项
注意事项
注意事项
展开
