AWR Warehouse Slow Loading AWR Dumps and AWR Snapshots Getting Stuck on 'enq: TM - contention'
(Doc ID 2390266.1)
Last updated on JULY 15, 2022
Applies to:
Oracle Database - Enterprise Edition - Version 12.1.0.2 and laterOracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
- AWR warehouse database is very slow to load AWR dumps and AWR snapshots are getting stuck
- Most significant waits on database is 'enq: TM - contention' with following blocker in hanganalyze:
INSERT /*+ APPEND */ INTO SYS.WRH$_FILESTATXS ("SNAP_ID", "DBID", "INSTANCE_NUMBER", "FILE#", "CREATION_CHANGE#", "PHYRDS", "PHYWRTS", "SINGLEBLKRDS", "READTIM", "WRITETIM", "SINGLEBLKRDTIM", "PHYBLKRD", "PHYBLKWRT", "WAIT_COUNT", "TIME", "CON_DBID")
SELECT "SNAP_ID",
2264567514 AS DBID,
"INSTANCE_NUMBER",
"FILE#",
"CREATION_CHANGE#",
"PHYRDS",
"PHYWRTS",
"SINGLEBLKRDS",
"READTIM",
"WRITETIM",
"SINGLEBLKRDTIM",
"PHYBLKRD",
"PHYBLKWRT",
"WAIT_COUNT",
"TIME",
DECODE(CON_DBID, DBID,2264567514,CON_DBID) AS CON_DBID
FROM "AWRLOAD$1019388"."WRH$_FILESTATXS"
WHERE "DBID" = :c_dbid;
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Cause |
| Solution |
| References |