Sample Header Ad - 728x90

Performance issue in 12c - Additional Full table scan after index range scan

1 vote
0 answers
970 views
I am trying to improve the performance of a procedure which we recently moved from 11g to 12c. I am simply using a refcursor which gets the result set from an underlying query. In 12c I am having a significant performance hit. The query running time in 11g was less than 3 seconds while in 12c it is taking more than 2 minutes. In 12c, the stats are not gathered for the last 1 month(testing environment). When I compared the execution plan, the only difference I could see was that there is a full table scan following an index range scan happening for a table. This full table scan is not present in 11g. Following is the plan snippet where this is happening. Thanks in advance for any help. 18 STATISTICS COLLECTOR 17 VIEW VIEW SYS.VW_1 Cost: 137 Bytes: 81 Cardinality: 9 16 HASH UNIQUE Cost: 137 Bytes: 432 Cardinality: 9 15 FILTER 14 CONNECT BY WITH FILTERING (UNIQUE) 6 TABLE ACCESS BY INDEX ROWID TABLE TAB1 Cost: 2 Bytes: 27 Cardinality: 1 5 INDEX UNIQUE SCAN INDEX (UNIQUE) TAB1_PK Cost: 1 Cardinality: 1 13 HASH JOIN Cost: 5 Bytes: 312 Cardinality: 8 11 NESTED LOOPS Cost: 5 Bytes: 312 Cardinality: 8 8 STATISTICS COLLECTOR 7 CONNECT BY PUMP 10 TABLE ACCESS BY INDEX ROWID BATCHED TABLE TAB1 Cost: 3 Bytes: 216 Cardinality: 8 9 INDEX RANGE SCAN INDEX TAB1_IE2 Cost: 1 Cardinality: 8 12 TABLE ACCESS FULL TABLE TAB1 Cost: 3 Bytes: 216 Cardinality: 8
Asked by Abegin (11 rep)
Oct 6, 2016, 03:30 PM
Last activity: Oct 6, 2016, 04:01 PM