8 Replies Latest reply on Dec 13, 2011 4:58 PM by Matperry-Oracle

    Too long query

    896511
      Hello,

      I'm working on Oracle 11.2.0.3.

      I'm trying to execute this query

      SELECT distinct s, prefLabel,o
      FROM TABLE(SEM_MATCH('PREFIX skos: <http://www.w3.org/2004/02/skos/core#>
      PREFIX orardf:<http://xmlns.oracle.com/rdf/>
      SELECT *
      WHERE

      ?s ?p ?o.
      ?s skos:prefLabel ?prefLabel .
      filter (lang(?prefLabel ) ="fr").
      filter (orardf:textContains(?prefLabel , "famille")).
      }
      ',
      SEM_Models('modelinf'),
      SEM_Rulebases('SKOSCORE'),
      null,
      null,
      null,
      null ))
      ;

      but it takes too long time.

      I'm not sure that all the necessary index has been create on the database.

      Could you help me to optimize this query ?

      Thanks.

      Cyril.
        • 1. Re: Too long query
          Matperry-Oracle
          Hi Cyril,

          There are some simple optimizations you can make to the query:

          1) Push the DISTINCT into the SPARQL query pattern
          2) Only project variables that are selected from the outer SQL query.
          3) Use the ALLOW_DUP=T query option

          SELECT s, prefLabel,o -- note that DISTINCT is removed here
          FROM TABLE(SEM_MATCH('PREFIX skos: <http://www.w3.org/2004/02/skos/core#>
          PREFIX orardf:<http://xmlns.oracle.com/rdf/>
          SELECT DISTINCT ?s ?prefLabel ?o
          WHERE
          {
          ?s ?p ?o.
          ?s skos:prefLabel ?prefLabel .
          filter (lang(?prefLabel ) ="fr").
          filter (orardf:textContains(?prefLabel , "famille")).
          }
          ',
          SEM_Models('modelinf'),
          SEM_Rulebases('SKOSCORE'),
          null,
          null,
          null,
          ' ALLOW_DUP=T ' ))
          ;

          You may also want to create an SPCM index to help with the ?s ?p ?o triple pattern. The PSCM and PCSM indexes don't work as well here because the predicate is unbound for this triple pattern. Make sure you rebuild the SPCM index after creating it so that it is usable.
          http://docs.oracle.com/cd/E11882_01/appdev.112/e25609/sdo_rdf_concepts.htm#CIHBEBIB

          As usual, make sure you have gathered stats for the network (sem_perf.gather_stats()), and you may want to experiment with dynamic sampling:
          http://docs.oracle.com/cd/E11882_01/appdev.112/e25609/sdo_rdf_concepts.htm#CIHIHAAE

          Hope this helps,
          Matt
          • 2. Re: Too long query
            Matperry-Oracle
            One more thing. If you plan to do named graph queries, then you should also include the G column.

            This would mean that you should create an SPCGM index instead of an SPCM index.

            - Matt
            • 3. Re: Too long query
              896511
              Hello,

              thanks for your reply.

              Creation of indexes

              EXECUTE SEM_APIS.ADD_SEM_INDEX('SPCM');
              anonymous block completed

              EXECUTE SEM_APIS.ALTER_SEM_INDEX_ON_MODEL('MODELINF','SPCM','REBUILD');
              anonymous block completed


              Test with ALLOW_DUP=T

              If I try this query with SQL Developer

              ------------------------------------------------------------------------------------
              SELECT s, prefLabel
              FROM TABLE(SEM_MATCH('PREFIX skos: <http://www.w3.org/2004/02/skos/core#>
              PREFIX orardf:<http://xmlns.oracle.com/rdf/>
              SELECT distinct ?s ?prefLabel
              WHERE
              {
              ?s rdf:type skos:Concept.
              ?s skos:prefLabel ?prefLabel .
              filter (lang(?prefLabel ) ="fr").
              filter (orardf:textContains(?prefLabel , "famille")).
              }
              ',
              SEM_Models('modelinf'),
              SEM_Rulebases('SKOSCORE'),
              null,
              null,
              null,
              null ))
              ;
              -----------------------------------------------------------------------------

              I obtain 12 rows in 2.813 sec. (It's too too long).

              with ' ALLOW_DUP=T ', I obtain 12 rows in 2.718 sec. (It's too long again).


              Optimization ?

              Do you think it's possible to be more speedly (in millisecond) ?

              I'm trying with this query (less criteria for example)

              -----------------------------------------------------------------------------
              SELECT s, prefLabel
              FROM TABLE(SEM_MATCH('PREFIX skos: <http://www.w3.org/2004/02/skos/core#>
              PREFIX orardf:<http://xmlns.oracle.com/rdf/>
              SELECT distinct ?s ?prefLabel
              WHERE
              {
              ?s skos:prefLabel ?prefLabel .
              filter (lang(?prefLabel ) ="fr").
              filter (orardf:textContains(?prefLabel , "famille")).
              }
              ',
              SEM_Models('eurovocinf'),
              SEM_Rulebases('SKOSCORE'),
              null,
              null,
              null,
              null))
              ;
              -----------------------------------------------------------------------------
              I obtain 13 rows in only 0.156 seconds. It's ok, but if I add some criteria in my query, It takes long time again.


              Solution ?

              I'm afraid beacause my database don't contains many records.

              I have only
              select count(1) from mdsys.rdfm_modelinf;
              2656651 rows in my model

              -----------------------------------------------------------------------------
              If I take only French skos:prefLabel, I have

              PREFIX skos: <http://www.w3.org/2004/02/skos/core#>
              PREFIX orardf:<http://xmlns.oracle.com/rdf/>
              SELECT count(?s)
              WHERE
              {
              ?s skos:prefLabel ?prefLabel .
              filter (lang(?prefLabel ) ="fr").
              }

              Response : 6945 records
              -----------------------------------------------------------------------------

              -----------------------------------------------------------------------------
              If I take all language skos:prefLabel, I have

              PREFIX skos: <http://www.w3.org/2004/02/skos/core#>
              PREFIX orardf:<http://xmlns.oracle.com/rdf/>
              SELECT count(?s)
              WHERE
              {
              ?s skos:prefLabel ?prefLabel .
              }

              Response : 161484 records
              -----------------------------------------------------------------------------

              It's more speed to create a classique relationnal table and to ask it with sql ?

              Could you help me to optimize my database and my query?

              Thanks.

              Cyril.
              • 4. Re: Too long query
                Matperry-Oracle
                Hi Cyril,

                Can you please post the execution plans for these queries:

                explain plan for
                select ...
                from table(sem_match( ... ));

                select plan_table_output from table(dbms_xplan.display('plan_table',null,'basic,parallel,partition,predicate'));

                Thanks,
                Matt
                • 5. Re: Too long query
                  896511
                  Hello,

                  this is the execution plan of this query

                  ------------------------------------------------------------------------------
                  SELECT s, prefLabel
                  FROM TABLE(SEM_MATCH('PREFIX skos: <http://www.w3.org/2004/02/skos/core#>
                  PREFIX orardf:<http://xmlns.oracle.com/rdf/>
                  SELECT distinct ?s ?prefLabel
                  WHERE
                  {
                  ?s rdf:type skos:Concept.
                  ?s skos:prefLabel ?prefLabel .
                  filter (lang(?prefLabel ) ="fr").
                  filter (orardf:textContains(?prefLabel , "famille")).
                  }
                  ',
                  SEM_Models('modelinf'),
                  SEM_Rulebases('SKOSCORE'),
                  null,
                  null,
                  null,
                  null ))
                  ;

                  It takes 2.703 seconds for 12 rows

                  ------------------------------------------------------------------------------


                  Plan hash value: 1619577833

                  ---------------------------------------------------------------------------------
                  | Id | Operation | Name | Pstart| Pstop |
                  ---------------------------------------------------------------------------------
                  | 0 | SELECT STATEMENT | | | |
                  | 1 | COLLECTION ITERATOR SUBQUERY FETCH | | | |
                  | 2 | COUNT | | | |
                  |* 3 | FILTER | | | |
                  | 4 | NESTED LOOPS | | | |
                  | 5 | NESTED LOOPS | | | |
                  | 6 | VIEW | | | |
                  | 7 | SORT GROUP BY | | | |
                  | 8 | NESTED LOOPS | | | |
                  | 9 | NESTED LOOPS | | | |
                  | 10 | NESTED LOOPS | | | |
                  | 11 | VIEW | | | |
                  | 12 | UNION-ALL | | | |
                  | 13 | PARTITION LIST SINGLE | | 3 | 3 |
                  |* 14 | INDEX RANGE SCAN | RDF_LNK_PCS_IDX | 3 | 3 |
                  | 15 | PARTITION LIST SINGLE | | 4 | 4 |
                  |* 16 | INDEX RANGE SCAN | RDF_LNK_PCSGM_IDX | 4 | 4 |
                  | 17 | VIEW | | | |
                  | 18 | UNION-ALL PARTITION | | | |
                  | 19 | PARTITION LIST SINGLE | | 3 | 3 |
                  |* 20 | INDEX RANGE SCAN | RDF_LNK_PSC_IDX | 3 | 3 |
                  | 21 | PARTITION LIST SINGLE | | 4 | 4 |
                  |* 22 | INDEX RANGE SCAN | RDF_LNK_PSCGM_IDX | 4 | 4 |
                  |* 23 | INDEX UNIQUE SCAN | C_PK_VID | | |
                  |* 24 | TABLE ACCESS BY INDEX ROWID| RDF_VALUE$ | | |
                  |* 25 | INDEX UNIQUE SCAN | C_PK_VID | | |
                  | 26 | TABLE ACCESS BY INDEX ROWID | RDF_VALUE$ | | |
                  |* 27 | TABLE ACCESS FULL | RDF_RI_SHAD_5$ | | |
                  ---------------------------------------------------------------------------------

                  Predicate Information (identified by operation id):
                  ---------------------------------------------------

                  3 - filter( NOT EXISTS (SELECT 0 FROM "MDSYS"."RDF_RI_SHAD_5$"
                  "RDF_RI_SHAD_5$" WHERE LNNVL("RDF_RI_SHAD_5$"."ID"<>1)))
                  14 - access("P_VALUE_ID"=834132227519661324 AND
                  "CANON_END_NODE_ID"=8129753520990573772 AND "START_NODE_ID">0 AND
                  "START_NODE_ID" IS NOT NULL)
                  16 - access("P_VALUE_ID"=834132227519661324 AND
                  "CANON_END_NODE_ID"=8129753520990573772 AND "START_NODE_ID">0 AND
                  "START_NODE_ID" IS NOT NULL)
                  20 - access("P_VALUE_ID"=8569708817671647133 AND
                  "START_NODE_ID"="from$_subquery$_007"."START_NODE_ID" AND
                  "CANON_END_NODE_ID">0 AND "CANON_END_NODE_ID" IS NOT NULL)
                  filter("START_NODE_ID">0)
                  22 - access("P_VALUE_ID"=8569708817671647133 AND
                  "START_NODE_ID"="from$_subquery$_007"."START_NODE_ID" AND
                  "CANON_END_NODE_ID">0 AND "CANON_END_NODE_ID" IS NOT NULL)
                  filter("START_NODE_ID">0)
                  23 - access("V0"."VALUE_ID"="from$_subquery$_011"."CANON_END_NODE_ID")
                  24 - filter("SEM_APIS"."GETV$LANGVAL"("V0"."VALUE_TYPE","V0"."VNAME_PRE
                  FIX","V0"."VNAME_SUFFIX","V0"."LITERAL_TYPE","V0"."LANGUAGE_TYPE")='fr'
                  AND "CTXSYS"."CONTAINS"("V0"."VNAME_PREFIX",'famille'||'')>0)
                  25 - access("R"."S$RDFVID"="V0"."VALUE_ID")
                  27 - filter(LNNVL("RDF_RI_SHAD_5$"."ID"<>1))

                  Thanks.

                  Cyril
                  • 6. Re: Too long query
                    Matperry-Oracle
                    Hi Cyril,

                    For this particular query, the filter on ?prefLabel is evaluated last instead of first, which is not the best execution plan.

                    One option would be to use an optimizer hint to influence the query plan as follows:

                    SELECT s, prefLabel
                    FROM TABLE(SEM_MATCH('PREFIX skos: <http://www.w3.org/2004/02/skos/core#>
                    PREFIX orardf:<http://xmlns.oracle.com/rdf/>
                    SELECT distinct ?s ?prefLabel
                    WHERE
                    { *# HINT0={ LEADING(?prefLabel) }*
                    ?s rdf:type skos:Concept.
                    ?s skos:prefLabel ?prefLabel .
                    filter (lang(?prefLabel ) ="fr").
                    filter (orardf:textContains(?prefLabel , "famille")).
                    }
                    ',
                    SEM_Models('modelinf'),
                    SEM_Rulebases('SKOSCORE'),
                    null,
                    null,
                    null,
                    null ))
                    ;

                    You can read more about optimizer hints with sem_match here:
                    http://docs.oracle.com/cd/E11882_01/appdev.112/e25609/sdo_rdf_concepts.htm#CHDJACII
                    http://docs.oracle.com/cd/E11882_01/appdev.112/e25609/sdo_rdf_concepts.htm#CIHJGCCA

                    Another option would be to use dynamic sampling to try to get a better execution plan:
                    http://docs.oracle.com/cd/E11882_01/appdev.112/e25609/sdo_rdf_concepts.htm#CIHIHAAE

                    First, alter the session to set the dynamic sampling level to 6:

                    ALTER SESSION SET OPTIMIZER_DYNAMIC_SAMPLING=6;

                    Then, execute your query.

                    Please let me know if either of these options helps.

                    Thanks,
                    Matt
                    • 7. Re: Too long query
                      896511
                      Hello Matt,

                      thanks you for your help.

                      I'm trying the HINTO - leading in SQL developper.

                      -------------------------------------------------------------------------------------------------------------------------------
                      SELECT s, prefLabel
                      FROM TABLE(SEM_MATCH('PREFIX skos: <http://www.w3.org/2004/02/skos/core#>
                      PREFIX orardf:<http://xmlns.oracle.com/rdf/>
                      SELECT distinct ?s ?prefLabel
                      WHERE
                      { # HINT0={ LEADING(?prefLabel) }
                      ?s rdf:type skos:Concept.
                      ?s skos:prefLabel ?prefLabel .
                      filter (lang(?prefLabel ) ="fr").
                      filter (orardf:textContains(?prefLabel , "famille")).
                      }
                      ',
                      SEM_Models('modelinf'),
                      SEM_Rulebases('SKOSCORE'),
                      null,
                      null,
                      null,
                      null ))
                      ;


                      With HINTO - 12 rows in 1.36 seconds

                      Without HINTO - 12 rows in 2.656 seconds
                      -------------------------------------------------------------------------------------------------------------------------------

                      It's better with HINTO, but it's not enough.

                      Thanks.

                      Cyril.
                      • 8. Re: Too long query
                        Matperry-Oracle
                        Hi Cyril,

                        Please contact me directly at matthew dot perry at oracle dot com, and we can go through some more query optimization steps. Also, please send the execution plan for the query with HINT0.

                        Thanks,
                        Matt