Please help to debug strange execution plan
- From: Michael Seiwert <mseiwert@xxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 05 Jun 2008 11:53:30 +0200
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
Other related posts:
