2024년 2월 2일 6교시 실행계획 무효화

for mancie ♡

 

 

쉐어드 풀의 메모리 플러시

SYS@ora11g> alter system flush shared_pool;
System altered.

 

플러시한 다음 employees 테이블을 참조하는 SQL 문을 찾는 쿼리문을 다시 던져보면:

(쉐어드 풀 안에 캐시된 SQL 문장들이 깨끗하게 씻겨 내려갔기 때문에) 결과가 하나도 안나옴

 

select sql_id, sql_text, parse_calls, loads, executions, plan_hash_value, hash_value
from v$sql
where sql_text
like '%hr.employees%'
and sql_text
not like '%v$sql%';

 

 

샘플 테이블 만들기

SYS@ora11g> drop table hr.emp purge;

hr.emp 라는 테이블이 있다면 먼저 지우고

 

SYS@ora11g> create table hr.emp as select * from hr.employees;
테이블 만들고

 

또 메모리 플러시 두 번

SYS@ora11g> alter system flush shared_pool;

 

/

 

100번 사원의 정보 조회하고

SYS@ora11g> select last_name, salary from hr.emp where employee_id = 100;

LAST_NAME                     SALARY
------------------------- ----------
King                           24000

 

hr.emp 테이블을 참조하는 아래의 쿼리문을 다시 수행해보면

select sql_id, sql_text, parse_calls, loads, executions, plan_hash_value, hash_value
from v$sql
where sql_text
like '%hr.emp%'
and sql_text
not like '%v$sql%';

 

parse_calls: sql 문장을 수행했을 때 하드 파싱이든 소프트 파싱이든, 파싱이 발생한 횟수

(문장을 수행할 때마다 컬럼값이 1씩 증가)

load: 한 번 최초로 하드파싱이 발생한 이후에는

커서가 실행계획을 공유하므로 더이상 하드파싱이 발생하지 않아서 계속 1

 

(QUESTION. 여기서 말하는 '커서' 란 뭐야? 서버 프로세스 안에 있는 커서 말이야?

그 SQL 문 처리 영역?)

(ANSWER. )

 

execution: SQL 문 수행 횟수

 

 

select sql_id, sql_text, parse_calls, loads, executions, invalidations
from v$sql
where sql_text
like '%hr.emp%'
and sql_text
not like '%v$sql%';

 

invalidations 이라는 새로운 컬럼 추가: 얘는  커서가 무효화된 횟수를 나타낸다.

(무효화되지 않아야 좋은 것)

 

(QUESTION. 그러니까 커서가 무효화됐다는 건 실행계획이 없어졌다는 걸 말하는 거지?

근데 저 커서는 대체 뭐하는 놈이길래 커서가 무효화되면 실행계획도 같이 사라진다는 거야?

커서 = LCO 인가?)

 

 

select * from table(dbms_xplan.display_cursor('8qhp0dmv9q94d'));

select last_name, salary from hr.emp where employee_id = 100; 이라는 SQL 문의 실행계획 보기

 

11행 : 100번 사원의 정보를 처리하기 위해 풀 테이블 스캔함 = 17행 filter

그리고 21행에 보면 dynamic sampling used for this statement (level=2) 라고 되어있는데

dynamic sampling 은 저 select 문장의 실행계획을 만들려고 통계정보를 바라보았는데

통계정보가 없어서 > 지가 직접 동적으로 표본을 추출해서 통계정보를 만들었다는 뜻

(블록 몇 개만 표본추출해서 테이블의 row 수를 추측하고 그걸 가지고 통계정보를 만든 것 = 다이나믹 샘플링)

 

 

select num_rows, blocks, avg_row_len from dba_tables where owner='HR' and table_name='EMP';

통계정보 보기: 아무 값도 안 나옴

그런데 통계정보가 없으면 실행계획 못 만들고

실행계획을 못 만들면 SQL 문을 수행할 수가 없다.

 

그래서 동적인 샘플링을 통해 표본을 추출해서 통계정보를 만든다.

그런데 그렇게 통계정보를 만들었으면

저장해주면 좋을 텐데 저장을 안하고 purge 하기 때문에

