2024년 2월 2일 5교시

for mancie ♡

 

 

ORA-04031: 프리 공간은 있지만 우리가 사용할 만큼의 프리 공간은 없는 경우에 발생하는 오류

이럴 때는 메모리 공간을 flush 하는 것말고는 해결할 방법이 없다.

 

★ 메모리 flush

Shared pool 안에 있는 내용 깨끗이 지우기

 

SYS@ora11g> alter system flush shared_pool;

System altered.

 

SYS@ora11g> alter system flush shared_pool;

System altered.

 

한 번으로는 안되고 두 번은 꼭 해야 한다.

 

(회사가 나를 열받게 할 때 낮 시간에 과감하게 수행하면 좋음 ♡

한 번만 하면 안되고 꼭 두 번 해주기!!!!!! ♡)

물론 농담 >.<

 

SYS@ora11g> select * from hr.employees where employee_id = 100;

100번 사원의 정보 조회하기:

flush 해서 ShP 안에 들어있는 내용이 모두 날아간 상태이므로

위의 select 문을 수행하는 순간 > 서버 프로세스는 커서를 만들어서 그 안에 저 select 문장을 집어넣고

parse 단계에서 문법 체크, 의미 분석, 권한 체크를 해야 한다.

그런데 의미 분석과 권한 체크에는 딕셔너리 정보가 필요한데 ShP 안의 내용이 싹 날아가 버려서

남아있는 딕셔너리 정보가 하나도 없다 > DBC 에 혹시 있는지 찾아보는데

> 만약 없다면: 디스크의 시스템 테이블스페이스로 하드 파싱 발생 > 딕셔너리 정보 DBC 로 올리기

> DBC 에 올라온 딕셔너리 정보 중에서 내가 필요로 하는 row 만 뽑아서 DDC 에 저장

> 의미 분석, 권한 체크

> 동일한 SQL 문장이 LC 안에 있는지 찾으러 가기

> 위 문장에 대한 해시밸류가 1이면: 해시밸류 1인 값에 대해서만 (래치를 잡고) 조회하면 된다.

 

(아래 접은 글은 칠판 그림 내용)

그럼 LCO 안에는 뭐가 있을까?

동일한 SQL 문장이 LC 안에 있는지 찾으러 갔을 때, LC 안에는 해시버킷 Hash Bucket 이 있고

(해시값이 담긴 양동이라는 건가...)

찾고 싶은 SQL 문을 해시밸류에 전달하면: 해시밸류값을 리턴받는데

만약 해시밸류값으로 1이 리턴되었다고 할 때

해시밸류 1에 가서  래치를 잡고 조회를 시작한다.

해시버킷 안의 해시밸류 1에는 그 1에 따라오는 LCO 들이 있는데

각각의 LCO 에는 핸들이 있고 name 이 있다.

(name 값 = 문패 = select 문장)

그래서 내가 지금 찾고자 하는 select 문장이 name, 즉 문패와 같은지 비교해보고

같으면: 그 안에 들어있는 실행계획을 가지고 소프트 파싱을 발생시키면 되지만

다르면: 그다음 LCO 로 넘어간다.

LCO 는 (칠판 그림에 따르면) 기차처럼 연결되어 있는데

첫번째 LCO 의 문패가 내가 가지고 있는 패와 같은지 살펴보고

다르면 > 다음 집(LCO) 으로 가서 문패를 살펴보고

또 다르면 > 또 그다음 집으로 가서 문패를 살펴보는 식

 

그런데 해시밸류 1 값에 따라오는 모든 LCO 를 다 찾아봤는데도 내가 찾는 SQL 문장과 같은 문장이 없으면?

하드 파싱이 발생한다.

하드 파싱이 발생하는 순간 래치 (latch shared pool) 를 잡고 ShP의 프리 공간을 찾아보는데

내가 필요로 하는 만큼의 공간이 있으면 > 공간 확보 > 래치 풀어주고 > 확보한 공간에 새로 LCO 를 생성한다.

 

LCO 를 생성하기 위해 또 래치를 (latch: library cache lock) exclusive 모드로 잡고

LCO 를 생성한 다음 > 새로 만든 그 LCO 에 library cache pin 을 exclusive 모드로 찍고

내가 가진 select 문장을 LCO 안에 등록시킨다.

 

(QUESTION. 그럼 래치를 잡고 LCO 생성한 다음에는 library cache lock 이 래치는 풀어주는 거지?)

(ANSWER. 그런거 없다. null 로 바꾸는 것뿐)

(QUESTION. 그리고 저 library cache lock 래치가 풀린 다음에 library cache pin 찍고 실행계획 만드는 거지?

래치 두개 동시에 거는 거야?)

(ANSWER. lock 안풀림, fetch 까지 다 끝나면 풀어주는 것)

 

LCO 안에는 여러가지 정보가 있는데

