Database Software

I have a table built that contains and xmltype column. I am noticing that for many of my xml's that extractvalue is taking a long time to complete. The average size of my xml's is about 12K, which the extractvalue works pretty quickly against, but I have some larger xml's around 10MB that takes forever. Something I noticed is that when I select the entire xml from the table, there are alot of logical reads, but when I pull the xmltype.getclobval of the same xml it is instant with hardly any logical reads. I am unable to post my actual xml, but I created a test case by making an xml document out of dba_objects. The test xml contains about 50,000 object entry that is contained in a single xml.

create table test (x xmltype);

SQL> select dbms_lob.getlength(xmltype.getclobval(x)) from test;

DBMS_LOB.GETLENGTH(XMLTYPE.GETCLOBVAL(X))
-----------------------------------------
                                 56775918


SQL> set autotrace traceonly

SQL> select x from test;

Elapsed: 00:01:25.81

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    87 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST |     1 |    87 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     304205  consistent gets
      63730  physical reads
          0  redo size
   71659353  bytes sent via SQL*Net to client
   14365304  bytes received via SQL*Net from client
      56781  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


SQL> select xmltype.getclobval(x) from test;

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    87 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST |     1 |    87 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          1  physical reads
          0  redo size
       1011  bytes sent via SQL*Net to client
        649  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Although this test xml I am using doesn't have a schema, the actual xml I have does. A single xml can contain up to about 15 different schemas. Below is a snippett of what my test xml looks like:

<OBJECTS>
<OWNER>SYS</OWNER>
<OBJECT_NAME>ICOL$</OBJECT_NAME>
<SUBOBJECT_NAME></SUBOBJECT_NAME>
<OBJECT_ID>20</OBJECT_ID>
<DATA_OBJECT_ID>2</DATA_OBJECT_ID>
<OBJECT_TYPE>TABLE</OBJECT_TYPE>
<CREATED>2005-06-30</CREATED>
<LAST_DDL_TIME>2005-06-30</LAST_DDL_TIME>
<TIMESTAMP>2005-06-30:19:10:16</TIMESTAMP>
<STATUS>VALID</STATUS>
<TEMPORARY>N</TEMPORARY>
<GENERATED>N</GENERATED>
<SECONDARY>N</SECONDARY>
</OBJECTS>


I guess the question I have is why does it take so long to select the xml out of the table as an xml compared to extracting it as a clob. I am hoping to understand what it does to the clob to make it an xml. Does it have to build a DTD or something. I done tracing against both queries. In the trace log, it shows 8 logical reads for both queries, which is different that what autotrace says. Thanks for any help!