MMON Process Causing Blocking And Contention - enq: WF - contention waits (Doc ID 2649588.1)

 In this Document

Symptoms
Changes
Cause
Solution
References


APPLIES TO:

Oracle Database - Enterprise Edition - Version 12.2.0.1 and later
Information in this document applies to any platform.

SYMPTOMS

MMON Process Causing High WF - contention waits.

 

WF - contention waits are seen when MMON (Manageability Monitor) is attempting the auto-flush function. That means that the
database is trying to flush the ASH (Active Session History) data into the AWR (Active Workload Repository) for the historical repository.

 

CHANGES

 Expired and old AWR related data not being cleared causing Auto-Flush Action to run long.

CAUSE

Flush operations are running long due to the size of AWR tables.

AWR data shows WRH$_SQL_PLAN consuming significant space.

(3b) Space usage within AWR Components (> 500K)
**********************************

COMPONENT    MB         SEGMENT_NAME - % SPACE_USED                                   SEGMENT_TYPE
---------         --------     --------------------------------------------------------------------- ---------------
SQLPLAN        11,731.0    WRH$_SQL_PLAN                                                         - 28% TABLE

Flush time is greater than 5 minutes

NAME                             TIME
---------------------------- -----------------------------
WRH$_SQL_PLAN            +000000000 00:05:51.512 <<---over 5 minutes
WRH$_SQL_PLAN            +000000000 00:06:29.960 <<---over 6 minutes

SOLUTION

1. Check to see which objects are the largest in the AWR by running an AWR info report

conn / as sysdba
@?/rdbms/admin/awrinfo.sql

 

2. Then obtain the time is takes to flush the data

   Obtain flush timing for all the tables. (obtained from note 1301503.1)

set pagesize 999
column name format a28
column time format a29
variable dbid number
exec select dbid into :dbid from v$database;

variable snap_id number
exec select max(snap_id) into :snap_id from wrm$_snapshot where dbid=:dbid;

select table_name_kewrtb name, end_time-begin_time time
from wrm$_snapshot_details, x$kewrtb
where snap_id = :snap_id
and dbid = :dbid
and table_id = table_id_kewrtb
order by table_id;

 

 

3. Reduce the space of the table by first purging old data using dbms_workload_repository.purge_sql_details.
  

 

This procedure purges SQL details, specifically rows from WRH$_SQLTEXT and WRH$_SQL_PLAN that do not have corresponding rows (DBID, SQL_ID) in WRH$_SQLSTAT

 .

Check row count and size first.

   select count(*) from WRH$_SQL_PLAN ;
   select sum(bytes) from dba_segments where segment_name ='WRH$_SQL_PLAN';

Run the purge.

   select distinct(dbid) from wrm$_snapshot;

   exec dbms_workload_repository.purge_sql_details(&dbid);

Move the table out of sysaux and back in, rebuild the index.

   alter table WRH$_SQL_PLAN move tablespace users parallel 10;
   alter table WRH$_SQL_PLAN move tablespace sysaux parallel 10;
   alter index SYS.WRH$_SQL_PLAN_PK rebuild online;

In addition, you can shrink the space allocated to the table with the following steps.

Shrink the space after purging the rows.

   ALTER TABLE WRH$_SQL_PLAN enable row movement;
   ALTER TABLE WRH$_SQL_PLAN SHRINK SPACE CASCADE;
   ALTER TABLE WRH$_SQL_PLAN disable row movement;

 

 

 

REFERENCES

BUG:21918948 - HUGE SIZE OF LOB SYS_IL0026593311C00038 BELONGING TO WRH$_SQL_PLAN

NOTE:308450.1 - How to Modify Statistics collection by MMON for AWR repository

NOTE:25954054.8 - Bug 25954054 - wrh$_sgastat_u becoming unusable state in upgraded db
NOTE:1366133.1 - SQL Tuning Health-Check Script (SQLHC)


Comments

Post a Comment

Popular posts from this blog

How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)

  In this Document Goal Solution References APPLIES TO: Oracle Database - Enterprise Edition - Version 12.2.0.1 and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Information in this document applies to any platform. GOAL Goal of this document is to provide the method to purge the old records of Optimizer Statistics Advisor Task namely AUTO_STATS_ADVISOR_TASK that consumes huge SYSAUX space. Huge no.of old records retained in WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK or INDIVIDUAL_STATS_ADVISOR_TASK incurs heavy SYSAUX space. AUTO_STATS_ADVISOR_TASK is meant for Automatic Statistics Advisor task while INDIVIDUAL_STATS_ADVISOR_TASK is for Manual Statistics Advisor task.  DBA_ADVISOR_PARAMETERS displays all advisor task parameters and their current values in the database. There is a parameter name called EXECUTION_DAYS_TO_EXPIRE. This parameter is set in no.of days. Executions older than the value(no.of days) set would be purged automaticall...
Read more

Oracle session snapper

-- use dbms_session.sleep instead of dbms_lock on oracle 18c or higher -- last column  is sid  -- scipt_name   stat_to_collect   secods_of_snap  how_many_snap   sessions_to_snap -- @snapper all 100 1 lgdr@* -- @snapper all 100 1 sqlid@* -- @snapper all 190 1 all -- @snapper all 190 1 584 -- @snapper all 30 3 584 -- @snapper all 5 1 869 -- @snapper all 30 1 584 -- @snapper ash 5 1 144 -- @snapper ash=sql_id+event+wait_class+blocking_session+p2+p3 5 1 144 -- @snapper ash 5 1 156 -- @snapper ash 190  1 all -- @snapper ash 5 1 all -- @snapper ash=sid+event+wait_class 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id,ash2=program+module+action 5 1 all -- @snapper ash=event+wait_class,stats,gather=ts,tinclude=CPU,sinclude=parse 5 1 all -- @snapper ash=sid+event+wait_class,ash1=sid+sqlid+module,stats,gather...
Read more

Oracle Materialized View In-Depth and Materialized View refresh issues in 19c

   We    had  situation demanding  tuning of  MV  refresh and  options  like  TUNE_MVIEW  and Atomic_Refresh was explored . Hence  Documenting materialized view sample commands  and views in this  blog .     After migration if we are  facing slowness  in  Materialized View refresh , we can explore to re-create  Materialized View   Since  main topic for this blog   was MV issues  in 19c ,  posting that topic   first in line below  We also   have Real-Time Materialized Views  to explore .  To Note :  1) We can  create Mv  on ON PREBUILT TABLE  2) We can create Index on Mv  3) We can  gather stats on mv  just like other table .  4) Mv can be REFRESH_MODE ON COMMIT / REFRESH ON DEMAND. 5) Mv  REFRESH_METHOD can be  Complete / FAST or FORCE . A FORCE attempts a FAST...
Read more