Re: query performance following 12c upgrade

Thanks for the response.

I did suggest adding an index to better match the query but its been met with resistance so far.

Steve

On Wed, Sep 23, 2015 at 11:26 AM, l.flatz_at_bluewin.ch <l.flatz_at_bluewin.ch> wrote:

> Hi Steve,
>
> The access by rowid batched is actually a optimization where not just one
> row is retrieved by rowidd but a number of them in one go. It would be
> strange (however not impossible) if that hurts you,
> By looking at the plan I think that the index access looks bad anyway.
> Some points:
> 1.) an Index skip scan is always suspicious. I rarely find efficient ones.
> Thus, you should have an index that starts withe the search criteria of
> line 2
> 2.) On the table access in operation 2 the estimate drops from 7 to 2 (or
> 1 respectively in 11G) . That means that there is a filter criteria applied
> that is missing in TABLE1_IDX_7. If it were there we would have a better
> access.
> 3.) Even considering above deficiancies the runtime of the query would be
> fine if the estimates were correct. Thus, it is likely that the estimates
> are wrong.
> Bottom line: create an index better suited for the query. If you want
> further insight you should share at least the selection criteria or much
> better runtime statistcis. (dbms_xplan.display_cursor( .., ..,
> 'RUNSTATS_LAST')
>
> regards
>
> Lothar
>
> ----Ursprüngliche Nachricht----
> Von : sjb1970_at_gmail.com
> Datum : 23/09/2015 - 12:08 (UTC)
> An : oracle-l_at_freelists.org
> Betreff : 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