This discussion is archived
4 Replies Latest reply: Aug 28, 2012 10:20 AM by 900576 RSS

Oracle Database Performance With Semantic

900576 Newbie
Currently Being Moderated
Hello,

Is there a Developer's Guide for Semantic that specifically talks about database performance with the Semantic network/tables/indexes? We are having issues with performance the larger the semantic network becomes.

Any help or pointers would be appriciated.

Thanks
-MichaelB
  • 1. Re: Oracle Database Performance With Semantic
    matperry Journeyer
    Currently Being Moderated
    Hi Michael,

    There is a query performance best practices section in the user guide:
    http://docs.oracle.com/cd/E11882_01/appdev.112/e25609/sdo_rdf_concepts.htm#sthref159

    Slides 61 - 68 from this presentation also have some performance related information:
    http://download.oracle.com/otndocs/products/semantic_tech/pdf/semtech_howto.pdf

    Some specifics about your setup will be helpful:
    1) Are you querying multiple models and/or a model + entailment? If so, are you using a virtual model and using the ALLOW_DUP=T query option?
    2) Are you using named graphs?
    3) How many triples are you querying?
    4) What semantic network and/or datatype indexes have been created?
    5) What is your hardware setup (number and type of disks, RAM, processor, etc.)?
    6) How much memory have you allocated to the database (pga, sga, memory_target, etc.)?
    7) Are you using parallel query execution?
    8) Have you tried dynamic sampling?

    Thanks,
    Matt
  • 2. Re: Oracle Database Performance With Semantic
    900576 Newbie
    Currently Being Moderated
    Matt,
    Thanks for your response. Here are the answers to the questions about our setup/environment.

    1) Are you querying multiple models and/or a model + entailment? If so, are you using a virtual model and using the ALLOW_DUP=T query option?

    A single model, no entailments. We attempted to use multiple models, and a virtual model (with ALLOW_DUP=T), however the UNION ALL in the explain plan made the query duration unacceptable.

    2) Are you using named graphs?

    No named graphs.

    3) How many triples are you querying?

    Approximately 85 million.

    4) What semantic network and/or datatype indexes have been created?

    We have PCSGM, PSCGM, PSCM, PCSM, CPSM, and SCM.

    5) What is your hardware setup (number and type of disks, RAM, processor, etc.)?

    We are running the 11.2.0.3 database on a Sun Solaris T2000, we have ASM managing our disks from RAID5, I believe currently we have two Disk Groups with the indexes in one and the data tables in the other. We have 32 GB of memory, and 32 CPUs. However, it is not the only thing running on the machine.

    6) How much memory have you allocated to the database (pga, sga, memory_target, etc.)?

    We have the memory_target set to 9GB, the db_cache_size set to 2GB, and the db_keep_cache_size set to 4.5GB. `pga_aggregate_target` is set to 0 (auto), as is `sga_target`.
    (Since my initial request, we pinned the RDF_VALUE$ (~2.5GB) and C_PK_VID (~1.7GB) objects in the KEEP buffer cache, which drastically improved performance)

    7) Are you using parallel query execution?

    Yes, some of the more complex queries we run with the parallel hint set to 8.

    8) Have you tried dynamic sampling?

    Yes. We have ODS set to 3 for our more complex queries, we have not altered this much to see if there is a performance gained by changing this value.


    Thanks again,
    -Michael
  • 3. Re: Oracle Database Performance With Semantic
    matperry Journeyer
    Currently Being Moderated
    Michael,

    Your setup looks pretty good. I would suggest dropping the SCM index and replacing it with SCPM. It's generally a good idea to include all columns in the network indexes.

    You should not see a UNION ALL in the query plan if you are using a virtual model.

    I would also recommend that you try ODS=6. We have seen significant improvement in query plans with this higher value.

    Are there certain SPARQL features that are common among the problematic queries?

    Cheers,
    Matt
  • 4. Re: Oracle Database Performance With Semantic
    900576 Newbie
    Currently Being Moderated
    Matt,

    We will try switching that index to SCPM. As well as test out ODS=6.

    As far as the virtual model goes, we didn't have time to fully get through all of the problems, but we will spend more time on it in the future and we'll iron out any of the performance issues we have at that time.

    As far as features, not really. We just noticied a really high 'db file sequential read' on RDF_VALUE$ and C_PK_VID on our more complex queries (due to the fact the the block were getting kicked out of the buffer cache by the other semantic objects; e.g. the PSCGM and PCSGM index partitions)

    We feel like pinning RDF_VALUE$ and C_PK_VID objects into the KEEP buffer cache is kind of a hack. It makes sense (after the fact) since every query has to look up the value for the VID before it returns the result set. But do we have to keep throwing more and more memory at the database to keep the performance reasonable if our data set grows significantly?


    Thanks
    -Michael

Legend

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