We are doing Load Testing on our application. We are using a load of about 100 - 500 hits/sec, I mean the application load, acheived with the help of a Tool.
When I pull the statspack report, in the "Top 5 Timed Events" I'm getting high amount of waits on 'buffer busy waits' and 'enqueue'.
I'm using segment space management as AUTO and even I have increased the INITRANS to 3 on the tables. I would like to know, how can I remove these wait events.
I see you are using 9.2.0.5. I dont mean to sound like Oracle support folks but 9.2.0.6 has some optimizer fixes... just fyi. we just went thru a major siebel upgrade....
1. Begin/end snap differences are like more then 2hrs.
2. your enqueue is high so fix that 1st but the wait isnt high at all.
3. buffer busy waits can be resolved. Think about using different buffer pools
4. you have latching problems...
5.high undo waits.
6. free buffer requested is high..allocate more buffers
7. High reads and waits on BIDRXDV1
8. What ever is in this datafile..needs to be segregated.. tablespace BIDRX_INDEX -->/ocfs-index/bidrx_index01.dbf
9. consider faster disk or spllitng hot tables into different datafiles or accross sep spindles..
10. bump up shared_pool_size
11, your userentity table has 1 Million reads. Maybe you can stick it in the keep pool or maybe partition the data
12. Usage PGA aggregate target instead of sort area size.
Dear jlakhani,
Thanks for your info. I have planned to increase the size of db_cache_size and shared_pool also. I planned for version upgrade from Oracle 9.2.0.5.0 to Oracle 9.2.0.6.0. I was able to upgrade it successfully on our Test Server. The database was fine and working normally. But, We are not able to do any DML from our web application. We are using JAVA/JSP/SERVLETS. There is some problem with the drivers. I'm not able to get the JDBC Drivers for Oracle 9.2.0.6.0 version. I tried with version JDBC drivers of other versions like 9.2.0.5.0 and 10g versions also. But they too are not working properly with version Oracle 9.2.0.6.0. I mean selections are fine but no DML. We are getting "Protocol Voilation Exception" error and the application is not able to connect to database.
I can see that your snap time is
Elapsed: 155.93 (mins)
well that makes the statspack report pretty useless.Just take it in a interval of 10-15 minutes max when you are running the load to make it meaningful.
select MODULENAME as Servlet_Name,count(MODULENAME) as NO_OF_EXE
, round(max(TIMETAKENINMILLIS )) as Max_Time_Taken_In_Millisecon
d, round(min(TIMETAKENINMILLIS )) as Min_Time_Taken_In_Milliseco
nd, round(avg(TIMETAKENINMILLIS )) as Averate_In_Millisecond fro
m ResponseTime group by MODULENAME order by MODULENAME
28 executions in 155 minutes is about once per 5 minutes.
And this statement looks like a performance statistic for servelets.
Please consider to turn that monitor off for a short time and check if your buffer busy waits disappear.
Afterwards you might consider to archive your table "ResponseTime" so that the actual records required by your performance monitor are so small that they are fully cached.
(2) Your statspack report shows many SQ Enqueue Waits.
There exists a document on Metalink which describes this SQ Enqueue in context with "Buffer Busy Waits.
Hai Hrishy,
I have uploaded the statspack reports from the two RAC nodes. This report was taken after doing a load test for about 25 minutes. This report is different from my other reports.