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

-------------------------------------------

---------------------------------------------------------

--------------------------------------------------------

| 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

-------------------------------------------------

-----------------------------------------------------

| 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

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 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

---------------------------------------------------------------------------

---------------------------------------------------------------------------

| 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時代之後，一些報表和功能就受到影響。

從這個例子裡，筆者還是有很多感觸的：

首先，就是資料庫的重要性。拿資料庫作為“黑盒”的結果是很可怕的，多瞭解一些資料庫知識，多積累一些經驗，是我們作為開發人員，特別是高階開發人員必須做的事情。這個必然上升到我們的事業發展高度。

第二，規範開發。最近接觸過一些開發團隊的開發方式，開發、測試、投產使用三種不同的資料庫版本。這樣做是非常錯誤的！每個資料版本都有其自己的特性，一旦開發階段、測試和投產使用版本不同，應用系統行為就會存在差異。所以，無論系統新舊，起碼要堅持一個版本來做。

第三，謹慎測試。資料庫、應用開發是一個龐雜的學科。實際工作中，沒有人可以全部瞭解所有的技術，也沒有人可以將所有的元件做到如數家珍。每一次部署、升級對於團隊而言，都存在風險。這種風險輕則導致錯誤發生，嚴重可能引起業務中斷，造成更大損失。在這樣的情況下，謹慎和測試是我們需要注重的要點。不要輕易上線、升級，多測試、多準備預案，想想怎麼恢復原來的狀態，對業務最小影響才是重點。

由於版本升級引發的SQL語句故障

相關文章