I am facing an interesting issue with a SQL. M_TRANSACTIONS is a large table (around 20M rows). The main restriction of the table in this SQL is TRX_REF. The preferred execution plan is to select from JOBS table, use a nested loop and 'VIEW PUSH PREDICATE' operation to feed the JOB into TXN subquery, so that index 'ML_TRANSACTION_B2' (index on TRANSACTIONS.TRX_REF only) is used to retrieve data from TRANSACTIONS.
The expected execution plan is used if 'ML_TRANSACTION_B2' BLEVEL=2 (Note ID 12 and 15 in the execution plan)
explain plan for
select count(*)
FROM
(SELECT T.ORG_ID,
T.ITEM_ID,
T.TRANSFER_TO,
T.TRX_REF
FROM TRANSACTIONS T,
TRANSACTION_DETAIL TD
WHERE T.ORG_ID = :B3
AND T.TRANSACTION_ACTION_ID = :B4
AND T.ITEM_ID = TD.ITEM_ID
AND T.ORG_ID = :B3
) TXN,
(SELECT J.JOB_NAME JOB,
JD.TRANSFER_FROM SUPPLY_INV,
JD.ITEM_ID
FROM JOBS J,
ITEM_INFO ITEM,
JOB_DETAILS JD
WHERE JD.ORG_ID = :B3
AND JD.JOB_ID = J.JOB_ID
AND ITEM.ORG_ID = :B3
AND ITEM.ITEM_ID = JD.ITEM_ID
AND J.ORG_ID = :B3
AND ITEM.ITEM_NUMBER = :B1
) JOB
WHERE TXN.ITEM_ID(+) = JOB.ITEM_ID
AND TXN.TRANSFER_TO(+) = JOB.SUPPLY_INV
AND TXN.TRX_REF(+) = JOB.JOB
SQL> select * from table(dbms_xplan.display);
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 162 | 539 (1)|
| 1 | SORT AGGREGATE | | 1 | 162 | |
| 2 | NESTED LOOPS OUTER | | 17 | 2754 | 539 (1)|
| 3 | VIEW | | 17 | 2414 | |
| 4 | NESTED LOOPS | | 17 | 4114 | 488 (1)|
| 5 | NESTED LOOPS | | 161 | 15134 | 326 (1)|
| 6 | TABLE ACCESS BY INDEX ROWID| ITEM_INFO | 161 | 7728 | 3 (34)|
|* 7 | INDEX RANGE SCAN | ITEM_INFO_B_N1 | 26 | | 2 (50)|
| 8 | TABLE ACCESS BY INDEX ROWID| JOB_DETAILS | 1 | 46 | 3 (34)|
|* 9 | INDEX RANGE SCAN | JOB_DETAILS_N1 | 1 | | 2 (50)|
|* 10 | TABLE ACCESS BY INDEX ROWID | JOBS | 1 | 148 | 2 (50)|
|* 11 | INDEX UNIQUE SCAN | JOBS_U1 | 1 | | |
|* 12 | VIEW PUSHED PREDICATE | | 1 | 20 | |
| 13 | NESTED LOOPS | | 1 | 38 | 11 (10)|
|* 14 | TABLE ACCESS BY INDEX ROWID | TRANSACTIONS | 1 | 25 | 10 (10)|
|* 15 | INDEX RANGE SCAN | ML_TRANSACTION_B2 | 21 | | 4 (25)|
|* 16 | INDEX RANGE SCAN | TRANSACTION_DETAIL_U1 | 1 | 13 | 2 (50)|
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("ITEM"."ORG_ID"=TO_NUMBER(:Z) AND "ITEM"."ITEM_NUMBER"=:Z)
9 - access("ITEM"."ITEM_ID"="JD"."ITEM_ID" AND "JD"."ORG_ID"=TO_NUMBER(:Z))
10 - filter("J"."ORG_ID"=TO_NUMBER(:Z))
11 - access("JD"."JOB_ID"="J"."JOB_ID")
12 - filter("TXN"."ITEM_ID"(+)="JOB"."ITEM_ID" AND "TXN"."TRANSFER_TO"(+)="JOB"."SUPPLY_INV")
14 - filter("T"."ORG_ID"=TO_NUMBER(:Z) AND "T"."TRANSACTION_ACTION_ID"=TO_NUMBER(:Z))
15 - access("T"."TRX_REF"="JOB"."JOB")
16 - access("T"."ITEM_ID"="TD"."ITEM_ID")
34 rows selected.
What's interesting is that if BLEVEL = 3, Oracle refuses to use 'VIEW PUSH PREDICATE' and 'ML_TRANSACTION_B2'. Instead, it used another index which filters only ORG_ID (other columns were not used in the criteria), which also has BLEVEL=3. By 'refuses', I mean Oracle doesn't consider the above plan -- even if I create a stored outline after setting BLEVEL=2 for 'ML_TRANSACTION_B2', Oracle does not use the above plan if later I set BLEVEL=3. (But besides doing an 'ALTER SESSION SET USE_STORED_OUTLINES=DEFAULT;', can I verify that stored outline is activated?)
-- Analyze related tables / indexes
SQL> ANALYZE TABLE TRANSACTIONS ESTIMATE STATISTICS SAMPLE 2 PERCENT;
Table analyzed.
SQL> ANALYZE index M_TRANSACTIONS_n1 ESTIMATE STATISTICS SAMPLE 2 PERCENT;
Index analyzed.
SQL> ANALYZE index ML_TRANSACTION_B2 ESTIMATE STATISTICS SAMPLE 2 PERCENT;
Index analyzed.
-- Set BLEVEL=3
SQL> BEGIN
2 DBMS_STATS.SET_INDEX_STATS (
3 ownname => 'TUNING',
4 indname => 'ML_TRANSACTION_B2',
5 indlevel=> 3
6 );
7 end;
8 /
PL/SQL procedure successfully completed.
-- Check Execution Plan
SQL> explain plan for
2 select count(*)
... SNIPPED
31 AND TXN.TRX_REF(+) = JOB.JOB;
Explained.
SQL> select * from table(dbms_xplan.display);
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 162 | 4034 (1)|
| 1 | SORT AGGREGATE | | 1 | 162 | |
|* 2 | HASH JOIN OUTER | | 17 | 2754 | 4034 (1)|
| 3 | VIEW | | 17 | 2414 | |
| 4 | NESTED LOOPS | | 17 | 4114 | 488 (1)|
| 5 | NESTED LOOPS | | 161 | 15134 | 326 (1)|
| 6 | TABLE ACCESS BY INDEX ROWID| ITEM_INFO | 161 | 7728 | 3 (34)|
|* 7 | INDEX RANGE SCAN | ITEM_INFO_B_N1 | 26 | | 2 (50)|
| 8 | TABLE ACCESS BY INDEX ROWID| JOB_DETAILS | 1 | 46 | 3 (34)|
|* 9 | INDEX RANGE SCAN | JOB_DETAILS_N1 | 1 | | 2 (50)|
|* 10 | TABLE ACCESS BY INDEX ROWID | JOBS | 1 | 148 | 2 (50)|
|* 11 | INDEX UNIQUE SCAN | JOBS_U1 | 1 | | |
| 12 | VIEW | | 364 | 7280 | |
|* 13 | TABLE ACCESS BY INDEX ROWID | TRANSACTIONS | 4 | 100 | 44 (3)|
| 14 | NESTED LOOPS | | 364 | 13832 | 3545 (1)|
| 15 | TABLE ACCESS FULL | TRANSACTION_DETAIL | 82 | 1066 | 4 (25)|
|* 16 | INDEX RANGE SCAN | M_TRANSACTIONS_N1 | 58 | | 3 (34)|
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TXN"."ITEM_ID"(+)="JOB"."ITEM_ID" AND
"TXN"."TRANSFER_TO"(+)="JOB"."SUPPLY_INV" AND "TXN"."TRX_REF"(+)="JOB"."JOB")
7 - access("ITEM"."ORG_ID"=TO_NUMBER(:Z) AND "ITEM"."ITEM_NUMBER"=:Z)
9 - access("ITEM"."ITEM_ID"="JD"."ITEM_ID" AND "JD"."ORG_ID"=TO_NUMBER(:Z))
10 - filter("J"."ORG_ID"=TO_NUMBER(:Z))
11 - access("JD"."JOB_ID"="J"."JOB_ID")
13 - filter("T"."TRANSACTION_ACTION_ID"=TO_NUMBER(:Z))
16 - access("T"."ITEM_ID"="TD"."ITEM_ID" AND "T"."ORG_ID"=TO_NUMBER(:Z))
34 rows selected.
-- Set BLEVEL=2 again
SQL> BEGIN
2 DBMS_STATS.SET_INDEX_STATS (
3 ownname => 'TUNING',
4 indname => 'ML_TRANSACTION_B2',
5 indlevel=> 2
6 );
7 end;
8 /
PL/SQL procedure successfully completed.
-- Check Execution Plan
SQL> explain plan for
2 select count(*)
... SNIPPED
31 AND TXN.TRX_REF(+) = JOB.JOB;
Explained.
SQL> select * from table(dbms_xplan.display);
-- Got the the first plan above, i.e., ML_TRANSACTION_B2 used
Oracle: 9.2.0.6
OS: Redhat Enterprise Linux 3