Slow XML Query Performance When Using TIMESTAMP Datatype In A Nested XML View
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.2Information 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