2 Replies Latest reply on Aug 24, 2007 10:59 AM by 594530

    query performance

    594530
      how do I speed up the following query (takes 33mins)

      select distinct type from
      table(sdo_rdf_match(
      '(?propertynode rdf:type ?type)',
      sdo_rdf_models('molecules'),
      null,
      sdo_rdf_aliases(
      sdo_rdf_alias('chem','http://green.chem.soton.ac.uk/rdf/chemschema.rdfs#')),
      null));


      TYPE
      --------------------------------------------------------------------------------
      http://green.chem.soton.ac.uk/rdf/units.rdfs#Celsius
      http://green.chem.soton.ac.uk/rdf/chemschema.rdfs#Bad
      http://green.chem.soton.ac.uk/rdf/chemschema.rdfs#VapourPressure
      http://green.chem.soton.ac.uk/rdf/chemschema.rdfs#Calculated
      http://green.chem.soton.ac.uk/rdf/chemschema.rdfs#Structure
      http://green.chem.soton.ac.uk/rdf/chemschema.rdfs#MeltingPoint
      http://green.chem.soton.ac.uk/rdf/units.rdfs#Pascal
      http://green.chem.soton.ac.uk/rdf/units.rdfs#Atmosphere
      http://green.chem.soton.ac.uk/rdf/chemschema.rdfs#OrganometallicMolecule
      http://green.chem.soton.ac.uk/rdf/chemschema.rdfs#Laboratory
      http://green.chem.soton.ac.uk/rdf/units.rdfs#Gram

      TYPE
      --------------------------------------------------------------------------------
      http://green.chem.soton.ac.uk/rdf/chemschema.rdfs#Mol
      http://green.chem.soton.ac.uk/rdf/chemschema.rdfs#Good
      http://green.chem.soton.ac.uk/rdf/chemschema.rdfs#PartitionCoefficient
      http://green.chem.soton.ac.uk/rdf/chemschema.rdfs#Temperature
      http://green.chem.soton.ac.uk/rdf/chemschema.rdfs#Suspect
      http://green.chem.soton.ac.uk/rdf/chemschema.rdfs#Solubility
      http://green.chem.soton.ac.uk/rdf/chemschema.rdfs#OrganicMolecule
      http://green.chem.soton.ac.uk/rdf/chemschema.rdfs#BoilingPoint
      http://www.w3.org/1999/02/22-rdf-syntax-ns#Seq
      http://green.chem.soton.ac.uk/rdf/chemschema.rdfs#InorganicMolecule
      http://green.chem.soton.ac.uk/rdf/units.rdfs#Meter

      TYPE
      --------------------------------------------------------------------------------
      http://green.chem.soton.ac.uk/rdf/units.rdfs#Mol
      http://green.chem.soton.ac.uk/rdf/chemschema.rdfs#Pressure

      24 rows selected.

      Elapsed: 00:33:18.26

      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 2358823770

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

      | Id | Operation | Name | Rows | Bytes | Cos
      t (%CPU)| Time |

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

      | 0 | SELECT STATEMENT | | 8168 | 16336 |
      26 (8)| 00:00:01 |

      | 1 | HASH UNIQUE | | 8168 | 16336 |
      26 (8)| 00:00:01 |

      | 2 | COLLECTION ITERATOR PICKLER FETCH| SDO_RDF_MATCH | | |
      | |

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



      Statistics
      ----------------------------------------------------------
      2323804 recursive calls
      0 db block gets
      41871875 consistent gets
      12297 physical reads
      0 redo size
      2060 bytes sent via SQL*Net to client
      411 bytes received via SQL*Net from client
      3 SQL*Net roundtrips to/from client
      14 sorts (memory)
      0 sorts (disk)
      24 rows processed

      SQL>
        • 1. Re: query performance
          Mannamal-Oracle
          Which database version are you on?

          First here are some generic tips to improve performance:

          (1) Running statistics will help query performance:

          begin
          dbms_stats.gather_table_stats(ownname => 'MDSYS',
          tabname => 'RDF_VALUE$',
          cascade => TRUE,
          no_invalidate => FALSE
          );
          dbms_stats.gather_table_stats(ownname => 'MDSYS',
          tabname => 'RDF_LINK$',
          cascade => TRUE,
          no_invalidate => FALSE
          );
          dbms_stats.gather_table_stats(ownname => 'MDSYS',
          tabname => 'RDF_BLANK_NODE$',
          cascade => TRUE,
          no_invalidate => FALSE
          );
          end;
          /

          (2) Upgrade to the latest patch: If you are on 10.2.0.1, then there is an RDF specific patch (Patch: 5557859 - RDF RELEASE BUNDLE BLR FOR 10.2.0.1 (metalink number 8940445)) that fixes a performance bug in case you are running into that. This patch is also available at the OTN Semantics page at http://www.oracle.com/technology/tech/semantic_technologies/index.html, click on software on the right to download the RDF specific patch for 10.2.0.1

          If you are on Oracle 10.2.0.3, then the RDF specific patch (Patch: 5754970 - RDF RELEASE BUNDLE BLR FOR 10.2.0.3) should be applied. This is currently only available on metalink.

          (3) Check your PGA/SGA settings.

          In your specific query, I suspect the SDO_RDF_MATCH is not very selective, and all the data is retrieved from the database before DISTINCT is performed. What is the size of your database, and are there many triples with predicate 'rdf:type' resulting in a large number of triples that match the pattern?

          This issue has been addressed in 11g, and we are considering addressing it in a 10gR2 patch.

          Meanwhile, steps (1) - (3) might help.

          Melli
          • 2. Re: query performance
            594530
            thanks for the fast reply.

            I am on 10.2.0.3 so I applied the patch you suggested and the query is now 13 mins which is better. I think it isn't a very restrictive query and will return lots of rows. (I was already collecting stats).

            I'll try a more restrictive query and see how that goes

            thanks