Identifying buffer busy wait

How to Identify the Segment Associated with Buffer Busy Waits

It is important to know the segments which are affected by buffer busy waits in order to address the cause for the waits.

This identification can be done by using 10046 traces (with TKProfs) or AWR / statspack output.

It is considered that 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.

How to analyzing both 10046 / TKprof

The technique of analyzing both 10046 / TKprof

TKProf

• In the "Overall Totals" section, its 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:

SQL>SELECT owner, object_name, object_type FROM dba_objects WHERE object_id = 57303;

• If you need to find the segment using file# and block#, you can use this query:

SQL>SELECT owner, segment_name, file_id, block_id starting_block_id, block_id + blocks ending_block_id, blocks
FROM
dba_extents
WHERE file_id = &file_num AND ( block_id <= &block_id AND (&block_id < (block_id + blocks)) );

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


TKPROF

If you are working with Tkprof connect to a new session, oracle create trace for a session

SQL> Alter session set sql_trace = true;

SQL> Alter session set time_statistics = true;

After this write a query for which you have to see performance

suppose below is the query for which you need to see performance.
SQL> select * from emp where deptno = 10;

SQL> Alter session set sql_trace = false;

Go to commond prompt
D:\>tkprof c:\ora01780.trc c:\report.txt explain = username/password sys = yes

Then see report.txt

Solution: - Find correct solution if there is problem.