如何提高PHP OCI-Lob :: save的性能

Question

I have a script which imports a lot of text files, processes them and then saves the data to an Oracle database. It also saves a copy of the raw data file into a BLOB column. When importing 72 files, each with around 40,000 lines of data in them (~ 3.5Mb each), the script takes well over a minute to execute.

After using xdebug to profile the script, it appears that calls to OCI-Lob::save are taking the most time (~90%). Here is the PHP code I'm using to save the BLOB - pretty standard I think:

$this->_db->setSQL('INSERT INTO IMPORTED_FILES (FILE_BLOB, FILE_NAME, LAST_MODIFIED_DATE, IMPORTED_BY, IMPORTED_DATE) VALUES
                        (EMPTY_BLOB(), :fileName, :lastMod, :userId, SYSDATE) RETURNING FILE_BLOB INTO :fileBlob');
    $blob = \oci_new_descriptor($this->_db->con);
    $this->_db->bind(":fileBlob",$blob,-1,OCI_B_BLOB);
    $this->_db->bind(':fileName',$this->_name);
    $this->_db->bind(':lastMod',$this->_lastModifiedDate);
    $this->_db->bind(':userId',$_SESSION['userid']);
    $this->_db->execute(false);
    $blob->save($this->_contents);

And the table definition:

CREATE TABLE IMPORTED_FILES
  (
    "FILE_ID" NUMBER(*,0) NOT NULL ENABLE,
    "FILE_BLOB" BLOB NOT NULL ENABLE,
    "FILE_NAME"          VARCHAR2(255 CHAR) NOT NULL ENABLE,
    "LAST_MODIFIED_DATE" VARCHAR2(255 CHAR) NOT NULL ENABLE,
    "IMPORTED_BY"        VARCHAR2(255 CHAR) NOT NULL ENABLE,
    "IMPORTED_DATE" DATE NOT NULL ENABLE,
    "REPORT" CLOB,
    CONSTRAINT "IMPORTED_FILES_PK" PRIMARY KEY ("FILE_ID") USING INDEX
    TABLESPACE "DATA_INDEX" ENABLE
  )

Is there any way to make saving BLOBs faster?

UPDATE

Not sure if this helps, but I found the following window in SQL Developer, which shows some parameters concerning the BLOB field. Can any of these settings be changed to make it more efficient?

Answer 1

(This is more of an extended comment than an answer.)

The first step, and often the most difficult one, is to find exactly what is slow. The code below runs only in the database and will tell you the best possible case for writing the data.

drop table test1;
create table test1(a clob);

--Time to write 72 3.5MB CLOBs.
--On my old desktop this runs in about 15 seconds.
declare
    v_clob clob := 'A';
begin
    --Create a 3.5MB LOB.
    for i in 1 .. 350 loop
        dbms_lob.append(v_clob, lpad('a',10000, 'a'));
    end loop;

    for i in 1 .. 72 loop
        insert into test1 values (v_clob);
    end loop;
    commit;
end;
/

If that code runs in significantly less than a minute the database likely isn't the problem - look at PHP more closely or look at the network. If that code runs in just under a minute, drill down further and find exactly what the statement is waiting on. A query like this might be a good start:

select event, v$active_session_history.*
from v$active_Session_history
where sql_id = 'ghwdpz6v9k2cj'
order by sample_time desc;