-
How can i know the % hit ratio of the buffer_pool_keep?
-
Sort Area Efficiency
-- Name: Sort Area Efficiency
-- Purpose: The percentage of sorts performed in memory
-- as opposed to sorts performed in temporary
-- segments on disk.
-- Results:
-- Data Warehouse systems should be greater than 98%
-- OLTP systems should be greater than 95%
-- Resolution:
-- Helpful indicator when DB needs more block buffers
-- or a system is not tuned very well.
-- Ref: Oracle Performance Tuning Tips & Techniques, p572
-------------------------------------------------------
-- select round((sum(decode(name, 'sorts (memory)', value, 0))
-- / (sum(decode(name, 'sorts (memory)', value, 0))
-- + sum(decode(name, 'sorts (disk)', value, 0))))
-- * 100,2) "% Sorts Performed In Memory"
-- from v$sysstat;
-------------------------------------------------------
-- Hit Ratio for Data (V$SYSSTAT View)
set serverout on
BEGIN
FOR I IN ( select
round(1 - (sum(decode(name, 'physical reads', value, 0)) /
(sum(decode(name, 'db block gets', value, 0)) +
(sum(decode(name, 'consistent gets', value, 0))))),2) * 100
ReadHitRatio
from v$sysstat) LOOP
DBMS_OUTPUT.PUT_LINE('DB Read Hit Ratio Is: ' || i.ReadHitRatio || '%');
IF (i.ReadHitRatio < 95) THEN
DBMS_OUTPUT.PUT_LINE(' Hit Ratio is very low, you must increase DB Block Buffers');
DBMS_OUTPUT.PUT_LINE(' or tune quries that are causing disk reads');
ELSE
DBMS_OUTPUT.PUT_LINE(' Hit Ratio Is Acceptable');
END IF;
END LOOP;
END;
/
Ken Hinerman
Data Interaction
DBASupport@datainteraction.com
-
Data Dictionary Calls
-- Purpose:
-- Used to find how often data dictionary calls are
-- effectively hitting the memory cache allocated by
-- the shared_pool_size.
-- Every time a statement is processed by Oracle, it
-- uses the dictionary cahce to find required info in
-- the data dictionary.
-- Results:
-- Desired results b/w 90 - 95%, less than is bad
-- Resolution:
-- A low indication means that the shared_pool_size
-- should be increased.
-- Ref: Oracle Performance Tuning Tips & Techniques, p572
-------------------------------------------------------
-------------------------------------------------------
-- Hit Ratio For The Data Cache
BEGIN
FOR I IN (select
sum(gets) gets,
sum(getmisses) getmisses ,
round((1-sum(getmisses) / (sum(gets) + sum(getmisses))) * 100, 2) HitRate
from v$rowcache) LOOP
DBMS_OUTPUT.PUT_LINE('The Dictionary Cache Hit Ratio Is: ' || i.HitRate || '%');
IF (i.HitRate < 95) THEN
DBMS_OUTPUT.PUT_LINE(' Dictionary Cache Hit Ratio Is To Low, Increase Shared Pool Size');
ELSE
DBMS_OUTPUT.PUT_LINE(' Dictionary Cache Hit Ratio is Fine, Leave Shared Pool Size As Is');
END IF;
END LOOP;
END;
/
Ken Hinerman
Data Interaction
DBASupport@datainteraction.com
-
PL/SAQL & SQL Memory Access
-- Purpose:
-- v$librarycache view will show how well actual
-- statementsare accessing memory. If shared_pool_size
-- is too small, enough room may not be available to
-- store all the statements into memory.
--
-- If shared pool becomes fragmented, large PL/SQL
-- may not fit. The following script will indicate how
-- often statements are being accessed from memory.
-- Results:
-- Pin Hits: greater than 95%
-- Reload Hit Ratio: Greater than 99%
-- Resolution:
-- If either of these falls below recommended value
-- increase shared pool.
-- Ref: Oracle Performance Tuning Tips & Techniques, p573
-------------------------------------------------------
-------------------------------------------------------
-- Hit Ratio for the Shared SQL and PL/SQL (V$LIBRARYCACHE View)
set serverout on
BEGIN
FOR I IN (select
sum(pins) Executions,
sum(pinhits) Hits,
round(((sum(pinhits) / sum(pins)) * 100), 2) PinHitRatio,
sum(reloads) Misses,
round(((sum(pins) / (sum(pins) + sum(reloads))) * 100), 2) RelHitRatio
from V$librarycache) LOOP
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('Shared Pool');
DBMS_OUTPUT.PUT_LINE(' PINNED Hit Ratio for SQL & PL/SQL:' || i.pinHitRatio || '%');
DBMS_OUTPUT.PUT_LINE(' RELOADED Hit Ratio for SQL & PL/SQL:' || i.relHitRatio || '%');
DBMS_OUTPUT.PUT_LINE('Recommendations:');
IF (i.pinHitRatio < 95) THEN
DBMS_OUTPUT.PUT_LINE(' Pinned Hit Ratio Is Low (Below 95%), Increase shared_pool');
ELSE
DBMS_OUTPUT.PUT_LINE(' Pinned Hit Ratio Is O.K.');
END IF;
IF (i.relHitRatio < 99) THEN
DBMS_OUTPUT.PUT_LINE(' Reload hit Ratio Is Low (Below 99%), Increase shared_pool');
ELSE
DBMS_OUTPUT.PUT_LINE(' Reload Hit Ration Is O.K.');
END IF;
END LOOP;
END;
/
Ken Hinerman
Data Interaction
DBASupport@datainteraction.com
-
v$buffer_pool_statistics
Hi, 24th April 2001 20:16 hrs chennai
Please query with this formula to work out on your problem.
1 SELECT NAME, 1-(physical_reads/(db_block_gets +consistent_gets))
2 "HIT RATIO" FROM V$BUFFER_POOL_STATISTICS WHERE
3* db_block_gets+consistent_gets >0
SQL> /
NAME HIT RATIO
-------------------- ----------
DEFAULT .97665668
In my case there is no "keep" buffer .
SQL> select ID,NAME,BUFFERS from v$buffer_pool;
ID NAME BUFFERS
---------- -------------------- ----------
1 KEEP 0
2 RECYCLE 0
3 DEFAULT 2102
Cheers
Padmam
[Edited by padmam on 04-24-2001 at 10:59 AM]
Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it
-
Question
This question is for padmam.
what happends if db_block_gets+consistent_gets >0
cuse mine is = 0 .
so the query does not return a result.
-
Okay, you'll have to forgive my ignorance of the dba-side of the house, but...
By definition, wouldn't the hit ratio for the buffer_pool_keep be 100%? Actually, since the tables have to be loaded from disk the first time, this would reduce it a little. But after that, it would keep creeping closer to 100% because, by definition, the blocks would never get flushed, no? This also assumes that the KEEP area is large enough to hold everything you have pointed at it. Or am I missing something?
- Chris
-
Question
Question for chrisrlong.
I think you have a good point with the hit ratio of the buffer
pool keep.
The only time the hit ratio would not be 100%,
is if the entire table is not in the buffer_pool_keep.
Is this a good observation?
And what can i do to check latch in the V$BUFFER_POOL_STATISTICS?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|