Buffer Busy Waits on ASSM

Advertisement
Oracle 11.1.0.7
Windows 2003
We have huge Buffer Busy Waits , Tablespace is in ASSM. How can we avoid this ?
Thank You
Advertisement

Replay

Buffer busy waits are reported when multiple sessions try to read the same block in the cache.
Wait events:
Buffer busy wait:
This event is commonly caused when multiple session are trying to read the same block or multiple session waiting for a change to complete in the same block. Block contention corrective actions depends on the type of block involved. Query on V$WAITSTAT and X$KCBFWAIT to detect the hottest blocks breaking down by the type of block. To reduce buffer busy waits on:
data blocks:
Reduce number of rows per block whether changing pctfree/pctused or reducing the DB_BLOCK_SIZE.
Check for 'right-hand-indexes' (indexes that get inserted into at the same point by many processes). You can use reverse key indexes to distribute the different information.
See Note:155971.1 for a detailed case-study on how to diagnose and resolve intensive random access performance problems.
segment header:
Use freelists or increase of number of freelists.
Extent size too small can cause contention on the header when the table grows regularly. Consider increasing the extent size for the table.
undo header:
Add more rollback segments to reduce the number of transaction per rollback segment.
Reduce the value of the parameter TRANSACTION_PER_ROLLBACK_SEGMENT
undo block:
Consider making rollback segments larger in exclusive mode
Refer to metalink DOC 62172.1

How to tune buffer busy waits in RAC

Hi, In our environment we se lot of buffer busy waits. How can I tune that. we are using 9i RAC on HACMP. Regards MMUThere could be several reasons for buffer busy waits, however the common reason is high logical reads.. Normally they relate to bad S

Buffer busy wait

i have a problem with the buffer busy wait, i got that message from spotlight my buffer cache hit ratio will become red and 100%, can someone help me to solve this problem?Spotlight / Foglight alarms are very miss leading at times, XYZ being busy for

Odd latch: buffer busy waits results

I'm looking at a database where OEMGC shows a bad concurrency issue and users report response times of as much as 5 minutes per query. I reviewed Note: 42152.1 which pointed me to Note: 163424.1. Running this, I'm seeing cases where the latch request

Sudden surge of 'gc buffer busy release/acquire' waits on index in 11gR2 leads to timeouts

Hello, We experienced a small applicative crash on Tuesday afternoon, when all of a sudden a huge soaring of gc buffer busy release & acquire-related waits led to timeouts, for about 10 to 15 minutes, in a RAC 11gR2 two-instance database. Drawing AWR

Gc current request waits for concurrent DL inserts

I see 8 different concurrent session runing following insert statement ( DL load ) on 2 different node of a 4 node RAC cluster (10.2.0.4). Table T1_TEMP is on ASSM tablespace. This DL insert from a ETL tool. INSERT /*+ SYS_DL_CURSOR */ INTO T1_TEMP (

ASSM (Automatic Segment Space Management)

Hi Folks. I have a severe problem regarding Buffer Busy Waits. I have deceided to implement ASSM (Automatic Segment Space Management). Please assist....is the approch OK & how should i apply this in a existing tablespace (pls also specify the syntax

What could cause concurrency wait on a query execution ?

Hi all, I have a DML that is part of a stored procedure(sp), the sp itself is executed from a front end. When user is not on peak load, all running OK. But on peak Load, the DML is on Top Activity and having Concurrency Wait 90%. When this happen, th

Wait events tuning

Hello SAP Community, I start by mentioning a few details about the system I'll be talking about in this subject: - SAP NetWeaver 7.0 - Oracle Database 10.2g I was reading the following Note: "Note 618868 - FAQ: Oracle performance", in order to t

Reason Codes for Wait Events

Does anyone know where I can find the reason codes for all the different wait events?Oxnard, Try the following to decode the class# when the class number is greater than or equal to 15: SELECT   DECODE(MOD(55 - 16,2),1,'Undo Header for Undo Segment '

Data Buffer Cache Quality

Hi All, Can somebody please please tell some ways in which i can improve the data buffer quality? Presently it is 51.2%. The DB is 10.2.0.2.0 I want to know, wat all factors do i need to keep in mind if i want to increase DB_CACHE_SIZE? Also, i want

Buffer pool parameter vs LRU/MRU

Dear Friends, I know that in the database buffer cache , the following parameters define the sizes of the caches for buffers : –     Default : DB_CACHE_SIZE –     Keep : DB_KEEP_CACHE_SIZE –     Recycle: DB_RECYCLE_CACHE_SIZE And also I know LRU and

INI_TRANS for TABLE and INDEX

Hi, My DB version is 10.2.0.3 As by default the INI_TRANS of tables is 1 and for indexes is 2. For some reasons I have to increase the INI_TRANS of a table to 5, then what value do I set for INI_TRANS of indexes on that table? I have read somewhere t

Question about advance compression in Oracle 11gR2

Hi, I am on Oracle 11gR2 on Solaris 10. I want to run the oracle advance compression advisor for my database and get compression ratios for the tables, how can I do it? I am looking for a sample command to run this advisor package (dbms_compression)

Any general tips on getting better performance out of multi table insert?

I have been struggling with coding a multi table insert which is the first time I ever use one and my Oracle skills are pretty poor in general so now that the query is built and works fine I am sad to see its quite slow. I have checked numerous artic

Statistics Problem ?

Hi, I have an insert statment that is waiting most of the time on P.K index. 82661 was inserted , but explain plan bellow shows that only : 6396 rows was fetched. 1. Does i have statistics problem ? 2. What would you suggest to do in case of waiting

What are the best solutions for data warehouse configuration in 10gR2

I need help on solutions to be provided to my Client for upgrading the data warehouse. Current Configuration: Oracle database 9.2.0.8. This database contains the data warehouse and one more data mart on the same host.Sizes are respectively 6 Terabyte

FREELIST TUNING(FREELIST의 CONTENTION 확인 방법)

제품 : ORACLE SERVER 작성날짜 : 2002-05-15 Freelist Tuning(Freelist의 contention 확인 방법) ================================================== PURPOSE Freelist Tuning은 Process Freelist 와 Freelist Group의 contention을 최소화하기 위한 것이다. Explanation 1. Freelist의 종류(Bul.

DB running slow...

I have a Oracle 11.2.0.3 system that is running on RHEL with 12 CPU and 6 Cores on 48GB of ram. We are in the process of upgrading our ERP system and are testing some workload of the system during upgrade process. I've noticed that there is a lot of

Database hang

Dear all, Recently we do a test. 800 users update the same table but different rows simultaneously. Our database was hung,we had to wait a long time to connect to database using "sqlplus". The statspack report is as below.Could you provide us so