8 Replies Latest reply: Dec 27, 2013 8:29 AM by Shivendra Narain Nirala RSS

dbms_lob.writeappend is slow

936666 Newbie
Currently Being Moderated

I am having a table with number , float, double of 100 columns

need to read the table value and return as blob

so i have used utl_raw.CAST_FROM_

something like this

i have used loop to read each column data type and used bind variable to insert

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

TYPE varColtypelist is varray(100) of NUMBER(20);

collist varColtypelist;

TYPE varColLenlist is varray(100) of number(20);

byteLenList varColLenlist;

 

 

  Select  CASE WHEN data_type ='BINARY_FLOAT' THEN 1

                  WHEN data_type ='BINARY_DOUBLE' THEN 2

                  WHEN data_type ='NUMBER' THEN 3 END  bulk collect into  collist

        from all_tab_columns where table_name=UPPER(Table_Name)

        ORDER BY COLUMN_ID ;

 

 

v_cursor := DBMS_SQL.OPEN_CURSOR;

statment :='Select all columns from mytable order by c1 desc ' and rownum between 5000 to 10000

 

 

execute immediate 'Select byte_info from my_byte_info where id=1'

bulk collect into  byteLenList ;

 

 

v_cursor := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.parse (v_cursor, statment, DBMS_SQL.native);  

 

FOR col_ind IN 1 .. 100--(100 is 100 columns in that table)

LOOP

  data_type := collist(col_ind);  

  if data_type =1 THEN

    DBMS_SQL.define_column  (v_cursor, col_ind, flid);

  ELSIF  data_type =2 THEN

    DBMS_SQL.define_column  (v_cursor, col_ind, dblid);

  ELSIF  data_type =3 THEN

  DBMS_SQL.define_column  (v_cursor, col_ind, nid);

  END IF;

END LOOP;

 

 

dumy := DBMS_SQL.Execute (v_cursor);

LOOP

EXIT WHEN DBMS_SQL.FETCH_ROWS (v_cursor) = 0;

FOR i IN 1..l_max LOOP 

              data_type := collist(i);  

              ncollength := byteLenList(i);         

  

  

  if data_type =1 THEN

                  BEGIN

                  DBMS_SQL.column_value (v_cursor, i, flid);

                    value :=  utl_raw.cast_from_BINARY_FLOAT( flid);

                  END;       

          end if;

  

  if data_type =2 THEN

                  BEGIN

                  DBMS_SQL.column_value (v_cursor, i, dblid);

                    value :=  utl_raw.cast_from_BINARY_DOUBLE( dblid);

                  END;       

          end if;

  

  if data_type =3 THEN

                  BEGIN

                  DBMS_SQL.column_value (v_cursor, i, nid);

                    value :=  utl_raw.CAST_FROM_BINARY_INTEGER( nid);

                  END;       

          end if;

 

 

IF nNewRecord = 0 then       

  buffer1 := utl_raw.cast_to_varchar2(dbms_lob.substr(value));

  dbms_lob.writeappend( l_out,ncollength,buffer1 );         

        End if;

 

 

   IF nNewRecord = 1 then

          nNewRecord := 0;       

          Select (utl_raw.cast_to_varchar2(dbms_lob.substr(var))) into l_out from dual;         

        end if;

 

end loop; 

END LOOP;

 

 

DBMS_SQL.CLOSE_CURSOR (v_cursor);

 

 

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

it is something like

SELECT  utl_raw.cast_to_varchar2(dbms_lob.substr(utl_raw.CAST_FROM_BINARY_INTEGER(int_column))) from mytable

SELECT  utl_raw.cast_to_varchar2(dbms_lob.substr(utl_raw.cast_from_BINARY_DOUBLE(double_coulmn))) from mytable

SELECT  utl_raw.cast_to_varchar2(dbms_lob.substr(utl_raw.cast_from_BINARY_FLOAT(float_column))) from mytable

SELECT  utl_raw.cast_to_varchar2(dbms_lob.substr(utl_raw.CAST_FROM_BINARY_INTEGER(int_column))) from mytable

SELECT  utl_raw.cast_to_varchar2(dbms_lob.substr(utl_raw.cast_from_BINARY_DOUBLE(double_coulmn))) from mytable

SELECT  utl_raw.cast_to_varchar2(dbms_lob.substr(utl_raw.cast_from_BINARY_FLOAT(float_column))) from mytable

SELECT  utl_raw.cast_to_varchar2(dbms_lob.substr(utl_raw.cast_from_BINARY_FLOAT(float_column))) from mytable

SELECT  utl_raw.cast_to_varchar2(dbms_lob.substr(utl_raw.CAST_FROM_BINARY_INTEGER(int_column))) from mytable

SELECT  utl_raw.cast_to_varchar2(dbms_lob.substr(utl_raw.cast_from_BINARY_FLOAT(float_column))) from mytable

SELECT  utl_raw.cast_to_varchar2(dbms_lob.substr(utl_raw.CAST_FROM_BINARY_INTEGER(int_column))) from mytable

SELECT  utl_raw.cast_to_varchar2(dbms_lob.substr(utl_raw.cast_from_BINARY_DOUBLE(double_coulmn))) from mytable

SELECT  utl_raw.cast_to_varchar2(dbms_lob.substr(utl_raw.cast_from_BINARY_FLOAT(float_column))) from mytable

 

 

