database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Tutorials 11938 게시물 읽기
 News | Q&A | Columns | Tutorials | Devel | Files | Links
No. 11938
DB_BLOCK_BUFFERS
작성자
정재익(advance)
작성일
2002-09-07 21:14
조회수
8,327

DB_BLOCK_BUFFERS

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

 

This parameter is available in all versions of Oracle, and is measured in Oracle blocks. The value of this parameter is very important for storing data into memory as users are requesting information from the system.

DB_Block_Buffers are the number of buffers in the SGA cache that will be available for user data to be stored in memory. The size of the buffer cache, which is used to cache db blocks in the SGA, is specified by this parameter. Because the data is cached, this reduces the amount of physical I/O.

 

In turn, the setting of this parameter has a large effect on the buffer cache hit ratio, which you generally want to be above 90%. The hit ratio can be dynamically determined based on the following query:

select round(((1-(sum(decode(name, 
        'physical reads', value,0))/ 
        (sum(decode(name, 'db block gets', value,0))+ 
        (sum(decode(name, 'consistent gets', value, 0))))))*100),2)  
        || '%' "Buffer Cache Hit Ratio" 
        from v$sysstat; 

The result of this query would be similar to the following: 

        Buffer Cache Hit Ratio: 
97.63% 

 

If the hit ratio is below 90%, then it would be advisable to increase

DB_Block_Buffers until you obtain a hit ration above 90%. The implications of the DB_Block_Buffers being set too low are that the least recently used data will be flushed from memory. So guess what? If another query needs this data, back out to disk we go. This will cause I/O and CPU resources to be used. If the value is set too high, swapping will began to occur on the OS and the system may come to a halt.

 

See <Note.62172.1> for further information on Tuning the Database Buffer Cache.

[Top]
No.
제목
작성자
작성일
조회
11941WEB상에서 PLSQL로 EXCEL FILE만드는 예제
정재익
2002-09-07
8388
11940Reference Note for Init.Ora Parameter "AUDIT_TRAIL
정재익
2002-09-07
7146
11939SHARED_POOL_SIZE
정재익
2002-09-07
10753
11938DB_BLOCK_BUFFERS
정재익
2002-09-07
8327
11937INDEX 생성 시 고려 사항.
정재익
2002-09-07
9925
11936DATE TYPE 데이타를 효과적으로 조회하는 방법
정재익
2002-09-07
11998
11935DBMS_JOB PACKAGE의 사용 방법과 예제
정재익
2002-09-07
10591
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.047초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다