목차
buffer busy waits等待事件
buffer busy waits
Solutions
Waits on Data blocks
Waits on Segment Header blocks
Expanded Definition
데이터 베이스 MySQL 튜토리얼 oracle 重要的等待事件

oracle 重要的等待事件

Jun 07, 2016 pm 03:50 PM
buffer busy oracle w 이벤트 기다리다

buffer busy waits等待事件 orcale的等待事件列表可以从v$event_name中查看完整的等待事件列表 buffer busy waits Wait occurs when a session attempts to access a block in memory, is denied and must wait until the buffer becomes available. This ev

buffer busy waits等待事件




orcale的等待事件列表可以从v$event_name中查看完整的等待事件列表

buffer busy waits

Wait occurs when a session attempts to access a block in memory, is denied and must wait until the buffer becomes available. This event happens because a buffer is either being read into the buffer cache by another session (and the session is waiting for that read to complete) or the buffer is in the buffer cache, but in a incompatible mode (that is, some other session is changing the buffer).

--当一个会话需要读取一个数据块,但这个数据块正在被另一个会话读取到内存中时。

--当一个会话视图修改一个数据块,但这个数据块正在被另一个会话修改时。

Solutions

Buffer busy waits often occur in the following cases:

  • Inefficient SQL statements read more blocks than necessary. If there are many sessions running these statements, they will attempt to read the same blocks and possibly wait on this event.
  • If the FREELISTS parameter for a table is too low, multiple sessions that are attempting to insert rows in the same table and end up waiting for freelists. This problem shows up as contention for the segment header of the table.
  • Multiple sessions are attempting to change an index block (possibly do to an insert).
  • The INITRANS parameters is too low for a segment. Any DML operation that needs to go into the block needs to lock an Interested Transaction List (ITL). If INITRANS parameter is set too low, then there will be less number of ITLs allocated originally. Oracle can only allocate more ITLs if there is space in PCTFREE area of the block. If, however, there is no space to increase the ITL, the transactions that cannot lock ITLs will have to wait until the previous transactions have completed operations on the block. The waiter will register ‘buffer busy wait’ for the block.

 

find the table or indexes waited for. Once the database object is known, consider the following causes of contention and their solutions.

Waits on Data blocks

Data blocks are the blocks that actually hold the row data in a table or index.

 

Problem: Multiple sessions are requesting a block that is either not in cache or in an incompatible mode.

 

Solution 1: Tune inefficient queries. Inefficient queries read too many blocks into the buffer cache. These queries could flush out blocks that may be useful for other sessions in the buffer cache. By tuning queries, the number of blocks that need to be read into the cache is reduced, reducing aging out of the existing “good” blocks in the cache.

 

Solution 2: Delete some of the hot rows and insert them back into the table. Most of the time, the rows will be place in a different block. The DBA may need to adjust pctfree and/or pctused to ensure the rows are placed into a different block.

 

Solution 3: Cache the table or keep the table in the KEEP POOL. When multiple sessions are requesting the blocks that reside in the disk, it takes too much time for a session to read it into the buffer cache. Other session(s) that need the same block will register ‘buffer busy wait’. If the block is already in buffer cache, however, this possibility is eliminated. Another alternative is to increase the buffer cache size. A larger buffer cache means less I/O from disk. This reduces situations where one session is reading a block from the disk subsystem and other sessions are waiting for the block.

 

Solution 4: Look for ways to reduce the number of low cardinality indexes. A low cardinality index is an index on a column(s) with a relatively low number of unique values such as a U. S. state column that has only fifty unique values. Low cardinality indexes could result in excessive block reads. Concurrent DML operations on low cardinality columns could also cause contention on a few index blocks.

Waits on Segment Header blocks

Each segment has one segment header block.

 

Problem: At times, this block can be a point of contention which manifests itself as a buffer busy wait. This occurs especially when multiple sessions are attempting to insert into or delete from the same table.

 