1. 종속관계에 대한 정보 (뷰와 테이블 / PLSQL 에서의 프로시저 & 함수와 테이블 등

    실제 select 문장에 대한 종속관계 정보) 가 있고

2. Child cursor (자식 커서): 실행 계획을 찾아주고, 저장해주고, 통계정보를 수집하는 커서

    유저 A 와 유저 B 가 모두 완벽히 똑같은 emp 테이블을 가지고 있을 때

    유저 A 와 B 둘 다 select * from emp where id = 100; 이라는 완벽히 똑같은 SQL 문장을 수행했다고 하자.

    둘 다 SQL 문장도 똑같고, 테이블도 똑같지만

    테이블의 소유자는 서로 다르다.

    이런 경우, A 와 B 가 던진 select 문장의 실행 계획을 가지고 있는 LCO 안에 자식 커서가 만들어지고

    그 자식 커서가 A를 위한 LCO와 B를 위한 LCO 와 그 안의 실행 계획을 따로따로 만들어준다.

    (그래서 결국은 원래부터 있었던 LCO, A를 위한 LCO, B를 위한 LCO

     이렇게 LCO 가 3개나 돌아가게 된다 - 똑같은 select 문장인데 LCO 3개 /

     실행 계획도 A 따로 B 따로 해서 2개가 만들어지므로 메모리 사용량이 나빠진다 + 버전 카운트가 높아진다.

     (버전 카운트: 자식 커서의 수 > 이 값이 클수록 ShP 안의 메모리 공간을 잘못 사용하고 있는 것))

LCO와 차일드 커서가 너무 많이 만들어졌을 때 그 원인을 분석하는 것도 DBA 의 수많은 할일들 중 하나이다.

3. 실행계획

... LCO 안에는 이것들 외에도 들어있는 것들이 수두룩빽빽이지만

간결하게 표현하면 이런 느낌 ↓

 

LCO

종속관계

(자식 커서)

실행계획

(QUESTION. 그럼 LCO 안에는 자식커서는 있을 수도 있고 없을 수도 있는 거지?)

(ANSWER. 그러하다.)

 

실행계획이 다 만들어지면 : library cache lock 과 library cache pin 을 shared 모드로 바꿔놓고

execute 단계로 넘어간다.

 

select * from hr.employees where employee_id = 100;

select 문장 한번 던져주고

 

 

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

v$sql 이라는 뷰를 통해 hr.employees 테이블을 참조하는 SQL 문장들 검색

 

parse_calls : 동일한 SQL 문이 ShP 안의 LC 안에 있는지 조회한 횟수 - 수행할 때마다 올라간다.

loads : 하드 파싱 발생 > 실행계획 생성 > 실행계획 공유 - 한 번만 만들어서 로드하면 재사용 가능

executions: 실행 횟수, parse_calls 와 executions 는 함께 증가

(오늘 하루 가장 많이 수행된 SQL 문을 찾을 때 유용하다)

 

(QUESTION. 그래서 저 loads 라는 게 정확히 뭐야? 이해가 잘 안돼...

그냥 실행계획을 만든 횟수야?)

(ANSWER. 그렇다.)

 

 

(퍼티 sys 창과 hr 창 2개 띄워놓고 동시에 작업하자.)

(21:10)

 

HR@ora11g> select * from hr.employees where employee_id = 100;

얘를 hr 계정에서 다시 조회하고

 

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

얘를 확인해보면

 

SQL_ID: SQL 문장의 고유 식별자 - sys 에서 수행했든 hr 에서 수행했든 수행한 문장 자체는 똑같으므로

두 문장의 SQL_ID 는 똑같다.

SQL_TEXT: 수행한 SQL 문장의 내용

 

 

다시 hr 계정에서

 

HR@ora11g> select * from hr.employees where employee_id = 101;

HR@ora11g> select * from hr.employees where employee_id = 101;

HR@ora11g> select * from hr.employees where employee_id = 102;

HR@ora11g> select * from hr.employees where employee_id = 103;

HR@ora11g> select * from hr.employees where employee_id = 104;

HR@ora11g> select * from hr.employees where employee_id = 105;

HR@ora11g> select * from hr.employees where employee_id = 106;

 

위의 문장들을 하나씩 다시 던진 다음

 

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

다시 조회해보면: loads 값이 모두 1 - 문장 하나마다 하드 파싱이 한 번씩 발생했음을 알 수 있다.

 

 

그럼 실행계획을 한 번 찾아보자.

 

sys 창에서

select * from table(dbms_xplan.display_cursor('7gg56zaty5br6'));

 

위 쿼리의 결과문에서 아무거나 하나 SQL_ID 뽑아서 괄호 안 작은따옴표에 붙여넣고 수행

(select * from hr.employees where employee_id = 101; 이라는 SQL 문의 실행계획 확인)

 

 

3행: 위에서 선택한 SQL_ID 에 해당하는 쿼리 문장

이 쿼리 문장의 실행계획이 아래에 표처럼 나와있는데

 

10행부터 13행까지 보이는 행들 중에서

