43 lines
1.2 KiB
SQL
43 lines
1.2 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display Buffer Hit Ratio from AWR data
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 05-Aug-06 Vishal Gupta First Draft
|
|
* 15-May-07 Vishal Gupta Formatted the output
|
|
*/
|
|
|
|
set pages 10000
|
|
|
|
COLUMN mydate HEADING 'YYYY-MM-DD HH24'
|
|
COLUMN bhr HEADING 'Buffer Hit|Ratio (%)' FORMAT 999
|
|
|
|
select
|
|
to_char(sn.END_INTERVAL_TIME,'yyyy-mm-dd HH24') mydate,
|
|
new.name buffer_pool_name,
|
|
(((new.consistent_gets-old.consistent_gets)+(new.db_block_gets-old.db_block_gets))-(new.physical_reads-old.physical_reads))
|
|
/((new.consistent_gets-old.consistent_gets)+(new.db_block_gets-old.db_block_gets))*100 bhr
|
|
from DBA_HIST_buffer_pool_stat old,
|
|
DBA_HIST_buffer_pool_stat new,
|
|
DBA_HIST_snapshot sn
|
|
where sn.dbid = (select dbid from v$database)
|
|
AND sn.dbid = new.dbid
|
|
AND sn.instance_number = new.instance_number
|
|
AND new.dbid = old.dbid
|
|
AND new.instance_number = old.instance_number
|
|
and new.name = old.name
|
|
and new.snap_id = sn.snap_id
|
|
AND old.snap_id = sn.snap_id-1
|
|
and sn.END_INTERVAL_TIME > sysdate - &&1
|
|
order by 1 asc
|
|
/
|
|
|
|
|
|
@@footer
|