Solution 1: When sessions insert rows into a block, the block must be taken out of the freelist if the PCTFREE threshold reached. When sessions delete rows from a block, the block will be put back in the freelist if PCTUSED threshold is reached. If there are a lot of blocks coming out of the freelist or going into it, all those sessions have to make that update in the freelist map in the segment header. This can cause contention for the segment header block which manifests itself as ‘buffer busy wait’. One solution to this problem is to create multiple freelists. This will allow different insert streams to use different freelists and thus update different freelist maps. This reduces contention on the segment header block. You should also look into optimizing the PCTUSED/PCTFREE parameters so that the blocks don’t go in and out of the freelists frequently.

 

Solution 2: Increase the size of the extents. If extents are too small, Oracle must constantly allocate new extents causing contention in the extent map

 

Solution 3: Increase the undocumented database parameter, _bump_highwater_mark_count, from the default of 5. Updating the high water mark on the table can become a bottleneck.

 

 

Expanded Definition

Wait until a buffer becomes available. This event happens because a buffer is either being read into the buffer cache by another session (and the session is waiting for that read to complete) or the buffer is in the buffer cache, but in a incompatible mode (that is, some other session is changing the buffer).

본 웹사이트의 성명
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.

뜨거운 기사 태그

메모장++7.3.1

메모장++7.3.1

사용하기 쉬운 무료 코드 편집기

SublimeText3 중국어 버전

SublimeText3 중국어 버전

중국어 버전, 사용하기 매우 쉽습니다.

스튜디오 13.0.1 보내기

스튜디오 13.0.1 보내기

강력한 PHP 통합 개발 환경

드림위버 CS6

드림위버 CS6

시각적 웹 개발 도구

SublimeText3 Mac 버전

SublimeText3 Mac 버전

신 수준의 코드 편집 소프트웨어(SublimeText3)

오라클에서 두 날짜 사이의 일수를 계산하는 함수 오라클에서 두 날짜 사이의 일수를 계산하는 함수 May 08, 2024 pm 07:45 PM

오라클에서 두 날짜 사이의 일수를 계산하는 함수

Oracle 데이터베이스 로그는 얼마나 오래 보관됩니까? Oracle 데이터베이스 로그는 얼마나 오래 보관됩니까? May 10, 2024 am 03:27 AM

Oracle 데이터베이스 로그는 얼마나 오래 보관됩니까?

Oracle 데이터베이스 시작 단계의 순서는 다음과 같습니다. Oracle 데이터베이스 시작 단계의 순서는 다음과 같습니다. May 10, 2024 am 01:48 AM

Oracle 데이터베이스 시작 단계의 순서는 다음과 같습니다.

오라클에서 간격을 사용하는 방법 오라클에서 간격을 사용하는 방법 May 08, 2024 pm 07:54 PM

오라클에서 간격을 사용하는 방법

오라클에 두 문자열이 포함되어 있는지 확인하는 방법 오라클에 두 문자열이 포함되어 있는지 확인하는 방법 May 08, 2024 pm 07:00 PM

오라클에 두 문자열이 포함되어 있는지 확인하는 방법

오라클에는 얼마나 많은 메모리가 필요합니까? 오라클에는 얼마나 많은 메모리가 필요합니까? May 10, 2024 am 04:12 AM

오라클에는 얼마나 많은 메모리가 필요합니까?

Oracle에서 특정 문자의 발생 횟수를 확인하는 방법 Oracle에서 특정 문자의 발생 횟수를 확인하는 방법 May 09, 2024 pm 09:33 PM

Oracle에서 특정 문자의 발생 횟수를 확인하는 방법

Oracle 데이터베이스 서버 하드웨어 구성 요구 사항 Oracle 데이터베이스 서버 하드웨어 구성 요구 사항 May 10, 2024 am 04:00 AM

Oracle 데이터베이스 서버 하드웨어 구성 요구 사항

See all articles