RE: AWR interpretation

From: Storey, Robert (DCSO) <"Storey,>
Date: Fri, 1 Nov 2013 15:03:21 +0000
Message-ID: <FE4C2B093843BB4B873D754E5E0BE4DB6CCDB142_at_DCSOSVMS02.dcso.org>

So if I save the commit to the end, at least this will clear up the 25K commits a minute and hopefully translate to a faster import...

One could hope.

-----Original Message-----
From: andyklock_at_gmail.com [mailto:andyklock_at_gmail.com] On Behalf Of Andy Klock Sent: Friday, November 01, 2013 10:00 AM To: Storey, Robert (DCSO)
Cc: David Barbour; Andrew Kerber; Oracle L Subject: Re: AWR interpretation

On Fri, Nov 1, 2013 at 10:37 AM, Storey, Robert (DCSO) <RStorey_at_dcso.nashville.org> wrote:
> It was made mention that LOB data will ignore the array COMMIT
> settings and will do a line by line commit. Does anyone know if this
> works the same for XMLTYPE columns?
>

That's a good question. Prior to 11.2.0.2, XMLTYPE (by default) were stored as CLOBs, but now default to faster, better BINARY XML which under the hood are still LOBs but now stored as SecureFiles.

See MOS note:

Change in default storage model of XMLType to BINARY XML in 11.2.0.2.0 (Doc ID 1207893.1)

So to answer your question, since you are coming from 9i it's safe to assume that these are clobs and also pretty safe to assume that the row commits will also happen with BINARY XMLs. COMMIT=Y is useful when you can restart imports, but if your process for this import is to truncate/re-import if there are any issues, then COMMIT=Y is doing you no favors. Switch back to the default, COMMIT=N and the commit will happen at the end.

Once you are all in 11.2 you can embrace Data Pump :)

Andy K

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Nov 01 2013 - 16:03:21 CET