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

    Performance indexes

    655091
    655091 Aug 14, 2009 5:58 PM
      Hi

      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.

      ram.
      • 3805 Views
      • Tags:
        • 1. Re: Performance indexes
          Sdas-Oracle
          Sdas-Oracle Aug 14, 2009 9:13 PM (in response to 655091)
          PSO (or PSCO) index should help.
          1 person found this helpful
          • 2. Re: Performance indexes
            Surachart Opun
            Surachart Opun Aug 16, 2009 8:10 AM (in response to 655091)
            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
              alwu-Oracle
              alwu-Oracle Aug 24, 2009 1:53 PM (in response to 655091)
              Ram,

              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?

              Cheers,

              Zhe Wu
              • 4. Re: Performance indexes
                655091
                655091 Aug 26, 2009 8:58 PM (in response to alwu-Oracle)
                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?

                Ram

                Edited by: user652088 on Aug 26, 2009 1:57 PM
                • 5. Re: Performance indexes
                  alwu-Oracle
                  alwu-Oracle Aug 26, 2009 9:26 PM (in response to 655091)
                  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?

                  Thanks,

                  Zhe Wu
                  • 6. Re: Performance indexes
                    655091
                    655091 Aug 26, 2009 9:46 PM (in response to alwu-Oracle)
                    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.

                    Ram
                    • 7. Re: Performance indexes
                      alwu-Oracle
                      alwu-Oracle Aug 27, 2009 12:56 PM (in response to 655091)
                      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.

                      Cheers,

                      Zhe Wu