들여쓰기가 가장 안으로 들어간 행 (13행) 부터 해석하면 된다.

 

13행 index unique scan :

employees 테이블에 가서 101번 사원의 정보를 가져오기 위해 INDEX UNIQUE SCAN 으로 실행계획을 만들었다는 뜻

101번 사원의 아이디를 가져오기 위해 emp_emp_id_pk 라는 인덱스에 가서 로아이디 찾고

(인덱스가 로아이디를 대신 가지고 있으므로)

→ 12행 TABLE ACCESS BY INDEX ROWID:

찾아낸 로아이디를 이용해서 employees 테이블에서 103번 행이 어디 있는지를 알아낸 다음

테이블에 액세스 > 끝

 

그리고 10행부터 13행까지 사이에 * 이 찍혀 있는 행은 아래 (19행부터 21행 부분) 에 주석을 달아주는데

21행 access : 로아이디를 알고 그걸로 찾아갔다는 뜻

19행 filter : employees 테이블 안에 있는 데이터를 모두 체에 붓고 101번인지 아닌지 거르면서 찾아봤다는 뜻,

값이 어디에 있는지 모를 때 filter 한다. (풀 테이블 스캔이 발생한 것)

 

(QUESTION. 뭐야...? 아까는 로아이디 알고 그걸로 찾아갔다며 ㅠ.ㅠ

근데 왜 갑자기 또 filter 야?)

(ANSWER. 경우에 따라 다를 수 있다.)

 

다른 실행계획도 한번 보자.

select * from table(dbms_xplan.display_cursor('7gg56zaty5br6'));

다른 SQL_ID 를 골라 실행계획을 보면:

 

실행 계획의 그림이 똑같다.

(실행계획 자체는 똑같은데 상수처리 부분 때문에 공유를 하지 못하고 하드 파싱이 발생한 것 - 너무 비효율적이다.)

 

 

그래서, SQL 문장들 중 동일한 실행계획을 사용하고 있는 것들을 찾을 수 있는데

 

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

 

SELECT 문 맨 끝부분에 plan_hash_value 컬럼을 추가해서 조회해보면

> 컬럼값이 모두 동일한 것이 보인다 = 실행계획이 모두 동일하다는 뜻

 

 

 

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%';

 

이번에는 hash_value 컬럼을 새로 추가 > 값이 모두 다르다 ↓

이건 실행 계획은 똑같은데 LCO 가 모두 다른 곳에 만들어져 있다는 뜻

(hash_value 는 해시버킷 번호라고 생각하면 된다)

plan_hash_value (실행계획) 는 같은데 hash_value (해시버킷 번호) 는 다른 이런 것들도

검거해서 고쳐놓아야 한다.

 

 

select plan_hash_value, count(hash_value)
from v$sql
group by plan_hash_value
having count(hash_value) >= 10
order by 2 desc;

 

실행계획이 같은데, 그 실행계획을 공유하지 못하고 있는 SQL 문들 검거하기:

실행계획은 똑같은데 LCO 는 8개, 8개, 7개, ... 만들어져 있는 SQL 문들이 총 44개씩이나 있다.

(원래는 실행계획이 똑같은 SQL 문장 하나당 LCO는 하나씩만 만들어지는 것이 가장 좋다)

 

 

select sql_id, sql_text
from v$sql
where plan_hash_value = 2890534904;

 

실행계획이 같은데 LCO 는 여러 개 만들어져 있는 SQL 문장 찾기

(위에 나온 plan_hash_value 값 중 아무거나 하나 복사해서 붙여넣고 SQL_ID 와 SQL 문 내용 보기)

실행계획을 공유하지 못하는 SQL 문들은 변수처리해야 한다.

 

 

변수처리하기

 

var b_id number

바인드 변수 선언

 

execute :b_id := 100

새로 선언한 바인드 변수에 100이라는 값 할당

 

select * from hr.employees where employee_id = :b_id;

employee_id 값에 바인드 변수 할당

 

수행하면: 프로시저가 성공적으로 완료되었다는 메시지가 뜬다.

 

 

HR@ora11g> execute :b_id := 101;

이번에는 바인드 변수에 101값 넣어보기

 

HR@ora11g> select * from hr.employees where employee_id = :b_id;

 

 

변수 처리를 한 다음 문장을 처음 수행했으므로 하드 파싱이 발생할 수밖에 없다.

(parse_calls, loads, executions 모두 1씩 증가)

그리고 그 바인드 변수값에 100, 101 넣어서 수행하면 > 그 때는 소프트 파싱이 발생하므로

parse_calls 와 executions 는 값이 증가하지만 loads 는 1인 채로 가만히 있다.

 

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%';

 

그래서 수행해보면:

엥... 나 저 변수처리한 SQL 문장 parse_calls 랑 executions 값 왜 다 1이야...

분명히 2번 수행했고 위에서 인증샷도 남겼는데...

 

또 버그인가? 어휴