RE: query performance following 12c upgrade

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 23 Sep 2015 10:50:34 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D9282A5226_at_EXMBX01.thus.corp>

If that's the case then you certainly don't want to add an index - you need to find out why it's not being used efficiently.

If the body of the plan really is unchanged between settings then you need to check the predicate section very carefully. Assuming you haven't set the optimizer_index_caching and optimizer_index_cost_adj parameters to non-defaulta values, a reasonable approximation of the optimizer's "thinking" is that expects to probe the index roughly 572 times but find only one rowid that requires it to go to the table.

The only way that the plan (body) could stay the same but take very much longer is if

1. there is a predicate in the 11g plan that gets checked in the index but 12c postpones to checking at the table - without the optimizer realising that that's going to happen
2. there is a predicate in the 11g plan that allows each probe of the index to be very narrow, but in 12c the probe turns into a wide probe (access predicate) with an expensive filter to discard lots of rowids.

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:31
To: Stefan Koehler
Cc: ORACLE-L
Subject: Re: query performance following 12c upgrade

Hi,

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<mailto: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<mailto: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

Received on Wed Sep 23 2015 - 12:50:34 CEST