Re: strange behavior in 9.2.0.4 - no blocks in keep buffer (KEEP broken?)
- From: Connor McDonald <hamcdc@xxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 10 Jun 2004 05:08:17 +0100 (BST)
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:
1) 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
----------
6
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.
10) 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
11) 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@xxxxxxxxx> wrote: > --- Jonathan Lewis
<jonathan@xxxxxxxxxxxxxxxxxx>
> 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@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //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@xxxxxxxxx
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
