Coskan’s Approach to Oracle

March 27, 2009

Good reason to move from 10.2.0.2

Filed under: Bugs — coskan @ 11:31 am

If somebody ask me, which type of bug I prefer to live with from two options which are
1-Performance related bugs 2- wrong resultset bugs. I would prefer to live with performance related ones because wrong resultset is something I never want to see on any of my databases. It is hard to catch and can cause big problems especially on database with Finance data on it.

Today I am going to give you a good reason to move from 10.2.0.2 because of a wrong resultset issue. Issue we hit was on our Peoplesoft database running on HP-UX 11i on Oracle EE 10.2.0.2 . It looks like it is smilar to the
Bug 5843316 Wrong results with index join and function-based index.

Here is my test case for the issue.

My table

create table t1 as
select
mod(object_id, 11000) id,
trunc(dbms_random.value(0,9)) seq,
trunc(dbms_random.value(0,3)) rec,
trunc(created) created,
trunc(last_ddl_time) lastddl,
substr(owner,1,1) status,
object_type,
object_name,
generated,
subobject_name from all_objects;=

Two index on the table (both index have the same four column but second one has two more columns and the 4th column on the index is changed for the second one)


CREATE INDEX T1_IX2 ON T1
(id,rec, created DESC, seq DESC, object_type);


CREATE INDEX T1_IX1 ON T1
(id,rec, created DESC, seq DESC, STATUS, object_name,subobject_name);

Gather the stats

exec dbms_stats.gather_table_stats (ownname=>user,tabname=>'T1',cascade=>TRUE)

On our production case there wasn’t any hint but for simulating the test I am forcing to use indexes to see the different resultset behaviour

These are the SQLs I use for the tests.

wrong result set T1_IX1 hinted


SELECT /*+gather_plan_statistics INDEX (t1 t1_ix1)*/
lastddl
FROM t1
WHERE id = '19'
AND t1.status 'P'
AND t1.created =
(SELECT MAX (created)
FROM t1 x
WHERE t1.id = x.id AND t1.rec=x.rec)
AND t1.seq =
(SELECT MAX (seq)
FROM t1 x
WHERE t1.id = x.id
AND t1.rec=x.rec
AND t1.created = x.created);

right result set T1_IX2 hinted

SELECT /*+gather_plan_statistics INDEX (t1 t1_ix2)*/
lastddl
FROM t1
WHERE id = '19'
AND t1.status 'P'
AND t1.created =
(SELECT MAX (created)
FROM t1 x
WHERE t1.id = x.id AND t1.rec=x.rec)
AND t1.seq =
(SELECT MAX (seq)
FROM t1 x
WHERE t1.id = x.id
AND t1.rec=x.rec
AND t1.created = x.created);

These are the runtime behaviours of SQLs
FIRST CASE- Not working when ,resultset column LAST_DDL, is not included in the index T1_IX1. No rows returned

 SQL> SELECT /*+gather_plan_statistics  INDEX (t1 t1_ix1)*/
  2 <strong> lastddl</strong>
  3    FROM t1
  4   WHERE id = '19'
  5     AND t1.status <> 'P'
  6     AND t1.created =
  7                    (SELECT MAX (created)
  8                       FROM t1 x
  9                      WHERE t1.id = x.id AND t1.rec=x.rec)
 10     AND t1.seq =
 11            (SELECT MAX (seq)
 12               FROM t1 x
 13              WHERE t1.id = x.id
 14                AND t1.rec=x.rec
 15                AND t1.created = x.created);

no rows selected
SQL> @x

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
Plan hash value: 4001972719

-----------------------------------------------------------------------------------------
| Id  | Operation                      | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |        |     1 |   154 |    14   (8)| 00:00:01 |
|*  1 |  FILTER                        |        |       |       |            |          |
|   2 |   HASH GROUP BY                |        |     1 |   154 |    14   (8)| 00:00:01 |
|   3 |    NESTED LOOPS                |        |     1 |   154 |     7   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T1     |     1 |    71 |     4   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | T1_IX1 |     1 |       |     3   (0)| 00:00:01 |
|   6 |       SORT AGGREGATE           |        |     1 |    35 |            |          |
|*  7 |        INDEX RANGE SCAN        | T1_IX1 |    12 |   420 |     3   (0)| 00:00:01 |
|   8 |     TABLE ACCESS BY INDEX ROWID| T1     |     1 |    83 |     3   (0)| 00:00:01 |
|*  9 |      INDEX RANGE SCAN          | T1_IX1 |     1 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("SEQ"))=MAX("SEQ") AND
              SYS_OP_DESCEND("T1"."SEQ")=SYS_OP_DESCEND(MAX("SEQ")))
   5 - access("ID"=19)
       filter("T1"."STATUS"<>'P' AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))=
               (SELECT MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))) FROM "T1" "X" WHERE
              "X"."REC"=:B1 AND "X"."ID"=:B2) AND SYS_OP_DESCEND("CREATED")=SYS_OP_DESCEND(
              (SELECT MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))) FROM "T1" "X" WHERE
              "X"."REC"=:B3 AND "X"."ID"=:B4)))
   7 - access("X"."ID"=:B1 AND "X"."REC"=:B2)
   9 - access("X"."ID"=19 AND "T1"."REC"="X"."REC" AND
              SYS_OP_DESCEND("CREATED")=SYS_OP_DESCEND("CREATED"))
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))=SYS_OP_UNDESCEND(SYS_OP
              _DESCEND("CREATED")))

