RE: Hot block question -10gr2 solaris 10

  • From: "Newman, Christopher" <cjnewman@xxxxxxxxxxxxx>
  • To: "Tanel Poder" <tanel@xxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 3 Mar 2009 12:56:56 -0600

Thanks Tanel,  that was it.  I had thought that the temp files would
have begun sequentially at or close to the last datafile #, based on the
your query however, that isn't the case:

DBAPUMP_SQL > select file_id + (select value from v$parameter where name
='db_files') file_id,
  2  file_name from dba_temp_files
  3  /

---------- ----------------------------------------
       301 /u07/oradata/DBAPUMP/tmp01DBAPUMP.dbf
       302 /u07/oradata/DBAPUMP/tmp02DBAPUMP.dbf
       303 /u07/oradata/DBAPUMP/tmp03DBAPUMP.dbf
       304 /u07/oradata/DBAPUMP/tmp04DBAPUMP.dbf

Thanks again for your help, very much appreciated  - Chris

-----Original Message-----
From: Tanel Poder [mailto:tanel@xxxxxxxxxx] 
Sent: Tuesday, March 03, 2009 12:53 PM
To: Newman, Christopher; oracle-l@xxxxxxxxxxxxx
Subject: RE: Hot block question -10gr2 solaris 10


If a file# in wait interface is larger than db_files parameter, then
it's a
temp tablespace file. You need to substract the db_files value (I guess
300 in your instance) from the file# in wait interface to get the temp

Or run such query:

SQL> select file_id + (select value from v$parameter where name =
'db_files') file_id,
  2         file_name
  3  from dba_temp_files
  4  /

---------- ------------------------------------------------------------

I see it's the block 2 in the file, this is where the LMT extent
bitmap header block lives. It's not part of any segment.

It can become hot (especially in RAC) when there are a lot of space
allocations/deallocations done at the file level (segments extending or
being truncated, thus releasing space back to file). However, for
tablespaces, once the temp segment has extended, all subsequent
allocations/deallocations should be done via in-memory bitmaps (as temp
segments don't shrink during instance lifetime)

So, if that block is constantly hot, are you sure your TEMP tablespace
created using "create TEMPORARY tablespace" option, not "create
.... temporary" option?

Tanel Poder

> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx 
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Newman, 
> Christopher
> Sent: 03 March 2009 20:40
> To: oracle-l@xxxxxxxxxxxxx
> Subject: Hot block question -10gr2 solaris 10
> We're trying to find a hot block:
> select p1 "File #", p2 "Block #", p3 "Reason Code" from 
> v$session_wait where event = 'buffer busy waits';
>     File #    Block # Reason Code
> ---------- ---------- -----------
>        304          2          13 (File block header)
>        304          2          13
> .....
> Here's the issue, there is *no* file# 304.  There are only 
> 240 datafiles in the database and only a handful of temp 
> files.  304 matches neither the file_id nor relative_fno in 
> dba_data_files.  Nothing maps to that in dba_segments either. 
>  Is this a bug, or are we doing something wrong here?  
> Chris Newman
> Database Specialist
> AITS, University of Illinois
> --


Other related posts: