1 Reply Latest reply: Nov 28, 2011 1:57 PM by Matperry-Oracle RSS

    Query performance

    792262
      Hi,

      I am facing performance problem while running the below query.

      It was working very well but due to some new patch (I am not sure about the patch applied) DB performance is very unstable.

      some times the same query takes very less time some times it take more time and the explain plan also is not same. (same amount of data)

      SELECT DISTINCT attribute_id, attribute_name, attribute_datatype,attribute_mandatory,identifier_id identifier_id,identifier_name identifier_name,identifier_code identifier_code,pi identifier_primaryIdentifier,domain_id,src_column_id ,int_view_column
      FROM TABLE ( SEM_MATCH ( ' {<http://www.cs.com/sbip/dwh/mdm/data_modeling/SMA_L3/account_currency> dm:hasConceptualSmaAttribute ?attribute_id . ?attribute_id rdf:type dm:Conceptual_SMA_Attribute . ?attribute_id dm:hasConceptualSmaAttributeName ?attribute_name . ?attribute_id dm:hasConceptualSmaAttributeDatatype ?attribute_datatype .
      ?attribute_id dm:isConceptualSmaAttributeMandatory ?attribute_mandatory OPTIONAL { ?attribute_id dm:hasConceptualDataDomain ?domain_id . ?domain_id rdf:type dm:Conceptual_Data_Domain } OPTIONAL { ?identifier_id rdf:type dm:Conceptual_SMA_Identifier .
      ?identifier_id dm:hasConceptualSmaAttribute ?attribute_id . ?identifier_id dm:hasConceptualSmaIdentifierName ?identifier_name . ?identifier_id dm:hasConceptualSmaIdentifierCode ?identifier_code . ?identifier_id dm:isConceptualSmaIdentifierPrimaryIdentifier ?pi }
      optional {?attribute_id rdf:type dm:Conceptual_SMA_Attribute . ?src_column_id  dt:isMappedTo ?attribute_id . ?src_column_id rdf:type dm:Conceptual_Source_Column } optional {?attribute_id rdf:type dm:Conceptual_SMA_Attribute . ?attribute_id dt:isMappedTo ?int_view_column .
      ?int_view_column rdf:type dm:Conceptual_SMA_View_Column } }',SEM_MODELS ('SMA_CURR','MAP_CURR'),sem_rulebases ('OWLPRIME'),
      SEM_ALIASES(SEM_ALIAS('dt','http://www.cs.com/sbip/dwh/mdm/data_transfer#'),SEM_ALIAS('dm','http://www.cs.com/sbip/dwh/mdm/data_modeling#'),SEM_ALIAS('ds','http://www.cs.com/sbip/dwh/mdm/data_sources#'))
      , NULL));


      I performed the following to increase the performance but it didnt give any improvement.
      Analyse model, analyse rule index, gather satistics


      Please help me to solve this problem.

      Regards,
      Kavitha.
        • 1. Re: Query performance
          Matperry-Oracle
          Hi Kavitha,

          I would recommend that you try a few things:

          1) Since you are doing a multi-partition query, Use a virtual model with ALLOW_DUP=T

          http://docs.oracle.com/cd/E11882_01/appdev.112/e25609/sdo_rdf_concepts.htm#CIHIGJAB

          exec sem_apis.create_virtual_model('MY_VM', sem_models('SMA_CURR','MAP_CURR'),sem_rulebases ('OWLPRIME'));

          select distinct ...
          from table(sem_match('{ ... }', sem_models('MY_VM'), null, sem_aliases(...), null, null, ' ALLOW_DUP=T '));

          2) Experiment with Dynamic Sampling

          http://docs.oracle.com/cd/E11882_01/appdev.112/e25609/sdo_rdf_concepts.htm#CIHIHAAE

          select /*+ DYNAMIC_SAMPLING(6) */ distinct ...
          from table(sem_match('{ ... }', sem_models('MY_VM'), null, sem_aliases(...), null, null, ' ALLOW_DUP=T '));

          3) If plans are still not stable, you can use SQL Plan Management http://docs.oracle.com/cd/E11882_01/server.112/e16638/optplanmgmt.htm#BABEAFGG
          or SQL Plan Stability http://docs.oracle.com/cd/E11882_01/server.112/e16638/outlines.htm#PFGRF707

          Hope this helps,
          Matt