在CBO的优化模式下,我们可以使用optimizer_mode参数控制优化模式。主要有两种模式,一种是ALL_ROWS模式,另外一种是FIRST_ROWS模式。
ALL_ROWS模式适用场景:希望优化程序给出一种尽快得到全部记录的执行计划,目标是增加系统的吞吐量。
FIRST_ROWS模式使用场景:希望优化程序给出一种可以迅速的得到第一行的执行计划,目标是减少系统的响应时间。
两种模式需要具体场景具体分析,比如常见的Web应用,很少有一次性得到全部记录的情况,都是分多页交互的响应操作者,因此默认的ALL_ROWS模式就不太适合了,应该考虑使用FIRST_ROWS模式进行优化。
又如,我们想要生成全部数据的报表,那么默认的ALL_ROWS模式就比较的适合。
通过一个实验看一下两种优化模式下的执行计划的不同之处。
1.默认情况下,数据库采用ALL_ROWS模式。
sec@ora10g> show parameter optimizer_mode
NAME TYPE VALUE
------------------- -------------------- -----------------
optimizer_mode string ALL_ROWS
2.创建千万级别的测试表t,开启autotrace,查看一下默认ALL_ROWS模式下的执行计划。
sec@ora10g> set autot trace explain
sec@ora10g> select t1.x, t2.x from t t1, t t2 where t1.x = t2.x and t1.owner='SEC';
Execution Plan
----------------------------------------------------------
Plan hash value: 2371815244
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99695 | 2823K| | 43627 (1)| 00:08:44 |
|* 1 | HASH JOIN | | 99695 | 2823K| 3408K| 43627 (1)| 00:08:44 |
|* 2 | TABLE ACCESS FULL | T | 99695 | 2239K| | 29985 (1)| 00:06:00 |
| 3 | INDEX FAST FULL SCAN| PK_T | 9969K| 57M| | 4871 (2)| 00:00:59 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."X"="T2"."X")
2 - filter("T1"."OWNER"='SEC')
优化程序给出了一个快速获得t表全部记录的执行计划,使用到了索引快速全扫描的方式执行,总的执行时间较快。
3.修改优化模式为FIRST_ROWS模式后,再次查询其执行计划。
sec@ora10g> alter session set optimizer_mode =first_rows;
Session altered.
sec@ora10g> select t1.x, t2.x from t t1, t t2 where t1.x = t2.x and t1.owner='SEC';
Execution Plan
----------------------------------------------------------
Plan hash value: 217223811
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 99695 | 2823K| 129K (1)| 00:25:57 |
| 1 | NESTED LOOPS | | 99695 | 2823K| 129K (1)| 00:25:57 |
|* 2 | TABLE ACCESS FULL| T | 99695 | 2239K| 29985 (1)| 00:06:00 |
|* 3 | INDEX UNIQUE SCAN| PK_T | 1 | 6 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T1"."OWNER"='SEC')
3 - access("T1"."X"="T2"."X")
优化程序给出了一种快速获得t表第一条记录的执行计划,使用到了索引唯一性扫描的方式执行,总的执行时间相对ALL_ROWS模式就长了许多。
4.参考一下Oracle 10g官方文档关于optimizer_mode参数的描述
OPTIMIZER_MODE
<!-- class="inftblinformal -->
OPTIMIZER_MODE
establishes the default behavior. for choosing an optimization approach for the instance.
Values:
-
first_rows_
n
The optimizer uses a cost-based approach and optimizes with a goal of best response time to return the firstn
rows (wheren
= 1, 10, 100, 1000).
-
first_rows
The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows.
-
all_rows
The optimizer uses a cost-based approach for all SQL statements in the session and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).
5.小结
这种优化手段给我们的启示是什么?Oracle默认的优化模式并不一定是我们想要的,必须根据自己的系统特点细心的定制。
Oracle的自动化进程越来越快,这就给一些DBA一种普遍的误解,认为在数据库层面上基本上不用做过多的优化调整,只要按照Oracle的自动化策略走就可以了。这种想法是不正确的。越是自动化,其优化细节就隐藏的越深,越是要静下心来深入的探索和调整。
Good luck.
-- The End --
本文为转载至secooler 快乐的DBA 空间,谢谢分享。
分享到:
相关推荐
【Maclean Liu技术分享】拨开Oracle CBO优化器迷雾,探究Histogram直方图之秘_0321.pdf
ORACLE CBO RBO 优化
其中主要包括收集数据库统计系统、收集列直方图、分析SQL执行计划、如何让CBO优化器选择最优的执行计划,以及如何使用Hint提示认为改变CBO优化器的执行计划等,希望此文档能够帮助大家更深入地理解Oracle优化!
Oracle的优化器共有3种:RULE(基于规则)、COST(基于成本)、CHOOSE(选择性)。设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS 。你当然也在SQL句...
cbooracle cbo
平安Oracle CBO 资料.doc
ORACLE的优化器共有3种 A、RULE (基于规则) b、COST (基于成本) c、CHOOSE (选择性) 设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS 。 ...
Oracle CBO 学习笔记之(1) : 深入理解Oracle Hash Join的代价模型及其执行流程:word,pdf,图例
ORACLE的优化器共有3种: a. RULE (基于规则) b. COST (基于成本) c. CHOOSE (选择性) 设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . 你...
选用适合的ORACLE优化器 ORACLE的优化器共有3种: a.RULE(基于规则) b.COST(基于成本) c.CHOOSE(选择性) 设置缺省的优化器,可以通过对init.ora 文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST, CHOOSE,...
优化器产生的执行计划会因“优化器目标”的不同而不同。如果以最佳吞吐量为目标, 结果更倾向于使用全表扫描而不是索引扫描,或者使用排序合并连接而不是嵌套循环连接; 如果以最快的响应速度为目标,其结果则通常...
【Maclean Liu技术分享】拨开Oracle CBO优化器迷雾,探究Histogram直方图之秘 20130429
ORACLE的优化器共有3种:A、RULE (基于规则) b、COST (基于成本) c、CHOOSE (选择性) 设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS 。 ...
主要介绍了Oracle CBO优化模式中的5种索引访问方法浅析,包含索引唯一扫描、索引范围扫描、索引全扫描、索引跳跃扫描、索引快速全扫描等内容,需要的朋友可以参考下
关于oracle的CBO下的HINT总结!
cbo数据库优化,数据性能,oracle性能分析优化
Oracle语句优化53个规则详解,我从网上看到整理出来的,希望大家养成良好的SQL书写习惯,会让我们受益终身,写出高效率SQL语句
oracle性能优化 ORACLE SQL性能优化系列 (一) 如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和... 如果table已经被analyze过, 优化器模式将自动成为CBO , 反之,数据库将采用RULE形式的优化器.
Oracle 直方图计算公式,对于Oracle cbo优化器的探究又进入一步