RE: query performance following 12c upgrade

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 23 Sep 2015 10:30:14 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D9282A520D_at_EXMBX01.thus.corp>


The difference in performance is so extreme that I'd first have to check whether or not the plans were true, they look like plans from EXPLAIN PLAN rather than plans pulled from memory. It would also help to see the predicate sections anyway.

If these really are the run-time plans with such a massive difference in performance I'd also want to check for any significant changes in data content, or physical data location.

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle



From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Steve Bradshaw [sjb1970_at_gmail.com] Sent: 23 September 2015 11:08
To: ORACLE-L
Subject: query performance following 12c upgrade

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:30:14 CEST

Original text of this message