Abdul Hafeez Kalsekar -- Oracle database tuning case studies

  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$KEWRTSQLP...

The Statistics of Optimizer Statistics History tables & AWR snapshots was not automatically purged. Due to that, the SYSAUX Tablespace grows very large. SOLUTION We have to manually purge the Optimizer Statistics, split the partitions of AWR objects and purge the AWR Snapshots manually. 1.) Run the below script to check the current space usage of Optimizer Statistics Histogram & AWR tables, its relevant indexes in SYSAUX tablespace.      SQL> conn / as sysdba      SQL> @?/rdbms/admin/awrinfo.sql      Output is written to awrinfo.lst in the current working directory 2.) Check the statistics availablility from Optimizer Statistics Histogram table by number of days.     SQL>  select systimestamp - min(savtime) from sys.wri$_optstat_histgrm_history; The query will return a result similar to the followiing: SYSTIMESTAMP-MIN(SAVTIME) ---------------------------- +000000099 22:30:28.418491 3.) Purg...

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 syntax and back in, and 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...

  In this Document Purpose Troubleshooting Steps   Detecting Missing snapshots   Snapshots creation does not start   Database Settings and Status   STATISTICS_LEVEL setting   Database is Closed   Database Open Mode   Snapshots creation starts but does not complete   General Reasons   ADDM takes a significant amount of time to complete   Workarounds   Complications   Timing information for each individual table flush   Known Issues   Community Discussions References APPLIES TO: Oracle Cloud Infrastructure - Database Service - Version N/A and later Oracle Database Exadata Express Cloud Service - Version N/A and later Oracle Database Backup Service - Version N/A and later Oracle Database Cloud Exadata Service - Version N/A and later Oracle Database Cloud Schema Service - Version N/A and later Information in this document applies to any platform. Oracle RDBMS Automatic Workload Repository PURPOSE The purpose of this ...

  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          ...