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
N 0 9514
N 2 152
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
On Behalf Of Andrey Kriushin
> Sent: Sunday, May 18, 2008 16:04
> To: tim@xxxxxxxxx
> Cc: Bradd Piontek; oracle-l@xxxxxxxxxxxxx
> Subject: Re: Reading a file# that doesn't exist
> 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
> 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
> > involve SGA resources at all? Tempfiles are just an
> extension to the
> > PGA, in essence...
> 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