테이블을 바라볼 때마다 동적 샘플링을 해야 해서 성능이 떨어진다.

(그래서 통계정보가 없는 것들도 나중에 다 하나하나 찾아서 gathering 작업해야 한다)

 

num_rows : 테이블 안에 있는 row 의 수

blocks 는 emp 테이블이 사용하고 있는 블록 수

avg_row_len: 한 행의 평균 바이트 값

(한 행이 어느 정도 바이트 값을 가지느냐에 따라 조인의 방법이 달라지는데,

avg_row_len 은 조인의 방법을 결정하기 위해 필요하다)

 

 

★ 실행계획이 무효화되는 경우 1. 통계 수집

 

SYS@ora11g> execute dbms_stats.gather_table_stats('hr','emp',no_invalidate=>false)

 

첫번째 인수값은 테이블의 소유자, 두번째 인수 값은 테이블 이름 (이 두 개의 인수는 필수)

그리고 세번째 인수로는 no_validate 를 넣으려고 하는데.

 

통계 수집을 하는 순간 emp 테이블에 관련된 LCO 는 다 찾아야 한다.

LCO 를 모두 찾았으면 라이브러리 캐시 핀을 exclusive 모드로 찍고

안에 있는 실행계획을 모두 무효화(invalidate)시켜야 하는데

(통계 수집을 새로 했고 + 그 새 정보에 맞춰 실행계획도 새로 만들어야 하므로)

 

무효화를 위해 LCO 에 락을 걸고 핀을 찍는 행위 때문에

wait event 가 발생하면 성능이 또 떨어질 수 있다.

 

그래서 invalidate 의 기본값은 안하는 것

no_invalidate=>true 로 해놓으면 무효화를 지금 당장은 하지 않겠다는 뜻이 된다.

 

(하지만 우리는 실습을 해야 하므로  no_invalidate=>false 로 지정)

 

퍼티 창에서 수행하고

(수행하면 hr 이 가진 emp 테이블에 대해 새로 통계정보 수집하고 + 원래 있던 실행계획 무효화도 같이 진행하게 된다)

 

select num_rows, blocks, avg_row_len from dba_tables where owner='HR' and table_name='EMP';

다시 (새) 통계정보 보기:

테이블의 행 수는 107건, emp 테이블이 사용하고 있는 블록은 5개, 한 행의 평균 바이트 값은 69라고 나온다.

 

 

select sql_id, sql_text, parse_calls, loads, executions, invalidations
from v$sql
where sql_text
like '%hr.emp%'
and sql_text
not like '%v$sql%';

 

그리고 위의 쿼리문을 수행해보면: 무효화 1건이 찍혀서 나온다.

무효화하면 현재 LCO 들은 모두 invalidation 으로 떨어진다.

 

 

다시

select last_name, salary from hr.emp where employee_id = 100

이 쿼리 문장을 hr 쪽에서 수행해보면:

얘는 실행계획이 무효화된 상태에서 SQL 문을 수행한 것이기 때문에 > 원래 있던 실행계획은 사용할 수가 없다.

실행계획을 다시 만들어야 하므로 loads 는 1 증가하게 된다.

 

(loads 가 큰 것들은 왜 중간중간에 실행계획이 invalidation 되었는지도 찾아내야 한다.)

 

 

select sql_id, sql_text, parse_calls, loads, executions, invalidations
from v$sql
where sql_text
like '%hr.emp%'
and sql_text
not like '%v$sql%';

 

진짜로 로드 값이 증가했는지 확인해보기: 1 증가한 것이 보인다.

 

 

select * from table(dbms_xplan.display_cursor('8qhp0dmv9q94d'));

다시 실행계획을 보면 : 풀 테이블 스캔했다고 나온다.

그리고 다이나믹 샘플링 정보가 없다.

(왜? 실행계획을 만들기 위한 통계정보를 위에서 수집해놓았기 때문에,

따로 통계정보를 추출할 필요가 없어서)

 

 

★ 실행계획이 무효화되는 경우 2. 테이블에 인덱스가 생성된 경우

 

테이블에 유니크 인덱스 (잘못된 순서로) 생성하기

