#1.SQL초보에서 Schema Objects까지(SQL학원/오라클학원/IT실무교육학원/재직자/실업자교육학원추천)탑크리에듀(구로디지털단지역3번출구 2분거리)탑크리에듀교육센터(topcredu.co.kr)제공
1-1. 오라클, 데이터베이스관련 용어, 1-2. 오라클 에디션(ORACLE EDITION), 1-3. 오라클 버전 변화 등.. 개요 및 실습데이터 설치에 관련하여 설명 한 자료입니다. 많은 도움 되시길 바랍니다.^^
오라클 커서(Cursor) 개념 및 오라클 메모리 구조_PL/SQL,오라클커서강좌,SGA, PGA, UGA, Shared Pool, Sha...탑크리에듀(구로디지털단지역3번출구 2분거리)탑크리에듀(http://www.topcredu.co.kr)에서 제공하는 오라클, SQL, PL/SQL, 힌트튜닝 강좌중 오라클 커서(Cursor) 개념 & 메모리 구조(Memory Strucre)와 관련된 PPT 강좌 입니다. CURSOR, PGA, UGA, Shared Server, Dedicate Server, DRCP, Library Cache, Shared SQL Area와 관련된 내용 참조하시기 바랍니다.
#1.SQL초보에서 Schema Objects까지(SQL학원/오라클학원/IT실무교육학원/재직자/실업자교육학원추천)탑크리에듀(구로디지털단지역3번출구 2분거리)탑크리에듀교육센터(topcredu.co.kr)제공
1-1. 오라클, 데이터베이스관련 용어, 1-2. 오라클 에디션(ORACLE EDITION), 1-3. 오라클 버전 변화 등.. 개요 및 실습데이터 설치에 관련하여 설명 한 자료입니다. 많은 도움 되시길 바랍니다.^^
오라클 커서(Cursor) 개념 및 오라클 메모리 구조_PL/SQL,오라클커서강좌,SGA, PGA, UGA, Shared Pool, Sha...탑크리에듀(구로디지털단지역3번출구 2분거리)탑크리에듀(http://www.topcredu.co.kr)에서 제공하는 오라클, SQL, PL/SQL, 힌트튜닝 강좌중 오라클 커서(Cursor) 개념 & 메모리 구조(Memory Strucre)와 관련된 PPT 강좌 입니다. CURSOR, PGA, UGA, Shared Server, Dedicate Server, DRCP, Library Cache, Shared SQL Area와 관련된 내용 참조하시기 바랍니다.
1.7 튜닝의도구 sql autorace탑크리에듀(구로디지털단지역3번출구 2분거리)SQL*Plus에서 사용자는 자동으로 Optimizer가 만드는 실행계획과 통계정보를 얻을 수 있다. 이런 경우 AUTOTRACE를 사용하며 DML문을 성공적으로 수행 시 실행계획과 통계정보가 만들어지며 DML문의 성능 튜닝을 위한 방법으로 자주 이용된다.
SET AUTOTRACE를 사용하기 위해선 실행 계획용 테이블(PLAN_TABLE)이 존재해야 하며 구문을 활성화하기 위해 SET AUTOTRACE ON, 비활성화 하기 위해 SET AUTOTRACE OFF하면 된다. 참고로 SET AUTOTRACE에서 사용할 수 있는 옵션은 다음과 같다.
1.8 튜닝의도구 dbms xplan탑크리에듀(구로디지털단지역3번출구 2분거리) 오라클9i 이후 사용가능한 DBMS_XPLAN 패키지는 실행계획을 DISPLAY하고, 포맷을 주기 위해 사용된다.
DBMS_XPLAN을 사용하기 위해서는 PLAN_TABLE이 존재해야 한다.
3.3 실행계획 SQL 연산 (Count,Count Stopkey/Filter)탑크리에듀(구로디지털단지역3번출구 2분거리)FILTER 연산은 데이터 추출 시 필터링이 일어나고 있음을 알려주는 SQL ROW 연산인데 WHERE 조건 절에서 인덱스를 사용하지 못할 때 발생한다. NESTED LOOP 방식으로 해석할 수 있는데 서브쿼리라면 메인쿼리 로우를 하나씩 읽을때 마다 서브쿼리를 한 번씩 실행하는 형태이다.
FILTER OPERATION은 IN, NOT IN, EXISTS, NOT EXISTS 를 사용하는 경우 발견할 수 있는 OPERATION이며 중첩 루프조인(Nedted Loop Join)과 유사하게 움직인다. 메인쿼리의 결과에 대해 서브쿼리의 결과값을 버퍼에 임시저장해 같거나 다른 것을 찾아 나가는 방식이다. 이러한 과정이 드라이빙되는 테이블의 각 로우에 대해 일어나기 때문에 NESTED LOOP JOIN과 유사하다고 볼 수있다.
3.2 실행계획 sql 연산 (concatenation)탑크리에듀(구로디지털단지역3번출구 2분거리)-- 먼저 EMP에 테이블에 생성되어 있는 인덱스 및 칼럼을 확인하자.
SQL> SELECT a.index_name, a.column_name, b.visibility
FROM user_ind_columns a, user_indexes b
WHERE a.table_name = 'EMP'
AND a.index_name = b.index_name ;
-- 인덱스가 없다면 생성, 있으면 SKIP
SQL> CREATE INDEX idx_emp_job ON EMP(job);
SQL> CREATE INDEX idx_emp_deptno ON EMP(deptno);
Sql기초강좌2_SET AUTOTRACE_SQL교육탑크리에듀(구로디지털단지역3번출구 2분거리)SQL*Plus에서 사용자는 자동으로 Optimizer에서 실행계획과 통계정보를 얻을 수 있다. AUTOTRACE를 사용하며 DML문을 성공적으로 수행 시 만들어지며 DML문의 성능 튜닝을 위한 방법으로 자주 이용 된다.
SET AUTOTRACE를 사용하기 위해선 실행 계획용 테이블(PLAN_TABLE)이 존재해야 하며 구문을 활성화 하기 위해 SET AUTOTRACE ON, 비활성화 하기위해 SET AUTOTRACE OFF하고 하면 됩니다. 참고로 SET AUTOTRACE에서 사용 할 수 있는 옵션은 다음과 같다.
1.9 튜닝의도구 10053 event탑크리에듀(구로디지털단지역3번출구 2분거리)Explain Plan, GATHER_PLAN_STATISTICS 힌트, DBMS_XPLAN 패키지를 이용해서 CBO의 SQL 실행계획을 확인할 수 있지만 10053 이벤트를 이용하면 보다 구체적으로 CBO의 쿼리변환, 최적화 과정을 시간순으로 모니터링 할 수 있다.
점점 발전하는 CBO의 쿼리실행을 위한 전과정을 확인 가능한데 Query Transformation, Query Optimization을 과정을 Trace 파일로 제공한다. Trace 파일의 위치는 show parameter user_dump_dest로 확인 가능하다.
3.6 실행계획 SQL 연산 (NESTED LOOP SEMI-JOIN)탑크리에듀(구로디지털단지역3번출구 2분거리)WHERE절에 인덱스 구성 컬럼이 ‘<’>’와 같이 범위 제한 연산자에 의해 이용되거나 BETWEEN or LIKE와 같은 조건 절에 이용될 때 INDEX RANGE SCAN을 하게 된다. 만약 결합(복합) 인덱스라면 범위 제한자에 사용되는 컬럼은 인덱스 구성 컬럼 중 선두 컬럼이여야 한다. 조건을 만족하는 첫 번째 레코드를 인덱스 블록에서 추출 후 조건에 맞는 데이터가 나올 때 까지 계속 스캔하는 나가는 방식으로 INDEX, INDEX_ASC 힌트를 사용할 때 나타나는 연산자이다.
아래 예에서 EMP TABLE에는 sal에 대해 오름차순 순방향 인덱스가 구성되어 있다.
(Index Range Scan이 가능하도록 하는 힌트는 INDEX_RS 이다.)
1.6 cursor sharing 파라미터탑크리에듀(구로디지털단지역3번출구 2분거리)Oracle 8.1.6에서 소개된 Cursor_Sharing 변수는 각각의 문장들에 대해 bind 변수로 처리하지 않더라도 내부적으로 바인드 변수로 처리하여 각각의 Cursor에 대해 공유가 가능하도록 했다. 실제 이 기능은 Bind 변수를 쓰는 것보다는 빠르지 않지만 Literal SQL문을 이용하는 것보다 20~30% 성능 향상이 있는 것으로 검증 되었으며 오라클12C에서 사용가능한 두 파라미터는 EXACT, FORCE이다. (SIMILAR는 오라클12C에서 Deprecated됨)
Oracle11g에서 Cursor_Sharing 파라미터의 기본값은 EXACT인데 기본값이 아닌 경우 SQL문장이 리터럴 SQL 형태로 작성되었다면 시스템에서 생성한 바인드 변수(:SYS_B_0)로 리터럴 값을 자동 변형 한다.
2.1 optimizer mode를 변경하는 힌트(rule)탑크리에듀(구로디지털단지역3번출구 2분거리)규칙 기반 옵티마이저(Rule-Based Optimizer)로 동작하여 실행 계획을 세우도록 하는 힌트인데 이 경우 테이블이나 인덱스의 통계 정보가 있다고 하더라도 무시하고 사용하지 않으며 규칙에 기반한 실행 계획을 세우게 된다.
옵티마이저는 순위가 매겨진 오퍼레이션에 근거하여 실행 계획을 세우며 순위가 높은 것이 우선 적용된다.
만약 SQL 문장에서 /*+ RULE INDEX(emp idx_ename) */ 와 같이 RULE 힌트와 다른 힌트가 같이 사용된다면 RULE 힌트만 적용되므로 주의하자.
3.5 실행계획 SQL 연산 (HASH SEMI-JOIN)탑크리에듀(구로디지털단지역3번출구 2분거리)세미 조인은 보통 EXISTS를 사용하는 서브쿼리의 형태로 나타나며 이러한 경우 서브 쿼리에 인덱스가 존재하지 않는다면 상당히 비효율적인데 SEMI-JOIN이 일어나도록 유도한다면 성능의 향상을 꽤 할 수 있다. 즉 인덱스 없이 EXISTS를 사용하는 쿼리라면 HASH_SJ or MERGE_SJ or NL_SJ 힌트를 이용해서 세미조인이 일어나도록 푸는 것이 좋다.
#2.SQL초보에서 Schema Objects까지_재직자/근로자환급/국비지원교육/IT실무교육/SQL기초교육/구로IT학원추천탑크리에듀(구로디지털단지역3번출구 2분거리)탑크리에듀교육센터(topcredu.co.kr)제공
SQL초보에서 Schema Objects까지 두번째 자료입니다.
2-1. 개요, 시작, 종료, 2-2. 데이터베이스 시작, 종료에 대하여 설명한 자료입니다. 참고하시어 많은 도움되시길 바랍니다.^^
3.4 실행계획 SQL 연산 (Hash Anti-Join)탑크리에듀(구로디지털단지역3번출구 2분거리)ANTI 조인은 조인의 대상이 되는 테이블과 일치하지 않는 데이터를 추출하는 연산으로 SQL연산에서 NOT IN, NOT EXISTS, MINUS 등이 있을 때 나타나는 실행계획 연산자이다. 안티 조인은 NESTED LOOP ANTI-JOIN, MERGE ANTI-JOIN 또는 HASH ANTI_JOIN으로 풀리도록 할 수 있는데 대체로 HASH ANTI_JOIN이 성능상 좋다.
#23.SQL초보에서 Schema Objects까지_SQL기초교육/오라클교육/국비지원환급교육/재직자교육/구로IT학원추천탑크리에듀(구로디지털단지역3번출구 2분거리)탑크리에듀교육센터(topcredu.co.kr)에서 제공하는
데이터 딕셔너리 개요, USER_ 데이터 딕셔너리 뷰에 대해 설명한 자료입니다.
1. Part 1 ORACLE │39
배치 프로그램에서 튜닝대상 SQL 추출하기
㈜엑셈 컨설팅본부/DB컨설팅팀 박 성호
“배치 프로그램의 성능문제를 진단하기 위해 트레이스를 사용할 수 없고, 개별 SQL 에 대한 성
능점검은 비효율적인 경우에 어떻게 배치 프로그램의 성능문제를 제대로 파악하고 개선안을 도
출할 것인가?”
복잡한 로직을 가지고 있는 프로그램 (이후 배치 프로그램)에 대한 성능문제를 파악하기 위해
수행되는 모든 SQL 에 대한 개별 수행내역을 정확히 판단할 수 있어야 한다. 왜냐하면, 특정 배
치 프로그램에서 수행되는 모든 SQL 의 I/O 처리량이나 수행시간 등에 대한 정확한 정보를 추
출할 수 있다면, 배치 프로그램의 SQL 중 튜닝대상을 선별하는 작업을 효율적으로 수행할 수 있
기 때문이다.
따라서 이 문서에서 Oracle 이 제공하는 X$KGLRD 테이블과 SQL 의 수행내역을 조회할 수 있
는 Dynamic Performance View 를 활용하여 배치 프로그램에서 수행하는 SQL 의 수행정보를
추출할 수 있는 방법을 제공할 것이다. 그래야만 추출된 SQL 의 수행정보를 정확하게 분석하여
배치 프로그램의 SQL 중 튜닝대상을 제대로 선정할 수 있기 때문이다.
보통 DB 서버에서 수행되는 프로그램 (SQL 이나 배치 프로그램 등)에 대해 성능관리를 위해서,
DBMS_APPLICATION_INFO 패키지를 활용하여 MODULE 명을 설정하거나 SQL 에 식별자를
부여하는 방법을 많이 사용한다. 앞으로 이 두 방법을 사용했을 경우에 어떻게 배치 프로그램에
서 수행되는 SQL 의 수행정보를 추출할 수 있는지 기술할 것이고, 또한 이 두 방법을 적용하지
않은 경우에 SQL 의 수행정보를 추출하는 방법으로 기술할 내용은 Oracle 의 오브젝트인
PACKAGE/PROCEDURE/FUNCTION 을 이용하여 작성된 배치 프로그램에만 적용되는 점을 미
리 알린다.
본격적으로 배치 프로그램의 SQL 에 대한 수행정보를 추출하는 방법에 대해 소개하기 전에 고
객 사에서 튜닝요청을 받았던 SQL 목록에 있던 배치 프로그램을 먼저 보도록 하자.
아래의 구문은 튜닝 요청을 받은 SQL 목록에 있던 것 중 하나이다. 그런데 SQL 을 확인해 보니
성능개선이 필요한 SQL 이 아닌, JOB 으로 수행되는 P_POS_TRAN 프로시저에 대한 튜닝요청
2. 40│2013 기술백서 White Paper
을 한 것이었다. P_POS_TRAN 프로시저는 SELECT, INSERT, UPDATE, DELETE 구문을 다양
하게 가진 약 5,000 라인의 프로그램으로, 소스 내에 또 다른 프로시저를 호출하는 등 상당히
복잡한 수행 로직을 가지고 있었다. 그런데 이렇게 복잡한 수행 로직을 가지고 있고, 점검해야
할 SQL 의 개수가 많은 프로그램에 대한 성능개선 요청을 받는 경우, 해당 프로그램에서 수행되
는 SQL 중 튜닝대상을 선별하는 것은 상당히 어려운 작업이다.
DECLARE
job BINARY_INTEGER := :job ;
next_date DATE := :mydate ;
broken BOOLEAN := FALSE ;
BEGIN P_POS_TRAN( 4 , 101 ) ; ---> 오라클 프로시저
:mydate := next_date ;
IF broken
THEN :b := 1 ;
ELSE :b := 0 ;
END IF ;
END ;
이런 경우 성능개선이 필요한 튜닝대상을 추출하는 가장 효율적인 방법은 트레이스를 통해 수행
내역을 분석하는 것이다. 그러나 앞에서 언급된 P_POS_TRAN 프로시저와 같이 데이터에 대한
입력, 변경, 삭제 작업이 있는 배치 프로그램을 운영 DB 서버에서 트레이스를 수행할 수 없다.
또한 개발 DB 서버가 아예 구성되어 있지 않거나, 개발 DB 서버에 프로그램의 테스트에 필요한
데이터가 없다면, 트레이스를 활용하여 튜닝대상을 추출하는 것은 사실상 불가능하다.
이렇게 프로그램에 트레이스를 활용하여 수행결과를 분석하는 것이 적절하지 않는 경우, 프로그
램의 성능점검을 위한 또 다른 방법으로 프로그램의 모든 SQL 에 대한 성능점검 (플랜 점검 등)
을 수행하게 된다. 그런데 이 방법은 튜닝요청을 받은 후 프로그램에 대한 성능개선안을 도출하
는데 까지 많은 시간이 소요될 것이고, 또한 성능문제를 정확하게 판단하지 못할 수 있어 비효율
적인 방법이다. 왜냐하면, 프로그램의 전체 수행시간 (Elapsed Time) 중 가장 많은 비중을 차지
하는 SQL 을 개선해야 하나, 해당 SQL 이 Loop 구문 안에서 수행되고, 1 회 수행 시 발생하는
I/O 처리량과 수행시간이 타 SQL 에 비해 적어 튜닝대상으로 추출되지 않을 수도 있기 때문이
다.
앞에서와 같이 트레이스 수행이나 배치 프로그램의 모든 SQL 에 대한 개별 성능점검을 통해 성
능개선안을 도출하는 것이 힘든 경우가 있다. 이럴 때 우리는 배치 프로그램의 튜닝대상 SQL 을
3. Part 1 ORACLE │41
선별하기 위해서 SQL 의 수행정보를 추출하는 방법으로 Oracle 이 제공하는 정보를 원활하게
조회하여 활용할 수 있다면 좀더 쉽고, 효율적이고, 빠르게 배치 프로그램에 대한 성능개선을 할
수 있을 것이다.
그럼 Oracle 이 제공하는 X$KGLRD 테이블과 SQL 의 수행내역을 조회할 수 있는 Dynamic
Performance View 를 활용하는 방법을 알아보도록 하자.
테스트를 진행하면서 내용을 확인하기 위해서 먼저 테스트 데이터를 생성하도록 하자.
Script. 배치 프로그램 테스트 데이터 생성
* 테이블 생성하기
drop table plsql_t1 purge;
create table plsql_t1
as
select level as c1, chr(65+mod(level,26)) as c2, level+99999 as c3
from dual
connect by level <= 1000000 ;
* 인덱스 생성 및 통계정보 수집하기
create index plsql_t1_idx_01 on plsql_t1 ( c1 ) ;
exec
dbms_stats.gather_table_stats(ownname=>'exem',tabname=>'plsql_t1',cascade=>true,estima
te_percent=>100) ;
* 프로시저 생성하기
drop procedure plsql_batch_1 ;
drop procedure plsql_batch_2 ;
create or replace procedure plsql_batch_1
as
begin
delete /*+ BatchTest_plsql_batch_1 */ plsql_t1 ---> SQL 에 식별자 부여
where c2 = 'aa';
commit;
4. 42│2013 기술백서 White Paper
end;
/
create or replace procedure plsql_batch_2
as
begin
dbms_application_info.set_module('BatchTest',''); ---> Module Name 설정
insert /*+ BatchTest_plsql_batch_2 */ into plsql_t1 ---> SQL 에 식별자 부여
select c1, 'a', c3
from plsql_t1
where c2 = 'A';
commit;
update /*+ BatchTest_plsql_batch_2 */ plsql_t1 ---> SQL 에 식별자 부여
set c2 = 'aa'
where c2 = 'a';
commit;
plsql_batch_1; ---> 데이터 delete
end;
/
배치 프로그램의 수행내역을 확인하기 위해서 앞에서 생성한 PLSQL_BATCH_2 프로시저를 수
행한다.
SQL> exec plsql_batch_2 ;
PLSQL_BATCH_2 의 소스 내용을 보면, DBMS_APPLICATION_INFO.SET_MODULE 으로
MODULE 명을 설정했다. 그리고 INSERT, UPDATE 구문에 SQL 설명을 가지는 주석을 추가하
였다. 프로시저에 적용한 이 두 가지는 일반적으로 배치 프로그램이나 단일 SQL 의 성능관리를
위해 사용되는 방법이다. 만약 튜닝요청을 받은 배치 프로그램에 둘 중 한 가지라도 설정되어 있
는 경우는 SQL 의 수행정보를 가지고 있는 V$SQLAREA 와 같은 Dictionary View 를 활용하여
튜닝대상을 추출할 수 있다. 그러나 둘 중 어떤 것도 설정되어 있지 않다면 튜닝대상을 추출하는
것은 어려워진다.
그럼 앞에서 언급한 프로그램이나 SQL 에 식별자를 부여한 경우와 부여하지 않은 경우에 따라
어떻게 튜닝대상을 추출할 수 있는지 알아보도록 하자.
5. Part 1 ORACLE │43
MODULE명 또는 SQL에 식별자가 있는 경우
MODULE명이 설정되어 있는 경우
PLSQL_BATCH_2 의 소스 내용을 확인해 보면, 아래와 같이 해당 배치 프로그램에 MODULE 명
을 설정하였다.
dbms_application_info.set_module('BatchTest',''); ---> Module 명 설정
Oracle 11.2.0.3 에서 테스트를 수행한 결과 PLSQL_BATCH_2 프로시저에 적용한 MODULE
명은 PLSQL_BATCH_2 프로시저에서 호출하는 PLSQL_BATCH_1 에도 적용되기 때문에, 배치
프로그램에서 수행되는 모든 SQL 의 수행정보를 V$SQL 의 MODULE 칼럼으로 조회가 가능하
다.
해당 배치 프로그램에 MODULE 명이 설정되어 있다고 가정하고, 배치 프로그램에서 수행하는
모든 SQL 중, 총 I/O 처리량이 많이 발생한 순서대로 정렬하여 추출하고자 한다면 아래의 스크
립트를 수행하면 된다.
select t1.module, t1.substr_sqltext, t1.executions, t1.buffer_gets
from (
select parsing_schema_name Schema, --> 1
module, --> 2
sql_id, --> 3
hash_value, --> 4
substr(sql_text,1,37) substr_sqltext --> 5
executions, --> 6
buffer_gets, --> 7
disk_reads, --> 8
rows_processed, --> 9
round(buffer_gets/executions,1) lio, --> 10
round(elapsed_time/executions/1000000,1) elapsed_sec, --> 11
round(cpu_time/executions/1000000,1) cpu_sec --> 12
from v$sqlarea s
where s.module = ‘BatchTest’ ---> MODULE 명으로 검색
order by 7 desc ---> 전체 I/O 처리량이 높은 순으로 정렬
) t1
where rownum <= 50 ;
6. 44│2013 기술백서 White Paper
MODULE SUBSTR_SQLTEXT EXECUTIONS BUFFER_GETS
------------- --------------------------------------- ---------- -----------
BatchTest DELETE /*+ BatchTest_plsql_batch_1 */ 1 159206
BatchTest UPDATE /*+ BatchTest_plsql_batch_2 */ 1 105014
BatchTest INSERT /*+ BatchTest_plsql_batch_2 */ 1 10901
SQL TEXT로 식별할 수 있는 경우
앞에서 PLSQL_BATCH_1, PLSQL_BATCH_2 을 생성할 때, 아래와 같이 개별 SQL 에 식별자를
추가하였다.
delete /*+ BatchTest_plsql_batch_1 */ …
insert /*+ BatchTest_plsql_batch_2 */ …
update /*+ BatchTest_plsql_batch_2 */ …
배치 프로그램의 모든 SQL 에 식별자를 추가했으므로, 아래와 같이 배치 프로그램의 모든 SQL
에 대한 수행정보를 조회할 수 있다.
select t1.module, t1.substr_sqltext, t1.executions, t1.buffer_gets
from (
select parsing_schema_name Schema, --> 1
module, --> 2
sql_id, --> 3
hash_value, --> 4
substr(sql_text,1,37) substr_sqltext, --> 5
executions, --> 6
buffer_gets, --> 7
disk_reads, --> 8
rows_processed, --> 9
round(buffer_gets/executions,1) lio, --> 10
round(elapsed_time/executions/1000000,1) elapsed_sec, --> 11
round(cpu_time/executions/1000000,1) cpu_sec --> 12
from v$sqlarea s
where s.sql_fulltext like ‘%BatchTest_plsql_batch%’ ---> SQL TEXT 로 검색
order by 7 desc
) t1
where rownum <= 50 ;
MODULE SUBSTR_SQLTEXT EXECUTIONS BUFFER_GETS
------------- --------------------------------------- ---------- -----------
7. Part 1 ORACLE │45
BatchTest DELETE /*+ BatchTest_plsql_batch_1 */ 1 159206
BatchTest UPDATE /*+ BatchTest_plsql_batch_2 */ 1 105014
BatchTest INSERT /*+ BatchTest_plsql_batch_2 */ 1 10901
MODULE명 또는 SQL에 식별자가 없는 경우
앞에서 PLSQL_BATCH_1, PLSQL_BATCH_2 프로시저 생성 시 MODULE 명 설정이나 SQL 에
식별자를 추가하지 않았다면, 어떻게 튜닝대상을 추출할 수 있을까? 이런 경우 Oracle 이 제공
하는 X$KGLRD 테이블과 DBA_OBJECTS.OBJECTID 와 V$SQL.PROGRAM_ID 으로 배치 프
로그램에서 튜닝대상 SQL 을 추출할 수 있다.
Note. Oracle 버전이 10g 이전까지는 X$KGLRD 를 활용하여 추출해야 한다. 왜냐하면, 10g 이후
버전에 V$SQL 나 V$SQLAREA 에 PROGRAM_ID 가 추가되었기 때문이다.
X$KGLRD 활용하기
아래에 X$KGLRD 의 칼럼 정보와 테스트 예제를 통해 사용방법을 알아보도록 하자.
x$kglrd 칼럼 구성 - Oracle Version: 11.2.0.3 에서 추출
Column Name DataType
----------------------- ---------------
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
KGLHDCDR RAW(4)
KGLNAOWN VARCHAR2(64)
KGLNACNM VARCHAR2(512) -----> Procedure & Function Name
KGLNACNL NUMBER
KGLNACHV NUMBER
KGLHDPDR RAW(4)
KGLDEPNO NUMBER
KGLRDHDL RAW(4)
KGLNADNM VARCHAR2(512) -----> SQL Text
KGLNADNL NUMBER
KGLNADHV NUMBER -----> SQL Hash Value
8. 46│2013 기술백서 White Paper
KGLRDFLG NUMBER
Oracle 이 제공하는 X$KGLRD 은 SQL 의 수행정보를 담고 있는데, 특정 PROCEDURE 나
FUNCTION 내에서 수행되는 SQL 에 대해 오브젝트명과 함께 확인할 수 있기 때문에, 특정 배치
프로그램에서 수행되는 모든 SQL 을 추출하고자 할 때 유용하다.
Procedure/Function 명으로 조회하기
PROCEDURE 나 FUNCTION 명은 대문자로 입력되어 있으므로 조회 시 유의하자. X$KGLRD
에서 PROCEDURE 나 FUNCTION 명으로 조회할 경우에 PLSQL_BATCH_2 프로시저에서 호출
하는 PLSQL_BATCH_1 도 같이 조회해야 전체 SQL 을 추출할 수 있다.
col kglnacnm for a15
col kglnadnm for a37
set pagesize 100
select kglnacnm, substr(kglnadnm,1,37) kglnadnm, kglnadhv
from x$kglrd
where kglnacnm in ('PLSQL_BATCH_2', 'PLSQL_BATCH_1') ;
KGLNACNM KGLNADNM KGLNADHV
--------------- ------------------------------------- ----------
PLSQL_BATCH_2 UPDATE /*+ BatchTest_plsql_batch_2 */ 3943223768
PLSQL_BATCH_2 COMMIT 255718823
PLSQL_BATCH_2 INSERT /*+ BatchTest_plsql_batch_2 */ 111618107
PLSQL_BATCH_1 COMMIT 255718823
PLSQL_BATCH_1 DELETE /*+ BatchTest_plsql_batch_1 */ 3094796916
SQL 을 SQL Text 로 조회하기
SQL Text 로 X$KGLRD 에서 조회할 경우에는 아래와 같이 수행하면 된다.
select distinct substr(kglnadnm,1,37) kglnadnm, kglnadhv
from x$kglrd
where kglnadnm like '%BatchTest_plsql_batch%';
KGLNADNM KGLNADHV
9. Part 1 ORACLE │47
------------------------------------- ----------
UPDATE /*+ BatchTest_plsql_batch_2 */ 3943223768
DELETE /*+ BatchTest_plsql_batch_1 */ 3094796916
INSERT /*+ BatchTest_plsql_batch_2 */ 111618107
SQL 을 Hash Value 로 조회하기
DB 서버를 모니터링 시 Hash_Value 를 알고 있을 때, 만약 해당 SQL 이 배치 프로그램에서 수
행되는 경우에 어떤 프로그램에서 수행된 것인지 찾아야 할 때 아래와 같이 Hash_Value 로
X$KGLRD 에서 조회하면 확인할 수 있다.
select distinct substr(kglnadnm,1,37) kglnadnm, kglnadhv
from x$kglrd
where kglnadhv = 3094796916 ;
KGLNADNM KGLNADHV
------------------------------------- ----------
DELETE /*+ BatchTest_plsql_batch_1 */ 3094796916
튜닝대상 추출하기
X$KGLRD 을 활용하여 배치 프로그램에서 수행된 SQL 을 추출 후, 아래와 같이 각 SQL 의 수
행정보를 분석 후 튜닝대상을 추출하면 된다.
select t1.module, t1.substr_sqltext, t1.executions, t1.buffer_gets
from (
select parsing_schema_name Schema, --> 1
module, --> 2
sql_id, --> 3
hash_value, --> 4
substr(sql_text,1,37) substr_sqltext, --> 5
executions, --> 6
buffer_gets, --> 7
disk_reads, --> 8
rows_processed, --> 9
round(buffer_gets/executions,1) lio, --> 10
round(elapsed_time/executions/1000000,1) elapsed_sec, --> 11
round(cpu_time/executions/1000000,1) cpu_sec --> 12
from v$sqlarea s
10. 48│2013 기술백서 White Paper
where s.hash_value in (3943223768, 3094796916, 111618107)
order by 7 desc
) t1
where rownum <= 50 ;
MODULE SUBSTR_SQLTEXT EXECUTIONS BUFFER_GETS
------------- --------------------------------------- ---------- -----------
BatchTest DELETE /*+ BatchTest_plsql_batch_1 */ 1 159206
BatchTest UPDATE /*+ BatchTest_plsql_batch_2 */ 1 105014
BatchTest INSERT /*+ BatchTest_plsql_batch_2 */ 1 10901
DBA_OBJECTS & V$SQLAREA 활용하기
Oracle 버전이 10g 이후부터 V$SQL 과 V$SQLAREA 에 PROGRAM_ID 칼럼이 추가되었다.
PROGRAM_ID 칼럼은 DBA_OBJECTS 의 OBJECT_ID 칼럼과 연결이 된다. 그러므로
PROCEDURE 나 FUNCTION 으로 작성된 배치 프로그램의 경우, DBA_OBJECTS 와 V$SQL
[V$SQLAREA]을 통해 튜닝대상 SQL 을 추출할 수 있다.
먼저 배치 프로그램 명으로 OBJECT_ID 를 추출한다.
select object_name, object_id
from dba_objects
where object_name IN ('PLSQL_BATCH_1','PLSQL_BATCH_2') ;
OBJECT_NAME OBJECT_ID
-------------------- ----------
PLSQL_BATCH_1 61738
PLSQL_BATCH_2 61739
DBA_OBJECTS 에서 추출된 OBJECT_ID 값으로 V$SQLAREA 의 PROGRAM_ID 와 연결하여
조회하면 아래와 같이 SQL 을 추출할 수 있다.
col substr_text for a30
col module for a15
select substr(sql_text,1,30) substr_text, module, program_id
from v$sqlarea
where program_id in (61738, 61739) ;
11. Part 1 ORACLE │49
SUBSTR_TEXT MODULE PROGRAM_ID
------------------------------ --------------- ----------
UPDATE /*+ BatchTest_plsql_bat BatchTest 61739
DELETE /*+ BatchTest_plsql_bat BatchTest 61738
INSERT /*+ BatchTest_plsql_bat BatchTest 61739
앞에서 DBA_OBJECTS 와 V$SQLAREA 를 활용하여, 해당 배치 프로그램에서 수행한 모든
SQL 에 대한 수행정보를 아래와 같이 조회할 수 있다. 그리고 조회된 정보를 면밀히 분석하면
배치 프로그램의 SQL 중 튜닝대상을 추출하는 것은 그리 어렵지 않을 것이다.
select t1.module, t1.substr_sqltext, t1.executions, t1.buffer_gets
from (
select parsing_schema_name Schema, --> 1
module, --> 2
sql_id, --> 3
hash_value, --> 4
substr(sql_text,1,37) substr_sqltext, --> 5
executions, --> 6
buffer_gets, --> 7
disk_reads, --> 8
rows_processed, --> 9
round(buffer_gets/executions,1) lio, --> 10
round(elapsed_time/executions/1000000,1) elapsed_sec, --> 11
round(cpu_time/executions/1000000,1) cpu_sec --> 12
from v$sqlarea s
where s.program_id in ( select object_id
from dba_objects
where object_name in ( 'PLSQL_BATCH_1',
'PLSQL_BATCH_2') )
order by 7 desc
) t1
where rownum <= 50 ;
MODULE SUBSTR_SQLTEXT EXECUTIONS BUFFER_GETS
------------- --------------------------------------- ---------- -----------
BatchTest DELETE /*+ BatchTest_plsql_batch_1 */ 1 159206
BatchTest UPDATE /*+ BatchTest_plsql_batch_2 */ 1 105014
BatchTest INSERT /*+ BatchTest_plsql_batch_2 */ 1 10901