Wednesday, September 07, 2011

optimizer_features_enable



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
 
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
 
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.
 
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(*)
------------ ----------
A                   316
B                   120
C                   135
D                   157
E                   143
F                    55
G                     7
H                   662
I                   550
J                    21
K                     9
L                   178
M                   448
N                    98
O                    37
P                   161
Q                    40
R                   303
S                   717
T                   250
U                    20
V                    83
W                   742
X                    23
Y                     3
_                    68
b                     1
 
27 rows selected.
 
SQL>

No comments: