Oracle Database Dictionary Statistics Taking Long Time
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
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.
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 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 ,
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.
You recommend to reduce the retention period
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
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
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)