SECOND CASE- working when resultset column ID which is IN the index T1_IX1.

 SQL> SELECT /*+gather_plan_statistics  INDEX (t1 T1_IX1)*/
  2  id
  3    FROM t1
  4   WHERE id = '19'
  5     AND t1.status <> 'P'
  6     AND t1.created =
  7                    (SELECT MAX (created)
  8                       FROM t1 x
  9                      WHERE t1.id = x.id AND t1.rec=x.rec)
 10     AND t1.seq =
 11            (SELECT MAX (seq)
 12               FROM t1 x
 13              WHERE t1.id = x.id
 14                AND t1.rec=x.rec
 15                AND t1.created = x.created);

       ID
----------
        19
        19
        19

SQL> @x

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
Plan hash value: 2740842560

-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |     1 |   100 |    13   (8)| 00:00:01 |
|*  1 |  HASH JOIN          |         |     1 |   100 |     7  (15)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN  | T1_IX1  |     1 |    63 |     3   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE   |         |     1 |    48 |            |          |
|*  4 |     INDEX RANGE SCAN| T1_IX1  |     1 |    48 |     3   (0)| 00:00:01 |
|   5 |   VIEW              | VW_SQ_1 |    11 |   407 |     3   (0)| 00:00:01 |
|   6 |    HASH GROUP BY    |         |    11 |   814 |     3   (0)| 00:00:01 |
|*  7 |     INDEX RANGE SCAN| T1_IX1  |    11 |   814 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))="VW_COL_1"
              AND SYS_OP_DESCEND("CREATED")=SYS_OP_DESCEND("VW_COL_1") AND
              "T1"."ID"="ID" AND "T1"."REC"="REC")
   2 - access("ID"=19)
       filter("T1"."STATUS"<>'P' AND
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("SEQ"))= (SELECT
              MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("SEQ"))) FROM "T1" "X" WHERE
              SYS_OP_DESCEND("CREATED")=SYS_OP_DESCEND(:B1) AND "X"."REC"=:B2 AND
              "X"."ID"=:B3 AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))=:B4) AND
              SYS_OP_DESCEND("SEQ")=SYS_OP_DESCEND( (SELECT
              MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("SEQ"))) FROM "T1" "X" WHERE
              SYS_OP_DESCEND("CREATED")=SYS_OP_DESCEND(:B5) AND "X"."REC"=:B6 AND
              "X"."ID"=:B7 AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))=:B8)))
   4 - access("X"."ID"=:B1 AND "X"."REC"=:B2 AND
              SYS_OP_DESCEND("CREATED")=SYS_OP_DESCEND(:B3))
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))=:B1)
   7 - access("X"."ID"=19)
       filter("X"."ID"=19)

THIRD CASE- Still not working when one of the resultset columns, LAST_DDL is NOT in the index T1_IX1. No rows returned

 
SQL> SELECT /*+gather_plan_statistics  INDEX (t1 t1_ix1)*/
  2  lastddl,id
  3    FROM t1
  4   WHERE id = '19'
  5     AND t1.status <> 'P'
  6     AND t1.created =
  7                    (SELECT MAX (created)
  8                       FROM t1 x
  9                      WHERE t1.id = x.id AND t1.rec=x.rec)
 10     AND t1.seq =
 11            (SELECT MAX (seq)
 12               FROM t1 x
 13              WHERE t1.id = x.id
 14                AND t1.rec=x.rec
 15                AND t1.created = x.created);

no rows selected

SQL> @x

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
Plan hash value: 4001972719

