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 /
FILE_ID FILE_NAME
---------- ----------------------------------------
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
Hi,
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
3 from dba_temp_files
4 /
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@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
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
