Re: Re: query performance following 12c upgrade

From: Cherif Ben Henda <cherif.benhenda_at_gmail.com>
Date: Wed, 23 Sep 2015 12:33:54 +0100
Message-ID: <CANH7Qp-1qORYAzqMwDzcGz4Lvmu5eNnAYoNKKdaNW7+CLga9QA_at_mail.gmail.com>



Hi Steve,

Could you please send us the result for both tests using the display_cursor select * from table(dbms_xplan.display_cursor('*SQL_ID*',null,'advanced'));

2015-09-23 12:08 GMT+01:00 l.flatz_at_bluewin.ch <l.flatz_at_bluewin.ch>:

> Hi STeve,
>
> that would be explain plan. Please check out this:
> http://kerryosborne.oracle-guy.com/2008/10/explain-plan-lies/
> Jonathan is correct of course. The real plan would likely be differnt.
> In sqlplus please use dbms_xplan.display_cursor to retrieve the real plan
> from the shared pool.
>
> regards
>
> Lothar
>
> ----Ursprüngliche Nachricht----
> Von : sjb1970_at_gmail.com
> Datum : 23/09/2015 - 12:54 (UTC)
> An : jonathan_at_jlcomp.demon.co.uk
> Cc : oracle-l_at_freelists.org
> Betreff : Re: query performance following 12c upgrade
>
>
> Hi Jonathan,
>
> 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
>>
>
>
>
>

-- 
Cordialement,
Cherif Ben Henda

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Sep 23 2015 - 13:33:54 CEST

Original text of this message