SYS@ora11g> create unique index hr.emp_idx on hr.emp(employee_id);
Index created.

 

하지만 얘는 유니크 인덱스이지 프라이머리 키는 아니다.

그런데 테이블의 컬럼에 유니크 인덱스를 추가하고 나서 프라이머리 키 제약조건을 만들고 싶을 때

제약조건을 추가하려고 하는 순간 오류가 발생한다.

(왜? 프라이머리 키 제약조건을 걸면 컬럼에 유니크 인덱스가 생성되는데

제약조건을 걸려고 하는 컬럼에는 이미 유니크 인덱스가 걸려 있기 때문에.)

 

이럴 때는 이미 걸려 있는 저 유니크 인덱스를 그대로 쓰겠다고 지정해주어야 한다.

 

일단 프라이머리 키 제약조건을 한번 걸어보면

 

SYS@ora11g> alter table hr.emp add constraint emp_id_pk primary key(employee_id);
Table altered.

 

어... 근데 오류가 나야 정상인데 오류가 안나네...?

(선생님도 반친구들도 모두가 다같이 오류 안만남)

그냥 자동으로 원래 있던 인덱스를 쓴건가...?

 

원래는 

SYS@ora11g> alter table hr.emp add constraint emp_id_pk primary key(employee_id) using index hr.emp_idx;

이렇게 끝부분에 using index 절로 원래 있던 인덱스를 쓰겠다고 지정해주어야 하는데

이거 왜 되는 거야...

 

SELECT *
FROM user_constraints
WHERE table_name = 'EMP' and owner = 'HR';

 

테이블에 걸려 있는 제약조건 확인하기

어... 그런데 걸려 있는 제약조건이 아무것도 없다고 나온다...?

아까는 분명 Table altered 라며? 니가 방금전에 분명히 그렇게 말해놓고 한입으로 두말하는 거 뭔데 ㅡ.ㅡ

 

 

(깨ㅡ끗)

 

아무튼 이제 테이블에 인덱스가 걸려 있으므로

저 테이블과 관련되어 있는 LCO 안에 들어있는 실행계획은 몽땅 무효화되게 된다.

(아마 실행계획이 full table scan 방식에서 index scan 방식으로 바뀌기 때문이겠지...)

 

근데 아래의 쿼리문을 다시 돌려보면

제약조건은 안보여주면서 무효화 수는 또 1 증가되어 있다.

뭐하자는 거야?...

 

select sql_id, sql_text, parse_calls, loads, executions, invalidations
from v$sql
where sql_text
like '%hr.emp%'
and sql_text
not like '%v$sql%';

 

 

★ 실행계획이 무효화되는 경우 3. 테이블의 구조가 바뀌는 경우

 

desc hr.emp

hr.emp 테이블 구조 보기

 

지금 last_name이 varchar2(25) 유형으로 되어 있는데

쟤를 varchar2(30) 으로 바꿔 보자.

 

 

SYS@ora11g> alter table hr.emp modify last_name varchar2(30);
Table altered.

 

desc hr.emp

다시 테이블 구조 확인: last_name 컬럼 유형이 varchar2(30) 으로 바뀜.

분명히 바뀌어 있단 말이지? 근데

 

 

여기서

select sql_id, sql_text, parse_calls, loads, executions, invalidations
from v$sql
where sql_text
like '%hr.emp%'
and sql_text
not like '%v$sql%';

무효화가 발생했는지 확인해보면: 발생 안했다. 아까랑 똑같이 2

뭐야... 장난해?

 

 

select last_name, salary from hr.emp where employee_id = 100

다시 100번 사원 월급 조회하면

 

 

select sql_id, sql_text, parse_calls, loads, executions, invalidations
from v$sql
where sql_text
like '%hr.emp%'
and sql_text
not like '%v$sql%';

 

무효화 초기화됨(버그로 추정)

어휴... ㅡ.ㅡ

(깊은 한숨)

 

 

아무튼 결론: 통계 수집이나 테이블 구조 바꾸기, 테이블의 컬럼에 인덱스를 거는 작업은

매우 조심해서 해야 한다. (함부로 수행하면 실행계획이 모조리 사라지니까)