Xml Query Executed in A 'EXECUTE IMMEDIATE ... BULK COLLECT' is Much Slower than the Direct Query (Doc ID 1594673.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 to 11.2.0.3 [Release 11.2]
Information in this document applies to any platform.

Symptoms

A query which is extracting value() from xmltype(bfilename()) executed as  
EXECUTE IMMEDIATE ... BULK COLLECT pl/sql statement, in Oracle Server 11.2.0.3 +
patch 12940637, takes 3 time the time to execute the direct query.

Actually the difference in sql*plus between the output returned by the direct query
and the bulk collect is only about 3 seconds:
Query Elapsed: 00:00:12.29
Bulk collect Elapsed: 00:00:15.40

However analyzing the sqltrace and tkprof's output the difference is wider.

tkprof's outputs extract show:
******************************* DIRECT QUERY **********************************
select rownum, value(tietue_taulu) as xmldoc from table(
           xmlsequence(
               XMLType(
                   bfilename('ANA_ASI_FILES_TYO_LOC', 'ASI_ANALYYSI.xml'),
                   nls_charset_id('WE8ISO8859P1')
              ).extract('//TIETUEET','xmlns="http://www.vero.fi/Asiakas/XMLSchema/Analyysi/v1"')
              .extract('//TIETUE','xmlns="http://www.vero.fi/Asiakas/XMLSchema/Analyysi/v1"')
           )
       ) tietue_taulu

call     count       cpu    elapsed       disk      query    current      rows
------- ------  -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.06       0.07          6        996          0         0
Execute      1      4.84       5.81         40      14660      79192         0
Fetch      243      0.67       0.69          0          0          0      3642
------- ------  -------- ---------- ---------- ---------- ---------- ----------
total      245      5.57       6.58         46      15656      79192      3642



****************************** BULK COLLECT ********************************
select rownum, value(tietue_taulu) as xmldoc from table(
          xmlsequence(
              XMLType(
                  bfilename('ANA_ASI_FILES_TYO_LOC', 'ASI_ANALYYSI.xml'),
                  nls_charset_id('WE8ISO8859P1')
              ).extract('//TIETUEET','xmlns="http://www.vero.fi/Asiakas/XMLSchema/Analyysi/v1"')
              .extract('//TIETUE','xmlns="http://www.vero.fi/Asiakas/XMLSchema/Analyysi/v1"')
          )
      ) tietue_taulu

call     count       cpu    elapsed       disk      query    current      rows
------- ------  -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.02       0.01          0        210          0         0
Execute      1      4.72       4.80          0      14651      79200         0
Fetch        1     10.17      10.35          0          0          0      3642
------- ------  -------- ---------- ---------- ---------- ---------- ----------
total        3     14.91      15.17          0      14861      79200      3642

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms