oracle 10g distinct算法的改进（没有了sort）
Oracle10g在distinct操作时作了算法改进，使用Hash Unique 代理了以前的Sort Unique.该行为由隐藏参数”_gby_hash_aggregation_enabled”决定，optimizer_features_enable设置为10.2.0.1时默认为TRUE.HASH UNIQUE 的CPU COST应该比SORT UNIQUE要低，同理常用HASH JOIN而少用SORT MERGE JOIN。
SQL>create table t as select * from dba_users;
Table created.
SQL> set autotrace on
SQL> select distinct password from t;
———————————–
| Id| Operation | Name |
———————————–
| 0 | SELECT STATEMENT | |
| 1 |SORT UNIQUE | |
| 2 | TABLE ACCESS FULL| T |
———————————–
Note
—–
- rule based optimizer used (consider using cbo)
Statistics
———————————————————-
1recursive calls
0db block gets
3consistent gets
1physical reads
0redo size
752bytes sent via SQL*Net to client
469bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
1sorts (memory)
0sorts (disk)
9rows processed
RBO模式下，仍然要做SORT,使用的是 SORT UNIQUE
SQL> show parameters opt
NAME TYPE VALUE
———————————— ———– ——————————
optimizer_features_enable string 10.2.0.1
optimizer_mode string RULE
SQL> alter session set optimizer_mode = choose;
Session altered.
SQL> analyze table t compute statistics;
Table analyzed.
SQL> select distinct password from t;
Execution Plan
———————————————————-
Plan hash value: 1901613472
—————————————————————————
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 9 | 144 | 3(34)| 00:00:01 |
| 1 |HASH UNIQUE | | 9 | 144 | 3(34)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T | 9 | 144 | 2 (0)| 00:00:01 |
—————————————————————————
Statistics
———————————————————-
1recursive calls
0db block gets
3consistent gets
0physical reads
0redo size
752bytes sent via SQL*Net to client
469bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
9rows processed
HASH UNIQUE避免了排序，在数据量很大的时候应该能够看到较低的%CPU COST
SQL>ALTER SESSION SET “_gby_hash_aggregation_enabled” = FALSE;
SQL>select distinct password from t;
Execution Plan
———————————————————-
Plan hash value: 965418380
—————————————————————————
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 9 | 144 | 3(34)| 00:00:01 |
| 1 |SORT UNIQUE | | 9 | 144 | 3(34)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T | 9 | 144 | 2 (0)| 00:00:01 |
—————————————————————————
Statistics
———————————————————-
1recursive calls
0db block gets
3consistent gets
0physical reads
0redo size
752bytes sent via SQL*Net to client
469bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
1sorts (memory)
0sorts (disk)
9rows processed
_gby_hash_aggregation_enabled决定默认使用哪种方式执行DISTINCT.
_gby_hash_aggregation_enabled=false
停用Oracle HASH排序功能， group by 也会受影响。
前一阶段我就遇到过这个问题。对一个表的分区两千多万行数据进行汇总时，由于BUg报错
ORA-00600: internal error code, arguments: , , [], [], [], [], [], [] with Parameters:p_Settlement_Date->20101024
文档ORA-600
对应Bug
Bug# 6471770 See Note:6471770.8
ora-32690/OERI from Hash GROUP BY
Fixed: 10.2.0.5, 11.1.0.7, 11.2, 10.2.0.3.P23, 10.2.0.4.P05
Bug# 5893340 See Note:5893340.8
OERI from hash aggregation
Fixed: 10.2.0.4, 11.1.0.6, 10.2.0.3.P15
xuexilexuexile 对于一个新手看不懂啊 。 后面要好好学习了。
页:
[1]