- SAP Managed Tags
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.
| User | Count |
|---|---|
| 10 | |
| 8 | |
| 6 | |
| 6 | |
| 6 | |
| 6 | |
| 5 | |
| 4 | |
| 4 | |
| 4 |
