How to Identify The Segment Associated with Buffer Busy Waits

This note shows how to determine which segment is associated with buffer busy waits found in an AWR / statspack report, or 10046 trace.

It is very important to know which segments are affected by buffer busy waits in order to address the cause for the waits. This can be accomplished using 10046 traces (with TKProfs) or AWR / statspack output. The 10046 trace is better than the AWR / Statspack data because it allow you to directly associate the waits with SQL statements causing them and allows you to easily see how the waits affect the execution of that statement. AWR / statspack reports are still useful in a general way, but you will still not really know precisely which statements caused the waits.

Solution
The technique of analyzing both 10046 / TKprof and AWR / statspack reports data are discussed below.

TKProf
In the “Overall Totals” section, confirm that “buffer busy wait” events (Overall Totals, recursive and non-recursive) have the highest wait times.
Determine which call type is associated with the highest elapsed time: execute or fetch
Generate a new TKProf report sorted by the call type found for the highest elapsed times. For example:

Execute calls:
tkpof trace_file_name output_file sort=exeela
Fetch calls:

tkpof trace_file_name output_file sort=fchela
Choose a few of the top statements in this new TKProf report and find them in the original trace file.
Examine parts of the raw trace file where the top statements are running and look at the lines with “WAIT #” for the buffer busy wait event corresponding to the cursors. For example:
WAIT #2: nam=’buffer busy waits’ ela= 222 file#=4 block#=78391 class#=1 obj#=57303 tim=1151844401945055
Find the value of the P1, P2, and P3 fields. These correspond to the file number, block number, and reason code (< 9.2) or block class (10g). You will likely find many "WAIT#" lines for different combinations of P1, P2, and sometimes P3 values. The goal is to determine which segments are related to these waits.
In 10g, this is very easy because theWAIT line will include the object ID for the segment (in the example above, it's: obj#=57303). You can find the information about the object using this query:
SELECT owner, object_name, object_type FROM dba_objects WHERE object_id = 57303;

OWNER OBJECT_NAME OBJECT_TYPE
———- ——————– ——————-
SCOTT STOCK_PRICES TABLE
If you need to find the segment using file# and block#, you can use this query:

SELECT owner, segment_name, file_id, block_id starting_block_id, block_id + blocks ending_block_id, blocksFROM
dba_extentsWHERE file_id = &file_num AND ( block_id <= &block_id AND (&block_id < (block_id + blocks)) ) OWNER
SEGMENT_NAME FILE_ID STARTING_BLOCK_ID ENDING_BLOCK_ID
BLOCKS———- ——————– ———- —————– ————— ———-SCOTT
STOCK_PRICES 4
78385 78393 8

Now you know the segment names and SQL statements with highest buffer busy waits.

AWR or statspack report

9.2 or higher:
Review the section Segments by Buffer Busy Waits, and note the segments with the highest waits (collected by statspack at level 7 or higher)
It is difficult to point to a specific query, but sometimes the statements with the highest wait time (elapsed – cpu) are related to these waits. Review the statements in the Top SQL sections and find the ones with the highest wait times that use the segments with high buffer busy waits. Ideally, you will obtain an extended SQL trace and TKProf to accurately identify the statements with the highest waits (see above).
9.0.x and prior,
Review the section Buffer wait Statistics and note the buffer class (data, undo, etc) that has the highest waits.
It is difficult to point to a specific query, but sometimes the statements with the highest wait time (elapsed – cpu) are related to these waits. Review the statements in the Top SQL sections and find the ones with the highest wait times that use the segments with high buffer busy waits. Ideally, you will obtain an extended SQL trace and TKProf to accurately identify the statements with the highest waits (see above).

——————————————————————————–

Help us improve our service. Please email us your comments for this document. .

Bookmarks Admin Profile Feedback Sign Out Help

Copyright © 2006, Oracle. All rights reserved. Legal Notices and Terms of Use | Privacy Statement

Sobre Alexandre Pires

ORACLE OCS Goldengate Specialist, OCE RAC 10g R2, OCP 12C, 11g, 10g , 9i e 8i - Mais de 25 anos de experiência na área de TI. Participei de projetos na G&P alocado na TOK STOK, EDINFOR alocado na TV CIDADE "NET", 3CON Alocado no PÃO DE AÇUCAR, DISCOVER alocado na VIVO, BANCO IBI e TIVIT, SPC BRASIL, UOLDIVEO alocado no CARREFOUR e atualmente na ORACLE ACS atendendo os seguintes projetos: VIVO, CLARO, TIM, CIELO, CAIXA SEGUROS, MAPFRE, PORTO SEGURO, SULAMERICA, BRADESCO SEGUROS, BANCO BRADESCO, BASA, SANTANDER, CNJ, TSE, ELETROPAULO, EDP, SKY, NATURA, ODEBRESHT, NISSEI, SICREDI, CELEPAR, TAM, TIVIT, IBM, SMILES, CELEPAR, SERPRO,OKI,BANCO PAN, etc
Esse post foi publicado em BUFFERS, WAIT EVENTS e marcado . Guardar link permanente.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s