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

    dbms_lob.writeappend is slow

    936666

      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

          Any suggestions ..!!

          • 2. Re: dbms_lob.writeappend is slow
            936666

            Suggestions ..!!

            • 3. Re: dbms_lob.writeappend is slow
              Jonathan Lewis

              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

                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

                  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

                    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
                      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

                        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