RC: Buffer Cache Hit Ratio 감소 관련 (Doc ID 2875289.1)

Last updated on SEPTEMBER 18, 2023

Applies to:

Symptoms

On : NA version, DATABASE

ACTUAL BEHAVIOR
---------------
buffer cache hit ratio 감소 관련

서버 재기동 이후 버퍼캐시 히트율이 굉장히 낮은 상태로 유지되고 있어 원인분석 부탁드립니다.

아래와 같이, awr 상으로는 buffer cache hit ratio 가 낮지 않음.

[ awrrpt_27_19~28_08.html ]

WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst num Startup Time Release RAC
ABTKG 2582152507 ABTKG1 1 24-May-22 22:05 11.2.0.4.0 YES <<-- 5월24일 22:05 재기동!

......
Instance Efficiency Percentages (Target 100%)

Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.98 In-memory Sort %: 100.00 <<<<<<<<<<<<<<-- Buffer Hit % 99.98% 임!
Library Hit %: 102.74 Soft Parse %: 99.91
Execute to Parse %: 0.15 Latch Hit %: 99.93
Parse CPU to Parse Elapsd %: 86.04 % Non-Parse CPU: 93.27

......
Memory Statistics

Begin End
Host Mem (MB): 262,086.1 262,086.1
SGA use (MB): 65,536.0 65,536.0
PGA use (MB): 2,438.3 2,429.0
% Host Mem used for SGA+PGA: 25.94 25.93
Cache Sizes

그러나 다음의 스크립트를 수행시에는 낮게 나옴.

++ SCRIPT: Calculating Buffer Cache Hit Ratio without Inputing Parameters (Doc ID 1039290.6)
->
SQL> @buf_check.sql

PL/SQL procedure successfully completed.

SQL> select global_name, :r "Hit Ratio" from global_name ;

GLOBAL_NAME Hit Ratio
-------------------- ----------
ABTKG .305207461

v$sysstat 과 v$buffer_pool_statistics 을 이용한 buffer cache hit 율에 차이가 발생.

감사합니다.

EXPECTED BEHAVIOR
-----------------------
.

STEPS
-----------------------
.

BUSINESS IMPACT
-----------------------
.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.