Re: strange behavior in 9.2.0.4 - no blocks in keep buffer (KEEP broken?)

From: Connor McDonald <hamcdc_at_yahoo.co.uk>
Date: Thu, 10 Jun 2004 05:08:17 +0100 (BST)
Message-ID: <20040610040817.32700.qmail@web25208.mail.ukl.yahoo.com>

Note that the FAQ item assumes that if you define an object as KEEP then it will indeed go into the KEEP pool...Sounds fair enough but it seems to suggest that KEEP (and I think RECYCLE as well) may be broken in v9. Segment header blocks from the candidate table seem to go into the correct pool but that seems to be all.

Here's my demo - I'll be very glad if someone proves me or the queries below wrong here - I'd like to use a KEEP pool on v9 but have so far resisted because of what follows:

I do have a keep pool

NAME                                     VALUE


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

db_16k_cache_size                        0
db_2k_cache_size                         0
db_32k_cache_size                        0
db_4k_cache_size                         0
db_8k_cache_size                         0
db_block_buffers                         0
db_cache_size                            25165824
db_keep_cache_size                       16777216
db_recycle_cache_size                    0

2) I have single object in the KEEP pool and its the only object in a tablespace DEMO, which makes it easy to identify buffers

SQL> select owner, segment_name, buffer_pool   2 from dba_segments
  3 where tablespace_name = 'DEMO'
  4 /

OWNER                          SEGMENT_NAME                             BUFFER_


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

SYS                            IX1                                      KEEP

3) The file for DEMO tablespace

SQL> select file_id from dba_data_files
2 where tablespace_name = 'DEMO'
3 /

FILE_ID

4) The table for index IX1 is:

SQL> select owner, table_name
  2 from dba_indexes
  3 where index_name = 'IX1'
  4 /

OWNER                          TABLE_NAME


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

SYS                            ALLOBJ

5) So far, the only things in the KEEP pool are:

SQL> select bh.TS#

  2  ,bh.FILE#
  3  ,bh.DBARFIL
  4  ,bh.DBABLK
  5  ,bh.state
  6  from x$kcbwds ds,
  7       x$kcbwbpd pd,
  8    (select /*+ use_hash(x) */ x.*
  9     from obj$ o, x$bh x
 10     where o.dataobj# = x.obj
 11     and dbarfil = 6 ) bh        -- file 6 only

12 where ds.set_id >= pd.bp_lo_sid

 13  and ds.set_id <= pd.bp_hi_sid
 14  and pd.bp_size != 0
 15  and ds.addr=bh.set_ds
 16  and pd.bp_id = 1               -- keep pool only

17 /

TS# FILE# DBARFIL DBABLK STATE
---------- ---------- ---------- ---------- ----------

        15          6          6          9          3
        15          6          6          9          3
        15          6          6          9          3

6) and when I dump block 9 from file 6 I get

Start dump data blocks tsn: 15 file#: 6 minblk 9 maxblk 9 buffer tsn: 15 rdba: 0x01800009 (6/9)
scn: 0x0000.5d2b2f39 seq: 0x01 flg: 0x04 tail: 0x2f391001 frmt: 0x02 chkval: 0x6d29 type: 0x10=DATA SEGMENT HEADER - UNLIMITED

so its the segment header for IX1.

7) Now I make sure that a query will use the IX1 index

SQL> variable b1 number
SQL> set autotrace traceonly explain
SQL> select * from allobj where object_id = :b1;

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'ALLOBJ' 2 1 INDEX (RANGE SCAN) OF 'IX1' (NON-UNIQUE) 8) and then hammer away at the index for a while

SQL> set serverout on
SQL> declare
  2 x allobj%rowtype;
  3 c number := 0;
  4 begin
  5 for i in 1 .. 10000 loop
  6 begin

  7      select * into x from allobj where object_id = i;
  8      c := c + 1;
  9    exception
 10      when no_data_found then
 11        null;

12 end;
13 end loop;
14 dbms_output.put_line(c||' reads');
15 end;
16 /
9874 reads

PL/SQL procedure successfully completed.

So by now, I should have something in my KEEP pool...but NO CHANGE OBSERVED

