[Building Sakai] Locks on SAKAI_EVENT table

[Benito J. Gonzalez]

Hi all,

We are seeing a number of locks on our SAKAI_EVENT table.  Has anyone 
else dealt with issues surrounding this table?  Some details follow:

    * Five public-facing tomcats and three task-specific tomcats against
      our production DB
    * Oracle 10g release 10.2.0.4.0 64bit
    * The following indices have been added to SAKAI_EVENT, then removed 
in case they caused performance issues
          o create bitmap index IDX_SAKAI_EVENT_0001 on SAKAI_EVENT(EVENT);
          o create index IDX_SAKAI_EVENT_0002 on SAKAI_EVENT(REF);
    * We had 60 million records last week.  Trimming the records to a
      few hundred thousand did not reduce deadlocks
    * Presence turned off recently (last day or so)
    * Deadlocks are from insert contention on SAKAI_EVENT
    * We suspected that the bitmap index on the table was contributing
      to the issue.  Removing it resulted major load from the following 
SELECT:
          o High load from "SELECT EVENT_DATE, REF, SESSION_ID,
            EVENT_CODE FROM SAKAI_EVENT WHERE EVENT = :1 ORDER BY
            EVENT_DATE"
          o No deadlocks / locks in last 24 hours
    * DB connection pool is DBCP 1.2.2 (out of date?)
    * No DBCP settings explicitly set

Any help appreciated,

-- 
Benito J. Gonzalez
Manager, Enterprise Web Application Development
Information Technology Department
University of California, Merced
Desk: 209.228.2974
Cell: 209.201.5052
Email: bgonzalez2 at ucmerced.edu