ORACLE - DAY 41

BUMSOO·2024년 10월 2일

Dynamic SQL Static SQL version count 오라클 DBA

Shared Pool

library cache : SQL 실행에 관련된 모든 객체에 대한 정보 관리
- shared sql area(공유 SQL영역) : sql문장, parse tree,실행계획 정보
- plsql area : plsql문장과 컴파일된 프로그램 저장, source, parse-code, machine-code, error
- library cache object = handle + LCO
  - handle = NAME(SQL문) + 메타정보
  - LCO = dependency(종속관련정보), child table(자식 LCO), data block(실행계획정보)
data dictionary cache : 딕셔너리 정보들이 저장되어 있는 메모리
session, process, enqueue, transaction 정보
shared server 환경일 경우 UGA(User Global Area)가 포함될 수 있다.

Static SQL

예) select * from hr.employees where employee_id = 100;

데이터베이스에 접근할지가 미리 정의되는 형태의 SQL문이다.
미리 컴파일이 되어있다.

Dynamic SQL

예) 'select * from hr.employees where employee_id = 100'; -- 잘못되었다 예
'select * from hr.employees where %'; -- 런타임시점까지 where 절의 조건을 모른다.

런타임에서 사용자의 입력값에 따라 동적으로 SQL 구문을 생성하여 실행하는 방식의 SQL문이다.
동적으로 처리하기 위해 문자열 변수를 활용하여 런타임시 구문이 확정된다는 특징을 가지고 있다.
수행할때마다 컴파일 해야하기 때문에 하드파싱이 발생된다.
plsql 안에서 DDL문을 사용하기 위해서는 dynamic sql(execute immediate문)을 사용해야 한다.
runtime 시점까지 where 절의 어떤 컬럼을 사용해서 조건을 줄지 모르면 dynamic sql을 사용하고 어떤 컬럼을 사용해서 where 조건을 줄지 알면 static sql을 사용한다.

Dynamic SQL 실습

<<SESSION 1>>

execute dbms_application_info.set_client_info('sess_1')

declare
	v_cnt number;
begin
	for i in 1..10000 loop
    	execute immediate 'select /* example */ count(*) from dual where dummy=to_char('||i||')' into v_cnt;
    end loop;
end;
/

dynamic sql문은 프로시저를 execute 할때마다 실행계획을 새로 생성한다.
i를 변수처럼 한거처럼 생각하지만 변수처리가 아니라 상수처리로 되기 때문에 개별 실행계획이 생성된다.

<<SESSION 2>>

execute dbms_application_info.set_client_info('sess_2')

declare
	v_cnt number;
begin
	for i in 1..10000 loop
    	execute immediate 'select /* example */ count(*) from dual where dummy=to_char('||i||')' into v_cnt;
    end loop;
end;
/

<<SESSION 3>>

select client_info, sid from v$session where client_info in ('sess_1','sess_2');


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

한 세션이 LCO를 만들면 다른 세션이 wait 하다가 해당 LCO를 사용하여 parse 와 execution이 2번씩 수행된걸로 확인된다.

select sid, event, total_waits, time_waited/100
from v$session_event 
where sid in (55,277);

하드파싱 하려고 두 세션이 병합하기때문에 latch shared pool wait event가 발생하는걸 확인할 수있다.
cursor : pin S wait on X : library cache pin을 shared 모드로 갖고 싶은데 다른 세션에서 exclusive 모드로 가지고 있어 기다리는 상황

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

전부 plan_hash_value가 똑같기 때문에 실행계획을 공유해야할 필요가 있는 쿼리문이다.

Dynamic SQL 실습 <<개선사항1. bind 변수처리>>

<<SESSION 1>>

bind 변수 안에 넣을 값을 using 절로 설정하면 된다.

execute dbms_application_info.set_client_info('sess_1')

declare
	v_cnt number;
begin
	for i in 1..10000 loop
    	execute immediate 'select /* example */ count(*) from dual where dummy=to_char(:b)' into v_cnt using i; 
    end loop;
end;
/

<<SESSION 2>>

execute dbms_application_info.set_client_info('sess_2')

declare
	v_cnt number;
