RE: AWR interpretation
- From: "Storey, Robert (DCSO)" <RStorey@xxxxxxxxxxxxxxxxxx>
- To: Andrew Kerber <andrew.kerber@xxxxxxxxx>
- Date: Wed, 30 Oct 2013 19:23:14 +0000
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@xxxxxxxxx]
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@xxxxxxxxxxxxxxxxxx<mailto:RStorey@xxxxxxxxxxxxxxxxxx>> 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@xxxxxxxxx<mailto:andrew.kerber@xxxxxxxxx>]
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@xxxxxxxxxxxxxxxxxx<mailto:RStorey@xxxxxxxxxxxxxxxxxx>> 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
--
//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.'
--
//www.freelists.org/webpage/oracle-l
Other related posts:
