SEO

1. If you select * from a table, shouldn't that load the whole table
into the database buffer cache, provided it fits. I have a script that
I found that shows me what's in the cache, but I cannot seem to make
100% of a tables block fit. I could not seem to find the answer in the
concepts manual.

2. What's does system put in the buffer cache. We notice that after
starting the db, system will occupy a good chunk of the buffer cache,
but that will start to decrease as various queries are run and the data
begins to fill the cache.

RogBa...@gmail.com wrote:
> 1. If you select * from a table, shouldn't that load the whole table
> into the database buffer cache, provided it fits. I have a script
that
> I found that shows me what's in the cache, but I cannot seem to make
> 100% of a tables block fit. I could not seem to find the answer in
the
> concepts manual.

Look up keep and recycle.
http://download-west.oracle.com/docs...emor.htm#11256

>
> 2. What's does system put in the buffer cache. We notice that after
> starting the db, system will occupy a good chunk of the buffer cache,
> but that will start to decrease as various queries are run and the
data
> begins to fill the cache.

I thought you said you had a script?

Maybe you should show us the script. Oracle does a lot of stuff in the
SGA, and unless you are halting the system and looking at all the bytes
in memory, I have difficulty believing any script can create a cohesive
and correct snapshot. Even Oracle doesn't know, google discussions of
delayed block cleanout for an extreme case, but generally for anything
you do, Oracle has to figure out if the necessary blocks are in memory,
and if they are part of your transactional view or if Oracle has to fix
them to look right. Whether it is a good thing to cache or keep an
entire table... depends.

It also helps to post basic informational context:
http://www.dbaoracle.net/readme-cdos.htm

jg
--
@home.com is bogus.
Or, as Tom would say, Why?

RogBaker@gmail.com wrote:
> 1. If you select * from a table, shouldn't that load the whole table
> into the database buffer cache, provided it fits. I have a script that
> I found that shows me what's in the cache ....

Please post the script ... I have my doubts too.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

I have a script, which ecludes SYSTEM and SYS objects. A Co-worker is
running a tool from the Enterprise Manager performance pack or
something that is showing graphs of what's in the Buffer Cache, and it
was showing a lot of SYSTEM objects initally after the database is
started. I suppose I could have edited the script I was using to
include SYS and SYSTEM.

I analyzed the scripts, and the bases for them are dba_objects.blocks
and the number of rows returned from v$bh.
Just ignoring the scripts and looking at dba_objects and v$bh, I get 29
blocks total from v$bh, and 32 blocks from dba_objects.
That gives me the same percentage of the scripts (91% for this example
table). Anyway, the scripts are below for your review.

Whenever I use a script that I find on the internet, I try to verify it
with something I found somewhere else.
Here is the script I used, it's from
http://www.pafumi.net/multi_buffers.htm
--------------------------------------------
set pages 999
set lines 92

ttitle 'Contents of Data Buffers'

drop table t1;

