buffer busy waits

buffer busy waits - enqueue

Hai Everyone.
We are using Oracle9i RAC. The setup is as follows:

Shared Storage: 1 - EMC Clarion CX-300 (RAID 10)
RAC Nodes: 2 - Dell Poweredge 2650 Servers (2 CPU, 8GB RAM)
Private Interconnect: Gigabit Ethenet Cards

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.

regards,
Dileep.

Hi Dileep

Can you please upload your statspack report.

regards
Hrishy

What type of enqueue waits?

1 Attachment(s)

Dear Hrishy,
I'm uploading the Statspack report from both the RAC nodes in the form of a zip file.

regards,
Dileep T.V.R.

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....

I also noticed somethings are sp85_86.lst

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.

How can we reduce buffer busy waits and enqueue?

regards,
Dileep.

Hi Dileep

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.

regards
Hrishy

Hi tvrdileep!

jlakhani suggests to optimize IO (tablespace BIDRXDV1)

(1) Before optimizing IO you should identify IF you really NEED that much IO !

The TOP-IO-statement causing 54% of all pysical reads is

CPU Elapsd

Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value

--------------- ------------ -------------- ------ -------- --------- ----------

203,513 28 7,268.3 54.7 15.18 111.55 1413706332

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.

http://metalink.oracle.com/metalink/...&p_id=155971.1

Please note that 12% of your parse-time is just caused by fetching new Sequence-Range:

% Total

Parse Calls Executions Parses Hash Value

------------ ------------ -------- ----------
11,728 11,728 12.41 3716207873

update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,
order$=:6,cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=:
1

Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value
------------ --------------- ---------------- ----------- ---------- ----------

SELECT SEQ_DRUGAUTOBIDLOG.NEXTVAL FROM DUAL

2,324,471 2,324,880 1.0 0.00 0.00 52363000

SELECT SEQ_DRUGAUTOBIDLOG.NEXTVAL FROM DUAL

3,995 3,995 1.0 0.00 0.00 1745196126

SELECT SEQ_SPPCRPTDET.NEXTVAL FROM DUAL

This are about those 12,000 accesses to seq$ to assign 2,3 Mio new values
(nearly all for the Insert-statement

CPU per Elap per

Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value

------------ --------------- ---------------- ----------- ---------- ----------

2,325,011 2,324,992 1.0 0.00 0.01 3941894919

INSERT INTO DRUGAUTOBIDLOG ( DABLID, DRUGAUCTIONID, BIDDATE, DRU
GID, PHARMACYID, REASON ) VALUES ( :B4, :B3, SYSTIMESTAMP, :B2,
:B1, 'No rule defined to calculate drug price' )

In total you are assigning 2,3 Mio new Sequence-Values with 12,000 access to SEQ$,
that means your Sequence-Cache size seems to be about 200.

Consider to increase the cache-size.
(2.3 Mio inserts in 155 min is about 250 per second)

Good Luck,

Telco_DBA
http://www.Mercury-Consulting-Ltd.com/

1 Attachment(s)

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.

regards,
Dileep T.V.R.

Hi

From what i understand from your statspack report.

global cache cr request and db file scattered read are contributing to your wait times.

I would suggest SQL tuning esp those that are on this table
USERENTITY

Top sql's to tune would be

Code:

1)SELECT COUNT(USERENTITYID) FROM USERENTITY WHERE USERID = :B1 2)SELECT ENTITYPRIVILEGE.PRIVID FROM USERPRIVILEGES, ENTITYPRIVILEGE WHERE USERPRIVILEGES.USERENTITYID = :B1 AND USERPRIVILEGES.PRIVID = ENTITYPRIVILEGE.PRIVID 3)SELECT A.USERENTITYID, A.USERID, A.FIRSTNAME, A.LASTNAME, A.EMAIL, A.ISDELETED, B.DESIGNATION, B.ENTITYID, B.PARENTID, B.ISUSERACTIVE, B.ISADMIN FROM USERMAST A, USERENTITY B WHERE A.USERENTITYID = B.USERENTITYID AND LOGINID = :B2 AND PASSWORD = :B1

Hope you have set segment space management for the BIDRXDV1 tablespace to auto.

Code:

bobject Obj. Physical Owner Tablespe Obj Name Type Reads %Total -------- --------- ------------ ----- ----- ------ BIDRXDV1 BIDRXDV1 USERENTITY 3,782,634 99.91 91

Another object where you would expect marginal performance improvement would be RESPONSE time.

regards
Hrishy

regards
Hrishy

Hai Hrishy.
Thanks for your suggestions. Yes, I can see to from the Statspack report that USERENTITY table was a big trouble. I have created an index on USERID column without which it was causing lot of consistent gets(3915). After creating Index I have seen a drsatic improvement in the query. There are only 2 consistent gets.

BEFORE INDEX (COST=584, consistent gets=3915, Full Table Scan)
-------------

SQL> select count(userentityid)
from userentity where userid = 8;

COUNT(USERENTITYID)
-------------------
0

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=584 Card=1 Bytes
=5)

1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'USERENTITY' (Cost=584 Card=1 Byt
es=5)

Statistics
----------------------------------------------------------
239 recursive calls
0 db block gets
3915 consistent gets
0 physical reads
0 redo size
389 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed

After Creating Index (2 consistent gets, COST=2, Index range Scan )
-----------------------

SQL> select count(userentityid)
from userentity where userid = 1;
2
COUNT(USERENTITYID)
-------------------
9

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=1 Bytes=5
)

1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'IND_USERENTITY_USERID' (NON-UNIQU
E) (Cost=1 Card=1 Bytes=5)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
390 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Even we changed the other query. I'll get back to you after doing another load test.

regards,
Dileep T.V.R.