Wrong CBO Selectivity On The CONTAINS Predicate (Doc ID 2197741.1)

Last updated on OCTOBER 31, 2016

Applies to:

Oracle Text - Version 11.2.0.3 and later
Information in this document applies to any platform.

Symptoms

On : 11.2.0.3 version, Creating/Maintaining context indexes

ACTUAL BEHAVIOR
---------------
Following query gives wrong CBO decision

SQL_ID fkkx16mqmj8c1, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ COL1, COL2, COL3, COL4 FROM
(SELECT DISTINCT COL1, COL2, COL3, COL4 FROM (SELECT DISTINCT
CON205.ROOTID COL1, CON205.ROOTID COL2, WOR206.SMLOS0_DEFAULTSTRINGT
COL3, 0 COL4 FROM WORKSPACETAB WOR206, CONTRACTWORKSPACETAB CON205,
PROJECTADDINTAB PRO207, ATTRSBLOBTAB ATT208, BASEIDTAB BAS210,
PROCUREMENTUNITTAB PRO211, COMMODITYCODETAB COM209, SAAPPROVERTAB
SAA202, COMMODITYCODETAB COM201, BASEIDTAB BAS203, US_USERTAB US_204,
PROCUREMENTUNITTAB PRO212, APPROVERTYPETAB APP213 WHERE WOR206.ROOTID =
CON205.ROOTID AND WOR206.WS_PROJECTADDIN = PRO207.ROOTID AND
WOR206.WS_ATTRSBLOB = ATT208.ROOTID AND WOR206.WS_SAHACATS =
BAS210.LVID AND WOR206.ROOTID = BAS210.ROOTID AND WOR206.WS_SACDD =
PRO211.ROOTID AND COM201.ROOTID(+) = SAA202.SA_SAHACAT AND
SAA202.SA_SAAPPROVERS = BAS203.LVID AND SAA202.ROOTID = BAS203.ROOTID
AND BAS203.VAL = US_204.ROOTID AND SAA202.SA_SAPROCUREMENTUNIT =
PRO212.ROOTID AND SAA202.SA_SATYPE = APP213.ROOTID AND (BAS203.VAL LIKE
:B23 ) A
Plan hash value: 2011900461
------------------------------------------------------------------------------
------------------------------------------------------------------------------
----------------------------
| Id | Operation | Name
| Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem |
 1Mem | Used-Mem | Used-Tmp|
------------------------------------------------------------------------------
------------------------------------------------------------------------------
----------------------------
| 0 | SELECT STATEMENT |
| 1 | | 1 |00:03:27.06 | 34M| 552K| 23030 | |
  | | |
|* 1 | COUNT STOPKEY |
| 1 | | 1 |00:03:27.06 | 34M| 552K| 23030 | |
  | | |
| 2 | VIEW |
| 1 | 1 | 1 |00:03:27.06 | 34M| 552K| 23030 | |
  | | |
|* 3 | SORT GROUP BY STOPKEY |
| 1 | 1 | 1 |00:03:27.06 | 34M| 552K| 23030 | 2048 |
 2048 | 2048 (0)| |
|* 4 | FILTER |
| 1 | | 1 |00:03:27.06 | 34M| 552K| 23030 | |
  | | |
|* 5 | FILTER |
| 1 | | 336 |00:30:16.92 | 34M| 552K| 23030 | |
  | | |
| 6 | NESTED LOOPS |
| 1 | 1 | 336 |00:30:16.92 | 34M| 552K| 23030 | |
  | | |
| 7 | NESTED LOOPS |
| 1 | 1 | 194K|00:02:43.88 | 33M| 543K| 23030 | |
  | | |
| 8 | NESTED LOOPS |
| 1 | 1 | 194K|00:02:42.58 | 32M| 542K| 23030 | |
  | | |
| 9 | NESTED LOOPS |
| 1 | 1 | 194K|00:02:41.59 | 32M| 542K| 23030 | |
  | | |
| 10 | NESTED LOOPS |
| 1 | 1 | 658K|00:04:53.46 | 31M| 540K| 23030 | |
  | | |
| 11 | NESTED LOOPS |
| 1 | 1 | 1960K|00:02:31.61 | 28M| 536K| 23030 | |
  | | |
| 12 | MERGE JOIN CARTESIAN |
| 1 | 177 | 106M|00:00:51.01 | 52738 | 536K| 23030 | |
  | | |
| 13 | NESTED LOOPS |
| 1 | 1 | 21 |00:00:00.07 | 26503 | 26230 | 0 | |
  | | |
| 14 | NESTED LOOPS |
| 1 | 1 | 21 |00:00:00.07 | 26482 | 26230 | 0 | |
  | | |
|* 15 | FILTER |
| 1 | | 21 |00:00:00.07 | 26476 | 26230 | 0 | |
  | | |
| 16 | NESTED LOOPS OUTER |
| 1 | 1 | 21 |00:00:00.04 | 26476 | 26230 | 0 | |
  | | |
| 17 | NESTED LOOPS |
| 1 | 1 | 21 |00:00:00.07 | 26445 | 26230 | 0 | |
  | | |
| 18 | NESTED LOOPS |
| 1 | 1 | 76 |00:00:00.17 | 26342 | 26230 | 0 | |
  | | |
| 19 | NESTED LOOPS |
| 1 | 1 | 79 |00:00:00.55 | 26242 | 26230 | 0 | |
  | | |
|* 20 | TABLE ACCESS BY INDEX ROWID| US_USERTAB
| 1 | 1 | 1 |00:00:00.01 | 3 | 0 | 0 | |
  | | |
|* 21 | INDEX UNIQUE SCAN | ARIBAPK319
| 1 | 1 | 1 |00:00:00.01 | 2 | 0 | 0 | |
  | | |
|* 22 | TABLE ACCESS FULL | BASEIDTAB
| 1 | 1 | 79 |00:00:00.55 | 26239 | 26230 | 0 | |
  | | |
|* 23 | TABLE ACCESS BY INDEX ROWID | SAAPPROVERTAB
| 79 | 1 | 76 |00:00:00.01 | 100 | 0 | 0 | |
  | | |
|* 24 | INDEX UNIQUE SCAN | ARIBAPK369
| 79 | 1 | 79 |00:00:00.01 | 16 | 0 | 0 | |
  | | |
|* 25 | TABLE ACCESS BY INDEX ROWID | PROCUREMENTUNITTAB
| 76 | 1 | 21 |00:00:00.01 | 103 | 0 | 0 | |
  | | |
|* 26 | INDEX UNIQUE SCAN | ARIBAPK214
| 76 | 1 | 76 |00:00:00.01 | 27 | 0 | 0 | |
  | | |
| 27 | TABLE ACCESS BY INDEX ROWID | COMMODITYCODETAB
| 21 | 1 | 21 |00:00:00.01 | 31 | 0 | 0 | |
  | | |
|* 28 | INDEX UNIQUE SCAN | ARIBAPK299
| 21 | 1 | 21 |00:00:00.01 | 10 | 0 | 0 | |
  | | |
|* 29 | INDEX UNIQUE SCAN | ARIBAPK365
| 21 | 1 | 21 |00:00:00.01 | 6 | 0 | 0 | |
  | | |
|* 30 | TABLE ACCESS BY INDEX ROWID | APPROVERTYPETAB
| 21 | 1 | 21 |00:00:00.01 | 21 | 0 | 0 | |
  | | |
| 31 | BUFFER SORT |
| 21 | 4878K| 106M|00:00:36.10 | 26235 | 509K| 23030 | 202M|
 4766K| 9M (0)| 180K|
| 32 | TABLE ACCESS FULL | BASEIDTAB
| 1 | 4878K| 5092K|00:00:00.36 | 26233 | 26230 | 0 | |
  | | |
|* 33 | TABLE ACCESS BY INDEX ROWID | COMMODITYCODETAB
| 106M| 1 | 1960K|00:01:58.03 | 28M| 72 | 0 | |
  | | |
|* 34 | INDEX UNIQUE SCAN | ARIBAPK299
| 106M| 1 | 1960K|00:01:16.08 | 26M| 54 | 0 | |
  | | |
|* 35 | TABLE ACCESS BY INDEX ROWID | WORKSPACETAB
| 1960K| 1 | 658K|00:00:07.18 | 3143K| 3880 | 0 | |
  | | |
|* 36 | INDEX UNIQUE SCAN | ARIBAPK476
| 1960K| 1 | 1698K|00:00:02.84 | 1445K| 113 | 0 | |
  | | |
|* 37 | TABLE ACCESS BY INDEX ROWID | CONTRACTWORKSPACETAB
| 658K| 1 | 194K|00:00:02.23 | 871K| 2422 | 0 | |
  | | |
|* 38 | INDEX UNIQUE SCAN | ARIBAPK518
| 658K| 1 | 323K|00:00:01.05 | 594K| 54 | 0 | |
  | | |
|* 39 | TABLE ACCESS BY INDEX ROWID | PROCUREMENTUNITTAB
| 194K| 1 | 194K|00:00:00.88 | 384K| 334 | 0 | |
  | | |
|* 40 | INDEX UNIQUE SCAN | ARIBAPK214
| 194K| 1 | 194K|00:00:00.44 | 189K| 63 | 0 | |
  | | |
|* 41 | TABLE ACCESS BY INDEX ROWID | PROJECTADDINTAB
| 194K| 1 | 194K|00:00:01.15 | 352K| 908 | 0 | |
  | | |
|* 42 | INDEX UNIQUE SCAN | ARIBAPK450
| 194K| 1 | 194K|00:00:00.40 | 189K| 74 | 0 | |
  | | |
|* 43 | TABLE ACCESS BY INDEX ROWID | ATTRSBLOBTAB
| 194K| 1 | 336 |00:00:18.10 | 961K| 8487 | 0 | |
  | | |
|* 44 | INDEX UNIQUE SCAN | ARIBAPK439
| 194K| 1 | 194K|00:00:00.54 | 199K| 424 | 0 | |
  | | |
|* 45 | TABLE ACCESS FULL | PROCUREMENTUNITTAB
| 0 | 55 | 0 |00:00:00.01 | 0 | 0 | 0 | |
  | | |
|* 46 | TABLE ACCESS FULL | PROCUREMENTUNITTAB
| 0 | 55 | 0 |00:00:00.01 | 0 | 0 | 0 | |
  | | |
------------------------------------------------------------------------------
------------------------------------------------------------------------------
----------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=101)
3 - filter(ROWNUM<=101)
4 - filter((("PRO211"."SAPROCU_HIERARCHYPATH" LIKE
"PRO212"."SAPROCU_HIERARCHYPATH"||'%' AND "APP213"."AT_SAISMGTCENTER"=:B15)
OR ("APP213"."AT_SAISPURCHASING"=:B14 AND IS
NOT NULL) OR ("APP213"."AT_SAISSSC"=:B13 AND IS NOT NULL)))
5 - filter((-1)<>:B4)
15 - filter((("COM201"."CCC_ACTIVE"=1 OR ("SAA202"."SA_SAHACAT" IS NULL AND
"COM201"."CCC_ACTIVE" IS NULL)) AND ("COM201"."CCC_PURGESTATE"=0 OR
("SAA202"."SA_SAHACAT" IS
NULL AND "COM201"."ROOTID" IS NULL)) AND ("COM201"."CCC_PARTITIONNUMBER"=:B7
OR ("SAA202"."SA_SAHACAT" IS NULL AND "COM201"."CCC_PARTITIONNUMBER" IS
NULL))))
20 - filter(("US_204"."CUS_ACTIVE"=1 AND "US_204"."CUS_PURGESTATE"=0 AND
"US_204"."CUS_PARTITIONNUMBER"=:B6))
21 - access("US_204"."ROOTID"=:B11)
filter("US_204"."ROOTID" LIKE :B23)
22 - filter(("BAS203"."VAL"=:B11 AND "BAS203"."VAL" LIKE :B23 AND
"BAS203"."ROOTID" LIKE :B22))
23 - filter(("SAA202"."SA_SAAPPROVERS"="BAS203"."LVID" AND
"SAA202"."SA_ACTIVE"=1 AND "SAA202"."SA_PURGESTATE"=0 AND
"SAA202"."SA_PARTITIONNUMBER"=:B8))
24 - access("SAA202"."ROOTID"="BAS203"."ROOTID")
filter("SAA202"."ROOTID" LIKE :B22)
25 - filter(("PRO212"."SAPROCU_ACTIVE"=1 AND "PRO212"."SAPROCU_PURGESTATE"=0
AND "PRO212"."SAPROCU_PARTITIONNUMBER"=:B2))
26 - access("SAA202"."SA_SAPROCUREMENTUNIT"="PRO212"."ROOTID")
28 - access("COM201"."ROOTID"="SAA202"."SA_SAHACAT")
29 - access("SAA202"."SA_SATYPE"="APP213"."ROOTID")
30 - filter(("APP213"."AT_ACTIVE"=1 AND "APP213"."AT_PURGESTATE"=0 AND
"APP213"."AT_PARTITIONNUMBER"=:B1))
33 - filter(("COM209"."CCC_ACTIVE"=1 AND
("APP213"."AT_SAISCOMMODITYCODE"=:B12 OR LOWER("COM209"."CCC_HIERARCHYPATH")
LIKE LOWER("COM201"."CCC_HIERARCHYPATH"||'%')) AND
"COM209"."CCC_PURGESTATE"=0 AND "COM209"."CCC_PARTITIONNUMBER"=:B9))
34 - access("COM209"."ROOTID"="BAS210"."VAL")
35 - filter(("WOR206"."WS_SAHACATS"="BAS210"."LVID" AND "WOR206"."WS_SACDD"
IS NOT NULL AND "WOR206"."WS_ATTRSBLOB" IS NOT NULL AND
"WOR206"."WS_NEXTVERSION" IS NULL AND
"WOR206"."WS_STATUS"<>:B16 AND "WOR206"."WS_ACTIVE"=:B20 AND
"WOR206"."WS_PURGESTATE"=0 AND "WOR206"."WS_PARTITIONNUMBER"=:B10))
36 - access("WOR206"."ROOTID"="BAS210"."ROOTID")
37 - filter("CON205"."CAP_ISCONTRACTREQUEST"=:B21)
38 - access("WOR206"."ROOTID"="CON205"."ROOTID")
39 - filter(("PRO211"."SAPROCU_PURGESTATE"=0 AND
"PRO211"."SAPROCU_PARTITIONNUMBER"=:B3))
40 - access("WOR206"."WS_SACDD"="PRO211"."ROOTID")
41 - filter((INTERNAL_FUNCTION("PRO207"."PTA_WORKSPACETYPE") AND
"PRO207"."PTA_PURGESTATE"=0 AND "PRO207"."PTA_PARTITIONNUMBER"=:B5))
42 - access("WOR206"."WS_PROJECTADDIN"="PRO207"."ROOTID")
43 - filter("CTXSYS"."CONTAINS"("ATT208"."BLB_BLOBFIELD",'{0ZPpartZQ0} AND
{0ZPprojZQContractWorkspace} AND {0ZPnextZQ} AND {0ZPactiveZQtrue} AND (
{0ZPwstypeZQ} OR
{0ZPwstypeZQZ20} OR {0ZPwstypeZQClauseLibrary} ) AND ( {0ZPownerZQo5zlZP61}
OR {0ZPownerZQ4lnutZP61} OR {0ZPownerZQe69i2oZP61} OR {0ZPownerZQdrkZP61} OR
{0ZPownerZQo5xnZP61}
OR {0ZPownerZQ22w9imZP61} OR {0ZPownerZQrxrvZP62} OR {0ZPownerZQo5y7ZP61} OR
{0ZPownerZQo5yhZP61} OR {0ZPownerZQ5hap1ZP61} OR {0ZPownerZQrxt3ZP62} OR
{0ZPownerZQrxv1ZP62} OR
{0ZPownerZQo5v5ZP61} OR {0ZPownerZQo5xxZP61} ) NOT
{0ZPwsstatusZQNotZ20Created}')>0)
44 - access("WOR206"."WS_ATTRSBLOB"="ATT208"."ROOTID")
45 - filter(((:B1 IS NULL OR "PRO301"."ROOTID"=:B2) AND
"PRO301"."SAPROCU_ACTIVE"=1 AND "PRO301"."SAPROCU_HIERARCHYPATH" LIKE
:B3||'%' AND "PRO301"."SAPROCU_PURGESTATE"=0
AND "PRO301"."SAPROCU_PARTITIONNUMBER"=:B24))
46 - filter(((:B1 IS NULL OR "PRO401"."ROOTID"=:B2) AND
"PRO401"."SAPROCU_ACTIVE"=1 AND "PRO401"."SAPROCU_HIERARCHYPATH" LIKE
:B3||'%' AND "PRO401"."SAPROCU_PURGESTATE"=0
AND "PRO401"."SAPROCU_PARTITIONNUMBER"=:B25))
.
PL/SQL procedure successfully completed.
.
Elapsed: 00:03:28.71

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms