Performed oracle database upgrade to 11.2.0.4. 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

Dictionary_1.jpg

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

Dictionary_1a.JPG

Decided to check database parameters as shown in the figure below     

Dictionary_2.jpg

As shown above  the database parameters are  found ok.

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

Dictionary_3.jpg

Executed Dictionary Statistics after oracle upgrade to  SAP Bundle Patch – SBP 11.2.0.4.3 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 , 

kernel.JPG

brtools.JPG

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

Dictionary_4.jpg

Dictionary_6a.JPG

Dictionary_6.jpg

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

Dictionary_7.jpg

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.

Dictionary_9.jpg

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.

Dictionary_10.jpg

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

Dictionary_11.jpg

.

Author:          A Prasad Rao

Compnay:     Tata Consultancy Services Ltd.

author_aprao.JPG

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.

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

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

    Regards

    (0) 
    1. A Prasad Rao 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;

      GET_STATS_HISTORY_RETENTION

      —————————————————

      7

      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

      Regards

      (0) 
      1. Fidel Vales

        Hi,

        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)

        (0) 

Leave a Reply