Poor Performance For Creating_Entailment After Upgrade To 11.2.0.3.6 (Doc ID 1581904.1)

Last updated on FEBRUARY 08, 2017

Applies to:

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

Symptoms

Poor performance for creating entailment after upgrade to 11.2.0.3.6

After upgrade to 11.2.0.3.6, the sem_apis.create_entailment is taking more than 2 hours while in 11.2.0.2.5 it took less than 2 minutes.

exec sem_apis.create_entailment('EUROVOC_SKOS_ENT2', sem_models('EUROVOC_ONTOLOGY','EUVOC_2'),sem_rulebases('OWLPRIME','skoscore'),sem_apis.reach_closure,'DIF-,DISJ-');

This is the underlying query:

/* Formatted on 6/11/2013 1:49:32 PM (QP5 v5.215.12089.38647) */
INSERT /*+ append */ /* rule: FP */
  INTO MDSYS.RDF#2900 (start_node_id, p_value_id, canon_end_node_id /* NGLI_PJ */
  )
  SELECT s_id start_node_id,
  6459981115371763560 p_value_id,
  c_id canon_end_node_id /* NGLI_OS */
  FROM (SELECT DISTINCT
  (m2.canon_end_node_id) s_id, (m3.canon_end_node_id) c_id /* NGLI_IS */
  FROM (SELECT start_node_id, p_value_id, canon_end_node_id
  FROM MDSYS.RDF#2900#2 s
  UNION ALL
  SELECT start_node_id, p_value_id, canon_end_node_id
  FROM MDSYS.RDF#2900) m2,
  (SELECT start_node_id, p_value_id, canon_end_node_id
  FROM MDSYS.RDF#2900#2 s
  UNION ALL
  SELECT start_node_id, p_value_id, canon_end_node_id
  FROM MDSYS.RDF#2900) m3
  WHERE m2.p_value_id = m3.p_value_id
  AND m2.start_node_id = m3.start_node_id
  AND m2.p_value_id IN
  (574133823893507420,
  2754854313406870411,
  2906813749135818662,
  4938334119036131987,
  5153485665112449948,
  6239243909802177258,
  7028835952254398902,
  8260326046847333407,
  8305763093382764999,
  8961463750610563532)/* NGLI_AP */
  )
  WHERE s_id <> c_id
  AND NOT EXISTS
  (SELECT /*+ HASH_AJ */
  1
  FROM (SELECT start_node_id,
  p_value_id,
  canon_end_node_id
  FROM MDSYS.RDF#2900#2 s
  UNION ALL
  SELECT start_node_id,
  p_value_id,
  canon_end_node_id
  FROM MDSYS.RDF#2900) x
  WHERE x.start_node_id = s_id
  AND x.p_value_id = 6459981115371763560
  AND x.canon_end_node_id = c_id /* NGLI_NP */
  )

Predicate Information (identified by operation id):
---------------------------------------------------
  2 - access("X"."START_NODE_ID"="S_ID" AND "X"."CANON_END_NODE_ID"="C_ID")
  6 - filter("P_VALUE_ID"=6459981115371763560)
  8 - filter("P_VALUE_ID"=6459981115371763560)
 15 - filter(("P_VALUE_ID"=574133823893507420 OR "P_VALUE_ID"=2754854313406870411 OR
  "P_VALUE_ID"=2906813749135818662 OR "P_VALUE_ID"=4938334119036131987 OR "P_VALUE_ID"=5153485665112449948 OR
  "P_VALUE_ID"=6239243909802177258 OR "P_VALUE_ID"=7028835952254398902 OR "P_VALUE_ID"=8260326046847333407 OR
  "P_VALUE_ID"=8305763093382764999 OR "P_VALUE_ID"=8961463750610563532))
 17 - filter(("P_VALUE_ID"=574133823893507420 OR "P_VALUE_ID"=2754854313406870411 OR
  "P_VALUE_ID"=2906813749135818662 OR "P_VALUE_ID"=4938334119036131987 OR "P_VALUE_ID"=5153485665112449948 OR
  "P_VALUE_ID"=6239243909802177258 OR "P_VALUE_ID"=7028835952254398902 OR "P_VALUE_ID"=8260326046847333407 OR
  "P_VALUE_ID"=8305763093382764999 OR "P_VALUE_ID"=8961463750610563532))
 18 - filter(("M2"."START_NODE_ID"="M3"."START_NODE_ID" AND
  "M2"."CANON_END_NODE_ID"<>"M3"."CANON_END_NODE_ID"))
 21 - filter("P_VALUE_ID"="M2"."P_VALUE_ID")
 23 - filter("P_VALUE_ID"="M2"."P_VALUE_ID")

--==================================================================================

And exec plan is :
Plan hash value: 340126493
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | | 4942M(100)| | | |
| 1 | LOAD AS SELECT | | | | | | | | |
|* 2 | HASH JOIN RIGHT ANTI | | 13G| 791G| 9472K| 4942M (3)| 57:58:48 | | |
| 3 | VIEW | | 190K| 7237K| | 1130 (2)| 00:00:01 | | |
| 4 | UNION-ALL | | | | | | | | |
| 5 | PARTITION HASH SINGLE | | 188K| 6445K| | 514 (2)| 00:00:01 | 4 | 4 |
|* 6 | TABLE ACCESS FULL | RDF#2900#2 | 188K| 6445K| | 514 (2)| 00:00:01 | 4 | 4 |
| 7 | PARTITION HASH SINGLE | | 1455 | 52380 | | 616 (2)| 00:00:01 | 4 | 4 |
|* 8 | TABLE ACCESS FULL | RDF#2900 | 1455 | 52380 | | 616 (2)| 00:00:01 | 4 | 4 |
| 9 | VIEW | | 13G| 324G| | 4918M (3)| 57:41:44 | | |
| 10 | SORT UNIQUE | | 13G| 637G| 74T| 4918M (3)| 57:41:44 | | |
| 11 | NESTED LOOPS | | 1270G| 58T| | 32M (2)| 00:22:50 | | |
| 12 | VIEW | | 328K| 11M| | 1062 (5)| 00:00:01 | | |
| 13 | UNION-ALL | | | | | | | | |
| 14 | PARTITION HASH INLIST | | 327K| 11M| | 1010 (5)| 00:00:01 |KEY(I) |KEY(I) |
|* 15 | TABLE ACCESS FULL | RDF#2900#2 | 327K| 11M| | 1010 (5)| 00:00:01 |KEY(I) |KEY(I) |
| 16 | PARTITION HASH INLIST | | 1195 | 43020 | | 52 (4)| 00:00:01 |KEY(I) |KEY(I) |
|* 17 | TABLE ACCESS FULL | RDF#2900 | 1195 | 43020 | | 52 (4)| 00:00:01 |KEY(I) |KEY(I) |
|* 18 | VIEW | | 105K| 1438K| | 99 (3)| 00:00:01 | | |
| 19 | UNION ALL PUSHED PREDICATE | | | | | | | | |
| 20 | PARTITION HASH SINGLE | | 32778 | 1152K| | 66 (2)| 00:00:01 | KEY | KEY |
|* 21 | TABLE ACCESS FULL | RDF#2900#2 | 32778 | 1152K| | 66 (2)| 00:00:01 | KEY | KEY |
| 22 | PARTITION HASH SINGLE | | 72465 | 2547K| | 33 (4)| 00:00:01 | KEY | KEY |
|* 23 | TABLE ACCESS FULL | RDF#2900 | 72465 | 2547K| | 33 (4)| 00:00:01 | KEY | KEY |
------------------------------------------------------------------------------------------------------------------------

If using the hint NO_QUERY_TRANSFORMATION in the underlying select we have results in seconds

Note: The NO_QUERY_TRANSFORMATION hint causes the optimizer to skip all query transformations including but not limited to OR expansion, view merging, subquery unnesting, star transformation and materialized view rewrite.

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