Database Reference
In-Depth Information
0000001101C02230 3 1 8764874 200
0000001101C02240 4 1 8769044 1
0000001101C02250 5 1 3.69E+08 1
0000001101C02260 6 1 3.69E+08 1
0000001101C02270 7 1 3.73E+08 0
0000001101C02280 8 1 8764893 0
0000001101C02290 9 1 8468694 0
Step 3
The previous output includes the following information.
ADDR
(address) is a buffer used by Oracle to track the hot
blocks.
INDX
(index) is used internally to search the buffer inside the array.
INST_ID
is the instance where the data
is being collected,
KSLHOT_ID
is the RDBA (relative data block address), and
KSLHOT_REF
is the count of the relative
number of times the block was accessed and contention was encountered.
Once we have the RDBA number, the next step in the process is to get the segment details using the information
we have.
The
DBMS_UTILITY
package has two procedures,
DATA_BLOCK_ADDRESS_FILE
and
DATA_BLOCK_ADDRESS_BLOCK
,
that can help convert the RDBA into a file number and block number, respectively.
SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE( TO_NUMBER('8896745', 'XXXXXXXX') ) FILE#,
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK( TO_NUMBER('8896749', 'XXXXXXXX') ) BLOCK#
FROM DUAL;
FILE# BLOCK#
---------- ----------
34 616261
Step 4
Using the
FILE#
and
BLOCK#
from the preceding output, the segment details for the hot block can be determined from
DBA_EXTENTS
.
SELECT OWNER ,
SEGMENT_NAME,
SEGMENT_TYPE,
TABLESPACE_NAME
FROM DBA_EXTENTS
WHERE FILE_ID = 34
AND 616261 BETWEEN BLOCK_ID AND BLOCK_ID+BLOCKS-1;
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
------ ----------------------------- ------------------ --------------
DWH X_SRV_REQ_DEBRIEF_LINE_F_M6 INDEX PARTITION DWH_DATA
The datafile associated with the
FILE#
can be obtained from
V$DATAFILE
using the following query:
SQL> SELECT NAME, TS# FROM V$DATAFILE WHERE FILE#=34;
NAME TS#
------------------------------------------------------ ----------
+PRD_DATA/PRD/datafile/dwh_data3.464.718185229 32
Search WWH ::
Custom Search