but it is time consuming taking more time to write and apennd

can we do it in alternate way.

  • 1. Re: dbms_lob.writeappend is slow
    936666 Newbie
    Currently Being Moderated

    Any suggestions ..!!

  • 2. Re: dbms_lob.writeappend is slow
    936666 Newbie
    Currently Being Moderated

    Suggestions ..!!

  • 3. Re: dbms_lob.writeappend is slow
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    I find it hard to understand what your code is trying to do, but I think you are selecting one row at a time from a table, then creating a lob by concatenating each column from the select list to a temporary lob so that you can return the entire result set as a single lob. If that's what you're attempting I think you win the 2013 award for the least efficient mechanism for extracting data from the database.

     

    Two thoughts:

    you haven't told us anything about the session stats (v$sesstat) or session time (v$session_event) that your operations records

    you haven't told use how you define the lob

     

    Both details are critical.

     

    A shot in the dark:  dbms_lob.write_append is very expensive; each row in your table has 100 columns which might be binary double. A pl/sql varchar() can be up to 32767 bytes. To reduce the calls to dbms_lob.write_append, build each row with simple character concatenation, and only use writeappend to add each row to the final lob ?  Also, I find it hard to understand why you are not simply selecting a single concatenated result from the table anyway.  (Prepare the select statement by examination of the data dictionary definition of the table, if necessary).

     

    Regards

    Jonathan Lewis

  • 4. Re: dbms_lob.writeappend is slow
    rp0428 Guru
    Currently Being Moderated

    I am having a table with number , float, double of 100 columns

    need to read the table value and return as blob

    There is NO such thing as 'table value'.

     

    Start over and tell us, in English, what BUSINESS PROBLEM you are trying to solve.

     

    Provide a small amount (2 rows of 3 columns each) of sample data (in the form of INSERT statements, the DDL for a sample table (again - 3 columns) and show us what the result should be: 2 rows each having 1 CLOB, 1 row having 1 CLOB, etc.

     

    Also - if all you do is concatenate multiple values together how do you expect the result to be useful? No one will be able to tell where one value begins and the next ends.

     

    Tell us how many columns were concatenated to produce this value:

    123456789

    Did I use these TWO values:

    1234, 56789

    Or did I use these TWO values:

    123456, 789

    Maybe I used FOUR values

    12, 34, 56, 789

  • 5. Re: dbms_lob.writeappend is slow
    936666 Newbie
    Currently Being Moderated

    Thanks Jonathan Lewis,

    My code is forming the BLOB (chunk ) from a table which is having 100 columns of datatypes double ,number, float.

    My code is already having something as you suggested

    buffer2 is varchar2 datatype

    buffer2 :=buffer2||buffer1;

    this code is inside the first loop.

     

    .

    .

    .

    IF nNewRecord = 1 then

              nNewRecord := 0;      

              Select (utl_raw.cast_to_varchar2(dbms_lob.substr(var))) into l_out from dual;        

            end if;

     

    end loop;

    buffer2 :=buffer2||buffer1;

    END LOOP;

    dbms_lob.write(l_out,len,l_f_len+1,buffer2);

     

    but still it is slow

    to fetch 1000 rows from 1 million record is slow.

     

    Thanks!

  • 6. Re: dbms_lob.writeappend is slow
    936666 Newbie
    Currently Being Moderated

    Jonathan Lewis wrote:

     

    Also, I find it hard to understand why you are not simply selecting a single concatenated result from the table anyway.  (Prepare the select statement by examination of the data dictionary definition of the table, if necessary).

     

    Regards

    Jonathan Lewis

    If you select a single concotenated result from table then how will you differentiate b/w datatypes of each column and how to convert them to blob.

    Thanks!

  • 7. Re: dbms_lob.writeappend is slow
    rp0428 Guru
    Currently Being Moderated
    If you select a single concotenated result from table then how will you differentiate b/w datatypes of each column and how to convert them to blob.

    Well that is the SAME question I ask you a month ago:

    Also - if all you do is concatenate multiple values together how do you expect the result to be useful? No one will be able to tell where one value begins and the next ends.

    I also ask you to do this:

    Start over and tell us, in English, what BUSINESS PROBLEM you are trying to solve.

     

    Provide a small amount (2 rows of 3 columns each) of sample data (in the form of INSERT statements, the DDL for a sample table (again - 3 columns) and show us what the result should be: 2 rows each having 1 CLOB, 1 row having 1 CLOB, etc.

    What you say you are doing doesn't make much sense. That is why you were ask to explain it more fully. We can't help you if we don't know what you are really trying to do.

     

    If you just string multiple VARCHAR2 values together that are different lengths without using some kind of delimiter it won't be meaningful.

     

    And that seems to be just the problem you are asking about in your other thread:

    https://community.oracle.com/thread/2614252?start=15&tstart=0

     

    You caused the problem - we are asking you why are you doing that?

  • 8. Re: dbms_lob.writeappend is slow
    Shivendra Narain Nirala Journeyer
    Currently Being Moderated

    Hi

     

    Please share, which version you are using and what's the wait event when you run this query ? Apart from that , also confirm whether your database has been upgraded or not ? if yes , then from which version to as of now ?

     

    Regards

     

    Shivendra Narain Nirala

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points