Implicit parameter for automatic HASH processing when oracle reads data: _ gby_hash_aggregation_enabled,

Source: Internet
Author: User

Implicit parameter for automatic HASH processing when oracle reads data: _ gby_hash_aggregation_enabled,
1. History of this parameter: _ gby_hash_aggregation_enabled

Oracle 11.1.0:
Parameter Name: _ Gby_hash_aggregation_enabled
Description: Enable group-by and aggregation using hash scheme
Type: BOOL Obsoleted: FALSE
Can alter session: TRUE Can alter system: IMMEDIATE
Oracle 10.2.0:
Parameter Name: _ Gby_hash_aggregation_enabled
Description: Enable group-by and aggregation using hash scheme
Type: BOOL Obsoleted: FALSE
Can alter session: TRUE Can alter system: IMMEDIATE
Oracle 10.1.0: No such parmeter in Oracle 10.1.0.Oracle 9.2.0: No such parmeter in Oracle 9.2.0.Oracle 8.1.7: No such parmeter in Oracle 8.1.7.Oracle 8.0.6: No such parmeter in Oracle 8.0.6.Oracle 7.3.4:

No such parmeter in Oracle 7.3.4.

Ii. Two ORACLE group by methods: hash group by and sort group

Oracle10g improved the algorithm during the distinct operation and used Hash Unique to represent the previous Sort Unique. The row is determined by the hidden parameter "_ gby_hash_aggregation_enabled". The default value of optimizer_features_enable is TRUE when it is set.

The cpu cost of hash unique should be lower than that of sort unique. Similarly, sort merge join is rarely used for hash 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
--------------------
1 recursive cballs
0 db block gets
3 consistent gets
1 physical reads
0 redo size
752 bytes sent via SQL * Net to client
469 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9 rows processed

In RBO mode, SORT is still required, and sort unique is used.

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
--------------------
1 recursive cballs
0 db block gets
3 consistent gets
0 physical reads
0 redo size
752 bytes sent via SQL * Net to client
469 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed

Hash unique avoids sorting. When the data volume is large, we should be able to see a low % 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
--------------------
1 recursive cballs
0 db block gets
3 consistent gets
0 physical reads
0 redo size
752 bytes sent via SQL * Net to client
469 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9 rows processed


Iii. hash group by bug and solution (reproduced)

As I have not encountered any related bugs, I will first introduce the experience of my predecessors here. Hope the predecessors do not mind.


In 10gR2, group by is changed from sort group by to hash group by. This algorithm is improved to cancel the sorting operation required by sort group, even if the hash algorithm is used, there is a possibility of collision. The godlessme of itpub has encountered such a problem. It should be a bug.

The following is a demonstration of how to solve this problem. In fact, we need to solve the problem of sorting inaccuracy caused by hash group by using the previous sort group, the _ gby_hash_aggregation_enabled hidden parameter is introduced in 10gR2. this parameter is set to true by default and set to false.

SQL> select status, count (*) from tmp_object group by status;
Status count (*)
---------
INVALID 29
VALID 10236

Execution Plan
-----------------------------
Plan hash value: 3490974944
-------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
-------------------------------------
| 0 | select statement | 2 | 12 | 35 (6) | 00:00:01 |
| 1 | hash group by | 2 | 12 | 35 (6) | 00:00:01 |
| 2 | table access full | TMP_OBJECT | 10265 | 61590 | 33 (0) | 00:00:01 |
-------------------------------------
Statistics
-----------------------------
24 recursive cballs
0 db block gets
136 consistent gets
0 physical reads
0 redo size
522 bytes sent via SQL * Net to client
385 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed

SQL> col ksppinm format a39
SQL> col ksppstvl format a39
SQL> select ksppinm, ksppstvl
2 from x $ ksppi pi, x $ ksppcv cv
3 where cv. indx = pi. indx and pi. ksppinm like '_ % 'escape''
4 and pi. ksppinm like '% & parameter % ';
Enter value for parameter: gby
Old 4: and pi. ksppinm like '% & parameter %'
New 4: and pi. ksppinm like '% gby %'

KSPPINM KSPPSTVL
--------------------------------
_ Gby_onekey_enabled TRUE
_ Gby_hash_aggregation_enabled TRUE

SQL> alter session set "_ gby_hash_aggregation_enabled" = false;
Session altered.
SQL> select status, count (*) from tmp_object group by status;
Status count (*)
---------
INVALID 29
VALID 10312

Execution Plan
-----------------------------
Plan hash value: 1360369603
-------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
-------------------------------------
| 0 | select statement | 10860 | 54300 | 32 (7) | 00:00:01 |
| 1 | sort group by | 10860 | 54300 | 32 (7) | 00:00:01 |
| 2 | table access full | TMP_OBJECT | 10860 | 54300 | 30 (0) | 00:00:01 |
-------------------------------------
Statistics
-----------------------------
0 recursive cballs
0 db block gets
134 consistent gets
0 physical reads
0 redo size
522 bytes sent via SQL * Net to client
385 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed

Address: http://tech.it168.com/db/o/2006-11-12/200611122129197.shtml

In fact, in my opinion, Oracle reads data from the table by data block by default, and some of the data stored in the table may be arranged in ascending or descending order. When we read data from data blocks, it should be in a regular order, or the sorting of SORT group by will satisfy our needs, and for small tables, such reading will not have a great impact on the performance. However, after 10 Gb, the default read mode is changed to hash group by, resulting in data that can be read in order, and order by is added to the memory. In addition, when reading large tables, it is possible to report

ORA-00600: internal error code, arguments: [32695], [hash aggregation can't be done], [], [], [], [], [], [], [], [] error.

For this error, we can only change the default value of parameter _ gby_hash_aggregation_enabled to false.

Appendix: how to view implicit parameters:

SQL> SELECT x. ksppinm NAME, y. ksppstvlVALUE, x. ksppdesc describ

From sys. x $ ksppi x, SYS. x $ ksppcv y

WHEREx. inst_id = USERENV ('instance ')

AND y. inst_id = USERENV ('instance ')

AND x. indx = y. indx

AND x. ksppinm LIKE '% xxx % ';


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.