[RDS ORACLE] auto optimizer stats collection의 영향

 

 

Oracle RDS를 사용하는 개발자분한테 7시 경에 서비스가 느리다고 연락이 왔음.

 

사내 RDS 모니터링 툴로 확인해본 결과

Hard Parsing이 7시경에 굉장히 많이 이뤄지고 있는 것을 확인함.

근데 일반 DB에 있는 쿼리들에서 하드파싱이 자주 일어나는 것은 어렵다. 

왜냐면 DW성이 아닌 이상 동일한 쿼리를 반복해서 수행하고 (소프트파싱)

상수 아닌 변수를 사용하기 때문. (변수를 사용하면 동일한 쿼리로 인식한다.)

 

7-8시 경에 모니터링 해봤더니 하드파싱되는 쿼리들이

통계정보 갱신과  관련된 내용이였음.

 

 

-- 하드파싱 쿼리 점검

(1시간 이내, 서비스가 느려지는 시점 전후로 사용하면 좋다.)

select substr(sql_text, 1, 30) "SQL",
       count(*) cnt, --파싱건수
       sum(executions) "TotExecs",
       sum(sharable_mem) mem,    --공유메모리사용량
       min(first_load_time) start_time,
       max(first_load_time) end_time,
       max(sql_text) full_sql,
       max(hash_value) hash
from v$sqlarea
where FIRST_LOAD_TIME between TO_CHAR(SYSDATE-24/24,'YYYY-MM-DD/HH24:MI:SS') and TO_CHAR(SYSDATE,'YYYY-MM-DD/HH24:MI:SS')
group by substr(sql_text, 1, 30)
--having count(*) > 30    -- 비슷한 문장이 30개 이상.
order by 4 desc;

 

 

그 결과 dbms_stats 관련된 쿼리가 4개정도 300번씩 수행됨을 확인하였다.

 

select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact  use_weak name_resl dynamic_sampling(0) 어쩌구 저쩌구..... 

 

 

-- 통계정보를 확인해본 결과 많은 테이블들이

7시에 통계정보가 갱신되는 것을 확인하였음.

select owner, table_name, num_rows, sample_size, last_analyzed from dba_tables;

 

 

--auto task를 확인해본 결과 모든 것이 다 enable로 되어있었다. 

select * from dba_autotask_client;

 

 

+ 그래서 들었던 생각

아! 7시경에 테이블들의 통계정보가 수집되면서

해당 서비스와 연결된 테이블들의 쿼리들이

새로 하드파싱이 되는구나! 라고 생각하였음.

 

아무리 그래도 통계정보를 갱신하면서 읽어들이는 쿼리들이

하드파싱이 될 거라고 생각은 안했다;

오라클에서 수집하는건데

어련히 알아서 잘 서비스에 무리안가게 수집하겠지!

 

-- _optimzer_invalidation_period 파라미터

: 예전에 10g에서 사용하던 파라미터인데, default 값이 18000이고 (5시간),

해당 초 시간이 지나기 전까지는 통계정보가 갱신되더라도 이미 파싱되어있는 실행계획이 틀어지지 않는다.

실행계획이 달라지더라도 5시간 이후에 변경이 되는 것을 의미함.

* 여담으로 다른 고객사의 경우 해당 시간 지나고 나서 Buffer Cache Hit Ratio가 뚝 떨어져서 뭐지? 하고 봤더니 하드파싱이 다 이루어져서 해당 값이 쭉 내려갔다고 한다. 

 

 

-- 해당 파라미터 검색 

show parameter _optimzer_invalidation_period 
show parameter optimizer
select name, value from v$parameter where name like '%optimizer%';

 

 

뭘해도 안나오길래,

현재 해당 DB가 19c라 이미 사라진 파라미터인가 라고 생각했다.

 

또, 예전 버전의 파라미터 값을 현재 버전에 적용시키게 되면

startup 헀을 때 obsolete parameter 값? 이라고 메세지가 떨어졌던 것을 기억했기 떄문에

alert로그를 확인해봤지만 보이지 않았다.

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

 

 

--히든 파라미터 확인

set linesize 150
set pagesize 50

col ksppinm format a50
col ksppstvl format a30

select ksppinm, ksppstvl 
from sys.x$ksppi x, x$ksppcv y
where x.indx = y.indx
and x.ksppinm in ('파라미터네임');

 

: 또 해당 테이블의 경우,, sys 권한이 필요하므로 rdsadmin의 경우 패키지를 사용하여 검색이 필요함

 

 

--히든 파라미터 확인(RDS) 

set serveroutput on 
set lines 200

prompt Enter a value for parameter
DECLARE
	intval BINARY_INTEGER;
	strval VARCHAR2(256);
	partyp BINARY_INTEGER;
	intval2 VARCHAR2(12);

BEGIN
	dbms_output.put_line(chr(13));
	dbms_output.put_line('+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-');

	partyp := dbms_utility.get_parameter_value('&1',intval, strval);
	
	IF partyp = 1 THEN
		dbms_output.put('parameter value is: ');
		dbms_output.put_line(strval);
		dbms_output.put_line('parameter type is: STRING/FILE parameter');
		dbms_output.put('parameter value length is: ');
		dbms_output.put_line(intval);
	ELSE
		dbms_output.put('parameter value is: ');

		IF intval=0 THEN intval2:='FALSE';
			dbms_output.put_line(intval||'/'||intval2);
			dbms_output.put_line('parameter type is: INTEGER/BOOLEAN parameter');
		ELSIF intval=1 THEN intval2:='TRUE';
			dbms_output.put_line(intval||'/'||intval2);
			dbms_output.put_line('parameter type is: INTEGER/BOOLEAN parameter');
		ELSE 
			dbms_output.put_line(intval);
			dbms_output.put_line('parameter type is: INTEGER/BOOLEAN parameter');
		END IF;

	END IF;
	dbms_output.put_line('+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-');
	END;
/

from 필유아사님.

 

 

해당 히든파라미터로 검색하니,

 _optimzer_invalidation_period 파라미터값이 18000(5시간) 으로 설정됨을 확인.

 

이는 통계정보가 갱신되도 7시가 아닌

12시에 하드파싱이 이뤄질 수 있음.

 

결국 통계정보 갱신되서 서비스에 이용되는

테이블들떄문에 해당 문제가 일어나는 것이 아닌,

통계정보를 갱신하기 위해 사용되는

system 단의 쿼리들이 문제 였음을 확인 하였다.

 

또 CPU, READ IOPS(read I/O per seconds) 수치를  봐도

해당시간에 팍 튀는 것을 확인.

정황상 이건 자동통계정보 수집 때문에 그런 것이다

추리가 딱딱 떨어지는 중...!!!!!

 

결국 dba_autotask_client에 있는 3가지 내용을 모두 disable로 변경시키기로 결정하였음.

자동 통계정보 수집은 막고, 통계정보 수집이 필요 할 때 수동으로 수행하기로 결정.

적용 이후에 어떻게 될지는 추가적으로 덧붙여 봐야지...

 

여러모로 DB하나 잘 운영하려면 SQL Tunning 포인트도 잘 알아야하고,,

여러가지 파라미터들도 알아야 하는구나 몸소 깨달은 날.

 

 

 

 

_____________________________

24.11.06 내용 추가

 

disable로 변경 및 7시에 통계정보 관련해서 돌지 않음에도 불구하고,,

하드파싱 및 cpu 다 7시경에 증가함을 확인하였음....

대체 뭘까...