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@xxxxxxxx> 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: @OracleSK
Steve Bradshaw <sjb1970@xxxxxxxxx> hat am 23. September 2015 um 12:08geschrieben:
execute since upgrading to 12.1.0.2 from 11.2.0.3.
Hi,
Looking for ideas as to why a query has started taking a lot longer to
Under 12c, the query is taking 12-13 seconds to return a row, whereas
Its a simple 1 table query, that is performing an index skip scan.
previously it was a fraction of a second.the table access is 'TABLE ACCESS BY INDEX ROWID BATCHED'.
There is a difference in the plans between the 2 versions. Under 12c,
------------------------------------------------------------------------------------------------------
From the 12c database:
Bytes | Cost (%CPU)| Time |
| Id | Operation | Name | Rows |
------------------------------------------------------------------------------------------------------
76 | 573 (80)| 00:00:01 |
| 0 | SELECT STATEMENT | | 2 |
76 | 573 (80)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TABLE1 | 2 |
| 572 (80)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | TABLE1_IDX_7 | 7 |
------------------------------------------------------------------------------------------------------
11.2.0.3 for the session):
From the 12c database (having set optimizer_features_enabled to
----------------------------------------------------------------------------------------------
Cost (%CPU)| Time |
| Id | Operation | Name | Rows | Bytes |
----------------------------------------------------------------------------------------------
573 (80)| 00:00:01 |
| 0 | SELECT STATEMENT | | 1 | 38 |
573 (80)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| TABLE1 | 1 | 38 |
572 (80)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | TABLE1_IDX_7 | 7 | |
----------------------------------------------------------------------------------------------
database level?
Any ideas how I can fix this without changing the parameter at the
Thanks in advance,
Steve
