Extremely slow bundle update under Oracle 11

[Andrey Grin <an...@gmail.com>]

Hello,

We are measuring performance of our application on the larger database
under big load. Jackrabbit tables are in the same database as application
tables. We use OraclePersistenceManager.
Oracle: 11,2,0,1,0
Jackrabbit: 2.2.10

According to our measurements the following update takes about 50% of all
time spent on the database side:

update WSP_BUNDLE set BUNDLE_DATA = :1
where
 NODE_ID = :2

We are having hundreds of different queries during the test, most of them
against the tables that are much larger than wsp_bundle. wsp_bundle has
about 230k records. This update takes 20 times more time than the 2nd
slowest query

At the same time similar SELECT is about 150 times faster than update:
select BUNDLE_DATA
from
 WSP_BUNDLE where NODE_ID = :1


Below I copied information provided by query analyzer. Most time is spent
on db sequential read.

This issue seems to be somehow related, but update to 2.2.10 didn't change
anything
https://issues.apache.org/jira/browse/JCR-2892<https://issues.apache.org/jira/browse/JCR-2892?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel>

Does anyone encounter the same problem? Are there any configuration level
options that may resolve it?

Thanks.
Andrey Grin

call     count       cpu    elapsed       disk      query    current
 rows
------- ------  -------- ---------- ---------- ---------- ----------
 ----------
Parse      258      0.00       0.00          0          0          0
    0
Execute    400     30.99     358.72     143672    1659282    3283976
  400
Fetch        0      0.00       0.00          0          0          0
    0
------- ------  -------- ---------- ---------- ---------- ----------
 ----------
total      658     30.99     358.73     143672    1659282    3283976
  400

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 87

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  UPDATE  WSP_BUNDLE (cr=7684 pr=718 pw=717 time=0 us)
      1   INDEX UNIQUE SCAN WSP_BUNDLE_IDX (cr=3 pr=0 pw=0 time=0 us cost=2
size=155 card=1)(object id 76604)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total
Waited
  ----------------------------------------   Waited  ----------
 ------------
  db file sequential read                    143472        5.54
 308.85
  direct path write                             403        5.67
 6.18
  SQL*Net more data from client              144428        0.47
 7.91
  direct path read                              200        0.02
 0.07
  Disk file operations I/O                        1        0.00
 0.00
  SQL*Net message to client                     400        0.00
 0.00
  SQL*Net message from client                   400        0.39
 0.84
  asynch descriptor resize                   141911        0.06
 1.17
  buffer busy waits                               4        0.00
 0.00
  log file sync                                   5        0.00
 0.00
  log file switch (checkpoint incomplete)         1        0.05
 0.05
  log file switch (private strand flush incomplete)
                                                  1        0.26
 0.26