Hi Bill,
In an effort not to miss, sharing with you, all of the interesting
encounters
I had the past week, I had to record it in an email, so that we can
address
them next week.
The good news....
Today, Sunday the 5th, I upgraded the SFA and Rockpile database
to version 8.0.5. I also took a backup of the database .
Tuned the CMSDEV & CMSTEST database for performance.
The interesting news....
Last Tuesday, immediately after you left for vacation, CMSDEV database
was rebooted for lack of performance. On wednesday I traced several
user's sessions, and the database as a whole, and found interesting
statistics. On Thursday the database performance slipped again and
that's
when I decided to make changes to the database setup.
Summary of database performance statistics before enhancements:
- Excessive library cache misses was detected.
- Buffer cache hit ratio was only 70%.
- Processes were waiting for space in the redo log buffer
- The database was going through a lot ( I mean a lot) of full table
scans.
This was attributed to SAP Buffer table queries.
- The OS main memory was too low for PGA memory allocation,
paging and swapping, as more than 60 users were logged on at one
time.
- The total bytes of a single row in the SAP buffer table exceeds 2K,
which eventually has caused all the rows to chain, and we all know
what row chaining does to the database.
Here's what I did, to improve the performance and has worked :
- Decreased the CMSTEST instance's SGA size, in order to expand
the size of CMSDEV's SGA.
- Increased the shared pool size
- Increased the database block buffer size
- Increased the log buffer size.
- Created indexes on the SAP buffer tables to avoid full table scans
that were mostly occupied in the SGA at any one time.
- Gave more room to the OS for its swapping and paging.
Here's what I would like to suggest for future enhancements:
- We can increase the main memory from 256M to 512M, because
CMSDEV is used by more number of users for 30 testing now, and we
need a larger SGA.
- After the memory increase, we can expand the SGA size, to get all
of the SGA hit ratios to 100%, in CMSDEV.
- We need to increase the default block size from 2K to 4K on all of
the
databases (not an easy task).
Sorry for this long email, but I wanted to communicate all the fun we
had
last week.
-Poorna Prakash
967-1043
Received on Sun Sep 05 1999 - 15:08:34 CDT
