做棋盘游戏辅助的网站,昆明软件定制,网站开发过程的基本环节,天眼查公司注册信息在CBO的优化模式下#xff0c;我们可以使用optimizer_mode参数控制优化模式。主要有两种模式#xff0c;一种是ALL_ROWS模式#xff0c;另外一种是FIRST_ROWS模式。 ALL_ROWS模式适用场景#xff1a;希望优化程序给出一种尽快得到全部记录的执行计划#xff0c;目标是增加… 在CBO的优化模式下我们可以使用optimizer_mode参数控制优化模式。主要有两种模式一种是ALL_ROWS模式另外一种是FIRST_ROWS模式。 ALL_ROWS模式适用场景希望优化程序给出一种尽快得到全部记录的执行计划目标是增加系统的吞吐量。 FIRST_ROWS模式适用场景希望优化程序给出一种可以迅速的得到第一行的执行计划目标是减少系统的响应时间。 两种模式需要具体场景具体分析比如常见的Web应用很少有一次性得到全部记录的情况都是分多页交互的响应操作者因此默认的ALL_ROWS模式就不太合适了应该考虑使用FIRST_ROWS模式进行优化。又如我们想要生成全部数据的报表那么默认的ALL_ROWS模式就比较的合适。 下面通过实验来比较all_rows和first_rows对执行计划的影响 1.实验环境 操作系统rhel 5.4 x32 数据库oracle 11.2.0.1.0 2.首先我们创建一个具有dba权限的用户jack_lindefault_tablespace使用默认的users。 1 SQL conn /as sysdba
2 Connected.
3 SQL create user jack_lin identified by jack;
4 User created.
5 SQL grant dba to jack_lin;
6 Grant succeeded. 3.创建该实验需要用到的一张表。 1 SQL conn jack_lin/jack;2 Connected.3 SQL create table test(id number,name varchar2(10));4 Table created.5 SQL insert into test values(100,aaaa);6 1 row created.7 SQL insert into test values(200,bbbb);8 1 row created.9 SQL insert into test values(300,cccc);
10 1 row created.
11 SQL insert into test values(400,dddd);
12 1 row created.
13 SQL commit;
14 Commit complete. 4.在没有索引的情况比较 首先来看FIRST_ROWS的效果为了保证CBO执行计划的准确我们需要analyze一下表。 1 SQL alter session set optimizer_modefirst_rows;2 3 Session altered.4 5 SQL analyze table test compute statistics;6 7 Table analyzed.8 9 SQL set autotrace trace exp;
10 SQL select * from test where nameaaaa;
11
12 Execution Plan
13 ----------------------------------------------------------
14 Plan hash value: 1357081020
15
16 --------------------------------------------------------------------------
17 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
18 --------------------------------------------------------------------------
19 | 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)| 00:00:01 |
20 |* 1 | TABLE ACCESS FULL| TEST | 1 | 6 | 3 (0)| 00:00:01 |
21 --------------------------------------------------------------------------
22
23 Predicate Information (identified by operation id):
24 ---------------------------------------------------
25
26 1 - filter(NAMEaaaa) 由于表上没有索引所以只有一种选择全表扫描。 现在再看一下ALL_ROWS的情况 1 SQL alter session set optimizer_modeall_rows;2 3 Session altered.4 5 SQL select * from test where nameaaaa;6 7 Execution Plan8 ----------------------------------------------------------9 Plan hash value: 1357081020
10
11 --------------------------------------------------------------------------
12 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
13 --------------------------------------------------------------------------
14 | 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)| 00:00:01 |
15 |* 1 | TABLE ACCESS FULL| TEST | 1 | 6 | 3 (0)| 00:00:01 |
16 --------------------------------------------------------------------------
17
18 Predicate Information (identified by operation id):
19 ---------------------------------------------------
20
21 1 - filter(NAMEaaaa) 通过上面的简单举例比较可以看到在表上没有索引当数据量很少并且值唯一的情况下两种模式的效果是一样的。 5.在有索引的情况下比较 创建索引并执行在FIRST_ROWS的操作 1 SQL create index ind_test on test(name);2 3 Index created.4 5 SQL analyze index ind_test compute statistics;6 7 Index analyzed.8 9 SQL analyze table test compute statistics;
10
11 Table analyzed.
12
13 SQL select /* first_rows */* from test where nameaaaa;
14
15 Execution Plan
16 ----------------------------------------------------------
17 Plan hash value: 3856466897
18
19 ----------------------------------------------------------------------------------------
20 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
21 ----------------------------------------------------------------------------------------
22 | 0 | SELECT STATEMENT | | 1 | 6 | 2 (0)| 00:00:01 |
23 | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 6 | 2 (0)| 00:00:01 |
24 |* 2 | INDEX RANGE SCAN | IND_TEST | 1 | | 1 (0)| 00:00:01 |
25 ----------------------------------------------------------------------------------------
26
27 Predicate Information (identified by operation id):
28 ---------------------------------------------------
29
30 2 - access(NAMEaaaa) 设置成ALL_ROWS的情况 1 SQL select /* all_rows */ * from test where nameaaaa;2 3 Execution Plan4 ----------------------------------------------------------5 Plan hash value: 38564668976 7 ----------------------------------------------------------------------------------------8 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |9 ----------------------------------------------------------------------------------------
10 | 0 | SELECT STATEMENT | | 1 | 6 | 2 (0)| 00:00:01 |
11 | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 6 | 2 (0)| 00:00:01 |
12 |* 2 | INDEX RANGE SCAN | IND_TEST | 1 | | 1 (0)| 00:00:01 |
13 ----------------------------------------------------------------------------------------
14
15 Predicate Information (identified by operation id):
16 ---------------------------------------------------
17
18 2 - access(NAMEaaaa) 通过上面的演示可以看到两种模式都走了索引目前来看一切正常。 6.现在通过insert into test select * from test;往test表中反复插入记录注意记录大部分是重复的其实只有四条各占1/4。 1 set autotrace off;2 SQL insert into test select * from test;3 4 16384 rows created.5 SQL analyze table test compute statistics;6 7 Table analyzed.8 SQL analyze index ind_test compute statistics;9
10 Index analyzed.
11
12 SQL alter session set optimizer_modefirst_rows;
13
14 Session altered.
15
16 SQL set autotrace trace exp;
17 SQL select * from test where nameaaaa;
18
19 Execution Plan
20 ----------------------------------------------------------
21 Plan hash value: 3856466897
22
23 ----------------------------------------------------------------------------------------
24 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
25 ----------------------------------------------------------------------------------------
26 | 0 | SELECT STATEMENT | | 8192 | 49152 | 87 (0)| 00:00:02 |
27 | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 8192 | 49152 | 87 (0)| 00:00:02 |
28 |* 2 | INDEX RANGE SCAN | IND_TEST | 8192 | | 28 (0)| 00:00:01 |
29 ----------------------------------------------------------------------------------------
30
31 Predicate Information (identified by operation id):
32 ---------------------------------------------------
33
34 2 - access(NAMEaaaa)
35
36 SQL alter session set optimizer_modeall_rows;
37
38 Session altered.
39
40 SQL select * from test where nameaaaa;
41
42 Execution Plan
43 ----------------------------------------------------------
44 Plan hash value: 1357081020
45
46 --------------------------------------------------------------------------
47 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
48 --------------------------------------------------------------------------
49 | 0 | SELECT STATEMENT | | 8192 | 49152 | 19 (0)| 00:00:01 |
50 |* 1 | TABLE ACCESS FULL| TEST | 8192 | 49152 | 19 (0)| 00:00:01 |
51 --------------------------------------------------------------------------
52
53 Predicate Information (identified by operation id):
54 ---------------------------------------------------
55
56 1 - filter(NAMEaaaa) 这时我们看到FIRST_ROWS走了索引就本例而言这显然不是一种理想的结果而ALL_ROWS走了全表扫描我们可以看到成本明显更低。 参考一下Oracle 10g官方文档关于optimizer_mode参数的描述 OPTIMIZER_MODE PropertyDescriptionParameter typeStringSyntaxOPTIMIZER_MODE { first_rows_[1 | 10 | 100 | 1000] | first_rows | all_rows } Default valueall_rowsModifiableALTER SESSION, ALTER SYSTEM 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 first n rows (where n 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). 总结 Oracle默认的优化模式并不一定是我们想要的必须根据自己的系统特定细心的定制。 转载于:https://www.cnblogs.com/Richardzhu/articles/2814599.html
