Slow XML Query Performance When Using TIMESTAMP Datatype In A Nested XML View (Doc ID 1352763.1)

Last updated on AUGUST 26, 2011

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.3 to 10.2.0.5.0 - Release: 10.2 to 10.2
Information in this document applies to any platform.

Symptoms

In 10.2.0.x, XML query performance is slow when using a TIMESTAMP datatype in a nested XML view table. The explain plan shows "COLLECTION ITERATOR PICKLER FETCH XMLSEQUENCEFROMXMLTYPE" when the performance is slow:

-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 |287K| 26 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 8168 |287K| 26 (0)| 00:00:01 |
| 2 | VIEW | DETAILED_METADATA | 1 |34 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | METADATA | 1 | 4265 | 2 (0)| 00:00:01 |
| 4 | COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE | | | | |
-------------------------------------------------------------------------------------------------------------

The same query runs fine when the TIMESTAMP column is removed.
This problem does not occur in 11g.

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