Please help to debug strange execution plan
From: Michael Seiwert <mseiwert_at_hbv.de>
Date: Thu, 05 Jun 2008 11:53:30 +0200
Message-ID: <4847B79A.7060808@hbv.de>
Date: Thu, 05 Jun 2008 11:53:30 +0200
Message-ID: <4847B79A.7060808@hbv.de>
Hi List,
I have two identical queries which differ only in the search term of the contains clause. Both queries produce completely different execution plans and execution times.
If I run the the following query with searchterm 'spartacus' in the contains clause the query executes within 2 seconds, if I run the same query with searchterm 'tatort' the query returns in about 20 minutes. Please see the attached explain plans for details. Could you please help me to debug this as I want to understand why oracle behaves how it behaves :-) I'll could provide more information such as view definition, 10046, 10053 traces if needed.
SELECT s.ID, s.folgen_nummer, s.nettolaenge, s.genre_id, s.mandant_id,
von_jahr, t.titel AS titel, utit.titel AS untertitel FROM sendungen s JOIN v_bildsuche t ON s.ID = t.send_id AND t.tiar_id = 1
LEFT OUTER JOIN v_bildsuche utit
ON (s.ID = utit.send_id) AND utit.tiar_id = 2
LEFT OUTER JOIN v_bildsuche origtit
ON (s.ID = origtit.send_id) AND origtit.tiar_id = 5
WHERE (titel IS NOT NULL AND contains (titel, 'tatort') > 0);
Thank you very much in advance.
Very best regards
Michael
SQL> select plan_table_output from table(dbms_xplan.display('PLAN_TABLE',null,'ALL'));
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 214 | 24496 (1)|
| 1 | NESTED LOOPS | | 1 | 214 | 24496 (1)|
| 2 | NESTED LOOPS OUTER | | 1 | 170 | 24495 (1)|
| 3 | NESTED LOOPS | | 1 | 122 | 24494 (1)|
| 4 | NESTED LOOPS | | 1 | 96 | 24493 (1)|
| 5 | NESTED LOOPS | | 1 | 77 | 24492 (1)|
| 6 | TABLE ACCESS BY INDEX ROWID| TITELNAMEN | 41 | 2091 | 6 (0)|
|* 7 | DOMAIN INDEX | TITELNAMEN2_CTX | | | 8 (0)|
|* 8 | TABLE ACCESS BY INDEX ROWID| TITELINSTANZ | 1 | 26 | 598 (1)|
|* 9 | INDEX RANGE SCAN | TITELINSTANZ_TITELNAMEN_ID_I | 3530 | | 7 (15)|
| 10 | TABLE ACCESS BY INDEX ROWID | SENDUNGEN | 1 | 19 | 2 (50)|
|* 11 | INDEX UNIQUE SCAN | SENDUNGEN_PK | 1 | | |
| 12 | TABLE ACCESS BY INDEX ROWID | TITELINSTANZ | 1 | 26 | 2 (50)|
|* 13 | INDEX RANGE SCAN | TITELINSTANZ_KEY1_I | 1 | | 2 (50)|
| 14 | VIEW PUSHED PREDICATE | V_BILDSUCHE | 1 | 48 | |
| 15 | NESTED LOOPS | | 1 | 70 | 3 (0)|
| 16 | TABLE ACCESS BY INDEX ROWID | TITELINSTANZ | 1 | 26 | 2 (0)|
|* 17 | INDEX RANGE SCAN | TITELINSTANZ_KEY1_I | 1 | | 4 (25)|
| 18 | TABLE ACCESS BY INDEX ROWID | TITELNAMEN | 1 | 44 | 2 (50)|
|* 19 | INDEX UNIQUE SCAN | TITELNAMEN_PK | 1 | | |
| 20 | TABLE ACCESS BY INDEX ROWID | TITELNAMEN | 1 | 44 | 2 (50)|
|* 21 | INDEX UNIQUE SCAN | TITELNAMEN_PK | 1 | | |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access(("SYS_ALIAS_0001"."TITEL",'spartacus')>0)
8 - filter("SYS_ALIAS_0000"."REIHENFOLGE"=0 AND "SYS_ALIAS_0000"."TIAR_ID"=5 AND
TO_NUMBER("SYS_ALIAS_0000"."AKTIV")=1)
9 - access("SYS_ALIAS_0001"."ID"="SYS_ALIAS_0000"."TITELNAMEN_ID" AND "SYS_ALIAS_0000"."OBJBR_ID"=0)
filter("SYS_ALIAS_0000"."OBJBR_ID"=0 AND TO_NUMBER("SYS_ALIAS_0000"."VERWENDUNGSART")<>3)
11 - access("S"."ID"="SYS_ALIAS_0000"."SEND_ID")
13 - access("S"."ID"="Z"."SEND_ID" AND "Z"."TIAR_ID"=1 AND "Z"."OBJBR_ID"=0 AND "Z"."REIHENFOLGE"=0)
filter(TO_NUMBER("Z"."AKTIV")=1 AND TO_NUMBER("Z"."VERWENDUNGSART")<>3)
17 - access("S"."ID"="Z"."SEND_ID" AND "Z"."TIAR_ID"=2 AND "Z"."OBJBR_ID"=0 AND "Z"."REIHENFOLGE"=0)
filter(TO_NUMBER("Z"."AKTIV")=1 AND TO_NUMBER("Z"."VERWENDUNGSART")<>3)
19 - access("N"."ID"="Z"."TITELNAMEN_ID")
21 - access("N"."ID"="Z"."TITELNAMEN_ID")
43 rows selected.
SQL> spool off;
SQL> select plan_table_output from table(dbms_xplan.display('PLAN_TABLE',null,'ALL'));
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 214 | 169K (5)|
| 1 | NESTED LOOPS | | 1 | 214 | 169K (5)|
| 2 | NESTED LOOPS | | 1 | 170 | 169K (5)|
| 3 | NESTED LOOPS OUTER | | 1 | 119 | 169K (5)|
| 4 | NESTED LOOPS | | 1 | 71 | 169K (5)|
| 5 | NESTED LOOPS | | 1 | 45 | 169K (5)|
|* 6 | VIEW | index$_join$_014 | 1 | 26 | |
|* 7 | HASH JOIN | | 1 | 26 | |
|* 8 | INDEX FAST FULL SCAN | TITELINSTANZ_CK2 | 1 | 26 | 20576 (5)|
|* 9 | INDEX FAST FULL SCAN | TITELINSTANZ_KEY1_I | 1 | 26 | 20576 (5)|
| 10 | TABLE ACCESS BY INDEX ROWID| SENDUNGEN | 1 | 19 | 2 (50)|
|* 11 | INDEX UNIQUE SCAN | SENDUNGEN_PK | 1 | | |
| 12 | TABLE ACCESS BY INDEX ROWID | TITELINSTANZ | 1 | 26 | 2 (50)|
|* 13 | INDEX RANGE SCAN | TITELINSTANZ_KEY1_I | 1 | | 2 (50)|
| 14 | VIEW PUSHED PREDICATE | V_BILDSUCHE | 1 | 48 | |
| 15 | NESTED LOOPS | | 1 | 70 | 3 (0)|
| 16 | TABLE ACCESS BY INDEX ROWID| TITELINSTANZ | 1 | 26 | 2 (0)|
|* 17 | INDEX RANGE SCAN | TITELINSTANZ_KEY1_I | 1 | | 4 (25)|
| 18 | TABLE ACCESS BY INDEX ROWID| TITELNAMEN | 1 | 44 | 2 (50)|
|* 19 | INDEX UNIQUE SCAN | TITELNAMEN_PK | 1 | | |
|* 20 | TABLE ACCESS BY INDEX ROWID | TITELNAMEN | 1 | 51 | 2 (50)|
|* 21 | INDEX UNIQUE SCAN | TITELNAMEN_PK | 1 | | |
| 22 | TABLE ACCESS BY INDEX ROWID | TITELNAMEN | 1 | 44 | 2 (50)|
|* 23 | INDEX UNIQUE SCAN | TITELNAMEN_PK | 1 | | |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("SYS_ALIAS_0000"."REIHENFOLGE"=0 AND "SYS_ALIAS_0000"."TIAR_ID"=5 AND
TO_NUMBER("SYS_ALIAS_0000"."AKTIV")=1 AND "SYS_ALIAS_0000"."OBJBR_ID"=0 AND
TO_NUMBER("SYS_ALIAS_0000"."VERWENDUNGSART")<>3)
7 - access("indexjoin$_alias$_017".ROWID="indexjoin$_alias$_016".ROWID)
8 - filter(TO_NUMBER("indexjoin$_alias$_016"."VERWENDUNGSART")<>3 AND
TO_NUMBER("indexjoin$_alias$_016"."AKTIV")=1 AND "indexjoin$_alias$_016"."OBJBR_ID"=0 AND
"indexjoin$_alias$_016"."TIAR_ID"=5)
9 - filter(TO_NUMBER("indexjoin$_alias$_017"."VERWENDUNGSART")<>3 AND
TO_NUMBER("indexjoin$_alias$_017"."AKTIV")=1 AND "indexjoin$_alias$_017"."REIHENFOLGE"=0 AND
"indexjoin$_alias$_017"."OBJBR_ID"=0 AND "indexjoin$_alias$_017"."TIAR_ID"=5)
11 - access("S"."ID"="SYS_ALIAS_0000"."SEND_ID")
13 - access("S"."ID"="Z"."SEND_ID" AND "Z"."TIAR_ID"=1 AND "Z"."OBJBR_ID"=0 AND
"Z"."REIHENFOLGE"=0)
filter(TO_NUMBER("Z"."AKTIV")=1 AND TO_NUMBER("Z"."VERWENDUNGSART")<>3)
17 - access("S"."ID"="Z"."SEND_ID" AND "Z"."TIAR_ID"=2 AND "Z"."OBJBR_ID"=0 AND
"Z"."REIHENFOLGE"=0)
filter(TO_NUMBER("Z"."AKTIV")=1 AND TO_NUMBER("Z"."VERWENDUNGSART")<>3)
19 - access("N"."ID"="Z"."TITELNAMEN_ID")
20 - filter(("SYS_ALIAS_0001"."TITEL",'tatort')>0)
21 - access("SYS_ALIAS_0001"."ID"="SYS_ALIAS_0000"."TITELNAMEN_ID")
23 - access("N"."ID"="Z"."TITELNAMEN_ID")
54 rows selected.
SQL> spool off
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jun 05 2008 - 04:53:30 CDT