Andrew's Oracle Blog: optimizer_features

In an earlier post, I pointed out that, starting with Oracle 10, you need to include an ORDER BY after a GROUP BY if you want the output to be in order.

While I was researching this on the Internet, I came across several people who had discovered unexpected behaviour after an upgrade. With proper testing, this should not happen but, if it does, you can try using optimizer_features_enable as a temporary workaround.

The example below demonstrates this. It runs a query in an Oracle 10 database with a GROUP BY but no ORDER BY. The output is not in order. The query is then rerun with optimizer_features_enable set to 9.2.0. This time the output is in order.

Setting this parameter disables all new features provided by an upgrade, not just the ones you do not like, so you should implement a long term solution ASAP:

TEST10 > sqlplus /

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Sep 5 13:58:05 2011

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL> @group_by

SQL> set pages 100

SQL> col first_letter format a12

SQL> col value format a30

SQL> select value from v$parameter

2 where name = 'optimizer_features_enable'

3 /

VALUE

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

10.2.0.3

SQL> select substr(segment_name,1,1) first_letter,

2 count(*)

3 from dba_segments

4 group by substr(segment_name,1,1)

5 /

FIRST_LETTER COUNT(*)

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

P 161

O 37

V 83

D 157

_ 68

M 448

N 98

W 742

Q 40

Y 3

C 135

I 550

B 120

F 55

U 20

S 717

T 250

A 316

J 21

E 143

L 178

R 303

H 662

K 9

b 1

G 7

X 23

27 rows selected.

SQL> alter session

2 set optimizer_features_enable = '9.2.0'

3 /

Session altered.