행위

"바인드변수"의 두 판 사이의 차이

DB CAFE

(→‎커서 쉐어링)
(→‎커서 (Cursor))
23번째 줄: 23번째 줄:
 
=== 커서 (Cursor) ===
 
=== 커서 (Cursor) ===
 
# 커서는 공유 SQL 영역(SGA 라이브러리 캐시 내부에 존재)과 프라이빗 SQL 영역(서버 프로세스 메모리 내에 존재)을 참조 하는 핸들
 
# 커서는 공유 SQL 영역(SGA 라이브러리 캐시 내부에 존재)과 프라이빗 SQL 영역(서버 프로세스 메모리 내에 존재)을 참조 하는 핸들
−
## 핸들은 프로그램이 리소스에 엑세스할수 있도록 만들어 주는 메모리 구조 (프로그래밍 언어에서 사용하는 핸들과 같음)
+
* 핸들은 프로그램이 리소스에 엑세스할수 있도록 만들어 주는 메모리 구조 (프로그래밍 언어에서 사용하는 핸들과 같음)
  
 
=== 커서 쉐어링 ===
 
=== 커서 쉐어링 ===

2023년 11월 22일 (수) 13:44 판

thumb_up 추천메뉴 바로가기

1 리터럴 SQL[편집]

android * 아래의 쿼리는 같은 결과과 나오지만 오라클은 각각 다른 쿼리라고 판단하여 작업을 하게 됩니다. 

SELECT EMP_NO FROM EMPLOYEE WHERE EMPID = 10;
SELECT emp_no FROM employee WHERE empid = 10 ;
SELECT EMP_NO
  FROM EMPLOYEE
 WHERE EMPID = 20 ;
  • 즉, 100% 동일해야 SQL파싱이 발생하지 않습니다.
  • 이를 동일한 쿼리로 인식시키기 위해서 바인드 변수를 사용하여야 합니다.


  1. literal SQL은 서로 다른 SQL로 인식하므로 매번 Parsing(Hard Parsing) 작업이 필요하며 , 많은 CPU사용량과 추가적인 메모리(Shared Pool의 Library Cache)가 필요함
  2. Shared Pool Latch 및 Library Cache Latch의 경합을 유발하여 CPU 사용량이 급증함.
  3. 다른 SQL의 Parsing된 정보를 밀어내 버리며 Shared Pool의 Memory Pregmentation을 발생시킨다.
  4. ORA-4031와 같은 상황이 발생되어 DB Reboot 상황이 발생 할수도 있다.
  5. SQL이 Literal로 되어 있으면 Data가 Skewed되어 있는 경우에는 유리하나 다수가 이용하는 사용자 환경에는 적합하지 않다.

2 커서 (Cursor)[편집]

  1. 커서는 공유 SQL 영역(SGA 라이브러리 캐시 내부에 존재)과 프라이빗 SQL 영역(서버 프로세스 메모리 내에 존재)을 참조 하는 핸들
  • 핸들은 프로그램이 리소스에 엑세스할수 있도록 만들어 주는 메모리 구조 (프로그래밍 언어에서 사용하는 핸들과 같음)

3 커서 쉐어링[편집]

  1. Literal SQL을 강제적으로 BIND변수로 바꿔서 SQL을 공유화 하는 기법
  2. 무조건 상수부를 바꿔 버리는 문제
Property Description
Parameter type String
Syntax EXACT | FORCE }
Default value EXACT
Modifiable ALTER SESSION , ALTER SYSTEM
Basic No
  • EXACT : 기본값, 서로 완전히 일치하는 SQL 문장만 커서가 공유됨
  • SIMILAR : SQL을 바인드 변수로 무조건 바꾸고 SQL당 PLAN이 Multi-Version을 가지고 선별적사용(의미분석)
  • FORCE : SQL을 바이드 변수로 무조건 바꼬고 SQL당 PLAN은 1개로 고정
select * from emp where ename='CLACK';
select * from emp where ename='CYKIM';
select * from emp where ename='JHone';

===> 다음과 같이 고정됨 

select * from amp where ename=:"SYS_B_0";

4 바인드 피킹 (bind peeking)[편집]

  • 실행계획을 생성하기 전에 바인드 변수의 값을 쿼리 옵티마이져가 가져와서 리터럴 처럼 이용하는것
  • 바인드 변수를 사용하는 SQL은 처음 실행 될때 어떤값이 바인딩되어 수행 되었는지에 따라 , 이후 동일 SQL에 대한 실행계획이 고정되어 버리는 문제점이 있음

