교육과정
SQL
PLSQL
Database Administration
Backup & Recovery
Performance Tuning
SQL Tuning
SGA(System Global Area)
모든 오라클 프로세스가 액세스하는 공유 메모리
show sga
Total System Global Area 713027608 bytes <- sga_max_size
Fixed Size 8900632 bytes <- 오라클 알고리즘이 수행되는 공간
Variable Size 641728512 bytes
Database Buffers 54525952 bytes <- data buffer cache
Redo Buffers 7872512 bytes <- redo log buffer- Variable Size
- shared_pool_size
- large_pool_size
- java_pool_size
- streams_pool_size
select * from v$sgainfo;
NAME BYTES RES CON_ID
--------------------------------------------- ---------- --- ----------
Fixed SGA Size 8900632 No 0
Redo Buffers 7872512 No 0
Buffer Cache Size 54525952 Yes 0
In-Memory Area Size 0 No 0
Shared Pool Size 301989888 Yes 0
Large Pool Size 8388608 Yes 0
Java Pool Size 4194304 Yes 0
Streams Pool Size 8388608 Yes 0
Shared IO Pool Size 16777216 Yes 0
Data Transfer Cache Size 0 Yes 0
Granule Size 4194304 No 0
Maximum SGA Size 713027608 No 0
Startup overhead in Shared Pool 169104992 No 0
Free SGA Memory Available 318767104 0Shared Pool
select count(*) from v$sgastat where pool='shared pool';
select *
from (select name,bytes
from v$sgastat
where pool = 'shared pool'
order by bytes desc)
where rownum <= 30;NAME BYTES
--------------------------------------------- ----------
SQLA 35879848
KGLH0 23629600
free memory 21845272
KGLS 16942368
ksunfy_meta 1 13015936
row cache mutex 9019032
SO private sga 7441712
PLMCD 7236760
private strands 7218176
KQR X PO 6748896
KGLHD 6366800
row cache hash 6012688
KGLSG 5260816
obj stats allocation chun 5160960
PLDIA 4986208
session 4985408
ASH buffers 4194304
kglsim hash table bkts 4194304
KTI-UNDO 4134000
KCB Table Scan Buffer 3981120
KSFD SGA I/O b 3977184
KSKQ master CG stats seg 3674680
ksipc state object 3447936
message pool freequeue 3051744
row cache mutex versions 3006344
KSRMA State Object 2587376
KKSSP 2428672
KGLDA 2418008
parameter table block 2338576
KTSL subheap 2097888library cache = SQL로 시작하는 components들
data dictionary cache = row cache로 시작하는 components들
실행계획
-
데이터 처리방법
1) rowid scan
- by user rowid
- by index rowid
2) full table scan -
조인방법
1) nested loop
2) sort merge
3) hash -
조인 순서
1) from절에 나열되어 있는 테이블의 갯수만큼 순서를 결정해야 한다.
2) 만약 조인 수행해야할 테이블의 갯수가 3개면 3! 경우수 만큼 결정
예) from a,b,c = (a,b,c),(a,c,b),(b,a,c),(b,c,a),(c,a,b),(c,b,a)
select문 처리단계
1. parse
1) 문법체크(syntax), 의미분석(semantic)체크, 권한(privilege)체크
- 문법체크는 server process가 가지고 있는 알고리즘으로 체크
- 의미분석, 권한 체크는 shared pool의 data dictionary cache에서 체크
2) soft parsing
- 동일한 SQL문이 shared pool 메모리안에 library cache에 있는지 조회
- 이유는 동일한 SQL문이 있으면 실행계획을 공유하기 위해서
library cache latch(mutex)를 잡고 검색해야 한다.latch : library cache(9i) wait event발생한다.library cache : mutex x(10g) wait event발생한다.
3) hard parsing
-
soft parsing이 실패 즉 동일한 SQL문이 shared pool 메모리안에 library cache에 존재하지 않습니다.
-
메모리 공간 확보
shared pool latch를 잡고 적절한 크기의 free chunk를 free list에서 찾는다.예)
free list안에 free chunk가 7k,10k,1k,100byte 이렇게 나있을 경우 내가 필요한 프리 공간은 8k 프리 공간이 필요할 경우 프리로 있는 10k 프리 공간을 8k로 확보하고, 남은 2k는 프리 공간으로 남겨 놓는다. 이제 남은 프리 청크는 7k, 2k, 1k, 100byte로 구성된다.그다음에 또 내가 필요한 프리 공간이 8k일 경우 , 즉 내가 필요한 프리 청크가 없을 경우 오류가 발생한다. ORA-04031 오류 발생.
오류를 해결할 수 있는 현재 유일한 방법은 shared_pool 공간을 flush 해줘야 한다.
-
프리청크를 찾기 위해서 latch를 잡아야하는데 못잡으면
latch : shared pool wait event발생한다. -
프리를 확보한 후 LCO(Library Cache Object)를 생성해야한다.
- LCO를 생성하기 위해서
library cache lock(exclusive)획득해야한다. 그래야 다른 세션이 생성중인 LCO를 참조할 수 없다. - LCO가 생성되면
library cache lock(NULL)모드로 변환된다. - null 모드로 변경하는 이유는 혹시 실행계획이 무효화가 되면 빠르게 invalid로 변경하기 위해서다.
- LCO를 생성하기 위해서
-
library cache pin를 exclusive 모드로 획득한 후 실행계획을 새롭게 생성해야 한다.LCO는 |SQL문/PLSQL문|parse된 code|실행계획|으로 이루어져 있고, LCO의 문패는 SQL문이다.
2. bind(선택)
- SQL문에 변수 처리가 되어 있을 경우 변수에 실제값이 입력되는 단계
3. execute
library cache lock과library cache pin을 shared 모드로 변환하고 SQL문을 실행한다.- 블록 I/O 발생, Data buffer cache영역을 access 한다.
- active set(결과집합)을 생성한다.
4. fetch
library cache lock을 null 모드로 변환하고library cache pin을 해제한다.- active set을 유저 프로세스에 전달한다.
동일한 SQL문의 기준
- 대소문자, 공백, TAB KEY, ENTER KEY
- 주석문자(/* */,--), 힌트문자(/*+ */)
- 테이블 소유자이름이 일치(LCO를 한개만 만들어지게 하기 위해서)
- 조건절에 값이 동일
LCO의 실행계획이 무효화 처리 될때
- 통계수집
- 제약조건 추가 및 제거
- 인덱스 설계
- 테이블구조 변경
실습
-
shared pool flush
- shared pool의 library cache에 있는 LOC가 전부 삭제된다.
alter system flush shared_pool; -
shared pool을 flush 해놓은 상태이기 때문에 조회되는 LCO는 없다.
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%';
- 하드파싱을 통해 LCO를 만들고 실행계획도 만든다.
select * from hr.employees where employee_id=100;- 수행 후 다시 확인
- parse_calls : library cache에 SQL문을 찾으려는 요청 수
- executions : SQL문 수행한 횟수
- loads : 하드파싱을 거친 SQL문을 library cache에 적재한 횟수
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문들을 수행하면 다른 LCO들이 생성된다.
select * from hr.employees where employee_id=100;
select * from hr.employees where employee_id=101;
select * from hr.employees where employee_id=102;
select * from hr.employees where employee_id=103;
select * from hr.employees where employee_id=104;
select * from hr.employees where employee_id=105;- 나머지도 하드파싱 시킨후 다시 확인, LCO가 6개 만들어졌다.
- 100번은 이전 실행계획을 공유하였기 때문에 PARSE_CALLS, EXECUTIONS가 2회이다.
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값을 이용해 LCO안에 있는 실행계획 보기
select * from table(dbms_xplan.display_cursor('6z02qx94wmk10'));
select * from table(dbms_xplan.display_cursor('2d3pkrknvggfd'));
select sql_id, sql_text, parse_calls, loads, executions,hash_value, plan_hash_value
from v$sql
where sql_text like '%hr.employees%'
and sql_text not like '%v$sql%';
plan_hash_value가 동일하다는 것은 실행계획이 같다는 것이기 때문에 실행계획을 공유 시켜줄 필요가 있다.
실습 2 - 통계수집을 통한 무효화
drop table hr.emp purge;
create table hr.emp as select * from hr.employees;
alter system flush shared_pool;- 특정 SQL문을 수행
select * from hr.emp where employee_id = 100;
- 수행한 SQL문에 대한 LCO 확인
- invalidations : 실행계획을 사용할 수 없는 무효화 발생
발생하지 않았으면 0, 발생하였으면 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%';
- 해당 SQL문에 대한 실행계획 확인
select * from table(dbms_xplan.display_cursor('5g6ygund8wa94'));
- filter로 검색 : 데이터가 어느 블록에 있는지 알 수 없어, 모든 블록을 다 찾는다.
- 실행계획을 만들기 위해서는 통계정보가 필요한데 처음 테이블을 만들었을때는 통계정보가 없지만 dynamic 샘플 표본을 통해 실행계획을 생성한다.
- 통계정보 확인
- CTAS로 테이블 생성시 자동으로 dynamic 샘플링을 통해 통계정보를 수집한다.
select num_rows, blocks, avg_row_len from dba_tables where owner='HR' and table_name='EMP';
- 실행계획의 무효화를 즉시 실행하고, 통계수집을 한다.
- no_invalidate의 기본값은 auto이다.
- auto는 invalidate로 천천히 변환을 의미한다.
execute dbms_stats.gather_table_stats('hr','emp',no_invalidate=>false);
- INVALIDATIONS가 1로 증가될걸 기대하고 LCO를 조회해보았지만 그대로 0이다.
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%';
- 무효화를 주문했지만 오라클은 자체적으로 invalidation 되는건 성능상 좋지 않다고 판단하기 때문에 말을 듣지 않는다.
- 다시 똑같은 sql문 수행
select * from hr.emp where employee_id = 100;- LCO 확인
- 똑같은 쿼리문인데 LCO를 공유하지 못하고 새롭게 생성되는 것은 버그성 오류 이다.
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 - 제약조건 추가를 통한 무효화
-
shared pool memory를 flush
alter system flush shared_pool; -
SQL문 수행
select * from hr.emp where employee_id = 100;
- 해당 SQL문의 LCO확인
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%';
- 실행계획 확인
select * from table(dbms_xplan.display_cursor('5g6ygund8wa94'));
- 제약조건 추가
alter table hr.emp add constraint emp_id_pk primary key(employee_id);-
LCO를 다시 확인해보면 제약조건 추가로 인해 해당 실행계획이 무효화되어 invalidations가 1 증가하였다.
-
다시 똑같은 SQL문 수행 후 LCO를 확인하면 이전 LCO를 그대로 사용하지만, 실행계획이 무효화 되었었기 때문에 하드파싱이 발생되어 LOADS는 1 증가 되었고 PARSE_CALLS와 EXECUTIONS는 초기화 후 재사용 하기 때문에 1로 조회된다.
select * from hr.emp where employee_id = 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%';
- 실행계획을 확인해보면 primary key 제약조건 추가로 index unique scan하는걸 확인할 수 있다.
select * from table(dbms_xplan.display_cursor('5g6ygund8wa94'));
실습 4 - 제약조건 삭제를 통한 무효화
- 제약조건 삭제
alter table hr.emp drop primary key;- 제약조건을 삭제하면 실행계획이 무효화가 되면서 INVALIDATIONS의 값이 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%';
- 다시 똑같은 SQL문을 수행해본다.
select * from hr.emp where employee_id = 100;- 실행계획이 무효화되어있는 상태이기 때문에 하드파싱이 발생되고, 새로운 실행계획이기 때문에 PARSE_CALLS와 EXECUTIONS는 초기화되어 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%';
- 제약조건이 삭제되었기 때문에 실행계획은 filter로 검색하게 된다.
select * from table(dbms_xplan.display_cursor('5g6ygund8wa94'));
실습 5 - 인덱스 설계를 통한 무효화
- 인덱스 생성
- 현장에서는 인덱스와 제약조건을 분리해서 생성한다.
create unique index hr.emp_idx on hr.emp(employee_id);- 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%';
- 제약조건 추가
alter table hr.emp add constraint emp_id_pk primary key(employee_id) using index hr.emp_idx;- primary key 제약조건을 설정하려고 하면 자동으로 unique index가 걸리는데 index의 이름은 제약조건의 이름으로 되게 자동으로 설정된다. 하지만 현재 이미 인덱스가 생성되어있기 때문에 using index절을 사용해야한다.
- LCO 확인
- 현재 이미 실행계획이 무효화상태 이기 때문에 INVALIDATIONS가 추가로 증가되지는 않는다.
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%';
- 같은 SQL문 수행
select * from hr.emp where employee_id = 100;- 실행계획이 무효화되어있는 상태였기 때문에 새롭게 하드파싱하여 LOADS값이 1증가하고 새로운 실행계획이기 때문에 실행계획 수행횟수는 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%';
실습 6 - 테이블구조 변경을 통한 무효화
-
테이블 컬럼 확인
desc hr.emp -
테이블 컬럼의 구조 변경
alter table hr.emp modify 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%';
- 같은 SQL문 수행
select * from hr.emp where employee_id = 100;- 실행계획이 무효화되어있는 상태였기 때문에 새롭게 하드파싱하여 LOADS값이 1증가하고 새로운 실행계획이기 때문에 실행계획 수행횟수는 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%';
Library cache lock
-
library cache lock은 LCO(Library Cache Object)를 접근하거나 변경하는 경우 handle에 대해 획득하는 lock이다.
-
여러 세션에서 동시에 동일한 LCO를 변경하는 것을 방지하기 위해서 사용한다.
-
Library Cache Lock을 획득하지 못해 대기하는 경우 Library Cache Lock wait event 발생한다.
Library Cache Lock wait event 발생하는 경우
-
SQL문 hard parsing : library cache lock을 exclusive mode로 획득해야한다.
-
SQL문 execute 단계 : library cache lock을 shared mode로 획득해야한다.
-
alter문(테이블 구조가 변경) : library cache exclusive mode 획득해야한다.
-
create or replace procedure(function,package) : library cache exclusive mode 획득해야한다.
Library Cache Lock wait event 발생하지 않는 경우
- SQL문 fetch 단계 : library cache lock을 null mode로 획득해야한다.
session 1
execute dbms_application_info.set_client_info('sess_1')
begin
for i in 1..10000 loop
execute immediate 'create or replace procedure hr.p1 is begin null; end;';
end loop;
end;
/session 2
execute dbms_application_info.set_client_info('sess_2')
begin
for i in 1..10000 loop
execute immediate 'create or replace procedure hr.p1 is begin null; end;';
end loop ;
end;
/session 3
select client_info, sid from v$session where client_info in ('sess_1','sess_2');
- 사라진 wait event는 조회되지 않는다.
select sid, event, wait_class, wait_time, seconds_in_wait, state
from v$session_wait
where sid in (274,294);
wait event: 작업을 수행하지 못하고 대기하는 상태v$session_wait: 세션이 현재 대기하고 있는 이벤트 정보, 누적정보가 아닌 실시간 정보이므로 반복적으로 조회해야 의미 있는 정보를 얻을 수 있다.- event 컬럼 : 대기 이벤트
- wait_class 컬럼 : 대기 이벤트 종류
- wait_time 컬럼 : 대기시간, state 컬럼의 값이 waited know time 일 경우에만 의미를 가진다. 단위는 1/100초
- seconds_in_wait 컬럼 : 대기시간, state컬럼의 값이 waiting 일 경우에만 의미를 가진다. 단위를 1초
- state컬럼 : 대기상태, 이 컬럼의 값이 waiting일 경우에만 실제 대기하고 있는 중이다.
- waiting : 현재 대기하고 있는 중이라는 의미
- waited know time : 1/100초 이상의 시간을 대기한 후 현재는 cpu를 점유하고 어떤 작업을 하고 있는 중이라는 의미
- waited unknow time : 알수 없는 시간 만큼 대기한 후 현재는 cpu를 점유하고 어떤 작업을 하고 있는 중이라는 의미
- wait event에 대해 누적으로 조회
select sid, event, total_waits, time_waited
from v$session_event
where sid in (274,294);
- 두 세션에서 create or replace 할때 병합이 발생되기 때문에 library cache lock wait event가 계속 발생되었다.
v$session_event: 세션별 대기 이벤트의 누적 정보- event 컬럼 : 대기 이벤트 이름
- total_waits 컬럼 : 전체 대기 횟수
- time_waited 컬럼 : 전체 대기 시간 1/100초 단위
- total_timeouts 컬럼 : 전체 타임 아웃 횟수(library cache lock의 상태가 변경되었는지 확인한 횟수)
- average_wait 컬럼 : 평균 대기 시간 1/100초 단위
- max_wait 컬럼 : 최대 대기 시간 1/100초 단위
- time_waited_micro 컬럼 : 전체 대기 시간 1/1000000초 단위
- wait_class 컬럼 : 대기 이벤트 종류
