Показать сообщение отдельно
  #2  
Старый 12.03.2010, 09:19
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,211
По умолчанию

Tuning the Database Buffer Cache

Database read buffer cache hit ratio



-- Database read buffer cache hit ratio =
-- 1 -(physical reads / (db block gets + consistent gets))

-- LIO / (PIO + LIO)% hints of block in the buffer

--
-- physical read - block request from disk (I/O)
-- consisten gets - consistent read blick request
-- db block gets - block requests
--


SELECT 'Database Buffer Cache Hit Ratio ' "Ratio",
ROUND (
(1 -
(
(SELECT SUM(value) From V$SYSSTAT WHERE name = 'physical reads')
/ ((SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'db block gets')
+ (SELECT SUM(value) FROM V$SYSSTAT WHERE name = 'consistent gets'))
)
)* 100
)

||'%' "Percentage"
FROM DUAL;



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

PGA cache hit ratio

-- PGA cache hit ratio = (bytes processed / (bytes processed + extra bytes read/written))*100

SELECT (
(SELECT VALUE FROM V$PGASTAT WHERE NAME = 'bytes processed')
/ ((SELECT VALUE FROM V$PGASTAT WHERE NAME = 'bytes processed')+
(SELECT VALUE FROM V$PGASTAT WHERE NAME = 'extra bytes read/written'))
)
*100 "PGA Cache Ht Ratio" FROM DUAL;


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


select * from v$waitstat;





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

-- buffer busy a free buffer waits
SELECT event "Event", total_waits "Waits", time_waited "Total"
FROM v$system_event WHERE event IN('buffer busy waits', 'free buffer waits');




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

SELECT event "Event", total_waits "Waits", time_waited "Total"
FROM v$system_event
ORDER BY event;
__________________
Телеграм чат

Последний раз редактировалось Marley; 21.09.2015 в 10:17.
Ответить с цитированием