create table t1 as
select o.owner owner,
o.object_name object_name,
o.subobject_name subobject_name,
o.object_type object_type,
count(distinct file# || block#) num_blocks
from dba_objects o, v$bh bh
where o.data_object_id = bh.objd
and o.owner not in ('SYS','SYSTEM')
and bh.status != 'free'
group by o.owner, o.object_name, o.subobject_name, o.object_type
order by count(distinct file# || block#) desc;

column c0 heading "Owner" format a12
column c1 heading "Object|Name" format a20
column c2 heading "Object|Type" format a7
column c3 heading "Number of|Blocks in|Buffer|Cache" format
99,999,999
column c4 heading "% of |object|blocks |in Buffer" format 999
column c5 heading "Buffer|Pool" format a7
column c6 heading "Block|Size" format 99,999

select t1.owner c0,
object_name c1,
case when object_type = 'TABLE PARTITION' then 'TAB PART'
when object_type = 'INDEX PARTITION' then 'IDX PART'
else object_type end c2,
sum(num_blocks) c3,
(sum(num_blocks)/greatest(sum(blocks), .001))*100 c4,
buffer_pool c5,
sum(bytes)/sum(blocks) c6
from t1, dba_segments s
where s.segment_name = t1.object_name
and s.owner = t1.owner
and s.segment_type = t1.object_type
and nvl(s.partition_name,'-') = nvl(t1.subobject_name,'-')
group by t1.owner, object_name, object_type, buffer_pool
having sum(num_blocks) > 10
order by sum(num_blocks) desc;

drop table t1;
---------------------------------------

I found another version that had just about the same results from
"everybody's favorite DBA:"
http://www.dba-oracle.com/art_builder_buffer.htm
----------------------
set pages 999
set lines 80

spool blocks.lst

ttitle 'Contents of Data Buffers'

drop table t1;

create table t1 as
select
o.object_name object_name,
o.object_type object_type,
count(1) num_blocks
from
dba_objects o,
v$bh bh
where
o.object_id = bh.objd
and
o.owner not in ('SYS','SYSTEM')
group by
o.object_name,
o.object_type
order by
count(1) desc
;


column c1 heading "Object|Name" format a30
column c2 heading "Object|Type" format a12
column c3 heading "Number of|Blocks" format 999,999,999,999
column c4 heading "Percentage|of object|data blocks|in Buffer" format
999

select
object_name c1,
object_type c2,
num_blocks c3,
(num_blocks/decode(sum(blocks), 0, .001, sum(blocks)))*100 c4
from
t1,
dba_segments s
where
s.segment_name = t1.object_name
and
num_blocks > 10
group by
object_name,
object_type,
num_blocks
order by
num_blocks desc
;
-------------------------------

"DA Morgan" <damorgan@x.washington.edu> wrote in message
news:1115160207.398912@yasure...
> RogBaker@gmail.com wrote:
>> 1. If you select * from a table, shouldn't that load the whole table
>> into the database buffer cache, provided it fits. I have a script that
>> I found that shows me what's in the cache ....
>
> Please post the script ... I have my doubts too.
> --

The point that's been missed is that if the table being selected is large
(greater than 2% of buffer cache), a full table scan will load blocks
directly into the cold or least recently used end of the buffer cache LRU
list resulting in it's blocks being subsequently overwritten during the
scan. Therefore, *no*, the whole table wouldn't be expected to be found in
the buffer cache, even if it's size is less than the buffer cache.

Thankfully it's expected and desirable behaviour else FTS of large tables
will have the undesirable effect of potentially forcing more useful blocks
out of cache. This mechanism is somewhat version dependent.

Of course, if the FTS is performed in parallel, then direct reads into the
PGA are performed, bypassing the buffer cache entirely.

Cheers

Richard

I don't know if size is the problem. I am using Oracle 9.2
I am the only user in the db. (I did have to make the cache pretty
small by the way). I have 2 tables that I am playing with:
HARITEMS and HARPKGHISTORY. They both are quite small.

According to Oracle, they both have the same number of blocks:
SQL> select segment_name, blocks from dba_segments where segment_name
in ('HARITEMS','HARPKGHISTORY');

SEGMENT_NAME BLOCKS
-------------- ----------
HARITEMS 8
HARPKGHISTORY 8

TOAD tells me that
HARITEMS has 7 Blocks, 0 Empty Blocks
HARPKGHISTORY has 1 Blocks, 6 Empty Blocks

yet when I run the aforementioned scripts after select * from either
table, it will only tell me that 100% of HARITEMS is in the Buffer
Cache.

After reading your post and replies, I still cannot determine what
problem you are attempting to solve, other than a possible
misunderstanding or misconception.
Perhaps some time spent in the concepts manual and other documentation
available online is your best bet.

-bdbafh

RogBaker@gmail.com wrote:
> 1. If you select * from a table, shouldn't that load the whole table
> into the database buffer cache, provided it fits.

No, unless the table is small.

> I have a script that
> I found that shows me what's in the cache, but I cannot seem to make
> 100% of a tables block fit.

Why are you trying to make that happen?

> I could not seem to find the answer in the
> concepts manual.
>
> 2. What's does system put in the buffer cache.

Whatever it needs to. System stuff.

> We notice that after
> starting the db, system will occupy a good chunk of the buffer cache,

What you notice is that after starting the db, the system will occupy a
good chunk of the *occupied portion* of the buffer cache. And of course
it does. What else would be occupying the buffer cache immediately
after a clean restart? Most of it is unused, and the part that is used
is used for system stuff.

> but that will start to decrease as various queries are run and the data
> begins to fill the cache.

Yes, of course.

Xho

--
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service $9.95/Month 30GB

I do not have a problem, I am just trying to understand something
better.

On Wed, 04 May 2005 10:28:40 -0700, RogBaker wrote:

> I do not have a problem, I am just trying to understand something
> better.

Take a peek at the contents of the buffer pool. One way of doing it would
be to take a look at V$BH or V$CACHE. That would be the best way to learn,
PFY.

--
Egoist: A person of low taste, more interested in themselves than in me.