2. 일반적인 반복문을 통한 개별 INSERT -- 24초 경과 declare cnt number := 0; begin while (cnt < 1000000) loop cnt := cnt + 1; insert /*+ loop insert */ into bulktest values ( cnt, cnt || '길동', '개발직'); end loop; commit; end;
3. Shared Pool에서 파싱된 SQL문 및 실행횟수 확인
-- 오라클이 INSERT 구문을 내부적으로 바인드 변수 처리를 하여 SQL문장은 하나, 실행횟수는 1,000,000임을 알수 있습니다. select substr(sql_text,1,60) "sql", count(*), sum(executions) "총실행횟수" from v$sqlarea where sql_text like '%loop%' group by substr(sql_text,1,60) having count(*) > 0 order by 2;
select count(1) from bulktest; truncate table bulktest;
4. FORALL을 이용한 BULK INSERT
alter system flush SHARED_POOL;
-- 2초 declare type bulktype is table of bulktest%rowtype index by binary_integer; mydata bulktype; begin for i in 1 .. 1000000 loop mydata(i).empno := i; mydata(i).ename := i || '길동'; mydata(i).job := '개발직'; end loop;
forall i in 1 .. 1000000 insert /*+ bulk insert */ into bulktest values mydata(i); commit; end;
5. Shared Pool에서 파싱된 SQL문 및 실행횟수 확인
-- 오라클이 INSERT 구문을 내부적으로 바인드 변수 처리를 하여 SQL문장은 하나, 실행횟수도 하나임을 확인할 수 있습니다. select substr(sql_text,1,60) "sql", count(*), sum(executions) "총실행횟수" from v$sqlarea where sql_text like '%bulk%' group by substr(sql_text,1,60) having count(*) > 0 order by 2;
select count(1) from bulktest; truncate table bulktest;
테이블 통계정보는 데이터 건수, 블록의 수, 레코드의 평균 길이, 마지막 통계정보 생성일자 등이다. 이러한 통계정보를 이용하여 옵티마이저는 실행계획상의 다양한 연산(Operations) 비용(Cost)을 다른 통계정보를 바탕으로 산정하며 오퍼레이션마다 추출되는 로우(ROW, RECORD)의 수를 예측 합니다.
테이블 또는 칼럼의 통계정보는 USER_TAB_STATISTICS, USER_TAB_COL_STATISTIC, USER_TABLES, USER_TAB_COLUMNS와 같은 딕셔너리 뷰에서 확인할 수 있습니다.
풀 테이블 스캔시 테이블 액세스의 비용(COST)은 DB_FILE_MULTIBLOCK_READ_COUNT 파라미터와 테이블의 데이터 블록 수를 이용하여 계산을 하게 된다. 이 파라미터는 Full Scan(Full Table Scan, Index Fast Full Scan) 수행 시 한번에 읽어 들일 Block 수를 결정하며 Full Scan의 성능을 제어하는 목적으로 사용된다. System Statistics가 있다면 이 파라미터는 고려대상이 아니며 없는 경우 이 파라미터 값을 기준으로 Full Scan을 수행 합니다.
ORACLE 초기화 파라미터인 OPTIMIZER_MODE는 오라클 옵티마이저 최적의 Access 경로를 선택하는 기본 설정이 됩니다. 즉 옵티마이저가 어떠한 기준으로 최적화된 Access 경로를 추출하는지 결정하는 방법에 대해 제시한다고 볼 수 있습니다.
초기화 파라미터 Optimizer_Mode?
[문서로 지원하는것]
ALL_ROWS : 테이블의 통계 정보의 유무와 상관없이 모든 SQL 문에 대해 CBO(Cost Based Optimizer, 비용기반 옵티마이저)를 이용하며 최대 처리량을 목표로 한다. 최대 처리량을 목표로 한다는 것은 전체 SQL문의 결과를 완료하는데 걸리는 시간이 적게 드는 것을 목표로 한다는 것 입니다. (Oracle19C의 기본값으로 테이블에 대한 통계정보가 없더라도 CBO로 동작된다.)
FIRST_ROWS_(1|10|100|1000): 통계 정보의 유무와 상관없이 모든 SQL 문에 대해 CBO를 이용하며 처음 N개의 레코드를 가장 빨리 추출할 수 있는 최적의 실행 경로를 찾습니다.
FIRST_ROWS: 옵티마이저는 첫 번째 레코드의 추출을 가장 빠르게 하는 최적의 실행경로를 찾습니다.
[문서로 지원하지 않지만 사용은 가능한것]
RULE : 통계정보의 유무와 관계 없이 모든 SQL에 대해 RBO를 사용하도록 한다. Oracle10g R2 이후 더 문서로 이상 지원은 하지 않지만 사용은 가능하다. RBO에서 CBO로 마이그레이션을 위해 아직까지 사용은 가능하지만 언젠가는 없어지므로 RBO를 사용하고 있다면 CBO로 전환이 필요하다.
CHOOSE : 오라클 옵티마이저는 CBO(Cost-Based Optimizer)와 RBO(Rule-Based Optimizer)중 하나를 선택한다. 통계 정보의 이용가능 여부에 따라 결정하는데 통계정보가 이용 가능하면 CBO를 사용하고 불가능한 경우에는 RBO를 사용한다. 만약 데이터딕셔너리에 액세스 되는 테이블 중 적어도 하나의 테이블에 대한 통계 정보가 있는 경우엔 CBO를 사용하며 최대 처리량(ALL_ROWS)을 목표로 최적화를 수행한다.
딕셔너리에 약간의 통계 정보만을 가지고 있다면 CBO가 사용될 수 있지만 옵티마이저는 통계 정보가 없는 부분에 대해서는 통계 정보를 추측해야 하며 이는 최선의 실행 계획이라기보다 차선의 실행 계획이라고 볼 수 있다.
만약 Access되는 테이블에 대한 통계 정보가 하나도 없다면 RBO를 사용한다.
오라클 11g 에서는 지난 방식이지만 지원은 하고 있다. 문서로는 지원하지 않지만 여전히 사용은 가능한 방식이다.
초기 매개 변수 파일(init.ora)에서 위의 파라미터를 변경할 수도 있으며 alter session set optimizer_mode, alter system set 명령을 이용할 수도 있다. 또한 명령 행에서 Oracle Hint 구문을 이용하여 Optimizer Mode를 변경하는 것도 가능 합니다.
[힌트 사용 형식]
/*+ CHOOSE */
/*+ RULE */
/*+ ALL_ROWS */
/*+ FIRST_ROWS_[(n)] */
주의 : FIRST_ROWS 힌트와 집합함수를 같이 사용하면 FIRST_ROWS 힌트는 무시된다.
결국 힌트는 개별 SQL문에 대해 CBO의 목표를 설정하기 위해 사용하는 것이다. 각 SQL문에서 힌트를 사용하게 되면 초기 파라미터에서 설정한 것보다 우선 적용 됩니다.
SQL> alter session set optimizer_mode = first_rows_1;
세션이 변경되었습니다.
SQL> show parameter optimizer_mode
NAME TYPE VALUE
------------------------------------ -----------
optimizer_mode string FIRST_ROWS_1
-- 설정할 수 없는 값을 주면 현재 시스템에서 사용 가능한 옵티마이저 모드를 확인할 수있다.
SQL> alter session set optimizer_mode = very_very_fast;
ERROR:
ORA-00096: 부적당한 VERY_VERY_FAST 값이 optimizer_mode 매개변수에 있고, first_rows_1000, first_rows_100, first_rows_10, first_rows_1, first_rows, all_rows, choose, rule의 사이에 있어야 합니다
옵티마이저 모드에 따라서 만들어지는 실행계획이 달라지므로 성능에 영향을 미칩니다.
CBO가 사용하는 통계 정보는 데이터 딕셔너리에 저장되며 DBMS_STATS 패키지나 ANALYZE 명령을 이용하여 스키마 객체에 대한 데이터의 분포도 등을 구할 수 있다. SQL문을 구사하면서 최적화된 CBO의 사용을 위해서는 반드시 통계 정보를 생성해야 합니다.
Optimizer Mode Setting 방법
Instance 수준의 셋팅 방법
예) OPTIMIZER_MODE=FIRST_ROWS
Session/System 수준의 셋팅 방법
예) alter session set optimizer_mode=rule과 같이 정의하면 해당 세션이 끝나기 전까지는 규칙 기반(RULE-BASED) 옵티마이저 모드를 이용하게 된다.
예) alter system set optimizer_mode=first_rows과 같이 정의하면 전체 오라클 시스템에서 FIRST_ROWS 옵티마이저 모드를 이용하게 된다.
Statement 수준의 셋팅 방법
예) SELECT /*+ FIRST_ROWS */
ENAME,
SAL,
JOB
FROM EMP
WHERE SAL > (SELECT MAX(SAL)
FROM EMP
WHERE DEPTNO = 10)
[실습]
Oracle Trigger를 이용하여 SCOTT 계정으로 접속하는 모든 사용자들의 Optimizer_Mode를 ALL_ROWS로 변경하세요.
CREATE OR REPLACE TRIGGER Tr_setOptimizerMode AFTER LOGON ON DATABASE BEGIN IF USER LIKE 'SCOTT%' THEN EXECUTE IMMEDIATE 'alter session set optimizer_mode=all_rows'; END IF; END; /
[실습]
동일한 쿼리문에 대해 Optimizer_Mode를 FIRST_ROWS, ALL_ROWS 변경한 후 실행계획 및 통계정보를 확인하고 SQL 성능에 대해 고려해 보세요.
2000만건 있는 사원테이블과 7건 있는 부서테이블을 조인하여 전체사원의 사원명, 부서명을 추출 합니다.
SQL> set autotrace traceonly; SQL> set timing on SQL> ALTER SESSION SET OPTIMIZER_MODE=ALL_ROWS;
세션이 변경되었습니다.
경 과: 00:00:00.00 SQL> SELECT E.EMPNO, E.ENAME, D.DNAME 2 FROM MYEMP1 E, MYDEPT1 D 3 WHERE E.DEPTNO = D.DEPTNO;
20000004 행이 선택되었습니다.
경 과: 00:02:00.50
Execution Plan ---------------------------------------------------------- Plan hash value: 2850841177
Predicate Information (identified by operation id): ---------------------------------------------------
4 - access("E"."DEPTNO"="D"."DEPTNO")
Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 3449522 consistent gets 0 physical reads 0 redo size 822344313 bytes sent via SQL*Net to client 14667112 bytes received via SQL*Net from client 1333335 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 20000004 rows processed
[실습]
이번에는 처음 10개만 가져오기 위해 인라인뷰, ROWNUM을 이용하여 작성한 쿼리 입니다. ALL_ROWS와 FIRST_ROWS에 따라 생성되는 실행계획 및 통계정보를 확인하세요. 통계정보의 consistent gets 에서 SELECT시 읽어 들이는 블록 수를 확인해보세요, 읽어들이는 블록이 수가 적을수록 좋은 실행계획 입니다.
ALTER SESSION SET OPTIMIZER_MODE=ALL_ROWS;
SELECT * FROM ( SELECt ROWNUM RNUM, T.* FROM ( SELECT E.EMPNO, E.ENAME, D.DNAME FROM MYEMP1 E, MYDEPT1 D WHERE E.DEPTNO = D.DEPTNO ) T ) WHERE RNUM >= 1 AND RNUM <= 10;
10 행이 선택되었습니다.
경 과: 00:00:04.69
Execution Plan ---------------------------------------------------------- Plan hash value: 3222485226
Predicate Information (identified by operation id): ---------------------------------------------------
1 - filter("RNUM"<=10 AND "RNUM">=1) 3 - access("E"."DEPTNO"="D"."DEPTNO")
Statistics ---------------------------------------------------------- 5 recursive calls 0 db block gets 200314 consistent gets 0 physical reads 0 redo size 1193 bytes sent via SQL*Net to client 573 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10 rows processed
ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS;
first_rows는 한건만 가지고 오는데 있어 가장 효율적인 실행계획을 만드는 옵티마이저 모드이므로 메모리에 전체 테이블을 올려서 조인하는 해시조인, 머지조인보다는 중첩루프 조인을 선호하게 됩니다.
SELECT * FROM ( SELECt ROWNUM RNUM, T.* FROM ( SELECT E.EMPNO, E.ENAME, D.DNAME FROM MYEMP1 E, MYDEPT1 D WHERE E.DEPTNO = D.DEPTNO ) T ) WHERE RNUM >= 1 AND RNUM <= 10;
경 과: 00:00:08.46
Execution Plan ---------------------------------------------------------- Plan hash value: 686395518