begin
	for i in 1..10000 loop
    	execute immediate 'select /* example */ count(*) from dual where dummy=to_char(:b)' into v_cnt using i; 
    end loop;
end;
/

<<SESSION 3>>

select client_info, sid from v$session where client_info in ('sess_1','sess_2');


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

plsql을 사용하면 반복되는 sql문을 실행할때마다 latch(mutex)를 잡을 필요 없이 바로 LCO로 접근할 수 있는 캐시 기능을 제공하기 때문에 두번째 접근부터는 바로 latch 잡지않고 LCO로 이동해서 parse는 더이상 증가되지 않고 execution만 증가하게 된다.
-execution은 증가하는 이유는 dynamic sql문은 프로그램과 분리된다.
프로그램안에 dynamic sql문을 사용한 경우 shared pool 안에 sql area와 plsql area에 LCO가 만들어진다.

select sid, event, total_waits, time_waited/100
from v$session_event 
where sid in (55,277);

Dynamic SQL 실습 <<개선사항2. static sql>>

<<SESSION 1>>

execute dbms_application_info.set_client_info('sess_1')

declare
	v_cnt number;
begin
	for i in 1..10000 loop
    	select /* example */ count(*) 
        into v_cnt 
        from dual 
        where dummy=to_char(i) ;
    end loop;
end;
/

static sql문장은 프로시저가 컴파일 되는 순간 안의 sql문도 컴파일을 하면서 이미 실행계획이 만들어져 있다. 후에 프로시저를 execute 할때 이미 만들어져 있는 실행계획을 사용한다.

<<SESSION 2>>

execute dbms_application_info.set_client_info('sess_2')

declare
	v_cnt number;
begin
	for i in 1..10000 loop
    	select /* example */ count(*) 
        into v_cnt 
        from dual 
        where dummy=to_char(i);
    end loop;
end;
/

<<SESSION 3>>

select client_info, sid from v$session where client_info in ('sess_1','sess_2');


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

프로그램(프로시저,함수,패키지) 안에서는 static sql문은 일체형이다. latch의 캐시기능으로 같은 SQL문장의 경우 두번째 경우부터 바로 해당 LCO로 이동하기 때문에 parse 와 execution이 증가하지 않는다.
프로그램 안에 static sql문으로 작성했을 경우 shared pool 안에 plsql area공간에만 LCO가 만들어진다.

select sid, event, total_waits, time_waited/100
from v$session_event 
where sid in (55,277);

session_cached_cursors

PGA의 저장할 LCO 포인터의 개수를 설정
즉 3회 이상 수행된 SQL문장의 커서 포인터를 PGA에 저장한다.
소프트 파싱시에 PGA 저장된 SQL 커서 포인터 정보를 이용하여 수행하면 SQL문의 LCO를 바로 찾아 갈수 있어서 library cache latch(mutex) 대기시간을 줄어드는 효과가 발생한다.

show parameter session_cached_cursors

shared pool latch

hard parsing이 발생하는 경우 shared pool의 library cache메모리를 할당(free chunk 획득) 받기위해서 shared pool latch를 획득해야 한다.
instance당 shared pool latch는 한개를 가지고 수행한다.
9i 버전 부터는 shared pool을 여러개의 서브풀로 최대 7개까지 나누워서 관리하며 단 오라클 cpu 개수가 4개 이상이고 shared_pool_size 값이 250mb 이상인 경우 _kghdsidx_count의 값만큼 서브풀을 생성해서 관리한다.
shared pool latch 경합을 줄이기 위해서이다.

사용중인 latch 확인

select name, gets from v$latch_children where name='shared pool';

NAME                                                GETS
--------------------------------------------- ----------
shared pool                                           35
shared pool                                           35
shared pool                                           35
shared pool                                           35
shared pool                                           35
shared pool                                           35
shared pool                                      2892659 <- 사용하고 있는 latch

7 rows selected.

히든 파라미터 확인

select a.ksppinm as parameter,b.ksppstvl as session_value, c.ksppstvl as instance_value 
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx
and a.indx = c.indx
and a.ksppinm = '_kghdsidx_count';

현재 latch는 1개인걸 확인할 수 있다.

- 현재 cpu 갯수 확인
show parameter cpu_count

현재 cpu가 2개 밖에 없어서 latch가 추가될수 없다.

