Re: query performance following 12c upgrade

Thanks for getting back to me so quickly.

Yes when I set optimizer_features_enabled to 11.2.0.3 the query executes quickly as before.

I'll take a look at the article you linked.

I will post the query and plans later, I'll need to change table/column names etc as its a 3rd party application.

Thanks, Steve

On Wed, Sep 23, 2015 at 11:24 AM, Stefan Koehler <contact_at_soocs.de> wrote:

> Hi Steve,
> just for clarification.
>
> Does the query also run fast (fraction of a second) on 12.1.0.2 when you
> have set optimizer_features_enabled to 11.2.0.3? Can you please post the
> query itself and the full execution plans (Outline Data, Predicate
> Information + Column Projection Information)?
>
> Have you already traced both SQLs to see where it spends the time (CPU,
> I/O, etc.)? If this also does not reveal anything obvious, then please
> snapper
> (http://blog.tanelpoder.com/files/scripts/snapper.sql) both SQLs
> executions.
>
> By the way (if it is really caused by the "new" batched behavior) you can
> also disable it with the hidden parameter
> "_optimizer_batch_table_access_by_rowid", but batching also happened in
> 11g in some way. Timur has written a nice blog post about this - it is
> definitely worth to read:
> http://www.pythian.com/blog/batched-table-access/
>
> Best Regards
> Stefan Koehler
>
> Freelance Oracle performance consultant and researcher
> Homepage: http://www.soocs.de
> Twitter: _at_OracleSK
>
> > Steve Bradshaw <sjb1970_at_gmail.com> hat am 23. September 2015 um 12:08
> geschrieben:
> >
> > 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