Skip to Content
Author's profile photo A Prasad Rao

Oracle Database Dictionary Statistics Taking Long Time

Performed oracle database upgrade to I was asked to analyze the database dictionary statistics that was taking long during post processing. Customer was not happy that dictionary statistics was taking long time after database upgrade.

When executing dictionary statistics using the command below

brconnect -u / -c -f stats -t oradict_stats


took 15 hours to complete  statistics.

The unix server (AIX) performance was really good.  It is Power7 Processor,  has large RAM capacity, has many CPU / cores and IP storage throughput was extremely fast.

The oracle RDBMS version was


Decided to check database parameters as shown in the figure below     


As shown above  the database parameters are  found ok.

Applied latest database bundle patch as shown in the figure below.


Executed Dictionary Statistics after oracle upgrade to  SAP Bundle Patch – SBP 201408 containing CPUJul2014  but showed no improvement i.e. took 15 hours to complete dictionary Statistics.  I was informed that customer is going to perform BW upgrade from NW 7.00 EHP 1 to NW 7.4. So I decided to wait till BW 7.4 (ABAP) upgrade completed.

After BW upgrade to 7.4 completed . applied  latest kernel patch 742/17 as well as latest brtools patch level 740/11 , 



Then executed Dictionary Database statistics but still showing no improvement i.e. it was completed in 15 hours.

So I have realized that there is something wrong with  statistics tables  Nealy almost two  months but was showing no improvement.

Then checked in tx code db02 or st04 as shown in the figure below




Double click on the highlighted yellow colourn line  as shown above and click on Explain Plan


based on the above  very high Elapsed time per sec observed in table WRI$_OPTSTAT_HISTGRM_HISTORY

That means database dictionary spent so many hours in accessing HISTGRM$  and WRI$_OPTSTAT_HISTGRM_HISTORY tables. This could be due to poor indexes or large fragmentation on index.

The following select sql  statement shows SM/OPTSTAT occupies the most space within SYSAUX tablespace.


As shown above SM/OPTSTAT occupies 65GB. That was too much.  Storage Cost  is  very high. Is it necessary to have very large table and let the table to grow unnecessarily.


As shown above, out of 65GB , index occupies 45GB space as compared to 16.91 GB in table. This was due to large index fragmentation.

After correcting the table and index, executed dictionary statistics, now it was completed in less than 2 hours



Author:          A Prasad Rao

Compnay:     Tata Consultancy Services Ltd.


Eleven years experience as an Unix System and Oracle database Administration and sixteen years experience as SAP Basis Consultant.

Executed more than 10 SAP Technical Upgrade, combine upgrade and uniocde conversion (Single downtime) and SAP OS/DB Migration in different clients in geographically locations across the world.

Experience in SAP troubleshooting, SAP Oracle performance tuning.

Certified SAP OS/DB Migration, certified OCP (Oracle Certified Professional) 9i Database and certified SAP WEB AS 640.

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Fidel Vales
      Fidel Vales

      You recommend to reduce the retention period

      Strongly recommended  to change the retention period to a lower value, thus data will be kept for shorter period of time. This will prevent table from growing unnecessarily and shorter time to complete the dictionary statistics.

      First, you do not mention which one.

      Second, SAP recommends to INCREASE the AWR retention period to 32 days to be able to analyze problems.

      Third, it is possible that the statistic size (nothing to do with the AWR retention) was due to an ORACLE BUG 14373728 as indicated on the SAP note 1952382. probably the bug was solved, content purged but sizes remained as oracle does not recover the space automagicaly


      Author's profile photo A Prasad Rao
      A Prasad Rao
      Blog Post Author

      Thanks for your feedback.

      Regarding your first query, by default, retention of old stats is 31 days.

      Set retention of old stats to less number of days. i.e. here set  it to 7  days as below.

      exec dbms_stats.alter_stats_history_retention(&days);

      SQL> exec dbms_stats.alter_stats_history_retention(7);

      SQL> select dbms_stats.get_stats_history_retention from dual;




      Purge stats older than 7days This purge will delete data from WRI$ tables.

      Fully agreed with you that statistics size was due to an Oracle Bug 14373728, Perhaps the bug was solved i.e. content purged on table only.

      However  index has to be rebuild   after purge . Hence index occupies  much space as compared to table. 

      This result huge fragmentation on index. 

      After  purge is done, reorg these tables to release space to the database


      Author's profile photo Fidel Vales
      Fidel Vales


      I do know the retention for the old statistics, but perhaps other readers no. And you do not specified which retention you are talking about, while in SAP we only talk to the AWR retention. That can cause confusion and other customers to change the wrong retention. Still, I would NOT recommend to change the retention time for the statistics either.

      The problem here is related to the "fragmentation" (first part of the article) where I think is good (analysis and explanation) where I had the problem is the recommendation as that would not solve or avoid the problem (root cause)