Re: Reading a file# that doesn't exist

From: Tim Gorman <tim_at_evdbt.com>
Date: Mon, 19 May 2008 22:17:51 -0600
Message-ID: <483250EF.3070703@evdbt.com>

That's what happens when I get lazy and utter an unsubstantiated comment.  Thanks Andrey and Tanel!

FWIW, I do agree with Andrey that Oracle ST Development appears to have cut corners in not developing a separate code branch to cache blocks in the PGA instead of the SGA.  Even if no redo is generated, there are still latches to be obtained/released for these blocks and instance-wide resources consumed when it is not really necessary....



Tanel Poder 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@freelists.org
> [mailto:oracle-l-bounce@freelists.org] On Behalf Of Andrey Kriushin
> Sent: Sunday, May 18, 2008 16:04
> To: tim@evdbt.com
> Cc: Bradd Piontek; oracle-l@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 Received on Mon May 19 2008 - 23:17:51 CDT