Snap Id Snap Time Sessions Curs/Sess

--------- ------------------- -------- --------------------------

Begin Snap: 13736 07-Sep-11 09:30:17 19 4.6

End Snap: 13737 07-Sep-11 10:00:17 27 3.5

Elapsed: 30.01 (mins)

DB Time: 0.15 (mins)

sessions:
Select Value From dba_hist_sysstat Where snap_id=13736 And stat_name In ('logons current');
cursors:
Select Value From dba_hist_sysstat Where snap_id=13736 And stat_name In ('opened cursors current');
DB Time:
Select (x2.value-x1.value)/(1000000*60) "DB time" From (
Select * From dba_hist_sys_time_model Where snap_id=13736 And stat_name='DB time') x1,
(Select * From dba_hist_sys_time_model Where snap_id=13737 And stat_name='DB time') x2;

Cache Sizes

~~~~~~~~~~~ Begin End

---------- ----------

Buffer Cache: 2,608M 2,608M Std Block Size: 8K

Shared Pool Size: 384M 384M Log Buffer: 14,404K

buffer Cache:
Select bytes/1024/1024 buffer_cache From DBA_HIST_SGASTAT WHERE SNAP_ID = 13736 And Name='buffer_cache';
shared pool size:
Select bytes/1024/1024 From v$sgainfo Where Name='Shared Pool Size';
log buffer:
Select bytes/1024 From v$sgainfo Where Name='Redo Buffers';

Load Profile

~~~~~~~~~~~~ Per Second Per Transaction

--------------- ---------------

Redo size: 636.92 67,469.18

Logical reads: 298.32 31,601.00

Block changes: 2.18 230.88

Physical reads: 0.07 7.88

Physical writes: 0.34 35.88

User calls: 0.73 77.18

Parses: 3.41 360.88

Hard parses: 0.02 2.65

Sorts: 0.65 68.59

Logons: 0.01 0.88

Executes: 7.17 759.29

Transactions: 0.01

Transactions:快照期间发生的事物数，包括提交和回滚
403=Select (Select Sum(Value)
From Dba_Hist_Sysstat
Where Snap_Id = 13737
And Stat_Name In ('user commits', 'user rollbacks')) -
(Select Sum(Value)
From Dba_Hist_Sysstat
Where Snap_Id = 13736
And Stat_Name In ('user commits', 'user rollbacks'))
From Dual;
Redo size:
select b.stat_name st,
round((e.value - b.value)/(60.03*60),2) persec,
round((e.value - b.value)/(403),2) pertran
from dba_hist_sysstat b,
dba_hist_sysstat e
where b.snap_id = 13736
and e.snap_id = 13737
and b.stat_id = e.stat_id
and e.stat_name In('redo size');

Logical reads:
select b.stat_name st,
round((e.value - b.value)/(60.03*60),2) persec,
round((e.value - b.value)/(403),2) pertran
from dba_hist_sysstat b,
dba_hist_sysstat e
where b.snap_id = 13736
and e.snap_id = 13737
and b.stat_id = e.stat_id
and e.stat_name In('session logical reads');

BLOCK CHANGES:
select b.stat_name st,
round((e.value - b.value)/(60.03*60),2) persec,
round((e.value - b.value)/(403),2) pertran
from dba_hist_sysstat b,
dba_hist_sysstat e
where b.snap_id = 13736
and e.snap_id = 13737
and b.stat_id = e.stat_id
and e.stat_name In('db block changes');

PHYSICAL READS:
select b.stat_name st,
round((e.value - b.value)/(60.03*60),2) persec,
round((e.value - b.value)/(403),2) pertran
from dba_hist_sysstat b,
dba_hist_sysstat e
where b.snap_id = 13736
and e.snap_id = 13737
and b.stat_id = e.stat_id
and e.stat_name In('physical reads');
physical writes:
select b.stat_name st,
round((e.value - b.value)/(60.03*60),2) persec,
round((e.value - b.value)/(403),2) pertran
from dba_hist_sysstat b,
dba_hist_sysstat e
where b.snap_id = 13736
and e.snap_id = 13737
and b.stat_id = e.stat_id
and e.stat_name In('physical writes');

