关于data block address的转换

gdpr-dba

.....
WAIT #0: nam='direct path read' ela= 199 file number=16 first dba=3929673 block cnt=1 obj#=71755 tim=2284409989
.....

这里的first dba怎么转换为object_name啊？

我尝试用DBMS_UTILITY来转换，不过貌似不好使，数据库版本是10.2.0.4：

SQL> SELECT DBMS_UTILITY.data_block_address_block (3929673) "BLOCK",
  2         DBMS_UTILITY.data_block_address_file (3929673) "FILE"
  3    FROM DUAL;
     BLOCK       FILE
---------- ----------
   3929673          0

Maclean Liu(刘相兵

WAIT #2: nam='direct path write temp' ela= 4 file number=201 first dba=25942 block cnt=1 obj#=20314 tim=1303724256118127
WAIT #2: nam='direct path write temp' ela= 4 file number=201 first dba=25943 block cnt=1 obj#=20314 tim=1303724256118685
WAIT #2: nam='direct path write temp' ela= 3 file number=201 first dba=25944 block cnt=1 obj#=20314 tim=1303724256119254
WAIT #2: nam='direct path write temp' ela= 4 file number=201 first dba=25945 block cnt=1 obj#=20314 tim=1303724256120252
WAIT #2: nam='direct path write temp' ela= 56 file number=201 first dba=25946 block cnt=1 obj#=20314 tim=1303724256120908
WAIT #2: nam='direct path write temp' ela= 5 file number=201 first dba=25947 block cnt=1 obj#=20314 tim=1303724256121500



direct path read
The direct path read event occurs when Oracle is reading data blocks directly into the session’s PGA instead of the buffer cache in the SGA. Direct reads may be performed in synchronous I/O or asynchronous I/O mode, depending on the hardware platform and the value of the initialization parameter, DISK_ASYNCH_IO. Direct read I/O is normally used while accessing the temporary segments that reside on the disks. These operations include sorts, parallel queries, and hash joins.

The number of waits and time waited for this event are somewhat misleading. If the asynchronous I/O is not available, the session waits till the I/O completes. But these are not counted as waits at the time the I/O request is issued. The session posts a direct path read wait event when accessing the data after the completion of the I/O request. In this case, the wait time will be negligibly small.

If the asynchronous I/O is available and in use, then the session may issue multiple direct path read requests and continue to process the blocks that are already cached in the PGA. The session will register direct path read wait event only when it cannot continue processing because the required block has not been read into the buffer. Therefore, the number of read requests may not be the same as the number of waits. Due to these anomalies, it is unlikely that you will see this wait event reported in V$SYSTEM_EVENT and V$SESSION_EVENT views.

Starting from Oracle Release 8.1.7 there is a separate direct path read (lob) event for reading LOB segments.

Wait Parameters
Wait parameters for direct path read are described here:

P1 Absolute file number to read from

P2 Starting block number to read from

P3 Number of blocks to read

Wait Time
No timeouts. Actual time until the outstanding I/O request completes.




P1  Absolute file number  绝对文件号
P2  起始块号 而非 data block address
P3    读取的块数


select segment_name, tablespace_name, segment_type
  from dba_segments
where HEADER_FILE = p1
   and p2 between HEADER_BLOCK and HEADER_BLOCK + blocks - 1;



select a.event,
       a.sid,
       c.sql_hash_value hash_value,
       decode(d.ktssosegt,
              1,
              'SORT',
              2,
              'HASH',
              3,
              'DATA',
              4,
              'INDEX',
              5,
              'LOB_DATA',
              6,
              'LOB_INDEX',
              null) as segment_type,
       b.tablespace_name,
       b.file_namefrom v$session_wait a,
       dba_data_files b,
       v$session c,
       x$ktsso dwhere c.saddr = d.ktssoses(+) and c.serial# = d.ktssosno(+) and d.inst_id(+) = userenv('instance') and a.sid = c.sidand a.p1 = b.file_idand a.event = 'direct path read' union allselect a.event,
       a.sid,
       d.sql_hash_value hash_value,
       decode(e.ktssosegt,
              1,
              'SORT',
              2,
              'HASH',
              3,
              'DATA',
              4,
              'INDEX',
              5,
              'LOB_DATA',
              6,
              'LOB_INDEX',
              null) as segment_type,
       b.tablespace_name,
       b.file_namefrom v$session_wait a,
       dba_temp_files b,
       v$parameter c,
       v$session d,
       x$ktsso ewhere d.saddr = e.ktssoses(+) and d.serial# = e.ktssosno(+) and e.inst_id(+) = userenv('instance') and a.sid = d.sidand b.file_id = a.p1 - c.valueand c.name = 'db_files' and a.event = 'direct path read' order by 1,
       2;