sort group by并不排序(一)

昨天在帮同事,看一条sql时,发现一个有趣现象。

SQL> SELECT * FROM v$version;
 
BANNER
----------------------------------------------------------------
Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS FOR Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
 
SESSION 1: sys用户
 
SQL> SHOW parameter hash;
 
NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------
_gby_hash_aggregation_enabled        BOOLEAN                          FALSE

先清空shared_pool,排除对测试的影响

SQL> ALTER system FLUSH shared_pool;
 
System altered.

session 2: rate_lx用户

SQL> SET autot ON
sql> SELECT /*+ index(crb, idx_tt_tt_bell_09_stat) */
  2   crb.user_id, MIN(crb.rowid) dt
  3    FROM tt_tt_bell_09 crb
  4   WHERE crb.status = 1
  5     AND crb.process_mode = 0
  6     AND rownum <= 20000
  7     AND database_code IN (0,3)
  8   GROUP BY crb.user_id;
 
 USER_ID DT
---------- ------------------
 175756801 AAAmh6ABKAAAAiNAAR
 175756803 AAAmh6ABKAAAAiNAAH
 175756805 AAAmh6ABKAAAAiNAAB
 175756807 AAAmh6ABKAAAAiNAAA
 900051063 AAAmh6ABKAAAAiNAAT
 
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 1701980675
 
------------------------------------------------------------------------
Id  |Operation                     |Name                  |ROWS |Bytes|Cost(%CPU)| TIME |
------------------------------------------------------------------------
 0 |SELECT STATEMENT              |                      |    1|   26|19391 (1)|00:03:53|
 1 | SORT GROUP BY                |                      |    1|   26|19391 (1)|00:03:53|
 2 |  COUNT STOPKEY               |                      |     |     |         |        |
 3 |   TABLE ACCESS BY INDEX ROWID|tt_tt_bell_09         |    1|   26|19390 (1)|00:03:53|
 4 |    INDEX RANGE SCAN          |IDX_tt_tt_bell_09_STAT| 119K|     |  301 (0)|00:00:04|
-------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   2 - FILTER(ROWNUM<=20000)
   3 - FILTER("CRB"."PROCESS_MODE"=0)
   4 - access("CRB"."STATUS"=1)
       FILTER("DATABASE_CODE"=0 OR "DATABASE_CODE"=3)
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      27434  consistent gets
          0  physical reads
          0  redo SIZE
        758  bytes sent via SQL*Net TO client
        492  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          1  sorts (memory)
          0  sorts (disk)
          5  ROWS processed

上面的sql连续执行两次,得到固定的逻辑读及排序次数,因为第一次执行的Statistics信息还不是固定的!
因为有了排序,得到的数据是按user_id排序的。

session 1: sys用户

SQL>  ALTER system SET "_gby_hash_aggregation_enabled" =TRUE;
 
System altered.
 
SQL> ALTER system FLUSH shared_pool;
 
System altered.

session 2:rate_lx用户

sql> SELECT /*+ index(crb, idx_tt_tt_bell_09_stat) */
  2   crb.user_id, MIN(crb.rowid) dt
  3    FROM tt_tt_bell_09 crb
  4   WHERE crb.status = 1
  5     AND crb.process_mode = 0
  6     AND rownum <= 20000
  7     AND database_code IN (0,3)
  8   GROUP BY crb.user_id;
 
USER_ID DT
---------- ------------------
 175756805 AAAmh6ABKAAAAiNAAB
 175756801 AAAmh6ABKAAAAiNAAR
 175756803 AAAmh6ABKAAAAiNAAH
 175756807 AAAmh6ABKAAAAiNAAA
 900051063 AAAmh6ABKAAAAiNAAT
 
 
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 426863912
 
