Re: query performance following 12c upgrade

Thanks for the reply.

The plans I've been looking at are from sqplus with autotrace on - is that incorrect?

Steve

On Wed, Sep 23, 2015 at 11:30 AM, Jonathan Lewis < jonathan_at_jlcomp.demon.co.uk> wrote:

>
>
> 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