Ineffecient Index Selection For Query With ORDER BY Clause Caused By Setting FIRST_ROWS in 12.2
(Doc ID 2442546.1)
Last updated on JULY 20, 2024
Applies to:
Oracle Database - Enterprise Edition - Version 12.2.0.1 and laterInformation in this document applies to any platform.
Symptoms
- After upgrade from 9.2 to 12.2, the SQL plan of a user query with ORDER BY clause is changed and an inefficient index is selected that causes very high physical reads and consistent gets and very slow response time.
In 12.2 with ORDER BY clause, index full scan is used:
NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.
SQL> select col4,col5 from tbl1_tbl2_join_view
where col4='16316363' ORDER BY col1,col2,col3;
col4 col5
-------- -------------
16316363 9999999999999
16316363 9999999777777
16316363 4549454945490
16316363 2099209920995
16316363 2000000000000
Elapsed: 00:00:55.81 *<<<<<---------
SQL Plan
----------------------------------------------------------
Plan hash value: 966240503
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |Bytes | Cost |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 161K|
| 1 | NESTED LOOPS | | 1 | 65 | 161K|
|* 2 | TABLE ACCESS BY INDEX ROWID| TBL1 | 1 | 45 | 161K|
| 3 | INDEX FULL SCAN | TBL1_PK | 715K| | 4080 |*<<<<--------
|* 4 | INDEX UNIQUE SCAN | TBL2_PK | 1 | 20 | 1 |
--------------------------------------------------------------------------------------
... ...
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
219276 consistent gets *<<<<--------
118571 physical reads *<<<<--------
0 redo size
774 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed
where col4='16316363' ORDER BY col1,col2,col3;
col4 col5
-------- -------------
16316363 9999999999999
16316363 9999999777777
16316363 4549454945490
16316363 2099209920995
16316363 2000000000000
Elapsed: 00:00:55.81 *<<<<<---------
SQL Plan
----------------------------------------------------------
Plan hash value: 966240503
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |Bytes | Cost |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 161K|
| 1 | NESTED LOOPS | | 1 | 65 | 161K|
|* 2 | TABLE ACCESS BY INDEX ROWID| TBL1 | 1 | 45 | 161K|
| 3 | INDEX FULL SCAN | TBL1_PK | 715K| | 4080 |*<<<<--------
|* 4 | INDEX UNIQUE SCAN | TBL2_PK | 1 | 20 | 1 |
--------------------------------------------------------------------------------------
... ...
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
219276 consistent gets *<<<<--------
118571 physical reads *<<<<--------
0 redo size
774 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed
In 12.2, without ORDER BY clause:
SQL> select col4,col5 from tbl1_tbl2_join_view
where col4='16316363';
col4 col5
-------- -------------
16316363 9999999999999
16316363 9999999777777
16316363 4549454945490
16316363 2099209920995
16316363 2000000000000
Elapsed: 00:00:00.00 *<<<<<---------
SQL Plan
----------------------------------------------------------
Plan hash value: 3099082192
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |Bytes | Cost |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 5 |
| 1 | NESTED LOOPS | | 1 | 65 | 5 |
|* 2 | TABLE ACCESS BY INDEX ROWID| TBL1 | 1 | 45 | 4 |
|* 3 | INDEX RANGE SCAN | TBL1_IE01 | 3 | | 3 |
|* 4 | INDEX UNIQUE SCAN | TBL2_PK | 1 | 20 | 1 |
--------------------------------------------------------------------------------------
... ...
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
23 consistent gets
0 physical reads
0 redo size
774 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed
where col4='16316363';
col4 col5
-------- -------------
16316363 9999999999999
16316363 9999999777777
16316363 4549454945490
16316363 2099209920995
16316363 2000000000000
Elapsed: 00:00:00.00 *<<<<<---------
SQL Plan
----------------------------------------------------------
Plan hash value: 3099082192
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |Bytes | Cost |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 5 |
| 1 | NESTED LOOPS | | 1 | 65 | 5 |
|* 2 | TABLE ACCESS BY INDEX ROWID| TBL1 | 1 | 45 | 4 |
|* 3 | INDEX RANGE SCAN | TBL1_IE01 | 3 | | 3 |
|* 4 | INDEX UNIQUE SCAN | TBL2_PK | 1 | 20 | 1 |
--------------------------------------------------------------------------------------
... ...
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
23 consistent gets
0 physical reads
0 redo size
774 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed
In 9.2, both with and without ORDER BY clause, index range scan is used:
col4 col5
-------- -------------
16316363 9999999999999
16316363 9999999777777
16316363 4549454945490
16316363 2099209920995
16316363 2000000000000
Elapsed: 00:00:00.48
SQL Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 NESTED LOOPS
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'TBL1'
4 3 INDEX (RANGE SCAN) OF 'TBL1_IE01' (NON-UNIQUE)
5 2 INDEX (UNIQUE SCAN) OF 'TBL2_PK' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
22 consistent gets
0 physical reads
0 redo size
709 bytes sent via SQL*Net to client
656 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
5 rows processed
-------- -------------
16316363 9999999999999
16316363 9999999777777
16316363 4549454945490
16316363 2099209920995
16316363 2000000000000
Elapsed: 00:00:00.48
SQL Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (ORDER BY)
2 1 NESTED LOOPS
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'TBL1'
4 3 INDEX (RANGE SCAN) OF 'TBL1_IE01' (NON-UNIQUE)
5 2 INDEX (UNIQUE SCAN) OF 'TBL2_PK' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
22 consistent gets
0 physical reads
0 redo size
709 bytes sent via SQL*Net to client
656 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
5 rows processed
- Index TBL1_IE01 is defined on COL4 of TBL1 which is the literal condition column of WHERE clause.
- Index TBL1_PK is defined on COL1,COL2,COL3, which are the columns of ORDER BY clause.
Changes
Upgrade from 9.2 to 12.2 and set OPTIMIZER_MODE=FIRST_ROWS as same as 9.2.
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 |