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 it's 300 in your instance) from the file# in wait interface to get the temp file id.

Or run such query:

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

   FILE_ID FILE_NAME

---------- ------------------------------------------------------------
       201 C:\ORACLE\PRODUCT\10.2.0\ORADATA\WIN10G\TEMP01.DBF

I see it's the block 2 in the file, this is where the LMT extent management 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 temporary 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 is created using "create TEMPORARY tablespace" option, not "create tablespace .... temporary" option?

--
Regards,
Tanel Poder
http://blog.tanelpoder.com






> -----Original Message-----

> From: oracle-l-bounce_at_freelists.org 

> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Newman, 

> Christopher

> Sent: 03 March 2009 20:40

> To: oracle-l_at_freelists.org

> 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

> 

> --

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

> 

> 


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