How To Identify a Hot Block Within The Database Buffer Cache. [ID 163424.1]
Possible hot blocks in the buffer cache normally can be identified by a high or
rapid increasing wait count on the CACHE BUFFERS CHAINS latch.
This latch is acquired when searching for data blocks cached in the buffer cache.
Since the Buffer cache is implemented as a sum of chains of blocks, each of those
chains is protected by a child of this latch when needs to be scanned. Contention
in this latch can be caused by very heavy access to a single block. This can
require the application to be reviewed.
To solve a hot block, the application maybe need to be reviewed.
By examining the waits on this latch, information about the segment and the
specific block can be obtained using the following queries.
First determine which latch id(ADDR) are interesting by examining the number of
sleeps for this latch. The higher the sleep count, the more interesting the
latch id(ADDR) is:
Run the above query a few times to to establish the id(ADDR) that has the most
consistent amount of sleeps. Once the id(ADDR) with the highest sleep count is found
then this latch address can be used to get more details about the blocks
currently in the buffer cache protected by this latch.
The query below should be run just after determining the ADDR with
the highest sleep count.
Depending on the TCH column (The number of times the block is hit by a SQL
statement), you can identify a hotblock. The higher the value of the TCH column,
the more frequent the block is accessed by SQL statements.
In order to reduce contention for this object the following mechanisms can be put in place:
1) Examine the application to see if the execution of certain DML and SELECT statements
can be reorganized to eliminate contention on the object.
2) Decrease the buffer cache -although this may only help in a small amount of cases.
3) DBWR throughput may have a factor in this as well.
If using multiple DBWR's then increase the number of DBWR's.
4) Increase the PCTFREE for the table storage parameters via ALTER TABLE
or rebuild. This will result in less rows per block.
5) Consider implementing reverse key indexes
(if range scans aren't commonly used against the segment)[@more@]
rapid increasing wait count on the CACHE BUFFERS CHAINS latch.
This latch is acquired when searching for data blocks cached in the buffer cache.
Since the Buffer cache is implemented as a sum of chains of blocks, each of those
chains is protected by a child of this latch when needs to be scanned. Contention
in this latch can be caused by very heavy access to a single block. This can
require the application to be reviewed.
To solve a hot block, the application maybe need to be reviewed.
By examining the waits on this latch, information about the segment and the
specific block can be obtained using the following queries.
First determine which latch id(ADDR) are interesting by examining the number of
sleeps for this latch. The higher the sleep count, the more interesting the
latch id(ADDR) is:
SQL> select CHILD# "cCHILD"
, ADDR "sADDR"
, GETS "sGETS"
, MISSES "sMISSES"
, SLEEPS "sSLEEPS"
from v$latch_children
where name = 'cache buffers chains'
order by 5, 1, 2, 3;
Run the above query a few times to to establish the id(ADDR) that has the most
consistent amount of sleeps. Once the id(ADDR) with the highest sleep count is found
then this latch address can be used to get more details about the blocks
currently in the buffer cache protected by this latch.
The query below should be run just after determining the ADDR with
the highest sleep count.
SQL> column segment_name format a35
select /*+ RULE */
e.owner ||'.'|| e.segment_name segment_name,
e.extent_id extent#,
x.dbablk - e.block_id + 1 block#,
x.tch,
l.child#
from
sys.v$latch_children l,
sys.x$bh x,
sys.dba_extents e
where
x.hladdr = '&ADDR' and
e.file_id = x.file# and
x.hladdr = l.addr and
x.dbablk between e.block_id and e.block_id + e.blocks -1
order by x.tch desc ;
Example of the output :
SEGMENT_NAME EXTENT# BLOCK# TCH CHILD#
-------------------------------- ------------ ------------ ------ ----------
SCOTT.EMP_PK 5 474 17 7,668
SCOTT.EMP 1 449 2 7,668
SEGMENT_NAME EXTENT# BLOCK# TCH CHILD#
-------------------------------- ------------ ------------ ------ ----------
SCOTT.EMP_PK 5 474 17 7,668
SCOTT.EMP 1 449 2 7,668
Depending on the TCH column (The number of times the block is hit by a SQL
statement), you can identify a hotblock. The higher the value of the TCH column,
the more frequent the block is accessed by SQL statements.
In order to reduce contention for this object the following mechanisms can be put in place:
1) Examine the application to see if the execution of certain DML and SELECT statements
can be reorganized to eliminate contention on the object.
2) Decrease the buffer cache -although this may only help in a small amount of cases.
3) DBWR throughput may have a factor in this as well.
If using multiple DBWR's then increase the number of DBWR's.
4) Increase the PCTFREE for the table storage parameters via ALTER TABLE
or rebuild. This will result in less rows per block.
5) Consider implementing reverse key indexes
(if range scans aren't commonly used against the segment)[@more@]
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/9907339/viewspace-1043538/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- How to check Database corrupt BlockDatabaseBloC
- oracle hot blockOracleBloC
- How to Identify Hard Parse Failures (文件 ID 1353015.1)IDEAI
- How to Identify Resource Intensive SQL for Tuning [ID 232443.1](metalink artic)IDESQL
- How to dynamically evaluate a tag within a java fileJava
- how webpack Hot Module Replacement worksWeb
- 行資料 引起的hot blockBloC
- How to Quiesce a DatabaseUIDatabase
- How to Duplicate a Database in NOARCHIVELOG mode (Doc ID 275480.1)DatabaseHive
- How to Resolve Invalid Objects in a Database [ID 158185.1]ObjectDatabase
- Oracle database buffer cacheOracleDatabase
- Database Buffer Cache (79)Database
- How to Perform a Healthcheck on the DatabaseORMDatabase
- How to enable the flashback database:Database
- How a Database Is Mounted (293)Database
- GC Buffer Busy Waits in RAC: Finding Hot BlocksGCAIBloC
- How To Size the Database Smart Flash Cache (Doc ID 1317950.1)Database
- How To List All The Named Events Set For A Database [ID 436036.1]Database
- Organization of the Database Buffer Cache (80)Database
- Size of the Database Buffer Cache (82)Database
- Database Testing: How to Regression Test a Relational DatabaseDatabase
- How to get and set the system socket buffer in AIXAI
- How To List All The Named Events Set For A Database (Doc ID 436036.1)Database
- How To Recreate A Database Using TTS (Transportable TableSpace) [ID 733824.1]DatabaseTTS
- How to Perform a Health Check on the DatabaseORMDatabase
- [原創] How to Quiesce a DatabaseUIDatabase
- How a Standby Database Is Mounted (295)Database
- How a Clone Database Is Mounted (296)Database
- How does one rename a database?Database
- Specifying Database Block Sizes (91)DatabaseBloC
- database buffer cache之我見Database
- hot backup 熱備份中很重要的概念-- split blockBloC
- How to Collect Diagnostics for Database Hanging Issues (Doc ID 452358.1)Database
- How to Collect Diagnostics for Database Hanging Issues (文件 ID 452358.1)Database
- How to Create or Remove Restore Point on Standby database (文件 ID 1672977.1)REMRESTDatabase
- How to Relink Oracle Database SoftwareOracleDatabase
- How to Shrink Undo Segment In Oracle DatabaseOracleDatabase
- [轉]How to release space from databaseDatabase