Update To Xmltype Column Very Slow When filesystemio_options Other Than DIRECTIO (Doc ID 1080661.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.4 to 10.2.0.4 [Release 10.2]
Information in this document applies to any platform.
***Checked for relevance on 04-Sep-2013***

Symptoms


Working on a table with XMLType column

SQL> desc XML_TABLE

Name Null? Type
----------------------------------------- -------- ----------------------------

ID NOT NULL NUMBER(38)
XML_COLUMN SYS.XMLTYPE
CREATED_BY NUMBER(38)
CREATED_DATE DATE
LAST_UPDATED_BY NUMBER(38)
LAST_UPDATED_DATE DATE



This update:

UPDATE XML_TABLE T
SET T.XML_COLUMN = UPDATEXML(T.FAIL_DIST_XML,'/workscopeDistribution/fleetId/text()',2468)
WHERE T.ID = 20

Updates 400 rows out of 140,000 rows in a table.

In a database instance it is taking 84 seconds. In another database instance with the exact same table it takes 5 seconds.

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