This discussion is archived
1 Reply Latest reply: Nov 28, 2011 1:57 PM by matperry RSS

Query performance

792262 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points