RE: AWR interpretation
Date: Wed, 30 Oct 2013 19:23:14 +0000
Message-ID: <FE4C2B093843BB4B873D754E5E0BE4DB6CCDABD4_at_DCSOSVMS02.dcso.org>
Using the traditional. I'm creating the export using the OEM database control. Didn't see an option there for parallelizing the import. From: Andrew Kerber [mailto:andrew.kerber_at_gmail.com] Sent: Wednesday, October 30, 2013 2:17 PM To: Storey, Robert (DCSO)
Cc: Oracle L
Subject: Re: AWR interpretation
Ok, in that case the log file sync wait suggests that you are committing too often. You might set your arraysize higher. Are you using tradition exp/imp or data pump? You have a lot of unused capacity, so if you can parallelize the operation in someway it would probably help the time.
On Wed, Oct 30, 2013 at 1:55 PM, Storey, Robert (DCSO) <RStorey_at_dcso.nashville.org<mailto:RStorey_at_dcso.nashville.org>> wrote: Here is the text output (thanks Andrew). I've just snipped off the top areas of the report. If you need some specific area let me know.
Snap Id Snap Time Sessions Curs/Sess --------- ------------------- -------- --------- Begin Snap: 310 30-Oct-13 10:00:17 40 3.2 End Snap: 313 30-Oct-13 13:00:01 44 4.0 Elapsed: 179.74 (mins) DB Time: 142.90 (mins) Cache Sizes Begin End ~~~~~~~~~~~ ---------- ---------- Buffer Cache: 10,208M 10,208M Std Block Size: 8K Shared Pool Size: 1,632M 1,632M Log Buffer: 8,452K Load Profile Per Second Per Transaction Per Exec Per Call ~~~~~~~~~~~~ --------------- --------------- ---------- ---------- DB Time(s): 0.8 0.0 0.00 0.00 DB CPU(s): 0.3 0.0 0.00 0.00 Redo size: 1,635,567.3 2,457.3 Logical reads: 14,527.7 21.8 Block changes: 9,767.9 14.7 Physical reads: 3.5 0.0 Physical writes: 151.2 0.2 User calls: 1,334.7 2.0 Parses: 1.4 0.0 Hard parses: 0.1 0.0 W/A MB processed: 0.0 0.0 Logons: 0.1 0.0 Executes: 689.2 1.0 Rollbacks: 0.0 0.0 Transactions: 665.6
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 100.00 Redo NoWait %: 99.99 Buffer Hit %: 99.98 In-memory Sort %: 100.00 Library Hit %: 99.94 Soft Parse %: 93.24 Execute to Parse %: 99.80 Latch Hit %: 100.00 Parse CPU to Parse Elapsd %: 70.66 % Non-Parse CPU: 99.88 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 18.90 23.13
% SQL with executions>1: 68.85 85.98
% Memory for SQL w/exec>1: 73.07 83.80
Top 5 Timed Foreground Events
Avg wait % DB Event Waits Time(s) (ms) time Wait Class ------------------------------ ------------ ----------- ------ ------ ---------- log file sync 7,176,907 5,301 1 61.8 Commit DB CPU 3,122 36.4 db file sequential read 20,772 129 6 1.5 User I/O SQL*Net message to client 14,386,916 17 0 .2 Network control file sequential read 4,186 12 3 .1 System I/O Host CPU (CPUs: 4 Cores: 4 Sockets: 2) ~~~~~~~~ Load Average Begin End %User %System %WIO %Idle --------- --------- --------- --------- --------- --------- 12.3 7.3 80.4
Instance CPU
% of total CPU for Instance: 9.4 % of busy CPU for Instance: 48.0 %DB time waiting for CPU - Resource Mgr: 0.0 Memory Statistics ~~~~~~~~~~~~~~~~~ Begin End Host Mem (MB): 24,566.1 24,566.1 SGA use (MB): 12,000.0 12,000.0 PGA use (MB): 188.4 307.5
% Host Mem used for SGA+PGA: 49.61 50.10
From: Andrew Kerber [mailto:andrew.kerber_at_gmail.com<mailto:andrew.kerber_at_gmail.com>]
Sent: Wednesday, October 30, 2013 1:32 PM
To: Storey, Robert (DCSO)
Cc: Oracle L
Subject: Re: AWR interpretation
The formatting is almost impossible to read. Can you pull a text version of that?
On Wed, Oct 30, 2013 at 1:27 PM, Storey, Robert (DCSO) <RStorey_at_dcso.nashville.org<mailto:RStorey_at_dcso.nashville.org>> wrote:
Afternoon all.
I want to make sure I'm interpreting this AWR (see below) correctly.
I am performing an import to an 11g database using a 9i export file. I'm importing 1 table that contains approximately 46 million rows. The system is 64 bit with dual core, dual cpu and 24gigs of ram. I am doing a character conversion and I changed the intitrans on the new table to 2 vice 1 and pre-created the table for the import. <snip
12,000.0
12,000.0
PGA use (MB):
298.0
307.5
% Host Mem used for SGA+PGA:
50.06
50.10
-- http://www.freelists.org/webpage/oracle-l -- Andrew W. Kerber 'If at first you dont succeed, dont take up skydiving.' -- Andrew W. Kerber 'If at first you dont succeed, dont take up skydiving.' -- http://www.freelists.org/webpage/oracle-lReceived on Wed Oct 30 2013 - 20:23:14 CET