query performance following 12c upgrade

From: Steve Bradshaw <sjb1970_at_gmail.com>
Date: Wed, 23 Sep 2015 11:08:07 +0100
Message-ID: <CA+nXkiUBR0PUrnqE+WD+u7rrgqzFSrdciJVmQxiKVdvzr1tvDw_at_mail.gmail.com>

Hi,

Looking for ideas as to why a query has started taking a lot longer to execute since upgrading to 12.1.0.2 from 11.2.0.3.

Its a simple 1 table query, that is performing an index skip scan. Under 12c, the query is taking 12-13 seconds to return a row, whereas previously it was a fraction of a second.

There is a difference in the plans between the 2 versions. Under 12c, the table access is 'TABLE ACCESS BY INDEX ROWID BATCHED'.

From the 12c database:

---

| Id  | Operation                           | Name           | Rows  |
Bytes | Cost (%CPU)| Time     |



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


|   0 | SELECT STATEMENT                    |                |     2

| 76 | 573 (80)| 00:00:01 |

|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TABLE1 | 2 | 76 | 573 (80)| 00:00:01 |

|*  2 |   INDEX SKIP SCAN                   | TABLE1_IDX_7   |     7
|       |   572  (80)| 00:00:01 |



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

From the 12c database (having set optimizer_features_enabled to 11.2.0.3 for the session):

---

| Id  | Operation                   | Name           | Rows  | Bytes | Cost
(%CPU)| Time     |



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


|   0 | SELECT STATEMENT            |                |     1 |    38

| 573 (80)| 00:00:01 |

|* 1 | TABLE ACCESS BY INDEX ROWID| TABLE1 | 1 | 38 | 573 (80)| 00:00:01 |

|*  2 |   INDEX SKIP SCAN           | TABLE1_IDX_7   |     7
|       |   572  (80)| 00:00:01 |



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

Any ideas how I can fix this without changing the parameter at the database level?

Thanks in advance,

Steve

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Sep 23 2015 - 12:08:07 CEST