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
