Database Reference
In-Depth Information
SEGMENT_NAME EXTENT# BLOCK# TCH CHILD#
------------------------------ -------- -------- -------- --------
DWH.W_ORG_D 70 44 260 251077
DWH.W_GL_BALANCE_F 48 7 6 251077
DWH.X_SRV_DEMAND_F_M9 18 24 5 251077
DWH.W_GL_BALANCE_F 125 853 40 251077
DWH.X_SRV_DEMAND_F 177 326 4 251077
DWH.X_SRV_INSTALL_BASE_SUM 71 17 2 251077
DWH.X_SRV_NOTES_F 233 83 1 251077
DWH.X_SRV_INSTALL_BASE_SUM 146 88 1 251077
DWH.X_SRV_REQ_F_M12 0 4 10 251077
Method 2
This method is not quite as straightforward as Method 1; however, it gives more precise details regarding the hot
blocks and the underlying segments.
Step 1
Oracle introduced a new
X$
table to collect the hot blocks in Oracle Database 10g Release 2 called
X$KSLHOT
. However,
this table is not normally populated. To get data into this view, the
_DB_HOT_BLOCK_TRACKING
parameter has to be
enabled.
10
ALTER SYSTEM SET "_db_hot_block_tracking"=TRUE SCOPE=BOTH;
In a RAC environment, depending on what service is used to access the various instances and what tables or
schema the specific instance is mapped to access, blocks can be accessed by any instance in the cluster. It is advisable
to enable this parameter across all instances in the cluster.
INT NAME VALUE
----- ---------------------------------------- ----------
1 _db_hot_block_tracking TRUE
2 _db_hot_block_tracking TRUE
3 _db_hot_block_tracking TRUE
4 _db_hot_block_tracking TRUE
Step 2
Once the parameter is enabled, Oracle will start populating this view with the current access statistics. Here is the
output for the view:
SQL> SELECT * FROM X$KSLHOT;
ADDR INDX INST_ID KSLHOT_ID KSLHOT_REF
---------------- -------- -------- --------- ----------
0000001101C02200 0 1 8896745 680
0000001101C02210 1 1 3.69E+08 1
0000001101C02220 2 1 8769114 2
10
Underscore parameters should be enabled only with prior consultation with Oracle Support.
Search WWH ::
Custom Search