Skip to Main Content
Forums

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Query performance

792262Nov 25 2011 — edited Nov 28 2011
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.

Comments

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
1 - 1
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 26 2011
Added on Nov 25 2011
#database-key-features, #oracle, #rdf-graph-property-graph
1 comment
201 views