Re: query performance following 12c upgrade

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