RE: Reading a file# that doesn't exist
- From: Tanel Poder <tanel.poder.003@xxxxxxx>
- To: Andrey.Kriushin@xxxxxxxx, tim@xxxxxxxxx
- Date: Sun, 18 May 2008 19:22:34 +0800
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> http://blog.tanelpoder.com
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto: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
>
> 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...
>
> --
> //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
>
Other related posts:
