[오라클학원/오라클교육동영상]오라클커서공유, CURSOR_SHARING 파라미터, 커서란? 소프트파싱,하드파싱, Literal SQL, Bind SQL, 리터럴SQL, 바인드변수SQL
오라클커서공유, CURSOR_SHARING 파라미터, 커서란? 소프트파싱,하드파싱, Literal SQL, Bind SQL, 리터럴SQL, 바인드변수SQLORACLE SQL 튜닝CURSOR_SHARING 파라미터ORACLE 19CCURSOR_SHARING 파라미터는 오라클 커서를 공유하는 방식을 셋팅하는데 기본값은 EXACT로 두 SQL문이 정확히 일치해야…
ojc.asia
CURSOR_SHARING 파라미터는 오라클 커서를 공유하는 방식을 셋팅하는데 기본값은 EXACT로 두 SQL문이 정확히 일치해야만 SQL 커서를 공유합니다.
만약 PL/SQL에서 “select * from emp where deptno = 10” 이라는 SQL문과 “select * from emp where deptno = 20” 두 SQL문장이 사용되었다면 이는 서로 다른 SQL로 인식을 해서 각각 하드 파싱을 하고 쿼리 성능이 저하 됩니다. 이런식으로 작성된 SQL문을 Literal SQL문 이라고 합니다. 위 두 SQL문장을 동일한 SQL문장으로 인식시키기 위해서는(커서를 공유) CURSOR_SHARING 파라미터 값을 “FORCE”로 주시면 되고. 이는 실습을 통해 확인 해 보겠습니다.
다음 예제를 통해 리터럴 SQL과 바인드 변수 SQL 사용에 따른 하드파싱과 소프트 파싱의 차이에 대해서 알아 보세요~
| –Shared Pool의 SQL Area를 클리어 합니다. 이후 실행되는 SQL문장은 하드파싱을 하게 됩니다. ALTER SYSTEM FLUSH SHARED_POOL; – DBMS_OUT 출력을 활성화 합니다. SQL Developer등에서 출력 결과를 확인할 수 있습니다. SET SERVEROUTPUT ON; – 리터럴 SQL을 사용한 예 DECLARE v_sql VARCHAR2(500); v_cnt NUMBER; BEGIN FOR I IN 1..5 LOOP v_sql := 'SELECT /*+ LITERAL */ COUNT(*) FROM MYEMP1 WHERE EMPNO = ' || i; DBMS_OUTPUT.PUT_LINE(v_sql); EXECUTE IMMEDIATE v_sql INTO v_cnt; DBMS_OUTPUT.PUT_LINE(v_cnt); END LOOP; END; / – Shared Pool의 SQL Area에서 위에서 실행한 SQL문장의 개수 및 총실행 횟수를 조회 합니다. 5번 각각 하드 파싱 되었고 총 실행횟수도 1임을 확인 합니다. SELECT SUBSTR(sql_text,1,60) "SQL", COUNT(*), SUM(EXECUTIONS) "총실행횟수" FROM V$SQLAREA WHERE SQL_TEXT LIKE '%LITERAL%' GROUP BY SUBSTR(sql_text,1,60) HAVING COUNT(*) > 0 ORDER BY 2; ALTER SYSTEM FLUSH SHARED_POOL; – 바인드 변수 SQL형태로 작성된 예문 입니다. DECLARE v_sql VARCHAR2(500); v_cnt NUMBER; BEGIN FOR i IN 1..5 LOOP v_sql := 'SELECT /*+ BIND */ COUNT(*) FROM MYEMP1 WHERE EMPNO = :1'; DBMS_OUTPUT.PUT_LINE(V_SQL); EXECUTE IMMEDIATE v_sql INTO v_cnt USING i; DBMS_OUTPUT.PUT_LINE(v_cnt); END LOOP; END; / – Shared Pool의 SQL Area에서 위에서 실행한 SQL문장의 개수 및 총실행 횟수를 조회 합니다. 1번 하드 파싱 되었고 총 실행횟수가 5임을 확인 합니다. SELECT SUBSTR(sql_text,1,60) "SQL", COUNT(*), SUM(EXECUTIONS) "총실행횟수" FROM V$SQLAREA WHERE SQL_TEXT LIKE '%BIND%' GROUP BY SUBSTR(sql_text,1,60) HAVING COUNT(*) > 0 ORDER BY 2; |
Oracle 8.1.6에서 소개된 CURSOR_SHARING 변수는 각각의 문장들에 대해 Bind 변수로 처리하지 않더라도 내부적으로 바인드 변수로 처리하여 각각의 Cursor에 대해 공유가 가능하도록 했습니다. 실제 이 기능은 Bind 변수를 쓰는 것보다는 빠르지 않지만 Literal SQL문을 이용하는 것보다 20~30% 성능 향상이 있는 것으로 검증 되었으며 오라클12C이후 사용가능한 두 파라미터는 EXACT, FORCE이다.
오라클11g,12C, 19C에서 CURSOR_SHARING 파라미터의 기본값은 EXACT인데 기본값이 아닌 FORCE인 경우 SQL문장이 리터럴 SQL 형태로 작성되었다면 시스템에서 생성한 바인드 변수(:SYS_B_0)로 리터럴 값을 자동 변형 한다.
CURSOR_SHARING 값이 기본값(EXACT)이 아닌 경우 즉 FORCE인 경우 아래와 같은 절차로 SQL구문을 파싱한다.
“select * from emp where deptno = 10” 이라는 SQL문장이 이미 실행되었고 “select * from emp where deptno = 20” 문장을 실행한다고 했을 때
- Shared Pool 안에서 공백, 대소문자까지 완벽하게 동일한 SQL 문장이 있는지 검사한다. 만약 동일한 SQL문장이 있으면 파싱트리 및 실행계획을 공유해서 사용하고 4번으로, 없다면 다음 2번을 수행한다.
- Shared Pool에서 유사한 SQL문장을 찾는다. (예를 들면 where절의 상수값만 다른 경우), 만약 유사한 SQL문장이 없다면 Hard Parsing을 수행하여 파싱트리, 실행계획을 만들고 4번으로, 이미 있다면 다음 3번을 수행한다.
- 남은 과정은 실행한 SQL문장(where deptno = 20)의 실행 계획이 상수값만 다른 상태로 존재하는 처음에 실행된 SQL문장(where deptno = 10)의 실행계획을 공유할 수 있는지를 확인하는 것인데 만약 공유 불가능하다면 Hard Parsing을 적용하여 새로운 실행계획을 만들고 공유가능 하다면 실행계획을 공유한 다음 4번을 진행한다.
- 공유 SQL Area에 SQL문장 및 파싱트리, 실행계획을 공유시킵니다.
- 실행 계획대로 로우 소스 트리(파싱 트리)에 따라 SQL문을 실행 합니다.
위에서 작성한 실습에 이어서 CURSOR_SHARING 파라미터를 사용하여 실습합니다.
| – 상수값이 하드코딩된 리터럴 SQL 구문을 커서가 공유될 수 있도록 셋팅 합니다. – CURSOR_SHARING 파라미터의 기본값은EXACT로 리터럴 SQL 형태로 작성된 구문은 sQL 커서를 공유할 수 없습니다(동일한 SQL로 인식되지 않습니다) ALTER SESSION SET CURSOR_SHARING = FORCE; –Shared Pool의 SQL Area를 클리어 합니다. 이후 실행되는 SQL문장은 하드파싱을 하게 됩니다. ALTER SYSTEM FLUSH SHARED_POOL; – 리터럴 SQL을 사용하여 SQL을 작성 합니다. DECLARE v_sql VARCHAR2(500); v_cnt NUMBER; BEGIN FOR I IN 1..5 LOOP v_sql := 'SELECT /*+ LITERAL */ COUNT(*) FROM MYEMP1 WHERE EMPNO = ' || i; DBMS_OUTPUT.PUT_LINE(v_sql); EXECUTE IMMEDIATE v_sql INTO v_cnt; DBMS_OUTPUT.PUT_LINE(v_cnt); END LOOP; END; / – Shared Pool의 SQL Area에서 위에서 실행한 SQL문장의 개수 및 총실행 횟수를 조회 합니다. 1번 하드 파싱 되었고 총 실행횟수가 5임을 확인 합니다. 커서가 공유되었습니다! SELECT SUBSTR(sql_text,1,60) "SQL", COUNT(*), SUM(EXECUTIONS) "총실행횟수" FROM V$SQLAREA WHERE SQL_TEXT LIKE '%LITERAL%' GROUP BY SUBSTR(sql_text,1,60) HAVING COUNT(*) > 0 ORDER BY 2; |
#오라클커서, #커서공유, #CURSOR_SHARING, #커서란?, #소프트파싱,#하드파싱, #LiteralSQL, #Bind SQL, #리터럴SQL, #바인드변수SQL, #오라클교육, #오라클동영상, #오라클학원, 오라클커서, 커서공유, CURSOR_SHARING, 커서란?, 소프트파싱,하드파싱, LiteralSQL, Bind SQL, 리터럴SQL, 바인드변수SQL, 오라클교육, 오라클동영상, 오라클학원,
