Hi,
I've got 10.2.0.3 warehouse and wondering what can I do more to tune I/O performance .
The cpu_count is 16 / got 6x 1member redolog 1GB size each .
During massive load via imp utlility I've got AWR report like that:
DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
DB1 1133607631 DB1 1 10.2.0.3.0 NO ora1
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 36888 11-Mar-12 03:00:44 37 4.7
End Snap: 36889 11-Mar-12 04:00:48 30 4.8
Elapsed: 60.06 (mins)
DB Time: 229.62 (mins)
Cache Sizes
~~~~~~~~~~~ Begin End
---------- ----------
Buffer Cache: 2,000M 2,000M Std Block Size: 16K
Shared Pool Size: 1,008M 1,008M Log Buffer: 14,352K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 15,915,632.74 1,186,746.85
Logical reads: 12,179.87 908.19
Block changes: 5,714.48 426.10
Physical reads: 3,021.16 225.27
Physical writes: 1,122.29 83.68
User calls: 97.46 7.27
Parses: 45.54 3.40
Hard parses: 2.56 0.19
Sorts: 4.96 0.37
Logons: 0.33 0.02
Executes: 55.15 4.11
Transactions: 13.41
% Blocks changed per Read: 46.92 Recursive Call %: 85.70
Rollback per transaction %: 0.00 Rows per Sort: ########
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 99.99
Buffer Hit %: 76.29 In-memory Sort %: 99.78
Library Hit %: 93.93 Soft Parse %: 94.39
Execute to Parse %: 17.43 Latch Hit %: 99.99
Parse CPU to Parse Elapsd %: 89.63 % Non-Parse CPU: 99.72
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 48.18 53.01
% SQL with executions>1: 76.15 89.33
% Memory for SQL w/exec>1: 85.95 92.57
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
CPU time 5,150 37.4
db file scattered read 694,210 3,094 4 22.5 User I/O
free buffer waits 129,392 1,431 11 10.4 Configurat
log file sync 41,591 993 24 7.2 Commit
SQL*Net more data from client 21,826,942 818 0 5.9 Network
-------------------------------------------------------------
Time Model Statistics DB/Inst: DB1/DB1 Snaps: 36888-36889
-> Total time in database user-calls (DB Time): 13777.3s
-> Statistics including the word "background" measure background process
time, and so do not contribute to the DB time statistic
-> Ordered by % or DB time desc, Statistic name
Statistic Name Time (s) % of DB Time
------------------------------------------ ------------------ ------------
sql execute elapsed time 12,329.4 89.5
DB CPU 5,150.2 37.4
parse time elapsed 20.7 .1
hard parse elapsed time 14.0 .1
connection management call elapsed time 9.5 .1
PL/SQL execution elapsed time 3.2 .0
failed parse elapsed time 0.5 .0
repeated bind elapsed time 0.3 .0
PL/SQL compilation elapsed time 0.1 .0
sequence load elapsed time 0.0 .0
DB time 13,777.3 N/A
background elapsed time 7,758.4 N/A
background cpu time 576.3 N/A
-------------------------------------------------------------
Operating System Statistics DB/Inst: DB1/DB1 Snaps: 36888-36889
Statistic Total
-------------------------------- --------------------
BUSY_TIME 1,089,111
IDLE_TIME 4,675,584
IOWAIT_TIME 604,088
NICE_TIME 14,105
SYS_TIME 208,302
USER_TIME 841,088
LOAD 5
RSRC_MGR_CPU_WAIT_TIME 0
PHYSICAL_MEMORY_BYTES 69,888
NUM_CPUS 16
NUM_CPU_SOCKETS 4
-------------------------------------------------------------
Instance Recovery Stats DB/Inst: DB1/DB1 Snaps: 36888-36889
-> B: Begin snapshot, E: End snapshot
Targt Estd Log File Log Ckpt Log Ckpt
MTTR MTTR Recovery Actual Target Size Timeout Interval
(s) (s) Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks Redo Blks
- ----- ----- ---------- --------- --------- ---------- --------- ------------
B 0 170 64795 6496340 5859936 9437130 5859936 N/A
E 0 94 29662 4467594 4356394 9437130 4356394 N/A
-------------------------------------------------------------
I've inlcuded 'Instance Recovery Stats ' because I'm wondering if checkpoint activity has huge impact on i/o as whole .
Noticed that log_checkpoint_timeout is set to 180 , this DB is noarchivelog mode and recovery time is not as critical as completion time of importing data :).
The logswitch is every 1min so kind of high .
Not sure which checkpoint related activity is crucial, find such measures
Statistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
CPU used by this session 519,387 144.1 10.8
CPU used when call started 519,158 144.1 10.7
CR blocks created 26,363 7.3 0.6
Cached Commit SCN referenced 4,527,758 1,256.4 93.7
Commit SCN cached 481 0.1 0.0
DB time 2,144,450 595.1 44.4
DBWR checkpoint buffers written 194,042 53.8 4.0
DBWR checkpoints 356 0.1 0.0
background checkpoints completed 57 0.0 0.0
background checkpoints started 56 0.0 0.0
What else can I say, well blocksize 16k dbmbrc = 16 (think I should set this up) .
Any comments .
Regards
GregG