user calls:
select b.stat_name st,
round((e.value - b.value)/(60.03*60),2) persec,
round((e.value - b.value)/(403),2) pertran
from dba_hist_sysstat b,
dba_hist_sysstat e
where b.snap_id = 13736
and e.snap_id = 13737
and b.stat_id = e.stat_id
and e.stat_name In('user calls');
parses:
select b.stat_name st,
round((e.value - b.value)/(60.03*60),2) persec,
round((e.value - b.value)/(403),2) pertran
from dba_hist_sysstat b,
dba_hist_sysstat e
where b.snap_id = 13736
and e.snap_id = 13737
and b.stat_id = e.stat_id
and e.stat_name In('parse count (total)');
hard parses:
select b.stat_name st,
round((e.value - b.value)/(60.03*60),2) persec,
round((e.value - b.value)/(403),2) pertran
from dba_hist_sysstat b,
dba_hist_sysstat e
where b.snap_id = 13736
and e.snap_id = 13737
and b.stat_id = e.stat_id
and e.stat_name In('parse count (hard)');

sorts:
select b.stat_name st,
round((e.value - b.value)/(60.03*60),2) persec,
round((e.value - b.value)/(403),2) pertran
from dba_hist_sysstat b,
dba_hist_sysstat e
where b.snap_id = 13736
and e.snap_id = 13737
and b.stat_id = e.stat_id
and e.stat_name In('sorts (memory)');

logons:
select b.stat_name st,
round((e.value - b.value)/(60.03*60),2) persec,
round((e.value - b.value)/(403),2) pertran
from dba_hist_sysstat b,
dba_hist_sysstat e
where b.snap_id = 13736
and e.snap_id = 13737
and b.stat_id = e.stat_id
and e.stat_name In('logons cumulative');

executes:
select b.stat_name st,
round((e.value - b.value)/(60.03*60),2) persec,
round((e.value - b.value)/(403),2) pertran
from dba_hist_sysstat b,
dba_hist_sysstat e
where b.snap_id = 13736
and e.snap_id = 13737
and b.stat_id = e.stat_id
and e.stat_name In('execute count');

% Blocks changed per Read: 0.73 Recursive Call %: 97.04

Rollback per transaction %: 0.00 Rows per Sort: 96.86

% block changes per read:
block changes/logical reads*100%

Rollback per transaction%
Select (Select Value
From Dba_Hist_Sysstat
Where Snap_Id = 13737
And Stat_Name='user rollbacks') -
(Select Value
From Dba_Hist_Sysstat
Where Snap_Id = 13736
And Stat_Name='user rollbacks') x
From Dual;
x/transactions;

Rescursive calls:
Rescursive calls/(rescursive calls+user calls)

select b.stat_name st,
round((e.value - b.value)/(60.03*60),2) persec,
round((e.value - b.value)/(403),2) pertran
from dba_hist_sysstat b,
dba_hist_sysstat e
where b.snap_id = 13736
and e.snap_id = 13737
and b.stat_id = e.stat_id
and e.stat_name In('recursive calls');

Rows per sort:
sorts (rows)/ sorts (memory)
select b.stat_name st,
round((e.value - b.value)/(60.03*60),2) persec,
round((e.value - b.value)/(403),2) pertran
from dba_hist_sysstat b,
dba_hist_sysstat e
where b.snap_id = 13736
and e.snap_id = 13737
and b.stat_id = e.stat_id
and e.stat_name In('sorts (rows)');

Instance Efficiency Percentages (Target 100%)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Buffer Nowait %: 100.00 Redo NoWait %: 100.00

Buffer Hit %: 99.98 In-memory Sort %: 100.00

Library Hit %: 99.81 Soft Parse %: 99.27

Execute to Parse %: 52.47 Latch Hit %: 100.00

Parse CPU to Parse Elapsd %: 91.94 % Non-Parse CPU: 93.66

Buffer nowait:

Redo nowait:
100.00= (1-redo buffer allocation retries/redo entries)*100%
redo entries =select b.stat_name st,
round((e.value - b.value)/(60.03*60),2) persec,
round((e.value - b.value)/(403),2) pertran
from dba_hist_sysstat b,
dba_hist_sysstat e
where b.snap_id = 4959
and e.snap_id = 4960
and b.stat_id = e.stat_id
and e.stat_name In('redo entries');

redo buffer allocation retries= select b.stat_name st,
round((e.value - b.value)/(60.03*60),2) persec,
round((e.value - b.value)/(403),2) pertran
from dba_hist_sysstat b,
dba_hist_sysstat e
where b.snap_id = 4959
and e.snap_id = 4960
and b.stat_id = e.stat_id
and e.stat_name In('redo buffer allocation retries');

buffer hit:
1-(physical reads/(db block gets+consistent gets))
526.61 =select b.stat_name st,
round((e.value - b.value)/(60.03*60),2) persec,
round((e.value - b.value)/(403),2) pertran
from dba_hist_sysstat b,
dba_hist_sysstat e
where b.snap_id = 4959
and e.snap_id = 4960
and b.stat_id = e.stat_id
and e.stat_name In('db block gets');
2924.92= select b.stat_name st,
round((e.value - b.value)/(60.03*60),2) persec,
round((e.value - b.value)/(403),2) pertran
from dba_hist_sysstat b,
dba_hist_sysstat e
where b.snap_id = 4959
and e.snap_id = 4960
and b.stat_id = e.stat_id
and e.stat_name In('consistent gets');

99.16%=(1-29.14/(526.61+2924.92))*100%

In-memory sort:
sorts (memory)/( sorts (memory)+ sorts (disk))

library hit:
pinhits/pins
pinhits= Select (Select Sum(Pinhits)
From Dba_Hist_Librarycache
Where Snap_Id = 4960) -
(Select Sum(Pinhits)
From Dba_Hist_Librarycache
Where Snap_Id = 4959)
From Dual;
Pins= Select (Select Sum(Pins)
From Dba_Hist_Librarycache
Where Snap_Id = 4960) -
(Select Sum(Pins)
From Dba_Hist_Librarycache
Where Snap_Id = 4959)
From Dual;

98.54%=172470/175027*100%

Soft parse%:
1- parse count (hard)/ parse count (total)
1-0.21/7.31

Execute to parse:
(1- parse/execute)*100%

latch hit:
(1- spin_gets/gets)*100%

Select snap_id,Sum(gets),Sum(spin_gets) From Dba_Hist_Latch Where snap_id In (4959,4960)
Group By snap_id;

Parse cpu to parse elapsed:
Parse time cpu/parse time elapsed
Parse time cpu =select b.stat_name st, e.value - b.value,
round((e.value - b.value)/(60.03*60),2) persec,
round((e.value - b.value)/(403),2) pertran
from dba_hist_sysstat b,
dba_hist_sysstat e
where b.snap_id = 4959
and e.snap_id = 4960
and b.stat_id = e.stat_id
and e.stat_name In('parse time cpu');

parse time elapsed= select b.stat_name st, e.value - b.value,
round((e.value - b.value)/(60.03*60),2) persec,
round((e.value - b.value)/(403),2) pertran
from dba_hist_sysstat b,
dba_hist_sysstat e
where b.snap_id = 4959
and e.snap_id = 4960
and b.stat_id = e.stat_id
and e.stat_name In('parse time elapsed');

%non-parse cpu:
(1- parse time cpu/CPU used by this session)*100%
dba_hist_sysstat.

Shared Pool Statistics Begin End

------ ------

Memory Usage %: 88.42 87.21

% SQL with executions>1: 95.81 94.12

% Memory for SQL w/exec>1: 94.76 93.31

Memory usage%:
(1- free memory/sum(share pool))*100%

free memory=Select * From dba_hist_sgastat Where snap_id=13737 And Name='free memory'
And pool='shared pool';

%sql with executions>1:
Select
(Select Count(*) From DBA_HIST_SQLSTAT Where snap_id=13736
And executions_total>1)/(Select Count(*) From DBA_HIST_SQLSTAT Where snap_id=13736)
From dual;

%Memory for sql w/exec>1:

Sharable_mem/sharable_mem

Select (
Select Sum(sharable_mem) From DBA_HIST_SQLSTAT Where snap_id=13736
And executions_total>1)/(
Select Sum(sharable_mem) From DBA_HIST_SQLSTAT Where snap_id=13736
) From dual;

Top 5 Timed Events Avg %Total

~~~~~~~~~~~~~~~~~~ wait Call

Event Waits Time (s) (ms) Time Wait Class

------------------------------ ------------ ----------- ------ ------ ----------

control file parallel write 598 14 24 156.4 System I/O

CPU time 9 97.1

log file parallel write 48 0 10 5.3 System I/O

db file sequential read 228 0 1 3.6 User I/O

os thread startup 2 0 47 1.0 Concurrenc

Select X2.Event_Name,
X2.Total_Waits - X1.Total_Waits Waits,
(X2.Time_Waited_Micro - X1.Time_Waited_Micro) / 1000000 time(s)
From (Select * From Dba_Hist_System_Event Where Snap_Id = 13736) X1,
(Select * From Dba_Hist_System_Event Where Snap_Id = 13737) X2
Where X2.Event_Id = X1.Event_Id
And X2.Wait_Class != 'Idle'
Order By Micro Desc

%total call time:
Time(s)/db time

[@more@]