Hey everyone,

 

Hope you’re doing good.

 

We have a client that has a database used for a SAP BI solution (BW – Business Warehouse), which had stale statistics for dictionary and fixed tables.

 

When I asked the DBA team they said: is taking a long time to gather stats, so, we are not performing it.

 

Unfortunately they were correct, I did a test and DBMS_STATS.GATHER_DICTIONARY_STATS with DEGREE=>12 took more than 13 hours to complete.

 

During this time, 80% was spent on table WRH$_ACTIVE_SESSION_HISTORY, which is the table that stores ASH data for this environment.

 

Well, this can happens in DBs with a large number of objects, active sessions (and on wait too), and also for very large databases.

 

Let’s check the AWR configuration:

 

SQL> @awr/config




           Minute             Minute               Days
Snapshot Interval Snapshot Retention Snapshot Retention
----------------- ------------------ ------------------
               60             259200                180

 

If you need, the above results can be checked with the below query:

 

COLUMN awr_env_interval_minutes HEADING "Minute|Snapshot Interval"

COLUMN awr_env_retention_minutes HEADING "Minute|Snapshot Retention"

COLUMN awr_env_retention_days HEADING "Days|Snapshot Retention"







SELECT

EXTRACT( day FROM snap_interval) *24*60+

EXTRACT( hour FROM snap_interval) *60+

EXTRACT( minute FROM snap_interval ) awr_env_interval_minutes,

EXTRACT( day FROM retention) *24*60+

EXTRACT( hour FROM retention) *60+

EXTRACT( minute FROM retention ) awr_env_retention_minutes,

((

EXTRACT( day FROM retention) *24*60+

EXTRACT( hour FROM retention) *60+

EXTRACT( minute FROM retention )

)/60/24)

awr_env_retention_days

from dba_hist_wr_control

where dbid = (select dbid from v$database);

 

OK, the interval of snapshots is 60 mins (default), the retention is 180 days (6 months). Client have informed that this is needed because they need to compare monthly end closure performance data.

 

Well, fortunately this is a known issue, so, for some tables we can safely perform the lock of statistics, which means that in the next operation of DBMS_STATS.GATHER_DICTIONARY_STATS, those tables will not have the statistics gathered anymore.

 

Below are the commands to lock the statistics on the tables which can impact negatively the duration of the dictionary statistics:

 

exec dbms_stats.lock_table_stats('SYS', 'WRI$_OPTSTAT_SYNOPSIS$');

exec dbms_stats.lock_table_stats('SYS', 'WRI$_OPTSTAT_OPR');

exec dbms_stats.lock_table_stats('SYS', 'WRI$_OPTSTAT_IND_HISTORY');

exec dbms_stats.lock_table_stats('SYS', 'WRI$_OPTSTAT_HISTGRM_HISTORY');

exec dbms_stats.lock_table_stats('SYS', 'WRH$_ACTIVE_SESSION_HISTORY');
After we lock the stats on above table, the duration of this job changed from 13 hours to 23 minutes.

 

The following notes describe the same issue:
Gathering Dictionary Statistics Runs Very Long Time (Doc ID 2364599.1)
DBMS_STATS.GATHER_DICTIONARY_STATS is Stuck on “SYS”.”WRH$_ACTIVE_SESSION_HISTORY” and is Taking a Very Long Time (Doc ID 2046826.1)

 

Hope this helps.

 

Peace,

 

Vinicius