Database Reference
In-Depth Information
Table Blocks
If the object_type suffering from gc buffer busy wait event is a table block, then it is imperative that you identify the
type of block inducing gc buffer busy wait event. Listing 10-6 provides a SQL statement querying gv$active_session_
history data and identifies the blocks inducing gc buffer busy acquire waits.
After identifying the specific block, you must identify the type of the block suffering from contention. The output
in Listing 10-12 shows that block with file_id=49 and block_id= 635413 belongs to the fnd_concurrent_processes table
(a familiar E-Business suite table). You may have to dump the data block to identify the block type.
Listing 10-12.
Ash_gcwait_to_block.sql
col inst format 9999
col current_file# format 99999 head file
col current_block# format 9999999 head blk
WITH ash_gc AS
(SELECT * FROM
(SELECT /*+ materialize */ inst_id, event, current_obj#, current_file#,
current_block#, COUNT(*) cnt
FROM gv$active_session_history
WHERE event=lower('&event')
GROUP BY inst_id, event, current_obj#,
current_file#, current_block#
HAVING COUNT(*) >5
)
WHERE rownum <101
)
SELECT * FROM
(SELECT inst_id, owner, object_name, object_type, current_file#,
current_block#, cnt
FROM ash_gc a, dba_objects o
WHERE (a.current_obj# =o.object_id (+))
AND a.current_obj# >=1
UNION
SELECT inst_id, '', '', 'Undo Header/Undo block' ,
current_file#, current_block#, cnt
FROM ash_gc a
WHERE a.current_obj#=0
UNION
SELECT inst_id, '', '', 'Undo Block' ,
current_file#, current_block#, cnt
FROM ash_gc a
WHERE a.current_obj#=-1
)
ORDER BY 7 DESC
/
Enter value for event: gc buffer busy acquire
