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

On Sun, May 18, 2008 at 1:22 PM, Tanel Poder <tanel.poder.003_at_mail.ee> wrote:

> GTT reads are still done into buffer cache as you might want to reuse the
> temp table data multiple times. Thus GTT reads from temp tablespace will
> still show "db file scattered/sequential read" as their waits.
> There's a TEMP flag in v$bh for identifying which buffers are temporary
> thus need no redo for their changes.
> In example below the temp tablespace is TS# 3:
>
> SQL> select temp,ts#, count(*) from v$bh group by temp,ts#;
>
> T TS# COUNT(*)
> - ---------- ----------
> N 1 4905
> *Y 3 1014
> *N 0 9514
> N 2 152
>
> --
> Regards,
> Tanel Poder
> http://blog.tanelpoder.com
>
>
> > -----Original Message-----
> > From: oracle-l-bounce_at_freelists.org
> > [mailto:oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org>]
> On Behalf Of Andrey Kriushin
> > Sent: Sunday, May 18, 2008 16:04
> > To: tim_at_evdbt.com
> > Cc: Bradd Piontek; oracle-l_at_freelists.org
> > Subject: Re: Reading a file# that doesn't exist
> >
> > Hi,
> >
> > an excellent point, Tim!
> >
> > Even when using global temporary tables, there is actually no
> > need to share the block's data and no reason to place those
> > blocks into the buffer cache.
> >
> > However (tested on 9.2, sorry for not having 10g/11g handy):
> >
> > CREATE GLOBAL TEMPORARY TABLE xGTT
> > ON COMMIT PRESERVE ROWS
> > AS
> > SELECT * FROM DBA_OBJECTS;
> >
> > SELECT COUNT(*),TS#,CLASS#
> > FROM V$BH
> > GROUP BY TS#,CLASS#
> > ORDER BY TS#; -- in my case ts#=2 is temporary tablespace
> > with the tempfiles ...
> > 81 2 1
> > 1 2 2
> > 1 2 4
> > ...
> >
> > IMHO, this is an illustration of (rational) laziness of
> > server development. Otherwise they would need to create yet
> > another branch of kernel code to cope with blocks in the PGA
> > and to distinguish between the usual and temp blocks... New
> > bugs, maintenance, etc
> >
> >
> > -- Andrey
> >
> > Tim Gorman wrote:
> > > Think about it -- no need to share with other sessions, so why
> > > involve SGA resources at all? Tempfiles are just an
> > extension to the
> > > PGA, in essence...
> >
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> > Internal Virus Database is out of date.
> > Checked by AVG.
> > Version: 8.0.100 / Virus Database: 269.23.2/1392 - Release
> > Date: 22/04/2008 15:51
> >
>

--
http://www.freelists.org/webpage/oracle-l