4 Replies Latest reply: Jan 30, 2014 3:29 AM by user8704911 RSS

    [11g] increasing efforts for select xmlserialize(content(...))) into ... from dual

    user8704911

      I wonder, that i don't get any feedback here (please see below).

      Can somebody first just confirm this observation?

       

      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      PL/SQL Release 11.2.0.3.0 - Production
      "CORE 11.2.0.3.0 Production"
      TNS for Linux: Version 11.2.0.3.0 - Production
      NLSRTL Version 11.2.0.3.0 - Production
      
      NLSRTL 11.2.0.3.0 Production
      Oracle Database 11g Enterprise Edition 11.2.0.3.0 64bit Production
      PL/SQL 11.2.0.3.0 Production
      TNS for Linux: 11.2.0.3.0 Production
      
      

       

      I recognized for my application, that there is an increasing efforts (in terms of time) for executing

      (in a pl/sql package procedure invoked by a BIU trigger)

       

      SELECT XMLSERIALIZE(CONTENT(p_xml_data)) INTO v_xml_clob FROM DUAL;
      
      
      
      
      
      

       

      Here is a little demo sqlplus script:

       

      create or replace package test_pkg
      as
             procedure check_xml(
               p_xml_data in out nocopy xmltype);
      end;
      /
      
      create or replace package body test_pkg
      as
             procedure log(
               p_txt in varchar2)
             is
               ts     TIMESTAMP WITH TIME ZONE := systimestamp;
             begin
               dbms_output.put_line(to_char(ts, 'hhmiss.FF3') || ':' || p_txt);
             end;
      
             procedure check_xml(
               p_xml_data in out nocopy xmltype)
             is
               v_xml_clob CLOB;
               v_len NUMBER;
             begin
               log('check_xml(): enter');
               SELECT XMLSERIALIZE(CONTENT(p_xml_data)) INTO v_xml_clob FROM DUAL;
               log('  serialized');
               v_len := dbms_lob.getlength(v_xml_clob);
               log('check_xml(): done - length = #'  || v_len);
             exception
             when others then
                 oerror_pkg.set_ora_error;
                 raise;
             end;
      end;
      /
      
      drop table test_table;
      
      create table test_table(
             tid number(19,0),
             data xmltype
      );
      /
      
      create or replace trigger BIU_TEST_TABLE
      before insert or update on test_Table
      for each row
      declare
      begin
         test_pkg.check_xml(:new.data);
      end;
      /
      
      
      
      insert into test_table(tid, data)
      select ctr_tab.ctr, '<root><node>' || ctr_tab.ctr || '</node></root>'
      from (  SELECT LEVEL ctr
             FROM dual
             CONNECT BY LEVEL <= 200) ctr_tab;
      
      
      
      
      
      

       

       

      The output is going like this

       

      021543.204:check_xml(): enter
      021543.204:  serialized
      021543.204:check_xml(): done - length = #32
      021543.206:check_xml(): enter
      021543.206:  serialized
      021543.206:check_xml(): done - length = #32
      021543.207:check_xml(): enter
      021543.208:  serialized
      021543.208:check_xml(): done - length = #32
      021543.209:check_xml(): enter
      021543.210:  serialized
      021543.210:check_xml(): done - length = #32
      021543.211:check_xml(): enter
      021543.212:  serialized
      021543.212:check_xml(): done - length = #32
      021543.214:check_xml(): enter
      021543.214:  serialized
      021543.214:check_xml(): done - length = #32
      [...]
      021549.625:check_xml(): enter
      021549.664:  serialized
      021549.665:check_xml(): done - length = #34
      021549.708:check_xml(): enter
      021549.746:  serialized
      021549.747:check_xml(): done - length = #34
      021549.791:check_xml(): enter
      021549.829:  serialized
      021549.830:check_xml(): done - length = #34
      021549.874:check_xml(): enter
      021549.912:  serialized
      021549.913:check_xml(): done - length = #34
      
      
      
      
      
      

       

       

      When i filter it with a little perl script to extract the efforts (xmlserialize / dbms_lob.getlength):

      0 / 0
      0 / 0
      1 / 0
      1 / 0
      1 / 0
      0 / 0
      0 / 0
      0 / 1
      1 / 0
      0 / 0
      0 / 1
      1 / 0
      0 / 0
      0 / 1
      1 / 0
      0 / 1
      0 / 1
      0 / 0
      0 / 1
      0 / 0
      0 / 1
      0 / 0
      1 / 0
      0 / 1
      0 / 0
      [...]
      31 / 1
      31 / 1
      32 / 1
      32 / 1
      32 / 1
      33 / 0
      33 / 0
      34 / 1
      34 / 0
      34 / 1
      34 / 0
      34 / 1
      34 / 1
      35 / 1
      35 / 1
      36 / 0
      36 / 1
      36 / 1
      37 / 1
      37 / 0
      37 / 1
      38 / 0
      38 / 0
      39 / 1
      38 / 1
      38 / 1
      38 / 1
      
      
      
      
      
      

       

      Unfortunately i can't easily change the way the insert from select is done (legacy code not under my control)

       

       

      Can someone tell me, if there is a way starting with the trigger to avoid those increasing efforts?

       

      - many thanks!

       

      best regards,

      Frank

        • 1. Re: [11g] increasing efforts for select xmlserialize(content(...))) into ... from dual
          user8704911

          Addition:

          Actually it is not related to the xmlserialize().

          If i replace

          SELECT XMLSERIALIZE(CONTENT(p_xml_data)) INTO v_xml_clob FROM DUAL;
          
          
          

          with

          v_xml_clob := p_xml_data.getclobval();
          
          
          

          ...i get different numbers, but the same trend: increasing efforts for the operation

           

          There is no such effect, if i use

          dbms_lock.sleep(...);
          
          

           

          Frank

          • 2. Re: [11g] increasing efforts for select xmlserialize(content(...))) into ... from dual
            user8704911

            When i do it differently - without the trigger, but take care about operation for the select - i don't have the problem:

            create or replace package test_pkg
            as
                   function check_xml(
                     p_xml_data in xmltype)
                   return xmltype;
            end;
            /
            
            create or replace package body test_pkg
            as
                   procedure log(
                     p_txt in varchar2)
                   is
                     ts     TIMESTAMP WITH TIME ZONE := systimestamp;
                   begin
                     -- null;
                     dbms_output.put_line(to_char(ts, 'hhmiss.FF3') || ':' || p_txt);
                   end;
                   
                   function check_xml(
                     p_xml_data in  xmltype)
                   return xmltype
                   is
                     v_xml_clob CLOB;
                     v_len NUMBER;
                   begin
                     log('check_xml(): enter');
                     SELECT XMLSERIALIZE(CONTENT(p_xml_data)) INTO v_xml_clob FROM DUAL;
                     log('  serialized');
                     v_len := dbms_lob.getlength(v_xml_clob);
                     log('check_xml(): done - length = #'  || v_len);
                     return p_xml_data;
                   exception
                   when others then
                       oerror_pkg.set_ora_error;
                       raise;
                   end;
            end;
            /
            
            drop table test_table;
            
            create table test_table(
                   tid number(19,0),
                   data xmltype
            );
               
            insert into test_table(tid, data)
            select ctr_tab.ctr, 
                  test_pkg.check_xml(
                     xmltype(
                       xmldata => '<root><node>' || ctr_tab.ctr || '</node></root>',
                       schema => null,
                       validated => 0,
                       wellformed => 1))
            from (  SELECT LEVEL ctr
                   FROM dual
                   CONNECT BY LEVEL <= 500) ctr_tab;
            
            • 3. Re: [11g] increasing efforts for select xmlserialize(content(...))) into ... from dual
              user8704911

              [ Addendum - 24.01.2014:

                This only worked for the test/demo program. It didn't work for my application.

                 For my application i really had to move the logic to the INSERT before the trigger

                 - similar to the 3rd posting for my monologue(!) here.

              ]

              A little step further:

              I also don't have the problem if i expand the code of the PROCEDURE test_pkg.check_xml(...) directly into the trigger

              - instead of invoking the PROCEDURE in the trigger:

               

              create or replace package test_pkg
              as
                     procedure log(
                       p_txt in varchar2);
              end;
              /
              
              create or replace package body test_pkg
              as
                     procedure log(
                       p_txt in varchar2)
                     is
                       ts     TIMESTAMP WITH TIME ZONE := systimestamp;
                     begin
                       dbms_output.put_line(to_char(ts, 'hhmiss.FF3') || ':' || p_txt);
                     end;
              end;
              /
              
              drop table test_table;
              
              
              create table test_table(  
                     tid number(19,0),
                     data xmltype
              );
              /
              
              
              create or replace trigger BIU_TEST_TABLE
              before insert or update on test_Table
              for each row
              declare
              begin  
                -- test_pkg.check_xml(:new.data);
              
                     declare
                       v_xml_clob CLOB;
                       v_len NUMBER;
                     begin
                       test_pkg.log('check_xml(): enter');
                       SELECT XMLSERIALIZE(CONTENT(:new.data)) INTO v_xml_clob FROM DUAL;
                       test_pkg.log('  serialized');
                       v_len := dbms_lob.getlength(v_xml_clob);
                      test_pkg.log('check_xml(): done - length = #'  || v_len);
                     exception
                     when others then
                         oerror_pkg.set_ora_error;
                         raise;
                     end;
                 end;  
              /
              
              insert into test_table(tid, data)
              select ctr_tab.ctr, '<root><node>' || ctr_tab.ctr || '</node></root>'
              from (  SELECT LEVEL ctr
                     FROM dual
                     CONNECT BY LEVEL <= 200) ctr_tab;
              
              -- rollback;
              
              
              
              
              
              
              
              
              
              
              

               

              That gives some hint.

              Does oracle have some problem / limit for invoking procedures (functions) from triggers?

              Or only if those use certain features?

              An issue about deterministic and re-entrance?

               

              Well, that boxes me into a corner.

              Because the single package procedure implements a functionality at a central place - i.e. a single central place to extend it or to fix it.

              If i now have to expand its content (like a macro) into the triggers of the respective table i am in trouble.

              Because those tables (and their triggers) are dynamically generated by a compiler tool of a c++ client.

              This means for extension and fixes i need to change, test, deliver and deploy a list of c++ client processes :-(

               

              Is there any way around?

               

              How can i inform oracle that the invocation of a PL/SQL procedure is functionally identically with expanding the PL/SQL functions code into the trigger?

               

              rgds,

              Frank

              • 4. Re: [11g] increasing efforts for select xmlserialize(content(...))) into ... from dual
                user8704911

                Hello,

                can somebody please comment this issue?

                At least verify/confirm, that this effect exists? (and for which oracle version?)

                - many thanks!

                best regards,

                Frank