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