version count (daily check)

자식 LCO(Library Cache Object) 수
버전 카운트가 증가하는 경우는 53가지 이다.
desc v$sql_shared_cursor
- 해당 뷰의 컬럼들이 53가지의 이유를 나타내고 있다.
- 이유에 해당하는 컬럼의 값은 Y로 표시된다.

bind 변수 크기 실습 1 - 처음에 작은값 다음에는 큰값

variable name varchar2(10)

exec :name := 'King'

SYS@ORA19C> print name

NAME
---------------------------------------------
King

SYS@ORA19C> select last_name, salary from hr.employees where last_name = :name;

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

variable name varchar2(1000)
exec :name := 'Grant'

SYS@ORA19C> print name

NAME
---------------------------------------------
Grant

SYS@ORA19C> select last_name, salary from hr.employees where last_name = :name;

LAST_NAME                     SALARY
------------------------- ----------
Grant                           2600
Grant                           7000

select sql_id, sql_text, version_count
from v$sqlarea
where sql_text like '%hr.employees%'
and sql_text not like '%v$sqlarea%';

version_count가 2개라는것은 child cursor가 2개 만들어졌다는 의미이다.

- version count 이유 찾기

select address, child_address, child_number, bind_length_upgradeable from v$sql_shared_cursor where sql_id ='gqgwd5rvu5ftu';

- 실행계획 확인

select * from table(dbms_xplan.display_cursor('gqgwd5rvu5ftu',0));
select * from table(dbms_xplan.display_cursor('gqgwd5rvu5ftu',1));

두 쿼리문의 실행계획이 똑같다.

- bind 변수의 대입값 확인

select address, child_address,datatype_string, max_length, value_string
from v$sql_bind_capture
where sql_id = 'gqgwd5rvu5ftu';

오라클이 임의로 크기를 조정했다.
처음 설정한 크기가 너무 작고 그 다음 설정한 크기가 크면 변수 크기의 공유가 안되기 때문에 따로 만들어 진다.

bind 변수 크기 실습 2 - 처음에 큰값 다음에는 작은값

alter system flush shared_pool;

variable name varchar2(4000)

exec :name := 'King'

SYS@ORA19C> print name

NAME
---------------------------------------------
King

SYS@ORA19C> select last_name, salary from hr.employees where last_name = :name;

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

variable name varchar2(10)
exec :name := 'Grant'

SYS@ORA19C> print name

NAME
---------------------------------------------
Grant

SYS@ORA19C> select last_name, salary from hr.employees where last_name = :name;

LAST_NAME                     SALARY
------------------------- ----------
Grant                           2600
Grant                           7000

select sql_id, sql_text, version_count
from v$sqlarea
where sql_text like '%hr.employees%'
and sql_text not like '%v$sqlarea%';

처음에 큰값으로 설정하게 되면 그다음에는 작게 설정되더라도 공유가 되기 때문에 version_count는 1개 이다.

- bind 변수의 대입값 확인

select address, child_address,datatype_string, max_length, value_string
from v$sql_bind_capture
where sql_id = 'gqgwd5rvu5ftu';

변수 확인은 처음 대입한 값만 확인이 된다.
타입 크기도 오라클이 임의로 더 큰 값으로 설정했다.

BUMSOO

이전 포스트

ORACLE - DAY 40

다음 포스트

ORACLE - DAY 41

Shared Pool

Static SQL

Dynamic SQL

Dynamic SQL 실습

<<SESSION 1>>

<<SESSION 2>>

<<SESSION 3>>

Dynamic SQL 실습 <<개선사항1. bind 변수처리>>

<<SESSION 1>>

<<SESSION 2>>

<<SESSION 3>>

Dynamic SQL 실습 <<개선사항2. static sql>>

<<SESSION 1>>

<<SESSION 2>>

<<SESSION 3>>

session_cached_cursors

shared pool latch

사용중인 latch 확인

히든 파라미터 확인

version count (daily check)

bind 변수 크기 실습 1 - 처음에 작은값 다음에는 큰값

bind 변수 크기 실습 2 - 처음에 큰값 다음에는 작은값

ORACLE - DAY 40

ORACLE - DAY 42

0개의 댓글