Database Reference
In-Depth Information
INST OWNER OBJECT_NAME OBJECT_TYP file blk CNT
---------- ---------- -------------------------------- ---------- ------ ------- -------
1 APPLSYS FND_CONCURRENT_PROCESSES TABLE 49 635413 2039
1 APPLSYS FND_CONCURRENT_PROCESSES TABLE 301 954692 18
1 SYSTEM AQ$_QUEUE_TABLES TABLE 4 1129 18
1 APPLSYS FND_CONCURRENT_QUEUES TABLE 382 123951 17
1 Undo Block 0 0 15
...
Dumping the data block using the following command
9
and reading through trace file, you can identify the type
of block. Oracle Support will help to read the trace file too. Table
10-1
shows possible block types and methods to
resolve the contention.
alter system dump datafile 49 block 635413 block max 49 635413 ;
Table 10-1.
Block Type and Possible Issues
Block type
Comment
Segment Header
Probable freelists contention. Alter the table to have multiple freelist
groups.
Table block
Possibly code updating a few rows of the table aggressively. Concurrent
deletes on inserts of a few rows. SQL statements scanning objects
aggressively. Partitioning or code change may be required.
ASSM blocks
ASSM bitmap block contention. Usually would require further analysis
from Oracle Support.
Index root or branch blocks
Heavy concurrent inserts and deletes.
Undo header blocks
Numerous short transactions?
Undo blocks
Long-pending transactions and excessive CR generation.
Dictionary
Possible sequence issues if the block belongs to SEQ$ table. Requires
further analysis.
If your database is a 12c CDB, then join to cdb_objects to identify PDB associated with the object. The following
script is useful in a database container to identify the block in a CDB.
Listing 10-13.
Ash_gcwait_to_block_12c.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, con_id, event, current_obj#, current_file#,
current_block#, COUNT(*) cnt
9
Another cautionary tale: do not dump numerous blocks in a RAC database from version 11.2 onward. For example, dumping all
blocks of an extent overloads the LMS processes and can lead to node crash. So, try to dump few blocks at a time if required.
