Why does the optimizer have problem with OR clause?
From: Marián Bednár <marian.bednar_at_gmail.com>
Date: Wed, 11 Apr 2018 14:50:28 +0200
Message-ID: <CAM2K34GkUuHs5PzAjzMre-cyMaRG5Wjqh7PZhAeaNA42vqYMOg_at_mail.gmail.com>
Hi List,
merged query 767216 consistent gets
Date: Wed, 11 Apr 2018 14:50:28 +0200
Message-ID: <CAM2K34GkUuHs5PzAjzMre-cyMaRG5Wjqh7PZhAeaNA42vqYMOg_at_mail.gmail.com>
Hi List,
12.1.0.2 with Oct-2017 Bundle Patch
Why does the optimizer have problem with OR clause if two simple queries are merged into one using OR clause?
1st query 17 consistent gets 2nd query 50 consistent gets
merged query 767216 consistent gets
Why does merged query use full table scan? Statistics are current.
---
--- 1st query
---
select * from exchange_reason
where transaction_version_id in
(select id from transaction_version where TRANSACTION_ID = 237704 );
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 333476315
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name
| Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | 34 | 7 (0)| 00:00:01 | | |
| 1 | NESTED LOOPS |
| 1 | 34 | 7 (0)| 00:00:01 | | |
| 2 | NESTED LOOPS |
| 1 | 34 | 7 (0)| 00:00:01 | | |
| 3 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| TRANSACTION_VERSION
| 1 | 10 | 4 (0)| 00:00:01 | ROWID | ROWID |
|* 4 | INDEX RANGE SCAN |
TRANSACTIONVERSIONUK | 1 | | 3 (0)| 00:00:01 | |
|
|* 5 | INDEX RANGE SCAN |
FI_EXC_REASON_TRAN_VER | 1 | | 2 (0)| 00:00:01 | |
|
| 6 | TABLE ACCESS BY GLOBAL INDEX ROWID | EXCHANGE_REASON
| 1 | 24 | 3 (0)| 00:00:01 | ROWID | ROWID |
---------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("TRANSACTION_ID"=237704)
5 - access("TRANSACTION_VERSION_ID"="ID")
Note
-----
- this is an adaptive plan
Statistics
----------------------------------------------------------
18 recursive calls
31 db block gets
17 consistent gets
0 physical reads
1296 redo size
522 bytes sent via SQL*Net to client
489 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
---
--- 2nd query
---
select * from exchange_reason
where transaction_in_export_id in
(select tie.id from transaction_in_export tie, transaction_version tv
where tie.transaction_version_id = tv.id and tv.TRANSACTION_ID =
237704 );
ID REASON TRANSACTION_IN_EXPORT_ID
TRANSACTION_VERSION_ID ACTUAL_TR
---------- -------------------------------- ------------------------
---------------------- ---------
234811 CAFI 24106
14-MAR-18
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3725984717
-------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name
| Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | 37 | 18 (6)| 00:00:01 | | |
| 1 | NESTED LOOPS |
| 1 | 37 | 18 (6)| 00:00:01 | | |
| 2 | NESTED LOOPS |
| 10 | 37 | 18 (6)| 00:00:01 | | |
| 3 | VIEW | VW_NSO_1
| 1 | 13 | 6 (0)| 00:00:01 | | |
| 4 | HASH UNIQUE |
| 1 | 20 | | | | |
| 5 | NESTED LOOPS |
| 1 | 20 | 6 (0)| 00:00:01 | | |
| 6 | NESTED LOOPS |
| 1 | 20 | 6 (0)| 00:00:01 | | |
| 7 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED|
TRANSACTION_VERSION | 1 | 10 | 4 (0)| 00:00:01 |
ROWID | ROWID |
|* 8 | INDEX RANGE SCAN |
TRANSACTIONVERSIONUK | 1 | | 3 (0)| 00:00:01 |
| |
|* 9 | INDEX RANGE SCAN |
FI_TRAN_IN_EXP_TRAN_VRER | 1 | | 1 (0)| 00:00:01 |
| |
| 10 | TABLE ACCESS BY GLOBAL INDEX ROWID |
TRANSACTION_IN_EXPORT | 1 | 10 | 2 (0)| 00:00:01 |
ROWID | ROWID |
|* 11 | INDEX RANGE SCAN |
FI_EXC_REASON_TRAN_IN_EXPORT | 10 | | 1 (0)| 00:00:01 |
| |
| 12 | TABLE ACCESS BY GLOBAL INDEX ROWID | EXCHANGE_REASON
| 1 | 24 | 11 (0)| 00:00:01 | ROWID | ROWID |
-------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("TV"."TRANSACTION_ID"=237704)
9 - access("TIE"."TRANSACTION_VERSION_ID"="TV"."ID")
11 - access("TRANSACTION_IN_EXPORT_ID"="ID")
Note
-----
- this is an adaptive plan
Statistics
----------------------------------------------------------
117 recursive calls
29 db block gets
50 consistent gets
0 physical reads
1416 redo size
642 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
---
--- merged query
---
select * from exchange_reason
where transaction_version_id in
(select id from transaction_version where TRANSACTION_ID = 237704
)
OR
transaction_in_export_id in
(select tie.id from transaction_in_export tie,
transaction_version tv
where tie.transaction_version_id = tv.id and tv.TRANSACTION_ID
= 237704 );
ID REASON TRANSACTION_IN_EXPORT_ID
TRANSACTION_VERSION_ID ACTUAL_TR
---------- -------------------------------- ------------------------
---------------------- ---------
234811 CAFI 24106
14-MAR-18
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4217242978
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name |
Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
1 | 24 | 866K (1)| 00:00:34 | | |
|* 1 | FILTER | |
| | | | | |
| 2 | PARTITION RANGE ALL | |
299K| 7015K| 610 (2)| 00:00:01 | 1 |1048575|
| 3 | TABLE ACCESS FULL | EXCHANGE_REASON |
299K| 7015K| 610 (2)| 00:00:01 | 1 |1048575|
|* 4 | TABLE ACCESS BY GLOBAL INDEX ROWID | TRANSACTION_VERSION |
1 | 10 | 3 (0)| 00:00:01 | ROWID | ROWID |
|* 5 | INDEX UNIQUE SCAN | PK_TRANSACTION_VERSION |
1 | | 2 (0)| 00:00:01 | | |
| 6 | NESTED LOOPS | |
1 | 20 | 4 (0)| 00:00:01 | | |
| 7 | TABLE ACCESS BY GLOBAL INDEX ROWID| TRANSACTION_IN_EXPORT |
1 | 10 | 2 (0)| 00:00:01 | ROWID | ROWID |
|* 8 | INDEX UNIQUE SCAN | PK_TRANSACTION_IN_EXPORT |
1 | | 1 (0)| 00:00:01 | | |
|* 9 | TABLE ACCESS BY GLOBAL INDEX ROWID| TRANSACTION_VERSION |
1 | 10 | 2 (0)| 00:00:01 | ROWID | ROWID |
|* 10 | INDEX UNIQUE SCAN | PK_TRANSACTION_VERSION |
1 | | 1 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT 0 FROM "TRANSACTION_VERSION"
"TRANSACTION_VERSION" WHERE "ID"=:B1 AND
"TRANSACTION_ID"=237704) OR EXISTS (SELECT 0 FROM
"TRANSACTION_VERSION"
"TV","TRANSACTION_IN_EXPORT" "TIE" WHERE "TIE"."ID"=:B2 AND
"TIE"."TRANSACTION_VERSION_ID"="TV"."ID" AND
"TV"."TRANSACTION_ID"=237704))
4 - filter("TRANSACTION_ID"=237704)
5 - access("ID"=:B1)
8 - access("TIE"."ID"=:B1)
9 - filter("TV"."TRANSACTION_ID"=237704)
10 - access("TIE"."TRANSACTION_VERSION_ID"="TV"."ID")
Statistics
----------------------------------------------------------
27 recursive calls
35 db block gets
767216 consistent gets
0 physical reads
5252 redo size
642 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Thanks.
Marian
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Apr 11 2018 - 14:50:28 CEST