7 Replies Latest reply: Apr 11, 2008 9:10 AM by mdrake-Oracle RSS

    insert in xmltype column: extremely slow

    432747
      Hi all,

      I've got following table rt_message_queue

      sequence number
      message_body xmltype not null
      time_entered timestamp
      processing_busy char
      entered_by varchar(30)

      I've got an anonymous pl/sql block that inserts the contents of an XML file into the table:



      declare
      v_xml xmltype;
      --
      begin
      v_xml := xmltype('###BODY OF XML###');

      insert into rt_message_queue
      ( message_body
      )
      values
      ( v_xml );
      end;

      I'm sorry, I can't show you the actual xml contents. Its size is 4.63 kb and has approx 112 lines and +4000 charachters in it. So it certainly isn't huge.
      Howver the insert statement takes forever and I have no clue why.

      My db version is 10.2.0.3.0

      Any help to speed this up is welcome, I have no idea why it takes so long to get this bit of xml inserted.
        • 1. Re: insert in xmltype column: extremely slow
          Marco Gralike
          It is a little bit difficult to answer your question, because I think that we lack some info.

          First of all. If you use XMLType (look it up in the reference guide!), you will see that there is at least a check on XML wellformedness. You trigger this action twice. I have no idea if this can be avoided, probably not, but be aware of the mechanics, because this will cost memory and CPU resources.
          1) v_xml := xmltype('###BODY OF XML###');
          2) and during insert into the column message_body column
          To give you an idea how fast it can be. I have a test set off 7500 records that range in size from 10k up to 1.5 Mb. These documents are initially inserted via a .Net / ODP program (a SAX parser written in C#) into the database directly into a table with a XMLType column (based on CLOB storage - this is the default storage model).

          The loading process is done within 5 a 6 minutes. Translated in docs/sec: 35
          If I do the worst possible, for instance, full blown DOM XML Schema validation off these documents than in my simple lab environment, my speed regarding this validation, slows down to 1 document / second.

          Without extra information about your environment, stuff that counts and should be carefully dimensioned:

          PGA_AGGREGATED_TARGET
          JAVA_POOL_SIZE
          LARGE_POOL_SIZE (shared server)
          BUFFER_CACHE / SHARED_POOL etc SGA settings.

          Do yourself a favour and test these settings by setting it manually.

          I have a SGA/PGA environment off 1 Gb of memory.
          • 2. Re: insert in xmltype column: extremely slow
            Jason_(A_Non)
            Could this be a possible design to increase speed by eliminating the wellformed check at least once? I didn't realize it did it a second time during the actual INSERT so that is good to know. I haven't tested this theory/code but seems plausible.
            declare
              v_xml clob;
            --
            begin
              v_xml := '###BODY OF XML###';
            
              insert into rt_message_queue
               ( message_body)
              values
               ( XMLPARSE(DOCUMENT v_xml WELLFORMED));
            end;
            Of course this depends upon trusting that the incoming XML is at least well formed. I'm not sure what happens when the XML is not well formed because I don't have that luxury to go that route.
            • 3. Re: insert in xmltype column: extremely slow
              Marco Gralike
              I would expect that an insert in an XMLType column off an XML document would fail with an Oracle Error message, like ORA-31011, if it is not wellformed.
              • 4. Re: insert in xmltype column: extremely slow
                ascheffer
                SQL> select xmltype( 'this is not wellformed' ).getstringval() from dual;
                select xmltype( 'this is not wellformed' ).getstringval() from dual
                       *
                ERROR at line 1:
                ORA-31011: XML parsing failed
                ORA-19202: Error occurred in XML processing
                LPX-00210: expected '<' instead of 't'
                Error at line 1
                ORA-06512: at "SYS.XMLTYPE", line 301
                ORA-06512: at line 1
                
                
                SQL> select xmltype( 'this is not wellformed', null, 1, 1 ).getstringval() from dual;
                
                XMLTYPE('THISISNOTWELLFORMED',NULL,1,1).GETSTRINGVAL()
                --------------------------------------------------------------------------------
                this is not wellformed
                
                SQL> 
                Anton
                • 5. Re: insert in xmltype column: extremely slow
                  Marco Gralike
                  SQL> create table weldone
                    2  (ora xmltype)
                    3  ;
                  
                  Table created.
                  
                  SQL> insert into weldone
                    2  values
                    3  ('<root>hmmm</root><extra>')
                    4  ;
                  insert into weldone
                              *
                  ERROR at line 1:
                  ORA-31011: XML parsing failed
                  ORA-19202: Error occurred in XML processing
                  LPX-00245: extra data after end of document
                  Error at line 1
                  
                  
                  SQL> insert into weldone
                    2  values
                    3  ('<root>hmmm</root>');
                  
                  1 row created.
                  • 6. Re: insert in xmltype column: extremely slow
                    Marco Gralike
                    Anton (he is standing right next to me / he is a colleague) had a more valid point regarding the following...
                    SQL> create table weldone
                      2  (ora xmltype)
                      3  ;
                    
                    Table created.
                    
                    SQL> insert into weldone
                      2  values
                      3  (xmltype( 'this is not wellformed', null, 1, 1 ));
                    
                    1 row created.
                    
                    SQL> select * from weldone;
                    
                    ORA
                    -------------------------------------------------------------------
                    this is not wellformed
                    
                    1 row selected.
                    Message was edited by:
                    Marco Gralike
                    • 7. Re: insert in xmltype column: extremely slow
                      mdrake-Oracle
                      If you tell us it's wellformed, and the underlying storage is CLOB, we will simply stream the bytes into the CLOB without parsing.

                      However if you tell us it's well formed and it's not well formed and some subsequent operation on the table causes an ORA-00600 please do not bother callling support.

                      If it crashes the server or causes an ORA-03113 we Might take a look, if it causes an trapped internal error then you get what you deserver!!!