-----------------------------------------------------------------------------------------
| Id  | Operation                      | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |        |     1 |   154 |    14   (8)| 00:00:01 |
|*  1 |  FILTER                        |        |       |       |            |          |
|   2 |   HASH GROUP BY                |        |     1 |   154 |    14   (8)| 00:00:01 |
|   3 |    NESTED LOOPS                |        |     1 |   154 |     7   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T1     |     1 |    71 |     4   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | T1_IX1 |     1 |       |     3   (0)| 00:00:01 |
|   6 |       SORT AGGREGATE           |        |     1 |    35 |            |          |
|*  7 |        INDEX RANGE SCAN        | T1_IX1 |    12 |   420 |     3   (0)| 00:00:01 |
|   8 |     TABLE ACCESS BY INDEX ROWID| T1     |     1 |    83 |     3   (0)| 00:00:01 |
|*  9 |      INDEX RANGE SCAN          | T1_IX1 |     1 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("SEQ"))=MAX("SEQ") AND
              SYS_OP_DESCEND("T1"."SEQ")=SYS_OP_DESCEND(MAX("SEQ")))
   5 - access("ID"=19)
       filter("T1"."STATUS"<>'P' AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))=
               (SELECT MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))) FROM "T1" "X" WHERE
              "X"."REC"=:B1 AND "X"."ID"=:B2) AND SYS_OP_DESCEND("CREATED")=SYS_OP_DESCEND(
              (SELECT MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))) FROM "T1" "X" WHERE
              "X"."REC"=:B3 AND "X"."ID"=:B4)))
   7 - access("X"."ID"=:B1 AND "X"."REC"=:B2)
   9 - access("X"."ID"=19 AND "T1"."REC"="X"."REC" AND
              SYS_OP_DESCEND("CREATED")=SYS_OP_DESCEND("CREATED"))
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))=SYS_OP_UNDESCEND(SYS_OP
              _DESCEND("CREATED")))

FOURTH- It is working when we force the database use T1_IX2 for both cases (resultset IN or not IN)

 SQL> SELECT /*+gather_plan_statistics  INDEX (t1 t1_ix2) */
  2  lastddl,id
  3    FROM t1
  4   WHERE id = '19'
  5     AND t1.status <> 'P'
  6     AND t1.created =
  7                    (SELECT  MAX (created)
  8                       FROM t1 x
  9                      WHERE t1.id = x.id AND t1.rec=x.rec)
 10     AND t1.seq =
 11            (SELECT MAX (seq)
 12               FROM t1 x
 13              WHERE t1.id = x.id
 14                AND t1.rec=x.rec
 15                AND t1.created = x.created);

LASTDDL                   ID
----------------- ----------
20070125 00:00:00         19
20070128 00:00:00         19
20090327 00:00:00         19

SQL> @x

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
Plan hash value: 3946360691

-----------------------------------------------------------------------------------------
| Id  | Operation                      | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |        |     1 |   154 |    14   (8)| 00:00:01 |
|*  1 |  FILTER                        |        |       |       |            |          |
|   2 |   HASH GROUP BY                |        |     1 |   154 |    14   (8)| 00:00:01 |
|   3 |    NESTED LOOPS                |        |     1 |   154 |     7   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| T1     |     1 |    71 |     4   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | T1_IX2 |     1 |       |     3   (0)| 00:00:01 |
|   6 |       SORT AGGREGATE           |        |     1 |    35 |            |          |
|*  7 |        INDEX RANGE SCAN        | T1_IX1 |    12 |   420 |     3   (0)| 00:00:01 |
|   8 |     TABLE ACCESS BY INDEX ROWID| T1     |     1 |    83 |     3   (0)| 00:00:01 |
|*  9 |      INDEX RANGE SCAN          | T1_IX1 |     1 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("SEQ"))=MAX("SEQ") AND
              SYS_OP_DESCEND("SEQ")=SYS_OP_DESCEND(MAX("SEQ")))
   4 - filter("T1"."STATUS"<>'P')
   5 - access("ID"=19)
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))= (SELECT
              MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))) FROM "T1" "X" WHERE
              "X"."REC"=:B1 AND "X"."ID"=:B2) AND SYS_OP_DESCEND("CREATED")=SYS_OP_DESCEND(
              (SELECT MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))) FROM "T1" "X" WHERE
              "X"."REC"=:B3 AND "X"."ID"=:B4)))
   7 - access("X"."ID"=:B1 AND "X"."REC"=:B2)
   9 - access("X"."ID"=19 AND "T1"."REC"="X"."REC" AND
              SYS_OP_DESCEND("CREATED")=SYS_OP_DESCEND("CREATED"))
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("CREATED"))=SYS_OP_UNDESCEND(SYS_OP
              _DESCEND("CREATED")))

Same resultset on HP-UX-Linux 10.2.0.2. Don’t have 10.2.0.2 windows but works fine on 10.2.0.3 on Windows.

on 10.2.0.4 and 11G problem is gone

This case is enough reason that lead me to check every solved bug for the upper releases to see if our databases are facing the problem especially for wrong resultset. Another good part of looking for bugs, is that it is nice way to learn how to crash your database, a bit of oracle internals, interesting test cases etc.

About these ads

2 Comments »

  1. [...] Gundogar similarly found a good reason to move from 10.2.0.2 to 10.2.0.4 or 11G: a resultset [...]

    Pingback by Log Buffer #142: a Carnival of the Vanities for DBAs | Pythian Group Blog — April 10, 2009 @ 4:43 pm

  2. Coskan,

    It looks like Oracle has back-ported the optimizer code handling descending indexes in 11.2.0.2 release.
    I am already getting two of such a different result set b/n 11.1.0.7 (correct result) and 11.2.0.2 (no rows returned).

    Antony

    Comment by orapsdba — December 5, 2011 @ 8:05 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 193 other followers

%d bloggers like this: