1.block_change_per_read
select to_char(a.value/b.value*100,'999.99') as blk_change_per_read
from v$sysstat a,v$sysstat b where a.name='db block changes' and b.name='session logical reads';
BLK_CHANGE_PER_READ
---------------------
1.53
2.sga_pool_size
with a as
(
select s.name, round((s.BYTES)/power(1024,2)) as pool_size_mb from v$sgainfo s
where s.name in(
'Fixed SGA Size',
'Maximum SGA Size',
'Free SGA Memory Available',
'Redo Buffers',
'Buffer Cache Size',
'Shared Pool Size',
'Large Pool Size',
'Java Pool Size',
'Streams Pool Size')) ,
b as
(
select s.name, round((s.BYTES)/power(1024,2)) as pool_size_mb from v$sgainfo s
where s.name in(
'Fixed SGA Size',
'Maximum SGA Size',
'Free SGA Memory Available',
'Redo Buffers',
'Buffer Cache Size',
'Shared Pool Size',
'Large Pool Size',
'Java Pool Size',
'Streams Pool Size'))
select a.* from a
union all
select 'Current_SGA',a.pool_size_mb-b.pool_size_mb
from a,b
where a.name='Maximum SGA Size'
and b.name='Free SGA Memory Available'
NAME POOL_SIZE_MB
------------------------------------------------------------------------------------------------ ------------
Fixed SGA Size 2
Redo Buffers 7
Buffer Cache Size 36
Shared Pool Size 500
Large Pool Size 4
Java Pool Size 4
Streams Pool Size 32
Maximum SGA Size 725
Free SGA Memory Available 140
Current_SGA 5853.redo_write_per_second
select snap_id,snap_time,round(value_offset/es_time*512,2) as redo_write_bytes_per_second
from(
select snap_id,snap_time,value,lag_value,value-lag_value value_offset,END_INTERVAL_TIME,lag_time,
EXTRACT(DAY FROM
END_INTERVAL_TIME - lag_time) *
86400 + EXTRACT(HOUR FROM END_INTERVAL_TIME -
lag_time) * 3600 +
EXTRACT(MINUTE FROM
END_INTERVAL_TIME - lag_time) * 60 +
EXTRACT(SECOND FROM
END_INTERVAL_TIME - lag_time) as es_time
from
(
select snap_id,snap_time ,stat_name,value,lag(value,1,null) over(order by value) lag_value,
END_INTERVAL_TIME,
lag(END_INTERVAL_TIME,1,null) over(order by END_INTERVAL_TIME) lag_time
from(
select h.snap_id,h.stat_name,h.value,s.END_INTERVAL_TIME,
to_date(to_char(s.END_INTERVAL_TIME,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') as snap_time
from dba_hist_sysstat h,dba_hist_snapshot s,v$instance i
where h.stat_name='redo writes'
and h.snap_id=s.snap_id
and to_date(to_char(s.END_INTERVAL_TIME,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')>=i.STARTUP_TIME
and trunc(to_date(to_char(s.END_INTERVAL_TIME,'yyyy-mm-dd'),'yyyy-mm-dd'))>=trunc(sysdate)-2
)order by snap_id desc
)
);
SNAP_ID SNAP_TIME REDO_WRITE_BYTES_PER_SECOND
---------- ------------------------------ ---------------------------
774 2024/7/3 21:49:25 3722.99
773 2024/7/3 21:49:22 2776.95
772 2024/7/3 21:49:17 1244.73
771 2024/7/3 21:49:11
4.data_io_speed
select snap_id,snap_time,
round(value_offset1/es_time_s/power(1024,2),2) as pr_mb_per_second,
round(value_offset2/es_time_s/power(1024,2),2) as pw_mb_per_second,
round((value_offset1+value_offset2)/es_time_s/power(1024,2),2) as io_speed_mb_per_second
from(
select snap_id,snap_time,
value1,lag_value1,value1-lag_value1 value_offset1,
value2,lag_value2,value2-lag_value2 value_offset2,
END_INTERVAL_TIME,lag_time,
EXTRACT(DAY FROM
END_INTERVAL_TIME - lag_time) *
86400 + EXTRACT(HOUR FROM END_INTERVAL_TIME -
lag_time) * 3600 +
EXTRACT(MINUTE FROM
END_INTERVAL_TIME - lag_time) * 60 +
EXTRACT(SECOND FROM
END_INTERVAL_TIME - lag_time) as es_time_s
from
(
select snap_id,snap_time ,
stat_name1,value1,lag(value1,1,null) over(order by value1) lag_value1,
stat_name2,value2,lag(value2,1,null) over(order by value2) lag_value2,
END_INTERVAL_TIME,
lag(END_INTERVAL_TIME,1,null) over(order by END_INTERVAL_TIME) lag_time
from(
select h1.snap_id,h1.stat_name stat_name1 ,h1.value value1 ,h2.stat_name stat_name2 ,h2.value value2,s.END_INTERVAL_TIME,
to_date(to_char(s.END_INTERVAL_TIME,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') as snap_time
from dba_hist_sysstat h1,dba_hist_sysstat h2,dba_hist_snapshot s,v$instance i
where h1.stat_name='physical read total bytes'
and h2.stat_name='physical write total bytes'
and h1.snap_id=s.snap_id
and h2.snap_id=s.snap_id
and to_date(to_char(s.END_INTERVAL_TIME,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')>=i.STARTUP_TIME
and trunc(to_date(to_char(s.END_INTERVAL_TIME,'yyyy-mm-dd'),'yyyy-mm-dd'))>=trunc(sysdate)-2
)order by snap_id desc
)
)
SNAP_ID SNAP_TIME PR_MB_PER_SECOND PW_MB_PER_SECOND IO_SPEED_MB_PER_SECOND
---------- ------------------------------ ---------------- ---------------- ----------------------
774 2024/7/3 21:49:25 3463.47 0.49 3463.96
773 2024/7/3 21:49:22 2260.99 0.26 2261.24
772 2024/7/3 21:49:17 1621.72 0.3 1622.01
771 2024/7/3 21:49:11
5.session_io_speed
select snap_id,snap_time,
round(value_offset1/es_time_s,2) as session_lr_per_second,
round(value_offset2/es_time_s,2) as session_pr_per_second,
round((value_offset1+value_offset2)/es_time_s,2) as session_io_per_second
from(
select snap_id,snap_time,
value1,lag_value1,value1-lag_value1 value_offset1,
value2,lag_value2,value2-lag_value2 value_offset2,
END_INTERVAL_TIME,lag_time,
EXTRACT(DAY FROM
END_INTERVAL_TIME - lag_time) *
86400 + EXTRACT(HOUR FROM END_INTERVAL_TIME -
lag_time) * 3600 +
EXTRACT(MINUTE FROM
END_INTERVAL_TIME - lag_time) * 60 +
EXTRACT(SECOND FROM
END_INTERVAL_TIME - lag_time) as es_time_s
from
(
select snap_id,snap_time ,
stat_name1,value1,lag(value1,1,null) over(order by value1) lag_value1,
stat_name2,value2,lag(value2,1,null) over(order by value2) lag_value2,
END_INTERVAL_TIME,
lag(END_INTERVAL_TIME,1,null) over(order by END_INTERVAL_TIME) lag_time
from(
select h1.snap_id,h1.stat_name stat_name1 ,h1.value value1 ,h2.stat_name stat_name2 ,h2.value value2,s.END_INTERVAL_TIME,
to_date(to_char(s.END_INTERVAL_TIME,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') as snap_time
from dba_hist_sysstat h1,dba_hist_sysstat h2,dba_hist_snapshot s,v$instance i
where h1.stat_name='session logical reads'
and h2.stat_name='physical reads'
and h1.snap_id=s.snap_id
and h2.snap_id=s.snap_id
and to_date(to_char(s.END_INTERVAL_TIME,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')>=i.STARTUP_TIME
and trunc(to_date(to_char(s.END_INTERVAL_TIME,'yyyy-mm-dd'),'yyyy-mm-dd'))>=trunc(sysdate)-2
)order by snap_id desc
)
)
SNAP_ID SNAP_TIME SESSION_LR_PER_SECOND SESSION_PR_PER_SECOND SESSION_IO_PER_SECOND
---------- ------------------------------ --------------------- --------------------- ---------------------
774 2024/7/3 21:49:25 1667.94 13.5 1681.44
773 2024/7/3 21:49:22 2810.85 75.71 2886.55
772 2024/7/3 21:49:17 3115.56 78.93 3194.49
771 2024/7/3 21:49:11 
717
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
