AWR interpretation

From: Storey, Robert (DCSO) <"Storey,>
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-l
Received on Wed Oct 30 2013 - 19:27:53 CET

Original text of this message