马上行动 - Action Now : Oracle

===========================================================

Oracle - Hit/Miss Ratios

===========================================================

作者: nmtcolin(http://nmtcolin.itpub.net)
发表于: 2008.05.06 15:03
分类: Testing
出处: http://nmtcolin.itpub.net/post/5135/461283
---------------------------------------------------------------

Oracle - Hit/Miss Ratios

Here are some scripts related to Hit/Miss Ratios .

Buffer Hit Ratio

BUFFER HIT RATIO NOTES:

Consistent Gets - The number of accesses made to the block buffer to retrieve data in a consistent mode.

DB Blk Gets - The number of blocks accessed via single block gets (i.e. not through the consistent get mechanism).

Physical Reads - The cumulative number of blocks read from disk.

Logical reads are the sum of consistent gets and db block gets.

The db block gets statistic value is incremented when a block is read for update and when segment header blocks are accessed.

Hit Ratio should be > 80%, else increase DB_BLOCK_BUFFERS in init.ora

select 	sum(decode(NAME, 'consistent gets',VALUE, 0)) "Consistent Gets",
	sum(decode(NAME, 'db block gets',VALUE, 0)) "DB Block Gets",
	sum(decode(NAME, 'physical reads',VALUE, 0)) "Physical Reads",
	round((sum(decode(name, 'consistent gets',value, 0)) + 
	       sum(decode(name, 'db block gets',value, 0)) - 
	       sum(decode(name, 'physical reads',value, 0))) / 
	      (sum(decode(name, 'consistent gets',value, 0)) + 
	       sum(decode(name, 'db block gets',value, 0))) * 100,2) "Hit Ratio"
from   v$sysstat

Data Dict Hit Ratio

DATA DICTIONARY HIT RATIO NOTES:

Gets - Total number of requests for information on the data object.

Cache Misses - Number of data requests resulting in cache misses

Hit Ratio should be > 90%, else increase SHARED_POOL_SIZE in init.ora

select 	sum(GETS),
	sum(GETMISSES),
	round((1 - (sum(GETMISSES) / sum(GETS))) * 100,2)
from 	v$rowcache

SQL Cache Hit Ratio

SQL CACHE HIT RATIO NOTES:

Pins - The number of times a pin was requested for objects of this namespace.

Reloads - Any pin of an object that is not the first pin performed since the object handle was created, and which requires loading the object from disk.

Hit Ratio should be > 85%

select 	sum(PINS) Pins,
	sum(RELOADS) Reloads,
	round((sum(PINS) - sum(RELOADS)) / sum(PINS) * 100,2) Hit_Ratio
from 	v$librarycache

Library Cache Miss Ratio

LIBRARY CACHE MISS RATIO NOTES:

Executions - The number of times a pin was requested for objects of this namespace.

Cache Misses - Any pin of an object that is not the first pin performed since the object handle was created, and which requires loading the object from disk.

Hit Ratio should be < 1%, else increase SHARED_POOL_SIZE in init.ora

select 	sum(PINS) Executions,
	sum(RELOADS) cache_misses,
	sum(RELOADS) / sum(PINS) miss_ratio
from 	v$librarycache

http://vsbabu.org/oracle/sect05.html

nmtcolin 发表于:2008.05.06 15:03 ::分类: ( Testing ) ::阅读:(103次) :: 评论 (0)

发表评论

«	七月 2008					»
一	二	三	四	五	六	日
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30	31

«	七月 2008					»
一	二	三	四	五	六	日
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30	31

«	七月 2008					»
一	二	三	四	五	六	日
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30	31

«	七月 2008					»
一	二	三	四	五	六	日
	1	2	3	4	5	6
7	8	9	10	11	12	13
14	15	16	17	18	19	20
21	22	23	24	25	26	27
28	29	30	31