Re: query performance following 12c upgrade

From: <l.flatz_at_bluewin.ch>
Date: Wed, 23 Sep 2015 10:26:42 +0000
Message-ID: <28988981.40567.1443004001987.JavaMail.webmail_at_bluewin.ch>

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

Received on Wed Sep 23 2015 - 12:26:42 CEST