[b]1. CBO & RBO[/b]
[b]Rule Based Optimizer(RBO)基于规则
Cost Based Optimizer(CBO)基于成本,或者讲统计信息[/b]
ORACLE 提供了CBO、RBO两种SQL优化器。CBO在ORACLE7 引入,但在ORACLE8i 中才成熟。ORACLE 已经明确声明在ORACLE9i之后的版本中(ORACLE 10G ),RBO将不再支持。[color=red]因此选择CBO 是必然的趋势。[/color]
CBO和 RBO作为不同的SQL优化器,对SQL语句的执行计划产生重大影响,如果要对现有的应用程序从RBO向CBO移植,则必须充分考虑这些影响,避免SQL语句性能急剧下降;但是,对新的应用系统,则可以考虑直接使用CBO,在CBO模式下进行SQL语句编写、分析执行计划、性能测试等工作,这需要开发者对CBO的特性比较熟悉。以下小结几点在CBO下写SQL语句的注意事项:
[url]http://blog.csdn.net/suncrafted/article/details/4239237[/url]
[b]2.看懂执行计划[/b]
(1)什么是执行计划
An explain plan is a representation of the [color=red]access path[/color] that is taken when a [color=red]query [/color]is executed within Oracle.
(2)如何访问数据
At the physical level Oracle [color=red]reads blocks[/color] of data. The smallest amount of data read is a single Oracle block, the largest is constrained by operating system limits (and multiblock i/o). Logically Oracle finds the data to read by using the following methods:
[color=red]Full Table Scan (FTS) --全表扫描
Index Lookup (unique & non-unique) --索引扫描(唯一和非唯一)
Rowid --物理[/color]行id
[url]http://blog.csdn.net/java3344520/article/details/5515497[/url]
[url]http://www.cnblogs.com/jianggc/articles/2029854.html[/url]
[b]Rule Based Optimizer(RBO)基于规则
Cost Based Optimizer(CBO)基于成本,或者讲统计信息[/b]
ORACLE 提供了CBO、RBO两种SQL优化器。CBO在ORACLE7 引入,但在ORACLE8i 中才成熟。ORACLE 已经明确声明在ORACLE9i之后的版本中(ORACLE 10G ),RBO将不再支持。[color=red]因此选择CBO 是必然的趋势。[/color]
CBO和 RBO作为不同的SQL优化器,对SQL语句的执行计划产生重大影响,如果要对现有的应用程序从RBO向CBO移植,则必须充分考虑这些影响,避免SQL语句性能急剧下降;但是,对新的应用系统,则可以考虑直接使用CBO,在CBO模式下进行SQL语句编写、分析执行计划、性能测试等工作,这需要开发者对CBO的特性比较熟悉。以下小结几点在CBO下写SQL语句的注意事项:
[url]http://blog.csdn.net/suncrafted/article/details/4239237[/url]
[b]2.看懂执行计划[/b]
(1)什么是执行计划
An explain plan is a representation of the [color=red]access path[/color] that is taken when a [color=red]query [/color]is executed within Oracle.
(2)如何访问数据
At the physical level Oracle [color=red]reads blocks[/color] of data. The smallest amount of data read is a single Oracle block, the largest is constrained by operating system limits (and multiblock i/o). Logically Oracle finds the data to read by using the following methods:
[color=red]Full Table Scan (FTS) --全表扫描
Index Lookup (unique & non-unique) --索引扫描(唯一和非唯一)
Rowid --物理[/color]行id
[url]http://blog.csdn.net/java3344520/article/details/5515497[/url]
[url]http://www.cnblogs.com/jianggc/articles/2029854.html[/url]