RE: problem interpreting wait events

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Mon, 23 Aug 2004 12:59:08 -0700
Message-ID: <B5C5F99D765BB744B54FFDF35F60262109F879EB@irvmbxw02>

You can speed up the process a little bit by knowing the tablespace extent management type (locally managed vs. dictionary managed.) E.g. I sometimes use this script for a quick find of the object to which an extent belongs (script written for Oracle 9.2) The PL/SQL block assumes that you are most likely going to find a non-partitioned table or index.
set verify off
set echo on
accept extent_management char prompt "tablespace type (local/dict) > " undefine ts_number
undefine file_number
undefine block_number
variable ts_number number

variable header_file_id number
variable header_block_id number
variable object_id number

-- for locally managed
begin

   :object_id := null ;
   if upper ('&&extent_management') = 'LOCAL'    then

      select
         a.segment_fileid,
         a.segment_block
       into
          :header_file_id,
          :header_block_id
       from
         dba_lmt_used_extents a
       where
         a.tablespace_id = &&ts_number
         and a.fileid = &&file_number
         and a.block = &&block_number ;
   else
      select
         a.segment_fileid,
         a.segment_block
       into
         :header_file_id,
         :header_block_id
       from
         dba_dmt_used_extents a
       where
         a.tablespace_id = &&ts_number
         and a.fileid = &&file_number
         and a.block = &&block_number ;

   end if ;
   if sql%rowcount > 0
   then

      select a.obj# into :object_id
       from sys.tab$ a
       where
         a.ts# = &&ts_number
         and a.file# = :header_file_id
         and a.block# = :header_block_id ;
      if sql%rowcount = 0
      then
         select a.obj# into :object_id
          from sys.ind$ a
          where
            a.ts# = &&ts_number
            and a.file# = :header_file_id
            and a.block# = :header_block_id ;
      end if ;
      if sql%rowcount = 0
      then
         select a.object_id into :object_id
          from sys.sys_objects a
          where
            a.ts_number = &&ts_number
            and a.header_file = :header_file_id
            and a.header_block = :header_block_id ;
      end if ;

   end if ;
end ;
/
column tablespace_id format 999 heading "ts#" column file_id format 99999 heading "file#" column block_id format 999999 heading "block#"

column header_file format 99999 heading "hfile#"
column header_block format 999999 heading "hblock#"
column object_id format 99999999 heading "obj#"
column object_name format a50 heading "object name"

select

   &&ts_number as tablespace_id,
   &&file_number as file_id,
   &&block_number as block_id,
   :header_file_id as header_file,

   :header_block_id as header_block,
   :object_id as object_id,
   b.owner || '.' || b.object_name || ' (' || b.object_type || ')' as object_name
 from
   dual a, dba_objects b
 where
   decode (b.object_name (+), null, 'X', 'X') = a.dummy    and b.object_id (+) = :object_id ;

-----Original Message-----
Daniel Fink

I have found that this type of query against dba_extents performs horribly. My solution has been to create a GTT, load relevant data from dba_extents, slap an index on file_id and block_id and query the GTT. Even in some of the larger dbs I've done this on, the overall time was much less than the original query.

You are on the right track, you just need to get off the congested freeway and take a back road.

Daniel Fink

ryan.gaffuri_at_comcast.net wrote:
> I am getting excessive 'db file sequential reads' in an array insert.
It is not from the select part, since those are all table scans with 'db file scattered reads'. This part runs pretty fast(I ran a select count(*) on it).
> I am reading my 10046 trace and from v$event_name I know that p2 for
db file sequential read is the 'block#'.
> Where do I find the segment that this is a part of? I look for the
block in dba_extents and it never returns? I try a variety of different block#s that I get back and it never comes back. I think I may not get this if its part of the 'undo' tablespace since its not static. However, I know that it is part of a data tablespace since I checked the 'p1' value in dba_data_files?
> So which view should I use to nail down where the wait is coming from?
I turned off my foreign key constraint before I started, so at this point, I want to nail down exactly what segment is being hit with a db file sequential read, but I cannot find it in dba_extents?

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon Aug 23 2004 - 14:54:58 CDT