Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
former_member227281
Active Participant

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.

3 Comments
Labels in this area