Sometimes, the CBO does not take hints. Can anyone explain it?
SQL:
SELECT * FROM v_osi_child v1 WHERE mainuuid IN (SELECT uuid FROM ot_sys_index_org v2 WHERE mainuuid IN(:p1))
v_osi_child is a view which union-alls about 50 tables. ot_sys_index_org is here only used for getting some mainuuids.
The statement itself is free of sense :)
Explain plan:
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 17252 | 402K (1)| 01:20:31 |
|* 1 | HASH JOIN | | 4 | 17252 | 402K (1)| 01:20:31 |
|* 2 | INDEX RANGE SCAN | PK_OTSYSINDEX | 4 | 340 | 4 (0)| 00:00:01 |
| 3 | VIEW | V_OSI_CHILD | 56M| 223G| 402K (1)| 01:20:27 |
| 4 | UNION-ALL | | | | | |
| 5 | TABLE ACCESS FULL| FL_ALLG_BENUTZERDATEN | 16797 | 1295K| 105 (0)| 00:00:02 |
| 6 | TABLE ACCESS FULL| FL_BERATUNG | 17 | 1343 | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL| PA_KBIVBI | 16797 | 1295K| 105 (0)| 00:00:02 |
...
This statement needs more than 10 minutes.
Inserting a hint:
SELECT /*+ push_pred(v1) */ * FROM v_osi_child v1 WHERE mainuuid IN (SELECT uuid FROM ot_sys_index_org v2 WHERE mainuuid IN(:p1));
Explain plan:
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 17852 | 12747 (1)| 00:02:33 |
| 1 | NESTED LOOPS | | 4 | 17852 | 12747 (1)| 00:02:33 |
|* 2 | INDEX RANGE SCAN | PK_OTSYSINDEX | 4 | 532 | 4 (0)| 00:00:01 |
| 3 | VIEW | V_OSI_CHILD | 1 | 4330 | 3186 (1)| 00:00:39 |
| 4 | UNION ALL PUSHED PREDICATE | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| FL_ALLG_BENUTZERDATEN | 1 | 79 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | OSI_FLALLGBENUTZE_MAINUUID | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| FL_BERATUNG | 1 | 79 | 2 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | OSI_FLBERATUNG_MAINUUID | 1 | | 1 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID| PA_KBIVBI | 1 | 79 | 2 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | OSI_PAKBIVBI_MAINUUID | 1 | | 1 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID| FL_K_AKTIVITAET | 1 | 100 | 2 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | OSI_FLKAKTIVITAET_MAINUUID | 1 | | 1 (0)| 00:00:01 |
...
Well, using the index on mainuuids is realy a good idea, if the estimated resultset of the subquery are about 4 rows.
Costs are decreased from 402k to 12747.
Query runs in a second.
Now, lets modify the subquery a little bit. Again, the estimated result of the subquery are 4 rows:
SELECT * FROM v_osi_child v1 WHERE mainuuid IN (SELECT mainuuid FROM ot_sys_index_org v2 WHERE value IN(:p1));
Explain plan:
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 17104 | 402K (1)| 01:20:31 |
|* 1 | HASH JOIN RIGHT SEMI| | 4 | 17104 | 402K (1)| 01:20:31 |
|* 2 | INDEX RANGE SCAN | IX_OT_SYS_INDEX01 | 4 | 192 | 5 (0)| 00:00:01 |
| 3 | VIEW | V_OSI_CHILD | 56M| 223G| 402K (1)| 01:20:27 |
| 4 | UNION-ALL | | | | | |
| 5 | TABLE ACCESS FULL| FL_ALLG_BENUTZERDATEN | 16797 | 1295K| 105 (0)| 00:00:02 |
| 6 | TABLE ACCESS FULL| FL_BERATUNG | 17 | 1343 | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL| PA_KBIVBI | 16797 | 1295K| 105 (0)| 00:00:02 |
| 8 | TABLE ACCESS FULL| FL_K_AKTIVITAET | 26 | 2600 | 3 (0)| 00:00:01 |
...
Well, almost the same values...
Now, use the same hint:
SELECT /*+ push_pred(v1) */ * FROM v_osi_child v1 WHERE mainuuid IN (SELECT mainuuid FROM ot_sys_index_org v2 WHERE value IN(:p1));
Explain plan:
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 17104 | 402K (1)| 01:20:31 |
|* 1 | HASH JOIN RIGHT SEMI| | 4 | 17104 | 402K (1)| 01:20:31 |
|* 2 | INDEX RANGE SCAN | IX_OT_SYS_INDEX01 | 4 | 192 | 5 (0)| 00:00:01 |
| 3 | VIEW | V_OSI_CHILD | 56M| 223G| 402K (1)| 01:20:27 |
| 4 | UNION-ALL | | | | | |
| 5 | TABLE ACCESS FULL| FL_ALLG_BENUTZERDATEN | 16797 | 1295K| 105 (0)| 00:00:02 |
| 6 | TABLE ACCESS FULL| FL_BERATUNG | 17 | 1343 | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL| PA_KBIVBI | 16797 | 1295K| 105 (0)| 00:00:02 |
...
Nothing has changed.
I can't explain it. It seems to be an oracle-bug...
Release: 11.1.0.7.0 - 64bit Production
OS: RHEL 5.3