Well, I have investigated it. Firstly, I took the db load statistics and generated graphs of three consecutive working days to see the load average on database. These days graphs are almost similar. Then I went back one month earlier and then one more month earlier to see the previous load graphs. It seems that in 2 months, db load increased about 10% - not stunning as the average db load is still around 1 during peak hours.
Secondly, I took Statspack reports to see the overall database health. The report seemed that we have a fantastic instance efficiency.
Instance Efficiency Percentages (Target 100%)
| Buffer Nowait %: | 100.00 | Redo NoWait %: | 100.00 |
| Buffer Hit %: | 99.64 | In-memory Sort %: | 100.00 |
| Library Hit %: | 98.94 | Soft Parse %: | 99.01 |
| Execute to Parse %: | 80.40 | Latch Hit %: | 99.99 |
| Parse CPU to Parse Elapsd %: | 100.05 | % Non-Parse CPU: | 98.50 |
Impressive! Nothing to do with instance tuning right at this moment. The small hard parse thing is well known to us and is unavoidable from development perspective. In load profile, Redo size and Logical reads were noticeable.
Load Profile
| Per Second | Per Transaction | |
|---|---|---|
| Redo size: | 38,515.67 | 3,663.59 |
| Logical reads: | 26,862.97 | 2,555.19 |
We have huge updates and inserts in the database. Concurrent 400 + users doing a lot of stuffs with the applications- oracle is generating big amount of redo. When we will integrate some caching solutions in memory for very frequently updated tables, we will get rid of it.
But Logical reads is alarming. We have an option here to reduce logical reads. We have plan to use larger database block size (currently using the default 8K) in near future. It reduce logical reads and thus elapsed time ensuring good db health.
I was also looking for some performance related article and got one very interesting - Oracle Myths
http://www.dba-oracle.com/t_oracle_myths_performance_testing.htm
No comments:
Post a Comment