1 Reply Latest reply: Feb 6, 2013 8:39 AM by Anuj Dwivedi-Oracle RSS

    Perfomance purging instances

    user816618
      Hello,

      I would like to know if the performance we experience is normal : delete of 30.000 composite instances per hour in a database with 9mln composite instances
      I have a feeling that this is much to slow and that we either don’t have the right indexes or the right setting (ex. Mulitblock read count) or that we just don’t have enough resources (CPU/Memory/ disk i/o speed)
      We do see that the process is i/o bound and that we can't get faster then 10ms per i/o
      Offcourse we can switch to parallel purging or introduce partitioning but I first want to be sure that everything is configured properly

      Thanks.. Jaco.
        • 1. Re: Perfomance purging instances
          Anuj Dwivedi-Oracle
          Can't comment on performance without having the details of your infrastructure but parallel purging will definitely perform better. You may like to thoroughly review this white paper -

          http://www.oracle.com/technetwork/database/features/availability/soa11gstrategy-1508335.pdf

          There are a couple of optimizations that are available for purging. The following indexes can help in purge performance but the decision to use these indexes should be left to your DBA -

          create index temp_cube_instance_idx on temp_cube_instance(cikey);
          create index dlv_message_cikey_idx1 on dlv_message(cikey);

          The below three indexes should be analyzed by a senior DBA prior to implementation-

          create index on DOCUMENT_DLV_MSG_REF.DOCUMENT_ID;
          create index on REFERENCE_INSTANCE.COMPOSITE_INSTANCE_ID;
          create index on DLV_MESSAGE(RECEIVE_DATE,ECID );

          Regards,
          Anuj