Re: query performance following 12c upgrade

Please see the attached. opti12 is the results when run with optimizer_featured_enable=12.1.0.2, and opti11 it is 11.2.0.3

I've had to anonymyse the data/columns/tables etc so the formatting may be a little out.

Thanks

Steve

On Wed, Sep 23, 2015 at 12:04 PM, Jonathan Lewis < jonathan_at_jlcomp.demon.co.uk> wrote:

>
> Stephen,
>
> They may be deceiving you.
>
> Since the queries take only a few seconds to run you can do the following:
>
> spool xxx
> set serveroutput off
> set linesize 180
> set trimspool on
> set pagesize 60
> alter session set statistics_level = all;
> {run the query}
> select * from table(dbms_xplan.display_cursor(null,null,'allstats last
> outline'));
> alter session set statistics_level = typcial;
> spool off
>
> This will pull the actual execution plan from memory.
> With the options I've given this will also include you some details about
> the work done (allstats last), and perhaps some clues about the strategy
> that the optimizer followed (outline).
>
>
>
>
> Regards
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
> _at_jloracle
> ------------------------------
> *From:* Steve Bradshaw [sjb1970_at_gmail.com]
> *Sent:* 23 September 2015 11:54
> *To:* Jonathan Lewis
> *Cc:* ORACLE-L
> *Subject:* 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
>>
>
>

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




text/plain attachment: opti11.txt



text/plain attachment: opti12.txt