Oracle10g学习笔记之二-优化器

显示全部楼层 · 2005-2-28 12:57:00

在Oracle Database 10g中,Oracle优化器的缺省模式由以前的CHOOSE，更改为现在的ALL_ROWS.
[PHP]
$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.1.0.2.0 - Production on Wed Mar 3 11:30:55 2004
Copyright (c) 1982, 2004, Oracle.All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> show parameter optimizer_mode
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
optimizer_mode
string'ALL_ROWS'
[/PHP]
这表明了Oracle在推动CBO优化器使用的坚定决心.

千问 · 2005-2-28 12:57:00

Oracle Database 10g提供一些新的特点帮助你收集数据库的统计数据，包括收集数据字典的统计数据。
RBO在10g里不再支持，虽然在10g中RBO仍然存在，但是Oracle可能会很快移除它。Oracle将不会提供关于RBO的任何修正及补丁。
此前,Oracle已发出在10g中不再支持rbo的声明
参考:
http://www.itpub.net/showthread.php?s=&threadid=88905[/COLOR]
所以，在10g中，Oracle增强了收集统计数据的能力，现在Oracle推荐你收集数据字典的统计数据，这在以前的版本里，是不推荐使用的，从Oracle9i开始，我们知道Oracle已经着手修改其原代码，对于系统SQL做了大量调整并且添加了大量的提示以稳定其执行计划，减少CBO可能带来的不确定性影响。现在显然，这些准备已经完成。

千问 · 2005-2-28 12:57:00

你可以使用以前的方式收集字典统计数据:
dbms_stats.gather_schema_stats('SYS')
或者
dbms_stats.gather_database_stats

我们看一个简单的测试:
这是一个系统查询的例子:
[PHP]
SQL> select count(*) from dba_users;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=ALL_ROWS (Cost=23 Card=1 Bytes=80)
10 SORT (AGGREGATE)
21 HASH JOIN (Cost=23 Card=9 Bytes=720)
32 HASH JOIN (Cost=21 Card=9 Bytes=693)
43 HASH JOIN (Cost=16 Card=9 Bytes=666)
54 HASH JOIN (OUTER) (Cost=12 Card=9 Bytes=639)
65
HASH JOIN (Cost=9 Card=9 Bytes=414)
76
HASH JOIN (Cost=7 Card=9 Bytes=396)
87
MERGE JOIN (CARTESIAN) (Cost=4 Card=1 Bytes=16)
98
TABLE ACCESS (FULL) OF 'PROFILE$' (TABLE) (Cost=2 Card=1 Bytes=8)
108
BUFFER (SORT) (Cost=2 Card=1 Bytes=8)
11 10
TABLE ACCESS (FULL) OF 'PROFILE$' (TABLE) (Cost=2 Card=1 Bytes=8)
127
TABLE ACCESS (FULL) OF 'USER$' (CLUSTER) (Cost=2 Card=13 Bytes=364)
136
TABLE ACCESS (FULL) OF 'PROFNAME$' (TABLE) (Cost=2 Card=1 Bytes=2)
145
TABLE ACCESS (FULL) OF 'RESOURCE_GROUP_MAPPING$'(TABLE) (Cost=2 Card=2 Bytes=50)
154 TABLE ACCESS (FULL) OF 'TS$' (CLUSTER) (Cost=4 Card=8 Bytes=24)
163 TABLE ACCESS (FULL) OF 'TS$' (CLUSTER) (Cost=4 Card=8 Bytes=24)
172 TABLE ACCESS (FULL) OF 'USER_ASTATUS_MAP' (TABLE) (Cost=2 Card=9 Bytes=27)
[/PHP]
然后收集系统统计数据:
[PHP]
SQL> exec dbms_stats.gather_schema_stats('SYS')
PL/SQL procedure successfully completed.
Elapsed: 00:08:34.23
[/PHP]
我们看现在的执行计划:
[PHP]
SQL> select count(*) from dba_users;
Elapsed: 00:00:00.18
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=ALL_ROWS (Cost=18 Card=1 Bytes=80)
10 SORT (AGGREGATE)
21 HASH JOIN (Cost=18 Card=1 Bytes=80)
32 NESTED LOOPS (Cost=15 Card=1 Bytes=78)
43 NESTED LOOPS (Cost=14 Card=1 Bytes=75)
54 NESTED LOOPS (Cost=12 Card=2 Bytes=134)
65
HASH JOIN (OUTER) (Cost=9 Card=3 Bytes=192)
76
HASH JOIN (Cost=7 Card=3 Bytes=117)
87
MERGE JOIN (CARTESIAN) (Cost=4 Card=7 Bytes=77)
98
TABLE ACCESS (FULL) OF 'PROFILE$' (TABLE) (Cost=2 Card=1 Bytes=8)
108
BUFFER (SORT) (Cost=2 Card=9 Bytes=27)
11 10
TABLE ACCESS (FULL) OF 'USER_ASTATUS_MAP' (TABLE) (Cost=2 Card=9 Bytes=27)
127
TABLE ACCESS (FULL) OF 'USER$' (CLUSTER) (Cost=2 Card=7 Bytes=196)
136
TABLE ACCESS (FULL) OF 'RESOURCE_GROUP_MAPPING$' (TABLE) (Cost=2 Card=2 Bytes=50)
145
TABLE ACCESS (CLUSTER) OF 'TS$' (CLUSTER) (Cost=1 Card=1 Bytes=3)
15 14
INDEX (UNIQUE SCAN) OF 'I_TS#' (INDEX) (Cost=0 Card=1)
164 TABLE ACCESS (BY INDEX ROWID) OF 'PROFILE$' (TABLE) (Cost=1 Card=1 Bytes=8)
17 16
INDEX (RANGE SCAN) OF 'I_PROFILE' (INDEX) (Cost=0 Card=17)
183 TABLE ACCESS (CLUSTER) OF 'TS$' (CLUSTER) (Cost=1 Card=1 Bytes=3)
19 18 INDEX (UNIQUE SCAN) OF 'I_TS#' (INDEX) (Cost=0 Card=1)
202 TABLE ACCESS (FULL) OF 'PROFNAME$' (TABLE) (Cost=2 Card=1 Bytes=2)

SQL>
[/PHP]
拥有了分析数据以后，最典型的，我们看到几个全表扫描现在使用了索引.

Oracle新增了两个过程用户字典统计数据的管理:
dbms_stats.gather_dictionary_stats和dbms_stats.delete_dictionary_ stats
分别用来收集和删除字典统计数据