AWR interpretation
Date: Wed, 30 Oct 2013 18:27:53 +0000
Message-ID: <FE4C2B093843BB4B873D754E5E0BE4DB6CCDAB81_at_DCSOSVMS02.dcso.org>
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.
Database is setup with 5, 50meg redo logs. I am seeing a pretty consistent 3 log switches a minute during this import.
The top 2 events are log file sync and DB CPU.
Question 1) If I up the redo log file size during import this should cut down on log file syncs? Question 2) IS this high DC CPU mean my CPU's are spending their time only focused on DB things? A good thing? Or is this high a count during an import a bad thing?
From a cpu/core stance I see that my CPU is actually idle about 78% of the time, which would tell me that I'm only using 22% of the CPU to do the import....a good thing.
What I'm really wanting to monitor is am I spending too much time writing out to the SAN, ie, is my IO bottlenecking me.
I am doing a commit of the array as I do the import.
At the time that I post this, my job has been running for 11308 seconds. My goal is to speed this import way way up. This was an older dump of my production system. I have since shaved the target table from 46 to 22million rows with a few more to go.
Thanks
Bob
AWR Snap Id
Snap Time
Sessions
Cursors/Session
Begin Snap:
312
30-Oct-13 12:00:46
43
4.0
End Snap:
313
30-Oct-13 13:00:01
44
4.0
Elapsed:
59.26 (mins)
DB Time:
48.51 (mins)
Report Summary
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,657,727.8
2,455.6
Logical reads:
15,088.9
22.4
Block changes:
9,905.4
14.7
Physical reads:
2.7
0.0
Physical writes:
166.7
0.3
User calls:
1,353.7
2.0
Parses:
1.2
0.0
Hard parses:
0.0
0.0
W/A MB processed:
0.0
0.0
Logons:
0.1
0.0
Executes:
697.8
1.0
Rollbacks:
0.0
0.0
Transactions:
675.1
Instance Efficiency Percentages (Target 100%) Buffer Nowait %:
100.00
Redo NoWait %:
99.99
Buffer Hit %:
99.99
In-memory Sort %:
100.00
Library Hit %:
99.99
Soft Parse %:
99.06
Execute to Parse %:
99.82
Latch Hit %:
100.00
Parse CPU to Parse Elapsd %:
64.52
% Non-Parse CPU:
99.98
Shared Pool Statistics
Begin
End
Memory Usage %:
23.01
23.13
% SQL with executions>1:
87.73
85.98
% Memory for SQL w/exec>1:
83.71
83.80
Top 5 Timed Foreground Events
Event
Waits
Time(s)
Avg wait (ms)
% DB time
Wait Class
log file sync
2,399,978
1,818
1
62.46
Commit
DB CPU 1,075
36.94
db file sequential read
4,789
28
6
0.95
User I/O
control file sequential read
860
6
8
0.22
System I/O
SQL*Net message to client
4,811,031
6
0
0.19
Network
Host CPU (CPUs: 4 Cores: 4 Sockets: 2)
Load Average Begin
Load Average End
%User
%System
%WIO
%Idle
13.8
7.5
78.7
Instance CPU
%Total CPU
%Busy CPU
%DB time waiting for CPU (Resource Manager)
9.8
46.0
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):
298.0
307.5
% Host Mem used for SGA+PGA:
50.06
50.10
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Oct 30 2013 - 19:27:53 CET