Performance of DBMS_LOB.WRITE

We have a 3rd party application [Parametric Technology Windchill] where a new module has been implemented which copies in files into the database using DBMS_LOB.WRITE

The LOBSEGMENT has a CHUNKsize of 32K and the Database BlockSize is 16K.
There are high waits on "direct path write". Queries on V$SESSION_WAIT show

       SID EVENT

---------- ----------------------------------------------------------------
P1TEXT                                                                   P1


---------------------------------------------------------------- ----------

P2TEXT                                                                   P2


---------------------------------------------------------------- ----------

P3TEXT                                                                   P3


---------------------------------------------------------------- ----------

        25 direct path write
file number                                                               7
first dba                                                             28684
block cnt                                                                 2

As the table and lobsegment have been pre-built by the application, I cannot now modify it [in any case CHUNK is 32K already]. It is set to LOGGING, CACHE is NO and PCTVERSION is 10.

The file system is fast enough. Copying in the same 1GB file with the unix "cp" command took 1.5minutes. The DBMS_LOB.WRITE operation for the file took about 4 hours.

My only finding currently is that the size of the write, at 2 16K blocks, is too small.

Hemant K Chitale
http://hkchital.tripod.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hemant K Chitale
  INET: hkchital_at_singnet.com.sg

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).