Re: Problems with BLOB.

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 18 Jan 2000 09:10:23 -0500
Message-ID: <40t88sgakjmap86op48q2s53tr07h55d3p@4ax.com>

A copy of this was sent to "Tomas" <tomas_at_web.es> (if that email address didn't require changing) On Tue, 18 Jan 2000 13:37:21 +0100, you wrote:

>I have a question about BLOBs in Oracle.
>
>I want to store a big file (about 30MB) in a blob field. The problem is that
>is spend more than 20 mitues to do it.
>Is it normal?? I hope no, because it is awful.
>
>If anyone have done these before I could send my code in order to could
>watch the posibles troubles I have.
>
>Thanks a lot.

20 minutes -- too long. Here is a quick and dirty untuned example:

ops$tkyte_at_8.0> create table demo

  2  ( id            int primary key,
  3    theBlob       blob

  4 )
  5 /

Table created.

Elapsed: 00:00:00.41
ops$tkyte_at_8.0>
ops$tkyte_at_8.0> create or replace directory my_files as '/tmp';

Directory created.

Elapsed: 00:00:00.08
ops$tkyte_at_8.0>
ops$tkyte_at_8.0> declare

  2          l_blob  blob;
  3          l_bfile bfile;
  4  begin
  5          insert into demo values ( 1, empty_blob() )
  6          returning theBlob into l_blob;
  7  
  7          l_bfile := bfilename( 'MY_FILES', 'binary.dat' );
  8          dbms_lob.fileopen( l_bfile );
  9  
  9          dbms_lob.loadfromfile( l_blob, l_bfile,
 10                                                     dbms_lob.getlength(

l_bfile ) );
 11
 11 dbms_lob.fileclose( l_bfile );  12 end;
 13 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:17.53
ops$tkyte_at_8.0>
ops$tkyte_at_8.0> select dbms_lob.getlength(theBlob) from demo   2 /

DBMS_LOB.GETLENGTH(THEBLOB)

---

                   29846552

Elapsed: 00:00:00.01
ops$tkyte_at_8.0>
ops$tkyte_at_8.0> set echo off

Check the size of your online redo log files -- you probably have lots of "checkpoint not complete, cannot allocate new log" messages in your alert.log indicating undersized redo logs.

How are you inserting the blob?

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Jan 18 2000 - 08:10:23 CST