------------------------------------------------------------------------------------------
Id | Operation                     |Name                  |ROWS |Bytes|Cost(%CPU)| TIME   
------------------------------------------------------------------------------------------
 0 | SELECT STATEMENT              |                      |    1|   26|19391 (1)| 00:03:53
 1 |  HASH GROUP BY                |                      |    1|   26|19391 (1)| 00:03:53
 2 |   COUNT STOPKEY               |                      |     |     |         |         
 3 |    TABLE ACCESS BY INDEX ROWID|tt_tt_bell_09         |    1|   26|19390 (1)| 00:03:53
 4 |     INDEX RANGE SCAN          |IDX_tt_tt_bell_09_STAT| 119K|     |  301 (0)| 00:00:04
---------------------------------------------------------------------------- -------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   2 - FILTER(ROWNUM<=20000)
   3 - FILTER("CRB"."PROCESS_MODE"=0)
   4 - access("CRB"."STATUS"=1)
       FILTER("DATABASE_CODE"=0 OR "DATABASE_CODE"=3)
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      27434  consistent gets
          0  physical reads
          0  redo SIZE
        758  bytes sent via SQL*Net TO client
        492  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
          5  ROWS processed

同样执行两次,因为采用10gR2的_gby_hash_aggregation_enabled,所以在group by不再需要排序!到这里都没有疑义。

接下来在session 1中:

SQL>  ALTER system SET "_gby_hash_aggregation_enabled" =FALSE;
 
System altered.

session 2中执行:

SQL> SELECT /*+ index(crb, idx_tt_tt_bell_09_stat) */
  2   crb.user_id, MIN(crb.rowid) dt
  3    FROM tt_tt_bell_09 crb
  4   WHERE crb.status = 1
  5     AND crb.process_mode = 0
  6     AND rownum <= 20000
  7     AND database_code IN (0,3)
  8   GROUP BY crb.user_id;
 
   USER_ID DT
---------- ------------------
 175756805 AAAmh6ABKAAAAiNAAB
 175756801 AAAmh6ABKAAAAiNAAR
 175756803 AAAmh6ABKAAAAiNAAH
 175756807 AAAmh6ABKAAAAiNAAA
 900051063 AAAmh6ABKAAAAiNAAT
 
 
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 1701980675
 
----------------------------------------------------------------------------------------
Id |Operation                     |Name                  |ROWS |Bytes|Cost(%CPU)| TIME  
----------------------------------------------------------------------------------------
 0 |SELECT STATEMENT              |                      |   1 |   26|19391(1)| 00:03:53
 1 | SORT GROUP BY                |                      |   1 |   26|19391(1)| 00:03:53
 2 |  COUNT STOPKEY               |                      |     |     |        |         
 3 |   TABLE ACCESS BY INDEX ROWID|tt_tt_bell_09         |   1 |   26|19390(1)| 00:03:53
 4 |    INDEX RANGE SCAN          |IDX_tt_tt_bell_09_STAT| 119K|     |  301(0)| 00:00:04
----------------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   2 - FILTER(ROWNUM<=20000)
   3 - FILTER("CRB"."PROCESS_MODE"=0)
   4 - access("CRB"."STATUS"=1)
       FILTER("DATABASE_CODE"=0 OR "DATABASE_CODE"=3)
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      27434  consistent gets
          0  physical reads
          0  redo SIZE
        758  bytes sent via SQL*Net TO client
        492  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
          5  ROWS processed

执行计划发生变化了,变成了SORT GROUP BY ,但不存在排序(0 sorts (memory)),数据也是无序的,oracle只是从buffer cache里面直接拿出来,
并没有对数据进行排序。找一下metalink,没有相关信息,这个bug对系统的影响不是很大,发生机率应该很小。


One Response to “sort group by并不排序(一)”

  1. admin说道:

    大概过了几分钟,再去执行
    select /*+ index(crb, idx_tt_tt_bell_09_stat) */
    crb.user_id, min(crb.rowid) dt
    from tt_tt_bell_09 crb
    where crb.status = 1
    and crb.process_mode = 0
    and rownum <= 20000 and database_code in (0,3) group by crb.user_id; 得到的结果就有序了。

Post a Comment