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