Hi Lothar,
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@xxxxxxxxxx <l.flatz@xxxxxxxxxx>
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@xxxxxxxxx
Datum : 23/09/2015 - 12:08 (UTC)
An : oracle-l@xxxxxxxxxxxxx
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
