Considering we were facing lot of issues related to awr snapshots and awr report thought of documenting known troubleshooting and known fix
Known Issues :
Issue 1 ) Generating AWR report Hangs on 'Control File Sequential Read
Solution :: alter session set "_hash_join_enabled"=true as per Doc Id : 2045523.1
Issue 2) AWR snaoshot hangs with enq: WF - contention
Solution 1: As per Doc ID 2695000.1 Kill the MMON child processes and the MMON process for the instance that is not generating AWR reports so that it is restarted by the database.
ps -ef|grep ora_m0
ps -ef|grep _mmon_
Solution 2 : Gather stats on these 3 tables and check the manual report generation again . If possible try gathering fixed objects stats
SQL> exec dbms_stats.gather_table_stats('SYS', 'X$KEWRSQLIDTAB');
SQL> exec dbms_stats.gather_table_stats('SYS', 'X$KEWRTSQLPLAN');
SQL> exec dbms_stats.gather_table_stats('SYS', 'X$KEHSQT');
Verify with the table last analyzed details
SQL> select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where table_name ='X$KEWRSQLIDTAB';
SQL> select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where table_name='X$KEWRTSQLPLAN';
SQL> select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where table_name='X$KEHSQT';
Solution 3 : As per Doc ID 2649588.1 MMON Process Causing Blocking And Contention - enq: WF - contention waits due to increase in size of awr data which needs to be purged
https://abdul-hafeez-kalsekar-tuning.blogspot.com/2023/09/mmon-process-causing-blocking-and.html -- Doc ID 2649588.1
https://abdul-hafeez-kalsekar-tuning.blogspot.com/2023/09/troubleshooting-missing-automatic.html
https://abdul-hafeez-kalsekar-tuning.blogspot.com/2023/09/purging-oracle-awr-details.html
https://abdul-hafeez-kalsekar-tuning.blogspot.com/2023/09/manually-purge-optimizer-statistics-awr.html
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;
Data Collection for analysis :
1) 10046 tracing
Try generating Snapshots manually . Try generating 10046 tracing in session where you are trying manual snapshots
alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set max_dump_file_size = unlimited;
alter session set tracefile_identifier = '10046_awr';
alter session set events '10046 trace name context forever,level 12';
set timing on
EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;
2) Try generating Tfa
$TFA_HOME/bin/tfactl diagcollect -srdc dbawrspace
3) Verify Mmon trace files and alert log files
4) . AWR info report
Check to see which objects are the largest in the AWR by running an AWR info report
conn / as sysdba
@?/rdbms/admin/awrinfo.sql
4) Other Views
================
SQL> select con_id, instance_number, snap_id, begin_interval_time, end_interval_time from cdb_hist_snapshot order by 1,2,3;
SQL> select * from cdb_hist_wr_control;
COLUMN NAME FORMAT a40
COLUMN VALUE FORMAT a30
set lines 200 pages 200
select ksppinm name,
ksppstvl value
from sys.x$ksppi x,
sys.x$ksppcv y
where (x.indx = y.indx)
and ksppinm = '_awr_mmon_cpuusage';
Select count(*) from WRI$_OPTSTAT_HISTHEAD_HISTORY ;
select dbms_stats.get_stats_history_retention from dual;
select dbms_stats.get_stats_history_availability from dual;
select systimestamp - min(savtime) from sys.wri$_optstat_histgrm_history;
select min(savtime) from sys.wri$_optstat_histgrm_history;
SELECT MIN(SNAP_ID), MAX(SNAP_ID), COUNT(*) FROM SYS.WRH$_SYSMETRIC_HISTORY A
WHERE NOT EXISTS
(SELECT * FROM SYS.DBA_HIST_SNAPSHOT B WHERE B.SNAP_ID = A.SNAP_ID AND A.DBID = B.DBID);
SELECT COUNT(1) Orphaned_ASH_Rows
FROM wrh$_active_session_history a
WHERE NOT EXISTS
(SELECT 1
FROM wrm$_snapshot
WHERE snap_id = a.snap_id
AND dbid = a.dbid
AND instance_number = a.instance_number
);
========================
**************************
set markup html on spool on
spool /tmp/AWRCheck25thNov.html
set echo on
select systimestamp from dual;
select instance_name,version,host_name,status from gv$instance;
alter session set container = CDB$ROOT;
show con_name
show parameter awr
select * from cdb_hist_wr_control;
select con_id, instance_number, snap_id, begin_interval_time, end_interval_time from cdb_hist_snapshot order by 1,2,3;
alter session set container=FREXR1P;
exec dbms_workload_repository.create_snapshot();
alter session set container = CDB$ROOT;
select con_id, instance_number, snap_id, begin_interval_time, end_interval_time from cdb_hist_snapshot order by 1,2,3;
spool off
set markup html off
**************************
References :
Troubleshooting: Missing Automatic Workload Repository (AWR) Snapshots and Other Collection Issues (Doc ID 1301503.1)
AWR Snapshots Not Generating ORA-13516 ( Doc ID 2756259.1 )
ORA-13516 AWR Operation failed: SWRF Schema not initialized ORA-06512 SYS.DBMS_WORKLOAD_REPOSITORY ( Doc ID 459887.1 )
Problem: ORA-13516 When Trying To Make A Manual Snapshot Of Repository Database ( Doc ID 365097.1 )
AWR Snapshots Not Generating ( Doc ID 308003.1 )
ORA-13516: AWR Operation Failed: CATPROC Not Valid (Doc ID 2547174.1)
MMON Trace Shows: "*** KEWRAFC: Flush slave failed, AWR Enqueue Timeout" ( Doc ID 560204.1 )
AWR Snapshots Fail to Generate With Associated "Suspending MMON action '%s' for 82800 seconds" alert log messages and Underlying ORA-12751 Errors ( Doc ID 2043531.1 )
