Query Using Full Table Scan Over Index Scan After Upgrade to 12c
(Doc ID 2362906.1)
Last updated on NOVEMBER 06, 2020
Applies to:
Oracle Database - Enterprise Edition - Version 12.1.0.2 and laterOracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
- Could also receive ORA-1489 or ORA-1427 while executing some queries after upgrade to 12c.
- After upgrade from 11g to 12c, query that used to use index scan is now using full table scan even with stats up to date:
From OFE 11.2.0.3:
Plan hash value: 3362019596
--------------------------------------------------------------------------------
-------------------PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A
-Time | Buffers |--------------------------------------------------------------------------------
-------------------| 0 | SELECT STATEMENT | | 1 | | 1 |00:0
0:00.04 | 432 || 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:0
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
0:00.04 | 432 || 2 | NESTED LOOPS OUTER | | 1 | 30624 | 17500 |00:0
0:00.05 | 432 || 3 | NESTED LOOPS OUTER | | 1 | 17500 | 17500 |00:0
0:00.02 | 151 || 4 | INDEX FAST FULL SCAN |Test| 1 | 17500 | 17500 |00:0
0:00.01 | 53 |PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 5 | INDEX UNIQUE SCAN |Test_1| 17500 | 1 | 4375 |00:0
0:00.01 | 98 || 6 | VIEW PUSHED PREDICATE | | 17500 | 1 | 13125 |00:0
0:00.02 | 281 ||* 7 | INDEX UNIQUE SCAN | Test_2 | 17500 | 1 | 13125 |00:0
0:00.01 | 281 |--------------------------------------------------------------------------------
-------------------From OFE 12.1.0.2:
Plan hash value: 1381235393
--------------------------------------------------------------------------------
--------------------------------------------------------------------PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows |
A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |--------------------------------------------------------------------------------
--------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | |
1 |00:01:07.07 | 682K| 59 | | | || 1 | SORT AGGREGATE | | 1 | 1 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 |00:01:07.07 | 682K| 59 | | | || 2 | NESTED LOOPS OUTER | | 1 | 17500 |
17500 |00:00:16.52 | 682K| 59 | | | || 3 | NESTED LOOPS OUTER | | 1 | 17500 |
17500 |00:00:00.15 | 151 | 59 | | | || 4 | INDEX FAST FULL SCAN | Test | 1 | 17500 |
17500 |00:00:00.06 | 53 | 47 | | | |PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 5 | INDEX UNIQUE SCAN | Test_1| 17500 | 1 |
4375 |00:00:00.08 | 98 | 12 | | | || 6 | VIEW PUSHED PREDICATE | | 17500 | 1 |
13125 |00:01:06.87 | 682K| 0 | | | || 7 | NESTED LOOPS OUTER | | 17500 | 1 |
13125 |00:01:06.85 | 682K| 0 | | | ||* 8 | INDEX UNIQUE SCAN | Test_2 | 17500 | 1 |
13125 |00:00:00.02 | 281 | 0 | | | |PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------|* 9 | VIEW | VW_SSQ_1 | 13125 | 1 |
13125 |00:01:06.80 | 682K| 0 | | | || 10 | SORT GROUP BY | | 13125 | 9281 |
172M|00:01:15.09 | 682K| 0 | 974K| 533K| 865K (0)||* 11 | TABLE ACCESS FULL | RT__PROCESS| 13125 | 13125 |
172M|00:00:06.21 | 682K| 0 | | | |--------------------------------------------------------------------------------
Changes
Change was upgrade from 11g to 12c.
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Changes |
| Cause |
| Solution |
| References |