SQL> select bh.TS#

  2  ,bh.FILE#
  3  ,bh.DBARFIL
  4  ,bh.DBABLK
  5  ,bh.state
  6  from x$kcbwds ds,
  7       x$kcbwbpd pd,
  8    (select /*+ use_hash(x) */ x.*
  9     from obj$ o, x$bh x
 10     where o.dataobj# = x.obj
 11     and dbarfil = 6 ) bh        -- file 6 only

12 where ds.set_id >= pd.bp_lo_sid

 13  and ds.set_id <= pd.bp_hi_sid
 14  and pd.bp_size != 0
 15  and ds.addr=bh.set_ds
 16  and pd.bp_id = 1               -- keep pool only

17 /

TS# FILE# DBARFIL DBABLK STATE
---------- ---------- ---------- ---------- ----------

        15          6          6          9          3
        15          6          6          9          3
        15          6          6          9          3

If I relax the predicates, I can see where the buffers for IX1 actually are

SQL> select bh.TS#

  2  ,bh.FILE#
  3  ,bh.DBARFIL
  4  ,bh.DBABLK
  5  ,bh.state
  6  ,pd.bp_id
  7  from x$kcbwds ds,
  8       x$kcbwbpd pd,
  9    (select /*+ use_hash(x) */ x.*
 10     from obj$ o, x$bh x
 11     where o.dataobj# = x.obj
 12     and dbarfil = 6 ) bh        -- file 6 still

13 where ds.set_id >= pd.bp_lo_sid

 14  and ds.set_id <= pd.bp_hi_sid
 15  and pd.bp_size != 0
 16  and ds.addr=bh.set_ds

17 /

TS# FILE# DBARFIL DBABLK STATE BP_ID
---------- ---------- ---------- ---------- ---------- ----------

        15          6          6         31          1          3
        15          6          6         30          1          3
        15          6          6         29          1          3
        15          6          6         28          1          3
        15          6          6         27          1          3
        15          6          6         26          1          3
        15          6          6         25          1          3
        15          6          6         24          1          3
        15          6          6         23          1          3
        15          6          6         22          1          3
        15          6          6         21          1          3
        15          6          6         20          1          3
        15          6          6         19          1          3
        15          6          6         18          1          3
        15          6          6         17          1          3
        15          6          6         16          1          3
        15          6          6         15          1          3
        15          6          6         14          1          3
        15          6          6         13          1          3
        15          6          6         12          1          3
        15          6          6         11          1          3
        15          6          6         10          1          3
        15          6          6          9          3          1
        15          6          6          9          3          1
        15          6          6          9          3          1

so it looks to me like they've all gone back into the DEFAULT pool...

Cheers
Connor

Paul Drake <discgolfdba_at_yahoo.com> wrote: > --- Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> > wrote: > > > > Paul, > > > > I've just read the item, and there is an error > > in the oci_block_header view , > > > > the line: > > where b.obj = o.obj# > > should read > > where b.obj = o.dataobj# > > > > This may fix your problem. > > I'll edit the item later on tonight. > > > > Regards > > > > Jonathan Lewis > > Jonathan, > > thanks for the prompt reply, that worked great. > 49672 blocks in the KEEP buffer_pool for that table. > Sorry about posting to the list, should have emailed > you first, I thought that since v$bh was also empty, > that the symptoms had nothing to do with the views. > > Paul > > > > > > > __________________________________ > Do you Yahoo!? > Friends. Fun. Try the all-new Yahoo! Messenger. > http://messenger.yahoo.com/ > ---------------------------------------------------------------- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > ---------------------------------------------------------------- > To unsubscribe send email to: oracle-l-request_at_freelists.org > put 'unsubscribe' in the subject line. > -- > Archives are at http://www.freelists.org/archives/oracle-l/ > FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html > -----------------------------------------------------------------

Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions" ISBN: 1590592174 web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com

Coming Soon! "Oracle Insight - Tales of the OakTable"

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day"

Yahoo! Messenger - Communicate instantly..."Ping" your friends today! Download Messenger Now http://uk.messenger.yahoo.com/download/index.html

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


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

Received on Wed Jun 09 2004 - 23:05:54 CDT