AWR Snaps not getting generated In Oracle



If AWR snaps are not getting generated, then we need to check and fix the issue.

Scenario1: After DB refresh AWR snapshots are not generating.

Trying to generate them manually might display the below error.

SQL> EXECUTE dbms_workload_repository.create_snapshot();
BEGIN dbms_workload_repository.create_snapshot(); END;
*
ERROR at line 1:
ORA-13509: error encountered during updates to a AWR table
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 99
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 137
ORA-06512: at line 1


PL/SQL is running from the last refresh which is related to AWR, blocking other sessions. 

Find the session and kill it. Please retry to create the snap, The error should be resolved.

Scenario2: SQLBIND taking more space


Please run $ORACLE_HOME/rdbms/admin/awrinfo.sql to get info on AWR of your system. It will display SQLBinds taking most of the space.

This is due to following bug:

Bug 16777441 - WRH$_SQL_BIND_METADATA IS NOT PURGED BY AUTO PURGE / DEEP PURGE. "

 Too many bind variables in SQL causing large size of SYSAUX component SQLBIND.
 

 

SOLUTION
1. The bug is fixed in 12.2

2. Apply Patch 16777441

3. Or use the following workaround:

1. Find :dbid of the database for which we wants to purge
WRH$_SQL_BIND_METADATA. If it is the active database, you can find :dbid by
running:

SQL> select dbid from v$database;

2. Find :end_snap_id, which is the largest snap_id to purge. You can find it
by running:

select min(snap_id) from wrm$_snapshot
where dbid = :dbid
and (cast(SYSTIMESTAMP as TIMESTAMP) - end_interval_time) <=
  (select retention
  from wrm$_wr_control
  where dbid = :dbid);

3. Delete Based on snap_id range. Here you have 2 choices since snap_id is
not part of the PK (i.e., no index), you can either delete all in one query

(delete all snap_ids less than :end_snap_id),

or use multiple queries, each
to delete a sub-range of snap_ids, with commits in between. For example, if
:end_snap_id = 900, then first

delete all snap_ids < 300 & commit,

then

delete all snap_ids < 600 & commit,

finally deletes all snap_ids less than
900 (:end_snap_id) & commit.
The delete query is:

delete from WRH$_SQL_BIND_METADATA tab
where ( tab.snap_id <= :end_snap_id and
  dbid = :dbid)
and not exists (select 1 from WRM$_BASELINE b
  where (tab.dbid = b.dbid) and
  (tab.snap_id >= b.start_snap_id) and
  (tab.snap_id <= b.end_snap_id));





If you like please follow and comment