low number of free buffers in the buffer cache
Hi. I have noticed that the number of free db buffers in my bd cache is pretty low. Based on this query select decode(state,0,'Free',1,decode lrba_seq,0,'Available','Being Used'), 'Being Used',state) "Block Status", count(*) from x$bh group by decode(state,0,'Free',1,decode(lrba_seq,0,'Available','Being Used'), 3,'Being Used',state) I see 36000 available buffers and about 1000 being used. I don't have any complaints about performance (yet) and I know that too many free buffers indicates that the db_cache is too big. Is there the minimum number of free buffers that I would need to keep or can I just ignore it. TIA Gene __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Question about buffer cache
Ok, another volley.
I have the same objects and conditions as described below (default block size 8k and
one 16k tablespace with one table).
The 16k table is small ( create table blah (col1 varchar2(10), col2 number) tablespace
my16ktbsp storage(buffer_pool KEEP);)
-- insert data
begin
for i in 1..1
loop
insert into blah values ('whatever',1);
end loop;
end;
/
commit;
-- result is 1 records in table blah.
select * from blah;
-- I ran this several times.
Now query for buffer info shows...
SUBCACHE OBJECT_NAMEBLOCKS
-- --
16K SUBCACHE BLAH 32
16K SUBCACHE BLAH 32
So, it strongly appears that even though I specified buffer cache of keep, it still
has to go in cache of same block size.
>> [EMAIL PROTECTED] 02/20/03 04:30PM >>>
John,
My earlier assumptions were wrong (usually are, that's why I test).
I set up a 9.2 instance with these parameters:
db_cache_size=300M
db_keep_cache_size=40M
db_16k_cache_size=40M
db_block_size=8192
Then, I created a tablespace MY16KTBSP with a 16k block size.
Then, create table TESTA tablespace MY16KTBSP storage(buffer_cache KEEP);
And this works. Does this really utilize the keep buffer cache? I'm not sure because
it also worked in another test when I had no keep buffer. I'm not sure how to tell
which buffer the blocks are in right now, maybe another list user can help us both
with that.
However, I just read your question again and realized I'm not anywhere in the
neighborhood of addressing your issue which is your 'waits'.
We aren't concerned with the number of waits (yes, but not only), but also the time
spent waiting and why.
A lot more information is needed really. Size and content of the table? Other
indexes on the table? Are statisics up to date and accurate? Is use of this index
really appropriate, sometimes full table scan is faster.
Something else maybe another person could address, I'm not sure that db file
sequential reads are physical only. They may be physical and logical depending on
your blocks already being read into buffer.
I'm thinking it doesn't matter so much which buffer, default, keep, or 16k is being
used, if further analysis/testing shows benefit of this index being in cache, use keep.
Test available scenarios a few times and use which is best.
>>> [EMAIL PROTECTED] 02/20/03 10:54AM >>>
Hi John,
If I'm understanding what I'm reading from the 9i Concepts Guide, I think that
non-default block size objects can only go into the cache with the same block size
(meaning it can't go into keep buffer). I'm making this assumption because we can't
create a tablespace of 16k blocksize until db_16k_cache exists. However, I'm working
on testing this today and will reply with results.
Darrell
>>> [EMAIL PROTECTED] 02/19/03 01:50PM >>>
I have indexes in a 16k page size tablespace.
I have the following init.ora parameters:
db_block_size=4096
db_cache_size=600M
db_keep_cache_size=200M
db_16k_cache_size=200M
If I alter an index to put it in the keep pool, how does Oracle hande the
discrepancy between the 4k default keep buffer and the 16k index block size?
Am I better off keeping the index in the 16k cache or in the db_keep_cache
pool?
Since statspack shows:
Event Waits Timeouts Time (s) (ms)
/txn
-- -- --
db file sequential read 2,725,553 0 40,710 15
355.2
I assume my indexes should be cached more to reduce the waits.
Is that correct?
Any advice would be appreciated.
Thanks
John Baylis
Database Administrator
Canadian Forest Products Ltd.
Vancouver B.C. Canada
> (604) 697-6476 (Office)
> (604) 313-6054 (Cell)
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Darrell Landrum
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Darrell Landrum
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
--
RE: Question about buffer cache
John, My earlier assumptions were wrong (usually are, that's why I test). I set up a 9.2 instance with these parameters: db_cache_size=300M db_keep_cache_size=40M db_16k_cache_size=40M db_block_size=8192 Then, I created a tablespace MY16KTBSP with a 16k block size. Then, create table TESTA tablespace MY16KTBSP storage(buffer_cache KEEP); And this works. Does this really utilize the keep buffer cache? I'm not sure because it also worked in another test when I had no keep buffer. I'm not sure how to tell which buffer the blocks are in right now, maybe another list user can help us both with that. However, I just read your question again and realized I'm not anywhere in the neighborhood of addressing your issue which is your 'waits'. We aren't concerned with the number of waits (yes, but not only), but also the time spent waiting and why. A lot more information is needed really. Size and content of the table? Other indexes on the table? Are statisics up to date and accurate? Is use of this index really appropriate, sometimes full table scan is faster. Something else maybe another person could address, I'm not sure that db file sequential reads are physical only. They may be physical and logical depending on your blocks already being read into buffer. I'm thinking it doesn't matter so much which buffer, default, keep, or 16k is being used, if further analysis/testing shows benefit of this index being in cache, use keep. Test available scenarios a few times and use which is best. >>> [EMAIL PROTECTED] 02/20/03 10:54AM >>> Hi John, If I'm understanding what I'm reading from the 9i Concepts Guide, I think that non-default block size objects can only go into the cache with the same block size (meaning it can't go into keep buffer). I'm making this assumption because we can't create a tablespace of 16k blocksize until db_16k_cache exists. However, I'm working on testing this today and will reply with results. Darrell >>> [EMAIL PROTECTED] 02/19/03 01:50PM >>> I have indexes in a 16k page size tablespace. I have the following init.ora parameters: db_block_size=4096 db_cache_size=600M db_keep_cache_size=200M db_16k_cache_size=200M If I alter an index to put it in the keep pool, how does Oracle hande the discrepancy between the 4k default keep buffer and the 16k index block size? Am I better off keeping the index in the 16k cache or in the db_keep_cache pool? Since statspack shows: Event Waits Timeouts Time (s) (ms) /txn -- -- -- db file sequential read 2,725,553 0 40,710 15 355.2 I assume my indexes should be cached more to reduce the waits. Is that correct? Any advice would be appreciated. Thanks John Baylis Database Administrator Canadian Forest Products Ltd. Vancouver B.C. Canada > (604) 697-6476 (Office) > (604) 313-6054 (Cell) > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Question about buffer cache
Hi John, If I'm understanding what I'm reading from the 9i Concepts Guide, I think that non-default block size objects can only go into the cache with the same block size (meaning it can't go into keep buffer). I'm making this assumption because we can't create a tablespace of 16k blocksize until db_16k_cache exists. However, I'm working on testing this today and will reply with results. Darrell >>> [EMAIL PROTECTED] 02/19/03 01:50PM >>> I have indexes in a 16k page size tablespace. I have the following init.ora parameters: db_block_size=4096 db_cache_size=600M db_keep_cache_size=200M db_16k_cache_size=200M If I alter an index to put it in the keep pool, how does Oracle hande the discrepancy between the 4k default keep buffer and the 16k index block size? Am I better off keeping the index in the 16k cache or in the db_keep_cache pool? Since statspack shows: Event Waits Timeouts Time (s) (ms) /txn -- -- -- db file sequential read 2,725,553 0 40,710 15 355.2 I assume my indexes should be cached more to reduce the waits. Is that correct? Any advice would be appreciated. Thanks John Baylis Database Administrator Canadian Forest Products Ltd. Vancouver B.C. Canada > (604) 697-6476 (Office) > (604) 313-6054 (Cell) > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Question about buffer cache
Title: RE: Question about buffer cache I have indexes in a 16k page size tablespace. I have the following init.ora parameters: db_block_size=4096 db_cache_size=600M db_keep_cache_size=200M db_16k_cache_size=200M If I alter an index to put it in the keep pool, how does Oracle hande the discrepancy between the 4k default keep buffer and the 16k index block size? Am I better off keeping the index in the 16k cache or in the db_keep_cache pool? Since statspack shows: Event Waits Timeouts Time (s) (ms) /txn -- -- -- db file sequential read 2,725,553 0 40,710 15 355.2 I assume my indexes should be cached more to reduce the waits. Is that correct? Any advice would be appreciated. Thanks John Baylis Database Administrator Canadian Forest Products Ltd. Vancouver B.C. Canada > (604) 697-6476 (Office) > (604) 313-6054 (Cell) >
Re: Live Webcast: The death of the buffer cache hit ratio
Hi, > You missed it, it seems that you were an hour late. > You can download it from www.precise.com. When the archives appear there? It's still in "pre-register" state. Alexandre -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexandre Gorbatchev INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Live Webcast: The death of the buffer cache hit ratio
already registered. Henry - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, August 02, 2002 6:33 PM > Ok, everybody, let's show up in support of Anjo. > > This should be a great presentation. > > Sorry if someone has already posted this to the list today, I just haven't > had time to read it yet today. > > Jared > > - > > SearchEBusiness invites you to this Vendor Connection Webcast: > > ********* > TOPIC: The death of the buffer cache hit ratio > WHEN:Wednesday, August 7 at 11:00 a.m. EDT (15:00 GMT) > SPEAKER: Anjo Kolk, Chief Oracle Technologist, Precise Software and > renowned Oracle performance author > SPONSOR: Precise Software > PRE-REGISTER FOR THIS WEBCAST TODAY: > http://searchEBusiness.com/webcasts > * > > > -- About this Webcast -- > > Join Anjo Kolk, Chief Oracle Technologist at Precise Software and > renowned Oracle performance author, for his views on Oracle > performance tuning. Anjo will discuss how conventional tuning > approaches rely too heavily on checking the buffer cache hit ratio. > Many DBAs do their best to get a 99% or better hit ratio but discover > that the performance of their database isn't really improving when > the hit ratio gets better. Anjo will discuss these points and others: > > * If the hit ratio was tuned to 99% or better, why is end user > response time still bad? > * If tuning the hit ratio is not the answer to end-user response > time, what is? > * Why does a hit ratio do exactly the opposite of what you would > think? > * Logical I/Os -- The myths dispelled. > * How to calculate the cost of a logical I/O. > * How to use the response time model in Oracle to find performance > problems. > > If database performance is important to you, click here to > pre-register for this Webcast: http://searchEBusiness.com/webcasts > > * > -- Sponsored By: Precise Software Solutions > -* > Precise Software Solutions (Nasdaq: PRSE) headquartered in Westwood, > MA, delivers Precise i3, a comprehensive solution for Application > Performance Management that focuses directly on the end-user > experience and delivers a rapid return on technology investments. > Precise i3 proactively detects and corrects the root causes of > performance degradation before they affect response times. Visit us > at http://www.precise.com. > * > > > -- Invite a colleague -- > > If you think this event topic would be interesting to a colleague, > invite them to this Vendor Connection Webcast. All you need to do is > forward this e-mail. > > > > Unsubscribe from 'Updates on upcoming online events' > - Simply Reply to this Email with REMOVE within the Body or Subject > > or > - Go to: http://searchEBusiness.techtarget.com/register > - Log in to edit your profile. > - Click on the link to Edit email subscriptions. > - Uncheck the box next to the newsletter you wish >to unsubscribe from. > - When finished, click "Save Changes to My Profile." > > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Henry Poras INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Live Webcast: The death of the buffer cache hit ratio
Thanks Jared, Drinks are on me ;-) [EMAIL PROTECTED] wrote: Ok, everybody, let's show up in support of Anjo.This should be a great presentation. Sorry if someone has already posted this to the list today, I just haven'thad time to read it yet today.Jared-SearchEBusiness invites you to this Vendor Connection Webcast:*TOPIC: The death of the buffer cache hit ratioWHEN:Wednesday, August 7 at 11:00 a.m. EDT (15:00 GMT)SPEAKER: Anjo Kolk, Chief Oracle Technologist, Precise Software and renowned Oracle performance authorSPONSOR: Precise SoftwarePRE-REGISTER FOR THIS WEBCAST TODAY: http://searchEBusiness.com/webcasts *-- About this Webcast --- ---Join Anjo Kolk, Chief Oracle Technologist at Precise Software andrenowned Oracle performance author, for his views on Oracleperformance tuning. Anjo will discuss how conventional tuningapproaches rely too heavily on checking the buffer cache hit ratio.Many DBAs do their best to get a 99% or better hit ratio but discoverthat the performance of their database isn't really improving whenthe hit ratio gets better. Anjo will discuss these points and others:* If the hit ratio was tuned to 99% or better, why is end user response time still bad?* If tuning the hit ratio is not the answer to end-user response time, what is? * Why does a hit ratio do exactly the opposite of what you would think?* Logical I/Os -- The myths dispelled.* How to calculate the cost of a logical I/O.* How to use the response time model in Oracle to find performance problems.If database performance is important to you, c lick here topre-register for this Webcast: http://searchEBusiness.com/webcasts *-- Sponsored By: Precise Software Solutions-*Precise Software Solutions (Nasdaq: PRSE) headquartered in Westwood,MA, delivers Precise i3, a comprehensive solution for ApplicationPerformance Management that focuses directly on the end-userexperience and delivers a rapid return on technology investments.Precise i3 proactively detects and corrects the root causes ofperformance degradation before they affect response times. Visit usat http://www.precise.com. *--- --- Invite a colleague --If you think this event topic would be interesting to a colleague,invite them to this Vendor Connection Webcast. All you need to do isforward this e-mail.Unsubscribe from 'Updates on upcoming online events' - Simply Reply to this Email with REMOVE within the Body or Subject or - Go to: http://searchEBusiness.techtarget.com/register - Log in to edit your profile. - Click on the link to Edit email subscriptions. - Uncheck the box next to the newsletter you wishto unsubscribe from. - When finished, click "Save Changes to My Profile."
Re: Live Webcast: The death of the buffer cache hit ratio
Well no Jacques, here's your chance to see what the competition is up to. :) Jared On Friday 02 August 2002 15:59, Jacques Kilchoer wrote: > > -Original Message- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > > > > Ok, everybody, let's show up in support of Anjo. > > > > This should be a great presentation. > > > > Sorry if someone has already posted this to the list today, I > > just haven't > > had time to read it yet today. > > But he's the competition! :) Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1" Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Live Webcast: The death of the buffer cache hit ratio
Title: RE: Live Webcast: The death of the buffer cache hit ratio > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > > Ok, everybody, let's show up in support of Anjo. > > This should be a great presentation. > > Sorry if someone has already posted this to the list today, I > just haven't > had time to read it yet today. But he's the competition! :)
Live Webcast: The death of the buffer cache hit ratio
Ok, everybody, let's show up in support of Anjo. This should be a great presentation. Sorry if someone has already posted this to the list today, I just haven't had time to read it yet today. Jared - SearchEBusiness invites you to this Vendor Connection Webcast: * TOPIC: The death of the buffer cache hit ratio WHEN:Wednesday, August 7 at 11:00 a.m. EDT (15:00 GMT) SPEAKER: Anjo Kolk, Chief Oracle Technologist, Precise Software and renowned Oracle performance author SPONSOR: Precise Software PRE-REGISTER FOR THIS WEBCAST TODAY: http://searchEBusiness.com/webcasts * -- About this Webcast -- Join Anjo Kolk, Chief Oracle Technologist at Precise Software and renowned Oracle performance author, for his views on Oracle performance tuning. Anjo will discuss how conventional tuning approaches rely too heavily on checking the buffer cache hit ratio. Many DBAs do their best to get a 99% or better hit ratio but discover that the performance of their database isn't really improving when the hit ratio gets better. Anjo will discuss these points and others: * If the hit ratio was tuned to 99% or better, why is end user response time still bad? * If tuning the hit ratio is not the answer to end-user response time, what is? * Why does a hit ratio do exactly the opposite of what you would think? * Logical I/Os -- The myths dispelled. * How to calculate the cost of a logical I/O. * How to use the response time model in Oracle to find performance problems. If database performance is important to you, click here to pre-register for this Webcast: http://searchEBusiness.com/webcasts * -- Sponsored By: Precise Software Solutions -* Precise Software Solutions (Nasdaq: PRSE) headquartered in Westwood, MA, delivers Precise i3, a comprehensive solution for Application Performance Management that focuses directly on the end-user experience and delivers a rapid return on technology investments. Precise i3 proactively detects and corrects the root causes of performance degradation before they affect response times. Visit us at http://www.precise.com. * -- Invite a colleague -- If you think this event topic would be interesting to a colleague, invite them to this Vendor Connection Webcast. All you need to do is forward this e-mail. Unsubscribe from 'Updates on upcoming online events' - Simply Reply to this Email with REMOVE within the Body or Subject > or - Go to: http://searchEBusiness.techtarget.com/register - Log in to edit your profile. - Click on the link to Edit email subscriptions. - Uncheck the box next to the newsletter you wish to unsubscribe from. - When finished, click "Save Changes to My Profile." -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: buffer cache -> and an empty v_$BH
in versions of oracle prior to v8.x, the v$bh view was dependent on a fixed
table called ext_to_objs, which contained a static copy of info on objects
from the data dictionary. to refresh the ext_to_objs table, rerun the
"catparr.sql" script, which is located in "$ORACLE_HOME/rdbms/admin"
periodically...
>From v8.x onward, the ext_to_objs table is obsolete and v$bh (and dependent
views such as v$cache and v$ping) read directly from the data dictionary...
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, June 12, 2002 1:08 AM
> With some changes (v_$bh instead of x_$bh, objd instead of obj) the '
> buffers distribution by objects ' script was worked BUT MY v_$bh IS EMPTY.
> Any idea why?
>
>
> -Original Message-
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Date: miercuri 12 iunie 2002 05:32
>
>
> First query does exactly this, but can be very slow, I mean very.
> Second query runs much faster, but care needs to be taken about CLUSTERs,
> all objects in cluster share the same data_object_id.
>
> Alex.
>
> -- buffers distribution by segments
> select
> count(*) buffers
> ,e.tablespace_name
> ,e.owner||'.'||e.segment_name||' ('||e.segment_type||')' segment
> from
> sys.x_$bh b
> ,sys.dba_extents e
> where b.file# = e.file_id
> and b.dbablk between e.block_id and e.block_id+e.blocks-1
> group by
> e.owner||'.'||e.segment_name||' ('||e.segment_type||')'
> ,e.tablespace_name
> order by 1 desc, 2, 3
> ;
> --
>
> -- buffers distribution by objects
> select
> count(*) buffers
> ,b.obj d_obj_id
> ,o.owner||'.'||o.object_name||' ('||object_type||')' object
> from
> sys.x_$bh b
> ,sys.dba_objects o
> where o.data_object_id = b.obj
> group by b.obj, o.owner||'.'||o.object_name||' ('||object_type||')'
> order by 1 desc, 2, 3
> ;
> --
>
>
>
>
> -Original Message-
> Sent: Tuesday, June 11, 2002 6:08 PM
> To: Multiple recipients of list ORACLE-L
>
>
>
> Hi Guys ,
> IS there any way I can find what is occupying how much of buffer cache .
> Like .. what table is taking most of space etc .
>
> Thanks ,
> Bp
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Tim Gorman
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: buffer cache -> and an empty v_$BH
With some changes (v_$bh instead of x_$bh, objd instead of obj) the '
buffers distribution by objects ' script was worked BUT MY v_$bh IS EMPTY.
Any idea why?
-Original Message-
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: miercuri 12 iunie 2002 05:32
First query does exactly this, but can be very slow, I mean very.
Second query runs much faster, but care needs to be taken about CLUSTERs,
all objects in cluster share the same data_object_id.
Alex.
-- buffers distribution by segments
select
count(*) buffers
,e.tablespace_name
,e.owner||'.'||e.segment_name||' ('||e.segment_type||')' segment
from
sys.x_$bh b
,sys.dba_extents e
where b.file# = e.file_id
and b.dbablk between e.block_id and e.block_id+e.blocks-1
group by
e.owner||'.'||e.segment_name||' ('||e.segment_type||')'
,e.tablespace_name
order by 1 desc, 2, 3
;
--
-- buffers distribution by objects
select
count(*) buffers
,b.obj d_obj_id
,o.owner||'.'||o.object_name||' ('||object_type||')' object
from
sys.x_$bh b
,sys.dba_objects o
where o.data_object_id = b.obj
group by b.obj, o.owner||'.'||o.object_name||' ('||object_type||')'
order by 1 desc, 2, 3
;
--
-Original Message-
Sent: Tuesday, June 11, 2002 6:08 PM
To: Multiple recipients of list ORACLE-L
Hi Guys ,
IS there any way I can find what is occupying how much of buffer cache .
Like .. what table is taking most of space etc .
Thanks ,
Bp
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: buffer cache
The X$BH table is a view into the Buffer Cache which has a column OBJ which can be joined to the OBJ$ table (a.k.a. DBA_OBJECTS view) via the column DATAOBJ# (a.k.a. column DATA_OBJECT_ID in DBA_OBJECTS). But for rollback segments, the OBJ column needs some special treatment, so I wouldn't recommend querying X$BH directly. Besides, you'd have to be SYS to do that, and it's never a good idea to fool around logged on as SYS often. Anybody is bound to get burned doing that, sooner or later... A good view that performs this join (and translates the TYPE# numeric column to a readable string as well as handling the translation of OBJ for rollback segments) is V$CACHE, which is normally associated with OPS/RAC but can be created in non-OPS/RAC environments too. Just CONNECT INTERNAL and run the script "catparr.sql" in the "$ORACLE_HOME/rdbms/admin" directory to create V$CACHE (and other views)... Hope this helps... - Original Message - From: BigP To: Multiple recipients of list ORACLE-L Sent: Tuesday, June 11, 2002 7:08 PM Subject: buffer cache Hi Guys , IS there any way I can find what is occupying how much of buffer cache . Like .. what table is taking most of space etc Thanks , Bp
Re: buffer cache
select obj.name, trunc(count(*)*100/vp.value,2) percentage from sys.x$bh bh, sys.obj$ obj, (select value from v$parameter where name = 'db_block_buffers') vp where bh.obj = obj.dataobj# group by obj.name, value order by percentage desc; will work (at least under 8i and 9i). On Tuesday, June 11, 2002, at 09:08 PM, BigP wrote: Hi Guys , IS there any way I can find what is occupying how much of buffer cache . Like .. what table is taking most of space etc . Thanks , Bp // George Schlossnagle // Principal Consultant // OmniTI, Inc http://www.omniti.com // (c) 301.343.6422 (e) [EMAIL PROTECTED] // 1024D/1100A5A0 1370 F70A 9365 96C9 2F5E 56C2 B2B9 262F 1100 A5A0
RE: buffer cache
Title: RE: buffer cache
First query does exactly this, but can be very slow, I mean very.
Second query runs much faster, but care needs to be taken about CLUSTERs, all objects in cluster share the same data_object_id.
Alex.
-- buffers distribution by segments
select
count(*) buffers
,e.tablespace_name
,e.owner||'.'||e.segment_name||' ('||e.segment_type||')' segment
from
sys.x_$bh b
,sys.dba_extents e
where b.file# = e.file_id
and b.dbablk between e.block_id and e.block_id+e.blocks-1
group by
e.owner||'.'||e.segment_name||' ('||e.segment_type||')'
,e.tablespace_name
order by 1 desc, 2, 3
;
--
-- buffers distribution by objects
select
count(*) buffers
,b.obj d_obj_id
,o.owner||'.'||o.object_name||' ('||object_type||')' object
from
sys.x_$bh b
,sys.dba_objects o
where o.data_object_id = b.obj
group by b.obj, o.owner||'.'||o.object_name||' ('||object_type||')'
order by 1 desc, 2, 3
;
--
-Original Message-
From: BigP [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, June 11, 2002 6:08 PM
To: Multiple recipients of list ORACLE-L
Subject: buffer cache
Hi Guys ,
IS there any way I can find what is occupying how much of buffer cache . Like .. what table is taking most of space etc .
Thanks ,
Bp
Re: buffer cache
p,
this query will tell you how many blocks are in cache
for each segment. multiply by your db_block_size and
divide that number by 1048576 to get megabytes.
remember that full table scan blocks get put on the
LRU end of the LRU list (pre-816) so they might not be
represented fairly by this query.
hth,
jack silvey
select
e.owner||'.'||e.segment_name segment_name,
sum(cnt) all_buffers,
sum(hot) hot_buffers,
sum(tch) touches
from
( select
min(file#||'.'||dbablk) fb,
count(*) cnt,
sum(decode(lru_flag, 8, 1, 0)) hot,
sum(tch) tch
from
sys.x_$bh
where
inst_id = userenv('Instance') and
state in (1, 3)
group by
obj,
class
) b,
sys.apt_extents e
where
e.file_id = substr(b.fb, 1, instr(b.fb, '.') - 1)
and
substr(b.fb, instr(b.fb, '.') + 1) between
e.block_id and e.block_id + e.blocks - 1
group by
e.owner||'.'||e.segment_name
order by
2
/
--- BigP <[EMAIL PROTECTED]> wrote:
> Hi Guys ,
> IS there any way I can find what is occupying how
> much of buffer cache . Like .. what table is taking
> most of space etc .
> Thanks ,
> Bp
>
__
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack Silvey
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
buffer cache
Hi Guys , IS there any way I can find what is occupying how much of buffer cache . Like .. what table is taking most of space etc . Thanks , Bp
Dump of the buffer cache to trace
Hi, Has anyone run into this error. I had an 8.1.6.3 database on Solaris hang and found hundreds of waits in v$session_wait. I also found the following in the alert.log: Dumping the buffer at level 8 instead of level 0 For full buffer dumps enable the following named event alter session set events 'immediate trace name full_dumps level 1' I found one forum on Metalink in which they said that it's related to bug 791428 and the explaination is that database encountered an internal error which causes a dump of the buffer cache to a trace file. I was not able to find this bug on Metalink. Thanks, Mike _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mike Killough INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: I need help understanding how Oracle uses the OS buffer cache...
We are having 134MB for buffer cache with same platform and version. You can go upto 256MB,if you want. There are other list members like Kirti who can give better explanation of this. Regards MOHAMMAD RAFIQ Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Fri, 21 Sep 2001 11:30:27 -0800 Hi Everyone, We have been experiencing some I/O issues; and the systems support team (HP) thinks that we should bump up our OS buffer cache. Currently we are sitting at 150MB; after researching on Metalink the recommendation from Oracle for HPUX is to have a max of 128MB of buffer cache. I have been trying to find a document to help me understand how Oracle uses this buffer cache, and even have opened up a TAR with Oracle; but does anyone know of a document or a book which would help me understand this? Metalink docs say that the buffer cache should be set to <=128MB to avoid "losing writes"; I cannot make sense of this statement. Could someone help me with this? We have HPUX 11.0 with 3 550MHZ CPU's and 4GB of memory running SIEBEL's CRM plus a couple of other databases; does anyone else have similar configuration? If so, what is your buffer cache, and why have you set it so? Any help would greatly be appreciated. Thanks, Pranav Shukle Database Administrator EPCOR Information Services 780-412-3052 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shukle, Pranav INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
I need help understanding how Oracle uses the OS buffer cache...
Hi Everyone, We have been experiencing some I/O issues; and the systems support team (HP) thinks that we should bump up our OS buffer cache. Currently we are sitting at 150MB; after researching on Metalink the recommendation from Oracle for HPUX is to have a max of 128MB of buffer cache. I have been trying to find a document to help me understand how Oracle uses this buffer cache, and even have opened up a TAR with Oracle; but does anyone know of a document or a book which would help me understand this? Metalink docs say that the buffer cache should be set to <=128MB to avoid "losing writes"; I cannot make sense of this statement. Could someone help me with this? We have HPUX 11.0 with 3 550MHZ CPU's and 4GB of memory running SIEBEL's CRM plus a couple of other databases; does anyone else have similar configuration? If so, what is your buffer cache, and why have you set it so? Any help would greatly be appreciated. Thanks, Pranav Shukle Database Administrator EPCOR Information Services 780-412-3052 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shukle, Pranav INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Theorical question about DBWR and Buffer Cache lists
Hope someone can answer this one. I'd just like to know how many lists are there in the SGA to administrate the buffer cache? (LRUW, LRU, hash chain list, etc) And how are they used by the DBWR ? TIA ___ Do You Yahoo!? Yahoo! Messenger: Comunicación instantánea gratis con tu gente - http://messenger.yahoo.es -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Pablo=20ksksksk?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: negative value for buffer cache hit ratio
Infact one of our production database (8.0.x) has a buffer cache hit ratio of 2% for the last 2 years. All others are about 90%+. Strange thing is that users are so happy with the performance of this database with 2% hitratio, they do not want us to increase the buffer cache!! Rama Rachel Carmichael wrote: > Ramon, > > if you find the performance of the database is good, why are you trying to > fix it?? > > Cache Hit Ratio is not the be-all and end-all of tuning. If the cache hit > ratio is high, and you have large numbers of wait events, then your database > needs tuning. > > If the cache hit ratio is low, but there are no wait events then your > database does not need tuning. > > Rachel > > >From: "Ramon Estevez" <[EMAIL PROTECTED]> > >Reply-To: [EMAIL PROTECTED] > >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > >Subject: RE: negative value for buffer cache hit ratio > >Date: Wed, 08 Aug 2001 06:27:11 -0800 > > > >Thanks, > > > >As far as I know, the correct value is as close to 100% as possible. > >So, if mine is 0.68 > > > >-* > >MISS_RATEI > >-I > > 0.67%I > > I > >This is now 9:15 am. I > >-* > > > >I find the performance of the DB good, but as the result of the query > >I think that it could get better. > > > >I executed this script that was posted yesterday in the list. > > > > > >select to_char(100 * misses / (logical - physical + misses), '9990.00') > >|| > > 2 '%' miss_rate > > 3from ( select total_waits misses > > 4from sys.v_$system_event > > 5 where event = 'db file sequential read'), > > 6 ( select value physical > > 7 from sys.v_$sysstat > > 8where name = 'physical reads'), > > 9 ( select sum(value) logical > >10 from sys.v_$sysstat > >11where name like '%consistent read gets' > >12 or name = 'db block gets'); > > > > > > > >Ramon Estevez > >[EMAIL PROTECTED] > > > > > > > > > > > >-Mensaje original- > >De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de K > >Gopalakrishnan > >Enviado el: Tuesday, 07 August, 2001 5:07 PM > >Para: Multiple recipients of list ORACLE-L > >Asunto: RE: negative value for buffer cache hit ratio > > > > > >Hi, > > > >Generally speaking.. HIT RATIOS does not give the true > >picture always. I have seen databases performing > >extremely good with 50% and bad databases with 99% hit > >ratio. > > > >You should never decide the database performance based > >on hit ratio and 90% hit ratio does not mean that 90% > >of the data is ALWAYS read from the cache. It > >translates in to something like this..A block is read > >(consistent get) 9-10 times before written to disk.. > > > >You should check your system wide wait statistics for > >better tuning.. > > > > > > > > > >--- Ramon Estevez <[EMAIL PROTECTED]> > >wrote: > > > Hi, > > > > > > My misses is > > > > > > MISS_RATE > > > - > > > 0.68% > > > > > > Is that good or bad, Oracle 8.0.5 Standard Edition, > > > Windows 2000. > > > > > > Is there a 8.1.7 Standard Edition Version ? > > > > > > Ramon Estevez > > > [EMAIL PROTECTED] > > > > > > > > > > > >= > >Have a nice day !! > > > >Best Regards, > >K Gopalakrishnan, > >Bangalore, INDIA. > > > >__ > >Do You Yahoo!? > >Make international calls for as low as $.04/minute with Yahoo! Messenger > >http://phonecard.yahoo.com/ > >-- > >Please see the official ORACLE-L FAQ: http://www.orafaq.com > >-- > >Author: K Gopalakrishnan > > INET: [EMAIL PROTECTED] > > > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > >San Diego, California-- Public Internet access / Mailing Lists > > > >To REMOVE yourself from this mailing list, send an E-Mail message > >to: [EMAIL PROTECTED] (note EXACT spellin
RE: negative value for buffer cache hit ratio
Thanks Rachel good point. :-) Ramón Estévez [EMAIL PROTECTED] -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de Rachel Carmichael Enviado el: Wednesday, 08 August, 2001 12:25 PM Para: Multiple recipients of list ORACLE-L Asunto: RE: negative value for buffer cache hit ratio Ramon, if you find the performance of the database is good, why are you trying to fix it?? Cache Hit Ratio is not the be-all and end-all of tuning. If the cache hit ratio is high, and you have large numbers of wait events, then your database needs tuning. If the cache hit ratio is low, but there are no wait events then your database does not need tuning. Rachel >From: "Ramon Estevez" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: RE: negative value for buffer cache hit ratio >Date: Wed, 08 Aug 2001 06:27:11 -0800 > >Thanks, > >As far as I know, the correct value is as close to 100% as possible. >So, if mine is 0.68 > >-* >MISS_RATEI >-I > 0.67%I > I >This is now 9:15 am. I >-* > >I find the performance of the DB good, but as the result of the query >I think that it could get better. > >I executed this script that was posted yesterday in the list. > > >select to_char(100 * misses / (logical - physical + misses), '9990.00') >|| > 2 '%' miss_rate > 3from ( select total_waits misses > 4from sys.v_$system_event > 5 where event = 'db file sequential read'), > 6 ( select value physical > 7 from sys.v_$sysstat > 8where name = 'physical reads'), > 9 ( select sum(value) logical >10 from sys.v_$sysstat >11where name like '%consistent read gets' >12 or name = 'db block gets'); > > > >Ramon Estevez >[EMAIL PROTECTED] > > > > > >-Mensaje original- >De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de K >Gopalakrishnan >Enviado el: Tuesday, 07 August, 2001 5:07 PM >Para: Multiple recipients of list ORACLE-L >Asunto: RE: negative value for buffer cache hit ratio > > >Hi, > >Generally speaking.. HIT RATIOS does not give the true >picture always. I have seen databases performing >extremely good with 50% and bad databases with 99% hit >ratio. > >You should never decide the database performance based >on hit ratio and 90% hit ratio does not mean that 90% >of the data is ALWAYS read from the cache. It >translates in to something like this..A block is read >(consistent get) 9-10 times before written to disk.. > >You should check your system wide wait statistics for >better tuning.. > > > > >--- Ramon Estevez <[EMAIL PROTECTED]> >wrote: > > Hi, > > > > My misses is > > > > MISS_RATE > > - > > 0.68% > > > > Is that good or bad, Oracle 8.0.5 Standard Edition, > > Windows 2000. > > > > Is there a 8.1.7 Standard Edition Version ? > > > > Ramon Estevez > > [EMAIL PROTECTED] > > > > > > >= >Have a nice day !! > >Best Regards, >K Gopalakrishnan, >Bangalore, INDIA. > >__ >Do You Yahoo!? >Make international calls for as low as $.04/minute with Yahoo! Messenger >http://phonecard.yahoo.com/ >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: K Gopalakrishnan > INET: [EMAIL PROTECTED] > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California-- Public Internet access / Mailing Lists > >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Ramon Estevez > INET: [EMAIL PROTECTED] > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California-- Public Internet access / Mailing Lists > >To REMOVE yourself from this
RE: negative value for buffer cache hit ratio
Ramon, if you find the performance of the database is good, why are you trying to fix it?? Cache Hit Ratio is not the be-all and end-all of tuning. If the cache hit ratio is high, and you have large numbers of wait events, then your database needs tuning. If the cache hit ratio is low, but there are no wait events then your database does not need tuning. Rachel >From: "Ramon Estevez" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: RE: negative value for buffer cache hit ratio >Date: Wed, 08 Aug 2001 06:27:11 -0800 > >Thanks, > >As far as I know, the correct value is as close to 100% as possible. >So, if mine is 0.68 > >-* >MISS_RATEI >-I > 0.67%I > I >This is now 9:15 am. I >-* > >I find the performance of the DB good, but as the result of the query >I think that it could get better. > >I executed this script that was posted yesterday in the list. > > >select to_char(100 * misses / (logical - physical + misses), '9990.00') >|| > 2 '%' miss_rate > 3from ( select total_waits misses > 4from sys.v_$system_event > 5 where event = 'db file sequential read'), > 6 ( select value physical > 7 from sys.v_$sysstat > 8where name = 'physical reads'), > 9 ( select sum(value) logical >10 from sys.v_$sysstat >11where name like '%consistent read gets' >12 or name = 'db block gets'); > > > >Ramon Estevez >[EMAIL PROTECTED] > > > > > >-Mensaje original- >De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de K >Gopalakrishnan >Enviado el: Tuesday, 07 August, 2001 5:07 PM >Para: Multiple recipients of list ORACLE-L >Asunto: RE: negative value for buffer cache hit ratio > > >Hi, > >Generally speaking.. HIT RATIOS does not give the true >picture always. I have seen databases performing >extremely good with 50% and bad databases with 99% hit >ratio. > >You should never decide the database performance based >on hit ratio and 90% hit ratio does not mean that 90% >of the data is ALWAYS read from the cache. It >translates in to something like this..A block is read >(consistent get) 9-10 times before written to disk.. > >You should check your system wide wait statistics for >better tuning.. > > > > >--- Ramon Estevez <[EMAIL PROTECTED]> >wrote: > > Hi, > > > > My misses is > > > > MISS_RATE > > - > > 0.68% > > > > Is that good or bad, Oracle 8.0.5 Standard Edition, > > Windows 2000. > > > > Is there a 8.1.7 Standard Edition Version ? > > > > Ramon Estevez > > [EMAIL PROTECTED] > > > > > > >= >Have a nice day !! > >Best Regards, >K Gopalakrishnan, >Bangalore, INDIA. > >__ >Do You Yahoo!? >Make international calls for as low as $.04/minute with Yahoo! Messenger >http://phonecard.yahoo.com/ >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: K Gopalakrishnan > INET: [EMAIL PROTECTED] > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California-- Public Internet access / Mailing Lists > >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Ramon Estevez > INET: [EMAIL PROTECTED] > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California-- Public Internet access / Mailing Lists > >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing).
Re: negative value for buffer cache hit ratio
Interesting. In 8.1.7 32-bit oracle binary, select * from v$type_size where type = 'UB4' tells me it's 4 bytes. So it's 32 bit and the maximum is 4 billion. Glad to see Jonathan on this forum. Yong Huang [EMAIL PROTECTED] you wrote: > From: K Gopalakrishnan > > Welcome Jonathan, > > I think most of the counter are limited by ub4maxval > and that makes the negative hit ratio. > > Welcome again ! > > > --- Jonathan Lewis > wrote: > > > > It is possible that after 4 months your stats > > have wrapped around the ( ? 64 bit ?) limit > > value for your platform. Check the actual > > values from v$sysstat to see if some of them > > have gone negative or appear to be > > 'counting backwards'. __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: yong huang INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: negative value for buffer cache hit ratio
Thanks, As far as I know, the correct value is as close to 100% as possible. So, if mine is 0.68 -* MISS_RATEI -I 0.67%I I This is now 9:15 am. I -* I find the performance of the DB good, but as the result of the query I think that it could get better. I executed this script that was posted yesterday in the list. select to_char(100 * misses / (logical - physical + misses), '9990.00') || 2 '%' miss_rate 3from ( select total_waits misses 4from sys.v_$system_event 5 where event = 'db file sequential read'), 6 ( select value physical 7 from sys.v_$sysstat 8where name = 'physical reads'), 9 ( select sum(value) logical 10 from sys.v_$sysstat 11where name like '%consistent read gets' 12 or name = 'db block gets'); Ramon Estevez [EMAIL PROTECTED] -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de K Gopalakrishnan Enviado el: Tuesday, 07 August, 2001 5:07 PM Para: Multiple recipients of list ORACLE-L Asunto: RE: negative value for buffer cache hit ratio Hi, Generally speaking.. HIT RATIOS does not give the true picture always. I have seen databases performing extremely good with 50% and bad databases with 99% hit ratio. You should never decide the database performance based on hit ratio and 90% hit ratio does not mean that 90% of the data is ALWAYS read from the cache. It translates in to something like this..A block is read (consistent get) 9-10 times before written to disk.. You should check your system wide wait statistics for better tuning.. --- Ramon Estevez <[EMAIL PROTECTED]> wrote: > Hi, > > My misses is > > MISS_RATE > - > 0.68% > > Is that good or bad, Oracle 8.0.5 Standard Edition, > Windows 2000. > > Is there a 8.1.7 Standard Edition Version ? > > Ramon Estevez > [EMAIL PROTECTED] > > = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ramon Estevez INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: negative value for buffer cache hit ratio
Hello Jonathan !
Thanks for reply !
First , i love your site and may i recommend it to all the listers :
http://www.jlcomp.demon.co.uk/#Index
Second, yes , U are right :
SQL> col HITS for 999,999,999,999,999,999
SQL> select sum(dbbget + conget - pread) hits from x$kcbwds
2where inst_id = userenv('Instance');
HITS
-2,959,172,014
It's 8.0.5. The DB has been up since March,2001
-Original Message-
Sent: Tuesday, August 07, 2001 10:16 PM
To: Multiple recipients of list ORACLE-L
It is possible that after 4 months your stats
have wrapped around the ( ? 64 bit ?) limit
value for your platform. Check the actual
values from v$sysstat to see if some of them
have gone negative or appear to be
'counting backwards'.
Jonathan Lewis
Seminars on getting the best out of Oracle
Last few places available for Sept 10th/11th
See http://www.jlcomp.demon.co.uk/seminar.html
-Original Message-
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 07 August 2001 19:52
|
|Dear gurus !
|I have a negative value for buffer cache hit ratio in my DB which is
up for
|some 4 months .
|Any ideas why ?
|Thanks in advance.
|
|SQL> SELECT (1 - (SUM(DECODE(a.name,'physical reads',value,0)) /
| 2 (SUM(DECODE(a.name,'consistent gets',value,0)) +
| 3 SUM(DECODE(a.name,'db block gets',value,0) * 100 "buffer
cache hit
|ratio"
| 4 FROM v$sysstat a;
|
|buffer cache hit ratio
|--
| -52.99284
|
|--
|Please see the official ORACLE-L FAQ: http://www.orafaq.com
|--
|Author: Andrey Bronfin
| INET: [EMAIL PROTECTED]
|
|Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
|San Diego, California-- Public Internet access / Mailing
Lists
|
|To REMOVE yourself from this mailing list, send an E-Mail message
|to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
|the message BODY, include a line containing: UNSUB ORACLE-L
|(or the name of mailing list you want to be removed from). You may
|also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jonathan Lewis
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Andrey Bronfin
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: negative value for buffer cache hit ratio
Hi Christopher !
SQL> select to_char(100 * misses / (hits + misses), '9990.00') || '%'
miss_rate
2from ( select total_waits misses
3from sys.v_$system_event
4 where event = 'db file sequential read' ),
5 ( select sum(dbbget + conget - pread) hits
6 from x$kcbwds
7 where inst_id = userenv('Instance') )
8 /
MISS_RATE
-
-3.90%
Strange , isn't it ?
The actual results are :
SQL> select total_waits misses
2from sys.v_$system_event
3 where event = 'db file sequential read' ;
MISSES
--
111086623
SQL> col HITS for 999,999,999,999,999,999
SQL> select sum(dbbget + conget - pread) hits from x$kcbwds
2where inst_id = userenv('Instance');
HITS
-2,959,172,014
Why is it negative ?
Do U think it's a buffer overflow or something ?
Thanks a lot !!!
By the way , i forgot what should i run to create those sys.x_$* synonyms
, i.e. sys.x_$kcbwds.
I had to connect as SYS and use x$kcbwds instead.
Thanks again !!!
-Original Message-
Sent: Tuesday, August 07, 2001 8:14 PM
To: Multiple recipients of list ORACLE-L
Try this more accurate query:
select to_char(100 * misses / (hits + misses), '9990.00') || '%' miss_rate
from ( select total_waits misses
from sys.v_$system_event
where event = 'db file sequential read' ),
( select sum(dbbget + conget - pread) hits
from sys.x_$kcbwds
where inst_id = userenv('Instance') )
/
Also, send the actual values if this query still leads to similar results.
The difference here is it takes in account of direct writes.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Andrey Bronfin
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: negative value for buffer cache hit ratio
Thanks a lot , Srini ! I have this feeling too. Unfortunately , bouncing the instance is not an option at the moment (it's up since March , 2001). The other problem is , when i'll restart the instance , the statistics will disappear , so i never know my hit ratio ;-( -Original Message- Sent: Tuesday, August 07, 2001 9:47 PM To: Multiple recipients of list ORACLE-L Kirti, Audrey, I was under the impression that negative numbers are caused by the numbers wrapping around i.e., the database has been up for a while and the statistics pile up and eventually exceed the defined format (e.g. value of 1000+ for a format of 9(3) - to use old COBOL representation !). Makes sense ? I have seen his before. If you bounce the database, all will be well ! Srini Chavali Oracle DBA Cummins Inc "Deshpande, Kirti" <[EMAIL PROTECTED]>@fatcity.com on 08/07/2001 01:13:52 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Andrey, I am not into cache hit ratios, but got curious about your negative value. So I casually searched Metalink for 'negative value for hit ratio'. And got a hit on 'MROC: I am receiving a negative Buffer Cache hit ratio' thread. You may want to check it out, there seems to be a new formula for hit ratio computation in 8i/9i. HTH, Regards, - Kirti Deshpande Verizon Information Services http://www.superpages.com > -Original Message- > From: Andrey Bronfin [SMTP:[EMAIL PROTECTED]] > Sent: Tuesday, August 07, 2001 12:51 PM > To: Multiple recipients of list ORACLE-L > Subject: negative value for buffer cache hit ratio > > > Dear gurus ! > I have a negative value for buffer cache hit ratio in my DB which is up > for > some 4 months . > Any ideas why ? > Thanks in advance. > > SQL> SELECT (1 - (SUM(DECODE(a.name,'physical reads',value,0)) / > 2 (SUM(DECODE(a.name,'consistent gets',value,0)) + > 3 SUM(DECODE(a.name,'db block gets',value,0) * 100 "buffer cache > hit > ratio" > 4 FROM v$sysstat a; > > buffer cache hit ratio > -- > -52.99284 > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Andrey Bronfin > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: negative value for buffer cache hit ratio
Hi, Generally speaking.. HIT RATIOS does not give the true picture always. I have seen databases performing extremely good with 50% and bad databases with 99% hit ratio. You should never decide the database performance based on hit ratio and 90% hit ratio does not mean that 90% of the data is ALWAYS read from the cache. It translates in to something like this..A block is read (consistent get) 9-10 times before written to disk.. You should check your system wide wait statistics for better tuning.. --- Ramon Estevez <[EMAIL PROTECTED]> wrote: > Hi, > > My misses is > > MISS_RATE > - > 0.68% > > Is that good or bad, Oracle 8.0.5 Standard Edition, > Windows 2000. > > Is there a 8.1.7 Standard Edition Version ? > > Ramon Estevez > [EMAIL PROTECTED] > > = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: negative value for buffer cache hit ratio
Hi,
My misses is
MISS_RATE
-
0.68%
Is that good or bad, Oracle 8.0.5 Standard Edition, Windows 2000.
Is there a 8.1.7 Standard Edition Version ?
Ramon Estevez
[EMAIL PROTECTED]
---
Christopher
Thanks. This script ran from 7.3.4 to 8.1.6 with following result...
I hope misses less than 1% is not bad...Any comment
SQL> select to_char(100 * misses / (logical - physical + misses), '9990.00')
||
2 '%' miss_rate
3from ( select total_waits misses
4from sys.v_$system_event
5 where event = 'db file sequential read'),
6 ( select value physical
7 from sys.v_$sysstat
8where name = 'physical reads'),
9 ( select sum(value) logical
10 from sys.v_$sysstat
11where name like '%consistent read gets'
12 or name = 'db block gets');
MISS_RATE
-
0.90%
Regards
MOHAMMAD RAFIQ
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Tue, 07 Aug 2001 12:23:02 -0800
I assume you have 8.1.7, since 8.1.7 they changed things back to how it was
done in 7.3.4.
This script works for 7.3.4 and 8.1.7/9.0.1
select to_char(100 * misses / (logical - physical + misses), '9990.00') ||
'%' miss_rate
from ( select total_waits misses
from sys.v_$system_event
where event = 'db file sequential read'),
( select value physical
from sys.v_$sysstat
where name = 'physical reads'),
( select sum(value) logical
from sys.v_$sysstat
where name like '%consistent read gets'
or name = 'db block gets');
"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."
Christopher R. Spence OCP MCSE MCP A+ RAPTOR CNA
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275
Fuelspot
73 Princeton Street
North, Chelmsford 01863
-Original Message-
Sent: Tuesday, August 07, 2001 3:36 PM
To: Multiple recipients of list ORACLE-L
Ran this query under 8i(HP-UX 11)
SQL> show user
USER is "SYS"
SQL> select to_char(100 * misses / (hits + misses), '9990.00') || '%'
miss_rate
2from ( select total_waits misses
3from sys.v_$system_event
4 where event = 'db file sequential read' ),
5 ( select sum(dbbget + conget - pread) hits
6 from sys.x_$kcbwds
7 where inst_id = userenv('Instance') )
8 /
from sys.x_$kcbwds
*
ERROR at line 6:
ORA-00942: table or view does not exist
Under 7.3.4.5 (HP-UX 10.20)
SQL> show user
user is "SYS"
SQL> select to_char(100 * misses / (hits + misses), '9990.00') || '%'
miss_rate
2from ( select total_waits misses
3from sys.v_$system_event
4 where event = 'db file sequential read' ),
5 ( select sum(dbbget + conget - pread) hits
6 from sys.x_$kcbwds
7 where inst_id = userenv('Instance') )
8 /
where inst_id = userenv('Instance') )
*
ERROR at line 7:
ORA-02003: invalid USERENV parameter
So what is wrong?
Regards
MOHAMMAD RAFIQ
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Tue, 07 Aug 2001 10:13:51 -0800
Try this more accurate query:
select to_char(100 * misses / (hits + misses), '9990.00') || '%' miss_rate
from ( select total_waits misses
from sys.v_$system_event
where event = 'db file sequential read' ),
( select sum(dbbget + conget - pread) hits
from sys.x_$kcbwds
where inst_id = userenv('Instance') )
/
Also, send the actual values if this query still leads to similar results.
The difference here is it takes in account of direct writes.
"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."
Christopher R. Spence OCP MCSE MCP A+ RAPTOR CNA
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275
Fuelspot
73 Princeton Street
North, Chelmsford 01863
-Original Message-
Sent: Tuesday, August 07, 2001 1:51 PM
To: Multiple recipients of list ORACLE-L
Dear gurus !
I have a negative value for buffer cache hit ratio in my DB which is up for
some 4 months . Any ideas why ? Thanks in advance.
SQL> SELECT (1 - (SUM(DECODE(a.name,'physical reads',value,0)) /
2 (SUM(DECODE(a.name,'consistent gets',value,0)) +
3 SUM(DECO
RE: negative value for buffer cache hit ratio
Christopher
Thanks. This script ran from 7.3.4 to 8.1.6 with following result...
I hope misses less than 1% is not bad...Any comment
SQL> select to_char(100 * misses / (logical - physical + misses), '9990.00')
||
2 '%' miss_rate
3from ( select total_waits misses
4from sys.v_$system_event
5 where event = 'db file sequential read'),
6 ( select value physical
7 from sys.v_$sysstat
8where name = 'physical reads'),
9 ( select sum(value) logical
10 from sys.v_$sysstat
11where name like '%consistent read gets'
12 or name = 'db block gets');
MISS_RATE
-
0.90%
Regards
MOHAMMAD RAFIQ
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Tue, 07 Aug 2001 12:23:02 -0800
I assume you have 8.1.7, since 8.1.7 they changed things back to how it was
done in 7.3.4.
This script works for 7.3.4 and 8.1.7/9.0.1
select to_char(100 * misses / (logical - physical + misses), '9990.00') ||
'%' miss_rate
from ( select total_waits misses
from sys.v_$system_event
where event = 'db file sequential read'),
( select value physical
from sys.v_$sysstat
where name = 'physical reads'),
( select sum(value) logical
from sys.v_$sysstat
where name like '%consistent read gets'
or name = 'db block gets');
"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."
Christopher R. Spence OCP MCSE MCP A+ RAPTOR CNA
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275
Fuelspot
73 Princeton Street
North, Chelmsford 01863
-Original Message-
Sent: Tuesday, August 07, 2001 3:36 PM
To: Multiple recipients of list ORACLE-L
Ran this query under 8i(HP-UX 11)
SQL> show user
USER is "SYS"
SQL> select to_char(100 * misses / (hits + misses), '9990.00') || '%'
miss_rate
2from ( select total_waits misses
3from sys.v_$system_event
4 where event = 'db file sequential read' ),
5 ( select sum(dbbget + conget - pread) hits
6 from sys.x_$kcbwds
7 where inst_id = userenv('Instance') )
8 /
from sys.x_$kcbwds
*
ERROR at line 6:
ORA-00942: table or view does not exist
Under 7.3.4.5 (HP-UX 10.20)
SQL> show user
user is "SYS"
SQL> select to_char(100 * misses / (hits + misses), '9990.00') || '%'
miss_rate
2from ( select total_waits misses
3from sys.v_$system_event
4 where event = 'db file sequential read' ),
5 ( select sum(dbbget + conget - pread) hits
6 from sys.x_$kcbwds
7 where inst_id = userenv('Instance') )
8 /
where inst_id = userenv('Instance') )
*
ERROR at line 7:
ORA-02003: invalid USERENV parameter
So what is wrong?
Regards
MOHAMMAD RAFIQ
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Tue, 07 Aug 2001 10:13:51 -0800
Try this more accurate query:
select to_char(100 * misses / (hits + misses), '9990.00') || '%' miss_rate
from ( select total_waits misses
from sys.v_$system_event
where event = 'db file sequential read' ),
( select sum(dbbget + conget - pread) hits
from sys.x_$kcbwds
where inst_id = userenv('Instance') )
/
Also, send the actual values if this query still leads to similar results.
The difference here is it takes in account of direct writes.
"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."
Christopher R. Spence OCP MCSE MCP A+ RAPTOR CNA
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275
Fuelspot
73 Princeton Street
North, Chelmsford 01863
-Original Message-
Sent: Tuesday, August 07, 2001 1:51 PM
To: Multiple recipients of list ORACLE-L
Dear gurus !
I have a negative value for buffer cache hit ratio in my DB which is up for
some 4 months . Any ideas why ? Thanks in advance.
SQL> SELECT (1 - (SUM(DECODE(a.name,'physical reads',value,0)) /
2 (SUM(DECODE(a.name,'consistent gets',value,0)) +
3 SUM(DECODE(a.name,'db block gets',value,0) * 100 "buffer cache
hit
ratio"
4 FROM v$sysstat a;
buffer cache hit ratio
--
-52.99284
--
Plea
Re: negative value for buffer cache hit ratio
Welcome Jonathan, I think most of the counter are limited by ub4maxval and that makes the negative hit ratio. Welcome again ! --- Jonathan Lewis <[EMAIL PROTECTED]> wrote: > > It is possible that after 4 months your stats > have wrapped around the ( ? 64 bit ?) limit > value for your platform. Check the actual > values from v$sysstat to see if some of them > have gone negative or appear to be > 'counting backwards'. > = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: negative value for buffer cache hit ratio
I assume you have 8.1.7, since 8.1.7 they changed things back to how it was
done in 7.3.4.
This script works for 7.3.4 and 8.1.7/9.0.1
select to_char(100 * misses / (logical - physical + misses), '9990.00') ||
'%' miss_rate
from ( select total_waits misses
from sys.v_$system_event
where event = 'db file sequential read'),
( select value physical
from sys.v_$sysstat
where name = 'physical reads'),
( select sum(value) logical
from sys.v_$sysstat
where name like '%consistent read gets'
or name = 'db block gets');
"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."
Christopher R. Spence OCP MCSE MCP A+ RAPTOR CNA
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275
Fuelspot
73 Princeton Street
North, Chelmsford 01863
-Original Message-
Sent: Tuesday, August 07, 2001 3:36 PM
To: Multiple recipients of list ORACLE-L
Ran this query under 8i(HP-UX 11)
SQL> show user
USER is "SYS"
SQL> select to_char(100 * misses / (hits + misses), '9990.00') || '%'
miss_rate
2from ( select total_waits misses
3from sys.v_$system_event
4 where event = 'db file sequential read' ),
5 ( select sum(dbbget + conget - pread) hits
6 from sys.x_$kcbwds
7 where inst_id = userenv('Instance') )
8 /
from sys.x_$kcbwds
*
ERROR at line 6:
ORA-00942: table or view does not exist
Under 7.3.4.5 (HP-UX 10.20)
SQL> show user
user is "SYS"
SQL> select to_char(100 * misses / (hits + misses), '9990.00') || '%'
miss_rate
2from ( select total_waits misses
3from sys.v_$system_event
4 where event = 'db file sequential read' ),
5 ( select sum(dbbget + conget - pread) hits
6 from sys.x_$kcbwds
7 where inst_id = userenv('Instance') )
8 /
where inst_id = userenv('Instance') )
*
ERROR at line 7:
ORA-02003: invalid USERENV parameter
So what is wrong?
Regards
MOHAMMAD RAFIQ
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Tue, 07 Aug 2001 10:13:51 -0800
Try this more accurate query:
select to_char(100 * misses / (hits + misses), '9990.00') || '%' miss_rate
from ( select total_waits misses
from sys.v_$system_event
where event = 'db file sequential read' ),
( select sum(dbbget + conget - pread) hits
from sys.x_$kcbwds
where inst_id = userenv('Instance') )
/
Also, send the actual values if this query still leads to similar results.
The difference here is it takes in account of direct writes.
"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."
Christopher R. Spence OCP MCSE MCP A+ RAPTOR CNA
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275
Fuelspot
73 Princeton Street
North, Chelmsford 01863
-Original Message-
Sent: Tuesday, August 07, 2001 1:51 PM
To: Multiple recipients of list ORACLE-L
Dear gurus !
I have a negative value for buffer cache hit ratio in my DB which is up for
some 4 months . Any ideas why ? Thanks in advance.
SQL> SELECT (1 - (SUM(DECODE(a.name,'physical reads',value,0)) /
2 (SUM(DECODE(a.name,'consistent gets',value,0)) +
3 SUM(DECODE(a.name,'db block gets',value,0) * 100 "buffer cache
hit
ratio"
4 FROM v$sysstat a;
buffer cache hit ratio
--
-52.99284
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Andrey Bronfin
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from). You may also send the HELP
command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Christopher Spence
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
Re: negative value for buffer cache hit ratio
It is possible that after 4 months your stats have wrapped around the ( ? 64 bit ?) limit value for your platform. Check the actual values from v$sysstat to see if some of them have gone negative or appear to be 'counting backwards'. Jonathan Lewis Seminars on getting the best out of Oracle Last few places available for Sept 10th/11th See http://www.jlcomp.demon.co.uk/seminar.html -Original Message- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: 07 August 2001 19:52 | |Dear gurus ! |I have a negative value for buffer cache hit ratio in my DB which is up for |some 4 months . |Any ideas why ? |Thanks in advance. | |SQL> SELECT (1 - (SUM(DECODE(a.name,'physical reads',value,0)) / | 2 (SUM(DECODE(a.name,'consistent gets',value,0)) + | 3 SUM(DECODE(a.name,'db block gets',value,0) * 100 "buffer cache hit |ratio" | 4 FROM v$sysstat a; | |buffer cache hit ratio |-- | -52.99284 | |-- |Please see the official ORACLE-L FAQ: http://www.orafaq.com |-- |Author: Andrey Bronfin | INET: [EMAIL PROTECTED] | |Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 |San Diego, California-- Public Internet access / Mailing Lists | |To REMOVE yourself from this mailing list, send an E-Mail message |to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in |the message BODY, include a line containing: UNSUB ORACLE-L |(or the name of mailing list you want to be removed from). You may |also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: negative value for buffer cache hit ratio
Nothing is wrong in your database. You don't have the required X_$ views created. Change X_$ to X$ or create X_$ views as select * from X$ views. = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: negative value for buffer cache hit ratio
Kirti, Audrey, I was under the impression that negative numbers are caused by the numbers wrapping around i.e., the database has been up for a while and the statistics pile up and eventually exceed the defined format (e.g. value of 1000+ for a format of 9(3) - to use old COBOL representation !). Makes sense ? I have seen his before. If you bounce the database, all will be well ! Srini Chavali Oracle DBA Cummins Inc "Deshpande, Kirti" <[EMAIL PROTECTED]>@fatcity.com on 08/07/2001 01:13:52 PM Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Andrey, I am not into cache hit ratios, but got curious about your negative value. So I casually searched Metalink for 'negative value for hit ratio'. And got a hit on 'MROC: I am receiving a negative Buffer Cache hit ratio' thread. You may want to check it out, there seems to be a new formula for hit ratio computation in 8i/9i. HTH, Regards, - Kirti Deshpande Verizon Information Services http://www.superpages.com > -Original Message- > From: Andrey Bronfin [SMTP:[EMAIL PROTECTED]] > Sent: Tuesday, August 07, 2001 12:51 PM > To: Multiple recipients of list ORACLE-L > Subject: negative value for buffer cache hit ratio > > > Dear gurus ! > I have a negative value for buffer cache hit ratio in my DB which is up > for > some 4 months . > Any ideas why ? > Thanks in advance. > > SQL> SELECT (1 - (SUM(DECODE(a.name,'physical reads',value,0)) / > 2 (SUM(DECODE(a.name,'consistent gets',value,0)) + > 3 SUM(DECODE(a.name,'db block gets',value,0) * 100 "buffer cache > hit > ratio" > 4 FROM v$sysstat a; > > buffer cache hit ratio > -- > -52.99284 > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Andrey Bronfin > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: negative value for buffer cache hit ratio
Ran this query under 8i(HP-UX 11)
SQL> show user
USER is "SYS"
SQL> select to_char(100 * misses / (hits + misses), '9990.00') || '%'
miss_rate
2from ( select total_waits misses
3from sys.v_$system_event
4 where event = 'db file sequential read' ),
5 ( select sum(dbbget + conget - pread) hits
6 from sys.x_$kcbwds
7 where inst_id = userenv('Instance') )
8 /
from sys.x_$kcbwds
*
ERROR at line 6:
ORA-00942: table or view does not exist
Under 7.3.4.5 (HP-UX 10.20)
SQL> show user
user is "SYS"
SQL> select to_char(100 * misses / (hits + misses), '9990.00') || '%'
miss_rate
2from ( select total_waits misses
3from sys.v_$system_event
4 where event = 'db file sequential read' ),
5 ( select sum(dbbget + conget - pread) hits
6 from sys.x_$kcbwds
7 where inst_id = userenv('Instance') )
8 /
where inst_id = userenv('Instance') )
*
ERROR at line 7:
ORA-02003: invalid USERENV parameter
So what is wrong?
Regards
MOHAMMAD RAFIQ
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Tue, 07 Aug 2001 10:13:51 -0800
Try this more accurate query:
select to_char(100 * misses / (hits + misses), '9990.00') || '%' miss_rate
from ( select total_waits misses
from sys.v_$system_event
where event = 'db file sequential read' ),
( select sum(dbbget + conget - pread) hits
from sys.x_$kcbwds
where inst_id = userenv('Instance') )
/
Also, send the actual values if this query still leads to similar results.
The difference here is it takes in account of direct writes.
"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."
Christopher R. Spence OCP MCSE MCP A+ RAPTOR CNA
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275
Fuelspot
73 Princeton Street
North, Chelmsford 01863
-Original Message-
Sent: Tuesday, August 07, 2001 1:51 PM
To: Multiple recipients of list ORACLE-L
Dear gurus !
I have a negative value for buffer cache hit ratio in my DB which is up for
some 4 months . Any ideas why ? Thanks in advance.
SQL> SELECT (1 - (SUM(DECODE(a.name,'physical reads',value,0)) /
2 (SUM(DECODE(a.name,'consistent gets',value,0)) +
3 SUM(DECODE(a.name,'db block gets',value,0) * 100 "buffer cache
hit
ratio"
4 FROM v$sysstat a;
buffer cache hit ratio
--
-52.99284
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Andrey Bronfin
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from). You may also send the HELP
command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Christopher Spence
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
_
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mohammad Rafiq
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: negative value for buffer cache hit ratio
1.- Go to this link of Steve Adam's site: http://www.ixora.com.au/scripts/cache.htm You gonna find a script to estimate the miss rate of the buffer cache based on the assumptions that certain operations are not well compute by statistics as direct path operations. 2.- From 8.X you should rely on v$buffer_pool_statistics from the script $ORACLE_HOME/rdbms/admin/catperf.sql accounting on the several pools you could have. 3.- Remember that even on 8.X, when the database has been opened for a long time and have had a lot of transactions, the value of statistics gets so big that reaches the maximum value and then begins from 0 again. This usually happens with 7.X. Regards. --- "Deshpande, Kirti" <[EMAIL PROTECTED]> wrote: > Andrey, > I am not into cache hit ratios, but got curious > about your negative value. > > So I casually searched Metalink for 'negative value > for hit ratio'. And got > a hit on 'MROC: I am receiving a negative Buffer > Cache hit ratio' thread. > You may want to check it out, there seems to be a > new formula for hit ratio > computation in 8i/9i. > > HTH, > > Regards, > > - Kirti Deshpande > Verizon Information Services >http://www.superpages.com > > > -Original Message- > > From: Andrey Bronfin > [SMTP:[EMAIL PROTECTED]] > > Sent: Tuesday, August 07, 2001 12:51 PM > > To: Multiple recipients of list ORACLE-L > > Subject:negative value for buffer cache hit ratio > > > > > > > Dear gurus ! > > I have a negative value for buffer cache hit ratio > in my DB which is up > > for > > some 4 months . > > Any ideas why ? > > Thanks in advance. > > > > SQL> SELECT (1 - (SUM(DECODE(a.name,'physical > reads',value,0)) / > > 2 (SUM(DECODE(a.name,'consistent > gets',value,0)) + > > 3 SUM(DECODE(a.name,'db block > gets',value,0) * 100 "buffer cache > > hit > > ratio" > > 4 FROM v$sysstat a; > > > > buffer cache hit ratio > > -- > > -52.99284 > > > > -- > > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > > -- > > Author: Andrey Bronfin > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 > FAX: (858) 538-5051 > > San Diego, California-- Public Internet > access / Mailing Lists > > > > > To REMOVE yourself from this mailing list, send an > E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > > the message BODY, include a line containing: UNSUB > ORACLE-L > > (or the name of mailing list you want to be > removed from). You may > > also send the HELP command for other information > (like subscribing). > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Deshpande, Kirti > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California-- Public Internet > access / Mailing Lists > > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). = Eng. Christian Trassens Senior DBA Systems Engineer [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : 541149816062 __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christian Trassens INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: negative value for buffer cache hit ratio
Andrey, I am not into cache hit ratios, but got curious about your negative value. So I casually searched Metalink for 'negative value for hit ratio'. And got a hit on 'MROC: I am receiving a negative Buffer Cache hit ratio' thread. You may want to check it out, there seems to be a new formula for hit ratio computation in 8i/9i. HTH, Regards, - Kirti Deshpande Verizon Information Services http://www.superpages.com > -Original Message- > From: Andrey Bronfin [SMTP:[EMAIL PROTECTED]] > Sent: Tuesday, August 07, 2001 12:51 PM > To: Multiple recipients of list ORACLE-L > Subject: negative value for buffer cache hit ratio > > > Dear gurus ! > I have a negative value for buffer cache hit ratio in my DB which is up > for > some 4 months . > Any ideas why ? > Thanks in advance. > > SQL> SELECT (1 - (SUM(DECODE(a.name,'physical reads',value,0)) / > 2 (SUM(DECODE(a.name,'consistent gets',value,0)) + > 3 SUM(DECODE(a.name,'db block gets',value,0) * 100 "buffer cache > hit > ratio" > 4 FROM v$sysstat a; > > buffer cache hit ratio > -- > -52.99284 > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Andrey Bronfin > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: negative value for buffer cache hit ratio
Try this more accurate query:
select to_char(100 * misses / (hits + misses), '9990.00') || '%' miss_rate
from ( select total_waits misses
from sys.v_$system_event
where event = 'db file sequential read' ),
( select sum(dbbget + conget - pread) hits
from sys.x_$kcbwds
where inst_id = userenv('Instance') )
/
Also, send the actual values if this query still leads to similar results.
The difference here is it takes in account of direct writes.
"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."
Christopher R. Spence OCP MCSE MCP A+ RAPTOR CNA
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275
Fuelspot
73 Princeton Street
North, Chelmsford 01863
-Original Message-
Sent: Tuesday, August 07, 2001 1:51 PM
To: Multiple recipients of list ORACLE-L
Dear gurus !
I have a negative value for buffer cache hit ratio in my DB which is up for
some 4 months . Any ideas why ? Thanks in advance.
SQL> SELECT (1 - (SUM(DECODE(a.name,'physical reads',value,0)) /
2 (SUM(DECODE(a.name,'consistent gets',value,0)) +
3 SUM(DECODE(a.name,'db block gets',value,0) * 100 "buffer cache hit
ratio"
4 FROM v$sysstat a;
buffer cache hit ratio
--
-52.99284
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Andrey Bronfin
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from). You may also send the HELP
command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Christopher Spence
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
negative value for buffer cache hit ratio
Dear gurus ! I have a negative value for buffer cache hit ratio in my DB which is up for some 4 months . Any ideas why ? Thanks in advance. SQL> SELECT (1 - (SUM(DECODE(a.name,'physical reads',value,0)) / 2 (SUM(DECODE(a.name,'consistent gets',value,0)) + 3 SUM(DECODE(a.name,'db block gets',value,0) * 100 "buffer cache hit ratio" 4 FROM v$sysstat a; buffer cache hit ratio -- -52.99284 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
How many lists are there in the buffer cache?
Hi list, I'd like to know how many lists are there in version 8.1.7 in the buffer cache that control buffer's aging. And how does server processes and DBW0 interact with them. TIA ___ Do You Yahoo!? Yahoo! Messenger: Comunicación instantánea gratis con tu gente - http://messenger.yahoo.es -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Pablo=20ksksksk?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Buffer Cache Lists and DBWR
Hi List, I'd just like to know exactly how many lists are there in the SGA to administrate the buffer cache? (LRUW, LRU, hash chain list, etc) And how they are used by the DBWR. thanks a lot. ___ Do You Yahoo!? Yahoo! Messenger: Comunicación instantánea gratis con tu gente - http://messenger.yahoo.es -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Pablo=20ksksksk?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Which tables are in buffer cache.?
I think if you offline a tablespace (or is it read-only? I can't remember), then any relevant buffers are flushed out... hth connor --- "Hillman, Alex" <[EMAIL PROTECTED]> wrote: > Mark, how do you manually remove objects from buffer > cache? I saw you > writing before that you know how to do it and I > would be very interested to > know also if it is not trade secret of course. > > Alex Hillman > > -Original Message- > Sent: Friday, May 04, 2001 11:11 PM > To: Multiple recipients of list ORACLE-L > > > Been here: this is a really interesting exercise! > You can query v$bh (or > its x$ base table) which has a record for every > buffer cache block. Link > on object ID's While tuning a 3000Tx/sec OLTP > database I could get constant > 24*7 performance by by examining objects in the > buffer cache and manually > removing unwanted objects at end of business day. > This prevents required > objects being aged out and having to be reloaded. > Typical scenario is to > remove the index for the partition for 'todays' > transactions to make room > in the buffer cache for 'tomorrows' transactions. > Its good fun tuning at > this level. I wrote a gui via perl & DBI that dumped > the bcache contents > (similar to the oracle tablespace manager gui) and > allows objects to be > manually/automatically removed from memory. I think > DB2 can do this > already. Does 9i allow definition of custom buffer > pools > (buffer_pool_Transactions for example), each with > its own aging algorithm? > Ive got the 9i beta cd here but havent even opened > it. Too busy. > > Mark Teehan > Singapore > > > From: "Bunyamin K.Karadeniz" > <[EMAIL PROTECTED]> > Date: Wed, 2 May 2001 09:23:57 +0300 > Subject: Which tables are in buffer cache.? > > This is a multi-part message in MIME format. > > --=_NextPart_000_00E6_01C0D2E9.9D9A9360 > Content-Type: text/plain; > charset="iso-8859-9" > Content-Transfer-Encoding: quoted-printable > > Hi all gurus, >=20 > I want to learn the way to ook at=20 > Which tables are in buffer cache./Analyzed? > > Is there a system view showing these tables?? > > > > Thanks to All. > > Bunyamin K.Karadeniz > Database Group / Information Systems > Department=20 > HAVELSAN Ankara /TURKEY > Tel : +903122873565 / 1681 > Mobile Tel : +90 535 3357729 > > > > ERG Group > -- > The contents of this email and any attachments are > confidential > and may only be read by the intended recipient. > - > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Mark Teehan > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California-- Public Internet > access / Mailing Lists > > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Hillman, Alex > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California-- Public Internet > access / Mailing Lists > > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) "Some days you're the pigeon, some days you're the statue" Do You Yahoo!? Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk or your free @yahoo.ie address at http://mail.yahoo.ie -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED]
RE: Which tables are in buffer cache.?
Mark, how do you manually remove objects from buffer cache? I saw you writing before that you know how to do it and I would be very interested to know also if it is not trade secret of course. Alex Hillman -Original Message- Sent: Friday, May 04, 2001 11:11 PM To: Multiple recipients of list ORACLE-L Been here: this is a really interesting exercise! You can query v$bh (or its x$ base table) which has a record for every buffer cache block. Link on object ID's While tuning a 3000Tx/sec OLTP database I could get constant 24*7 performance by by examining objects in the buffer cache and manually removing unwanted objects at end of business day. This prevents required objects being aged out and having to be reloaded. Typical scenario is to remove the index for the partition for 'todays' transactions to make room in the buffer cache for 'tomorrows' transactions. Its good fun tuning at this level. I wrote a gui via perl & DBI that dumped the bcache contents (similar to the oracle tablespace manager gui) and allows objects to be manually/automatically removed from memory. I think DB2 can do this already. Does 9i allow definition of custom buffer pools (buffer_pool_Transactions for example), each with its own aging algorithm? Ive got the 9i beta cd here but havent even opened it. Too busy. Mark Teehan Singapore From: "Bunyamin K.Karadeniz" <[EMAIL PROTECTED]> Date: Wed, 2 May 2001 09:23:57 +0300 Subject: Which tables are in buffer cache.? This is a multi-part message in MIME format. --=_NextPart_000_00E6_01C0D2E9.9D9A9360 Content-Type: text/plain; charset="iso-8859-9" Content-Transfer-Encoding: quoted-printable Hi all gurus, =20 I want to learn the way to ook at=20 Which tables are in buffer cache./Analyzed? Is there a system view showing these tables?? Thanks to All. Bunyamin K.Karadeniz Database Group / Information Systems Department=20 HAVELSAN Ankara /TURKEY Tel : +903122873565 / 1681 Mobile Tel : +90 535 3357729 ERG Group -- The contents of this email and any attachments are confidential and may only be read by the intended recipient. - -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Teehan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hillman, Alex INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Which tables are in buffer cache.?
Been here: this is a really interesting exercise! You can query v$bh (or its x$ base table) which has a record for every buffer cache block. Link on object ID's While tuning a 3000Tx/sec OLTP database I could get constant 24*7 performance by by examining objects in the buffer cache and manually removing unwanted objects at end of business day. This prevents required objects being aged out and having to be reloaded. Typical scenario is to remove the index for the partition for 'todays' transactions to make room in the buffer cache for 'tomorrows' transactions. Its good fun tuning at this level. I wrote a gui via perl & DBI that dumped the bcache contents (similar to the oracle tablespace manager gui) and allows objects to be manually/automatically removed from memory. I think DB2 can do this already. Does 9i allow definition of custom buffer pools (buffer_pool_Transactions for example), each with its own aging algorithm? Ive got the 9i beta cd here but havent even opened it. Too busy. Mark Teehan Singapore From: "Bunyamin K.Karadeniz" <[EMAIL PROTECTED]> Date: Wed, 2 May 2001 09:23:57 +0300 Subject: Which tables are in buffer cache.? This is a multi-part message in MIME format. --=_NextPart_000_00E6_01C0D2E9.9D9A9360 Content-Type: text/plain; charset="iso-8859-9" Content-Transfer-Encoding: quoted-printable Hi all gurus, =20 I want to learn the way to ook at=20 Which tables are in buffer cache./Analyzed? Is there a system view showing these tables?? Thanks to All. Bunyamin K.Karadeniz Database Group / Information Systems Department=20 HAVELSAN Ankara /TURKEY Tel : +903122873565 / 1681 Mobile Tel : +90 535 3357729 ERG Group -- The contents of this email and any attachments are confidential and may only be read by the intended recipient. - -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Teehan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Which tables are in buffer cache.?
Thanks so much Suhen.. Bunyamin - Original Message - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Wednesday, May 02, 2001 11:25 AM > Bunyamin, > > You can check how many blocks are in the buffer cache for segments through > the x$bh and v$bh views. > > Try this query, > > select "count",b.object_name from > (select count(*) "count",objd from v$bh > group by objd) a, user_objects b > where a.objd=b.data_object_id > / > > Regards > Suhen > > > > > > Hi all gurus, > > I want to learn the way to ook at > > Which tables are in buffer cache./Analyzed? > > Is there a system view showing these tables?? > > > > Thanks to All. > > Bunyamin K.Karadeniz > Database Group / Information Systems Department > HAVELSAN Ankara /TURKEY > Tel : +903122873565 / 1681 > Mobile Tel : +90 535 3357729 > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Suhen Pather > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bunyamin K.Karadeniz INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Which tables are in buffer cache.?
Bunyamin, You can check how many blocks are in the buffer cache for segments through the x$bh and v$bh views. Try this query, select "count",b.object_name from (select count(*) "count",objd from v$bh group by objd) a, user_objects b where a.objd=b.data_object_id / Regards Suhen Hi all gurus, I want to learn the way to ook at Which tables are in buffer cache./Analyzed? Is there a system view showing these tables?? Thanks to All. Bunyamin K.Karadeniz Database Group / Information Systems Department HAVELSAN Ankara /TURKEY Tel : +903122873565 / 1681 Mobile Tel : +90 535 3357729 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Suhen Pather INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Which tables are in buffer cache.?
Hi all gurus, I want to learn the way to ook at Which tables are in buffer cache./Analyzed? Is there a system view showing these tables?? Thanks to All. Bunyamin K.Karadeniz Database Group / Information Systems Department HAVELSAN Ankara /TURKEY Tel : +903122873565 / 1681 Mobile Tel : +90 535 3357729