5 어뎁티브 커서 쉐어링 (adaptive cursor sharing)[편집]

  • 공유가능한 커서를 재사용할때 비효율이 발생하는지 자동으로 식별
  • Skewed 데이터에 대한 Cursor Sharing의 문제점을 보완하고 바인드 변수에 따른 최적의 Plan을 결정하는 기능
  • _optimizer_adaptive_cursor_sharing = true (default)
  • 바인드 변수에 따라 여러개의 Plan을 가지고 있어 Bind Peeking 문제점 보완

5.1 장점[편집]

  1. Bind 값이 동일한 Selectivity를 가질때 Plan 공유
  2. Bind 값이 다른 Selectivity를 가질때 새로운 Plan 생성

5.2 어뎁티브 커셔 쉐어링 확인 조회[편집]

select child_number,is_bind_sensitive ,is_bind_aware,is_shareable,plan_hash_value 
  from v$sql
 where sql_id ='xxxx';
  • is_bind_sensitive : 실행계획을 생성하는데 바인드 변수피킹이 사용되었는지,어뎁티브 커서 쉐어링이 공유 될지 있는지를 나타냄. 어뎁티브 커셔 쉐어링을 고려해 볼수 있다면 Y , 아니면 N
  • is_bind_aware  : 커서의 어뎁티브 커서 공유 사용여부를 나타냄. 사용하고 있다면 Y, 아니면 N
  • is_shareable  : 커서가 쉐어(공유) 될수 있는지를 나타냄. Y는 공유가능 , 아니면 N

6 바인드 변수 확인[편집]

6.1 where절에 사용되는 바인드 값 확인[편집]

SELECT name, position, dup_position, datatype_string, value_string
  FROM (SELECT a.*
             , ROW_NUMBER () OVER (PARTITION BY a.position ORDER BY a.last_captured DESC) AS rn 
          FROM v$sql_bind_capture a
         WHERE a.sql_id = 'ckrnp9t0x37xh'
           AND a.child_number = 0)
 WHERE rn = 1;
WITH BB AS
 (SELECT DISTINCT NAME BIND_VAL, VALUE_STRING VAL_VAL,DATATYPE_STRING,SQL_ID
    FROM V$SQL_BIND_CAPTURE A
   WHERE (LAST_CAPTURED, SQL_ID) IN
         (SELECT MAX(LAST_CAPTURED), SQL_ID FROM V$SQL_BIND_CAPTURE GROUP BY SQL_ID)),
     CC AS 
 (SELECT 1 RNUM FROM DUAL UNION ALL SELECT 2 FROM DUAL)
SELECT SQL_ID,DECODE(MOD(ROWNUM, 2), 1, A, B)
  FROM (SELECT SQL_ID,REPLACE('VARIABLE BIND_VAL '||DATATYPE_STRING,'BIND_VAL',BIND_VAL) A,
               REPLACE(REPLACE('EXEC :BIND_VAL :=''VAL_VAL'';','VAL_VAL',VAL_VAL),'BIND_VAL',BIND_VAL) B
          FROM BB,
               CC)
WHERE sql_id = 'gsqxm6zz3w2by';
  • AWR 이용 (DBA_HIST_SQLBIND + DBA_HIST_SNAPSHOT)

- SQL문 텍스트를 보려면 DBA_HIST_SQLTEXT 

SELECT DISTINCT 
       A.SNAP_ID , A.INSTANCE_NUMBER , B.END_INTERVAL_TIME
     , A.LAST_CAPTURED, A.NAME , A.VALUE_STRING 
  FROM DBA_HIST_SQLBIND A
     , DBA_HIST_SNAPSHOT B
 WHERE A.DBID = B.DBID
   AND A.SNAP_ID = B.SNAP_ID
   AND A.INSTANCE_NUMBER = B.INSTANCE_NUMBER 
   AND B.END_INTERVAL_TIME >= TRUNC(SYSDATE) + 9/24 - 1 / 8
   AND A.SQL_ID = :sqlid
   AND A.LAST_CAPTURED IS NOT NULL
 ORDER BY B.END_INTERVAL_TIME DESC;
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=바인드변수&oldid=10008"