Oracle作为目前市场占有率最高的数据库服务器产品，其发展是稳定且快速的。每一个版本的变化，都会带来很多新特性、新功能。我们在使用这些特性的同时，同样也面对一个升级和迁移的问题。
就SQL语句而言，这种现象是比较明显的。Oracle每一个版本都会提出一些新的函数、方法。同时，一些过去的功能也在细节之处做出了改变。
我们的应用程序在升级过程中，对软硬件、数据一致性的验证是一方面，而功能上的变化验证更是需要关注的方面。实际升级中，我们常常会遇到这样的场景：原先在9i上面运行的应用程序，转到10g上，硬件环境还升级了，反而作业时间变长。甚至一些正常显示的报表也出现错误。
本篇关注一个从9i升级到10g经常遇到的问题：group by排序。
1、问题简述
信息安全原因，笔者采用实验模拟的方法。原先系统在9i的数据库上，运行报表SQL语句。
SQL> select * from v$version;
BANNER
--------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
SQL> select job,count(*) from scott.emp group by job;
JOB COUNT(*)
--------- ----------
ANALYST 2
CLERK 4
MANAGER 3
PRESIDENT 1
SALESMAN 4
报表按照job列进行排序，结果集合中Job自然按照升序进行排序。开发应用是在9i版本上进行，所以开发人员也正好用的方便。
随着系统的使用，运维方希望将系统升级到10g版本。在测试过程中，发生了问题。相同的SQL语句没有显示相同的结果。
SQL> select * from v$version;
BANNER
------------------------------
Oracle Database 10g Release 10.2.0.4.0 - 64bit Production
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
SQL> select job,count(*) from scott.emp group by job;
JOB COUNT(*)
--------- ----------
CLERK 4
SALESMAN 4
PRESIDENT 1
MANAGER 3
ANALYST 2
相同的SQL语句，我们却发现group by之后，Job排序的情况消失了。数据结果是以无序的状态显示。
2、原因分析
客户明确的要求是排序。相同的SQL语句为什么结果顺序不同呢？SQL语句是一种描述语句，我们只负责描述，不负责实际程序代码生成。研究细节的第一步就是执行计划。
我们首先检查SQL的执行计划。在9i下，SQL语句执行计划如下：
SQL> explain plan for select job, count(*) from scott.emp group by job;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------
---------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT GROUP BY | | | | |
| 2 | TABLE ACCESS FULL | EMP | | | |
---------------------------------------------------------
Note: rule based optimization
10 rows selected
而我们在10g下的执行计划是：
SQL> explain plan for select job, count(*) from scott.emp group by job;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------
Plan hash value: 4067220884
-------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 40 | 4 (25)| 00:00:01 |
| 1 | HASH GROUP BY | | 5 | 40 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 112 | 3 (0)| 00:00:01 |
--------------------------------------------------------
9 rows selected
分析的和我们预想的接近，是SQL执行计划发生了变化。我们在9i版本上，看到两个值得关注的现象：
首先，9i采用的是RBO。从9i到10g的诸多变化中，RBO退出舞台、CBO占主流是一个重要方面。默认情况下，9i还是采用RBO策略。
另一个是进行group by动作的类型，是“SORT GROUP BY”。我们看Oracle执行计划，要以一种“实际编程”的心态来看待每个动作。Sort group by显然是进行group动作是将所有的结果集合进行排序，之后合并相邻结果，最后形成汇总结果。这样也就可以解释为什么结果集合在9i时候是有序的。因为在操作的过程中，Oracle不得不进行排序，才能汇总。
而10g时候，情况发生了不同。首先是CBO正式开始推进，RBO不在是默认的执行计划生成器。另外在结果group动作中，出现了hash group by操作。
Hash是从10g开始大量出现的操作类型，最有名的是Hash Join。相比Sort动作，Hash消耗更小的PGA空间和排序CPU资源。Hash Group By显然依托的是Hash算法。
Hash Group By不需要全排序，而是“分堆”之后再堆内排序的过程。最后将结果集合合并返回。从而是不存在全局排序的动作的！
Oracle的思路是这样的：从SQL描述过程中，我们只要求了group by。Group by规范中从来就没有声明过结果集合有序。另一方面，只要SQL中没有的语句子句，自由度是Oracle自己的。所以，这个内部算法改变是可以接受的。
下面，我们怎么处理？
3、问题解决
了解了原因，才能有入手点进行处理。解决这个方法有三条思路。
ü SQL语句改写
这个故障，本质上不是Oracle的故障，而是应用程序的问题。如果有条件改写SQL，最简单的策略就是将显示的order by添加在语句里面。加入order by之后，Oracle自然将排序结果返回。
ü 隐含参数改写
很多时候，我们不具有改写SQL的条件。比如源代码丢失、开发团队撤离等。Oracle也是考虑到这种情况带来的问题，于是也有内部的处理方法。
隐含参数：_gby_hash_aggregation_enabled用于控制Hash Group By动作的执行，默认是开启的。如果我们设置我false，Oracle就不会采用Hash Group By动作。这样，原来的Sort Group By就会出现。
SQL> alter session set "_gby_hash_aggregation_enabled" = false;
Session altered
SQL> alter system flush shared_pool;
System altered
SQL> select job,count(*) from scott.emp group by job;
JOB COUNT(*)
--------- ----------
ANALYST 2
CLERK 4
MANAGER 3
PRESIDENT 1
SALESMAN 4
执行计划上，也的确是我们预想的那样。
SQL> explain plan for select job, count(*) from scott.emp group by job;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 15469362
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 40 | 4 (25)| 00:00:01 |
| 1 | SORT GROUP BY | | 5 | 40 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 112 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
9 rows selected
这种策略有适应性。如果原有系统中大量使用group by排序特性，可以在实例层面修改该参数。虽然不能发挥hash group by的优点，但起码功能性需求是满足的，这个才是系统的根本。
ü Hint手段
针对特殊的SQL，我们可以添加hint来避免hash group by动作。
SQL> conn sys/oracle@mmm as sysdba
Connected to Oracle Database 10g Release 10.2.0.4.0
Connected as SYS
SQL> select /*+ NO_USE_HASH_AGGREGATION */job,count(*) from scott.emp group by job;
JOB COUNT(*)
--------- ----------
ANALYST 2
CLERK 4
MANAGER 3
PRESIDENT 1
SALESMAN 4
SQL> explain plan for select /*+ NO_USE_HASH_AGGREGATION */job,count(*) from scott.emp group by job;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 15469362
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 40 | 4 (25)| 00:00:01 |
| 1 | SORT GROUP BY | | 5 | 40 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 112 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
9 rows selected
4、结论
Group by排序的特性，在9i时代被应用的很广。一些参考书里面也会将其作为“秘籍”加以宣传。这样的系统进入CBO时代之后，一些报表和功能就受到影响。
从这个例子里，笔者还是有很多感触的：
首先，就是数据库的重要性。拿数据库作为“黑盒”的结果是很可怕的，多了解一些数据库知识，多积累一些经验，是我们作为开发人员，特别是高级开发人员必须做的事情。这个必然上升到我们的事业发展高度。
第二，规范开发。最近接触过一些开发团队的开发方式，开发、测试、投产使用三种不同的数据库版本。这样做是非常错误的！每个数据版本都有其自己的特性，一旦开发阶段、测试和投产使用版本不同，应用系统行为就会存在差异。所以，无论系统新旧，起码要坚持一个版本来做。
第三，谨慎测试。数据库、应用开发是一个庞杂的学科。实际工作中，没有人可以全部了解所有的技术，也没有人可以将所有的组件做到如数家珍。每一次部署、升级对于团队而言，都存在风险。这种风险轻则导致错误发生，严重可能引起业务中断，造成更大损失。在这样的情况下，谨慎和测试是我们需要注重的要点。不要轻易上线、升级，多测试、多准备预案，想想怎么恢复原来的状态，对业务最小影响才是重点。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-1063257/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/17203031/viewspace-1063257/
2405
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
