RE: AWR interpretation

From: Storey, Robert (DCSO) <"Storey,>
Date: Fri, 1 Nov 2013 14:37:23 +0000
Message-ID: <FE4C2B093843BB4B873D754E5E0BE4DB6CCDB115_at_DCSOSVMS02.dcso.org>

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?

I'm trying some things this morning to reduce the number of commits per minute, which ADDM is telling me 25,488. I found out that even though I was not doing the indexes, somewhere in all the mess I had 2 indexes on the table. So, I've recreated the table with no indexes and will try that again.

ADDM also said that I/O performance on redo logs was bad. 1.1m per second on reads and 2.2m per second on writes. I am removing the SAN copies of the redo logs and just leaving the copies that reside on the internal drive.

But, if the XMLTYPE is causing a row by row commit, then I'm not seeing any good way to improve the performance of the import.

From: David Barbour [mailto:david.barbour1_at_gmail.com] Sent: Wednesday, October 30, 2013 4:53 PM To: Andy Klock
Cc: Storey, Robert (DCSO); Andrew Kerber; Oracle L Subject: Re: AWR interpretation

I've had some success turning off archiving as it reduces contention on the disk as well ensuring availability since I don't change back into one that's still archiving and hence not available. I did mention creating the indexes separately via script with nologging and parallel.

On Wed, Oct 30, 2013 at 3:28 PM, Andy Klock <andy_at_oracledepot.com<mailto:andy_at_oracledepot.com>> wrote: On Wed, Oct 30, 2013 at 4:12 PM, Storey, Robert (DCSO) <RStorey_at_dcso.nashville.org<mailto:RStorey_at_dcso.nashville.org>> wrote:
> No, its not portioned.
>
> I'm going to change the buffer size to reduce the commits and David referenced doing the import with the db in noarchive mode. I could do the import, then configure the setup and once I've verified that all is well, place the db in archivelog mode before going live on it.

Running a database in "noarchivelog" mode doesn't reduce the amount of REDO required for your import. Perhaps David meant to turn logging off at the table level? However, imp classic doesn't do direct path operations so you aren't going to get any savings there. Redo is for recovery so personally I would leave it alone.

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

Received on Fri Nov 01 2013 - 15:37:23 CET