7 Replies Latest reply on Aug 27, 2009 12:56 PM by alwu-Oracle

    Performance indexes


      Our software uses Jastor- which is an object-triple mapping package on Jena (think Hibernate for Jena). It makes lots and lots of db calls that look like this:

      SELECT o$RDFVTYP, o$RDFLTYP, o$RDFLANG, o$RDFCLOB, decode(o$RDFVTYP, 'BLN', ('_:'||substr(o,instr(o,'m',4)+1)), o) o FROM TABLE( sdo_rdf_match( '(<mdc:xxx:yyyy> <http://myprop> ?o )' , sdo_rdf_models('IAMTEST') , sdo_rdf_rulebases('owlprime') , null , null , 'INCOMPLETE' ) )

      It seems like the default (performance) indexes on pscm and psmo should be sufficient for these kind of queries? If not what additional indexes might help making these faster.

        • 1. Re: Performance indexes
          PSO (or PSCO) index should help.
          1 person found this helpful
          • 2. Re: Performance indexes
            Surachart Opun
            use EXECUTION PLAN to check

            SQL> set autot trace explain
            SQL> sql statements

            Or use SQL_TRACE=TRUE to check what wait from this sql statement:

            SQL> alter session set SQL_TRACE=TRUE;

            SQL>sql statements

            and then check in trace file at udump.
            • 3. Re: Performance indexes

              You are right. The default index is good enough for this kind of queries. Additional indexes may not help because each query goes really fast if I am not mistaken. It is the number of queries that caused your problem.

              What kind of queries are your executing? Are there OPTIONAL used?


              Zhe Wu
              • 4. Re: Performance indexes
                Thanks for the response Zhe.

                The queries do run fairly fast but yeah many many of these get called- so any performance improvement there would make a difference. We did try building a PSO index like Souri suggested and the explain plans on the queries show that it is being used. I do not think the performance improved that much with the new indexes- but I think we can live with what we have right now..

                Amongst the SPARQL queries that we run, quite a few of them have OPTIONALS (and many of them return a fairly large no. of "rows" :(). The current jena adapter (understandably) performs badly on these queries. When do you plan to release an updated Jena adapter which uses the native OPTIONAL support?


                Edited by: user652088 on Aug 26, 2009 1:57 PM
                • 5. Re: Performance indexes
                  Hi Ram,

                  Could you please do one thing? Just get rid of the OPTIONAL queries and re-test, do you still see many many small queries?


                  Zhe Wu
                  • 6. Re: Performance indexes
                    I am sorry if I am making this confusing..

                    There are two ways we access the data from the Jena Model:
                    1) Through SPARQLs
                    2) Through Jastor beans (http://jastor.sourceforge.net/)

                    The SPARQLs (other than the OPTIONALS) dont lead to many many small queries.

                    The Jastor based access DOES lead to many small queries. This is because each Jastor call gets translated to a Model.listStatements(<subj>, <prop>, null)-where both <subj> and <prop> are URIs. This in turn gets translated to the simple sem_match query. Many Jastor calls are made i.e. many small queries.

                    Hope this makes sense.

                    • 7. Re: Performance indexes
                      I see. Thanks.

                      Just as a FYI, 1) will be addressed by an upcoming Jena Adaptor release.

                      2) is application specific. There is not much we can do. There is one API in GraphOracleSem that might be useful for you.

                      public ExtendedIterator graphBaseFindSubjectObject(Node[] predicates)

                      You can check the Javadoc for that.


                      Zhe Wu