Re: Reading a file# that doesn't exist

From: Yong Huang <yong321_at_yahoo.com>
Date: Mon, 19 May 2008 11:32:41 -0700 (PDT)
Message-ID: <400580.97256.qm@web80606.mail.mud.yahoo.com>


Frits,

I tried to reproduce and can't. On a 9.2.0.8 database,

--Make sure the temp buffers are almost none select count(*) from v$bh where temp = 'Y'; --Create GTT
create global temporary table yhuang.gtt on commit preserve rows as select * from dba_tables;
--Check again
select count(*) from v$bh where temp = 'Y'; --Get them into buffer cache
select count(*) from yhuang.gtt;
--Check again
select count(*) from v$bh where temp = 'Y'; --I see 124 buffers. Are the child latches covering them unique? SQL> select HLADDR, count(*) from x$bh where ts# = 2 group by HLADDR;

HLADDR COUNT(*)
-------- ----------

68D596C0          1
68E50420          1

...

Indeed, all of them are, for this small table. How about the (file#,block#) combinations, which are used to create dba's which ultimately lead to latch assignment (through bucket#'s)?

SQL> select file#, block#, count(*) from v$bh where temp = 'Y' group by file#, block#;

     FILE# BLOCK# COUNT(*)
---------- ---------- ----------

         1          2          1
         1     172900          1

...

They're unique too. So unless there's a bug in 9.2.0.6, there may be something else that caused the problem that one latch protects all GTT blocks.

Yong Huang

> Date: Sun, 18 May 2008 14:44:42 +0200
> From: "Frits Hoogland" <frits.hoogland_at_gmail.com>
> Subject: Re: Reading a file# that doesn't exist
>
> Slightly related note:
> I'v experienced (severe) CBC latchwaits on GTT's in version 9.2.0.6, RAC,
> on Solaris 8. This was a webapplication with all connections (+30) using
> thin JDBC and using 1 username. All these sessions used a GTT with the same
> name. As far as I could investigate it back then (approx. 2 years ago) it
> appeared all blocks of all GTT's were protected by 1 latch, thus resulting
> in the CBC latch issues.
>
> (Obviously the GTT was elimenated as soon as possible.)
>
> frits
      

--
http://www.freelists.org/webpage/oracle-l
Received on Mon May 19 2008 - 13:32:41 CDT

Original text of this message