728x90

제품 : ORACLE SERVER


작성날짜 : 2004-08-13

PARALLEL DML 사용 방법
======================

Purpose

Oracle8 version부터는 DML 문장에도 Parallel 기능을 사용할 수 있다. 
그 기능을 사용하는 방법을 확인하고 적용해 보자.

SCOPE

8~10g Standard Edition 에서는 지원하지 않습니다.

Explanation


Oracle8에서 대용량의 Database를 지원하기 위한 방법의 하나로 
Parallel DML이 있다. Oracle8은 Oracle7의 Parallel Query Option과 
비슷한 구조로 Parallel DML을 실행한다. 
Parallel하게 조회를 하면 테이블에 대한 부분적인 scan을 하는 
동시에 여러 개의 parallel server process 가 블럭 범위에 의해 
동적으로 나누어진다. Parallel DML도 DML 작업을 parallel 하게 
여러 개의 process를 동시에 실행하여 처리할 수 있는 방법으로 
batch job에서 실행하는 partitioned table이나 large table의 경우 
performance를 크게 향상시킬 수 있는 방법이다.

1. 실행 방법

먼저 다음의 parameter 를 init<SID>.ora file에 setting해 준다.

PARALLEL_MAX_SERVERS - Parallel server process의 최대 개수
(default 5)
PARALLEL_MIN_SERVERS - Oracle startup 시 뜨는 parallel process 개수 
(default 0)

Parallel DML을 사용을 위해서는 먼저 해당 세션에서 parallel DML을
enable 시키는 명령문을 실행해야 한다.

alter session enable parallel dml; -> enabling parallel dml
alter session disable parallel dml; -> disabling parallel dml

parallel dml 을 enable하거나 disable 하기 전에는 반드시 commit 
이나 rollback 을 해 주어야 한다. 

SQL> insert into dept values (50,'abc','abc');
1 row created.

SQL> alter session enable parallel dml;
ERROR:
ORA-12841: Cannot alter the session parallel DML state within a 
transaction

SQL> commit;
SQL> alter session enable parallel dml;
Session altered.
SQL> update /*+ parallel(testdml,10) */ testdml set c = '2';

serial update를 한 후에 같은 object를 다시 parallel하게 udpate
할 수는 없다. Parallel dml은 transaction의 첫번째 DML이 되어야 한다. 

SQL> alter session enable parallel dml;
Session altered.
SQL> update testdml set c = '1';
999998 rows updated.
SQL> update /*+ parallel(testdml,10) */ testdml set c = '2';
update /*+ parallel(testdml,10) */ testdml set c = '2'
*
ERROR at line 1:
ORA-12839: cannot modify an object in parallel after modifying it

default 는 disable이므로 parallel dml을 사용하기 전에 반드시 
enabling 해주어야 한다. Disable parallel dml mode의 세션에서는 
parallel dml을 실행하여도 에러 없이 serial하게 실행된다. 

2. Space 의 관리 

(1) Create table .. as select, create index 작업의 저장 영역

Parallel 하게 table 이나 index를 생성하는 경우 create 명령의 
storage 절의 initial 값으로 temporary segment를 생성한다. 
Initial 값이 5M이고 parallel degree 를 10으로 설정하여 table을 
생성하는 경우는 5M 씩 10개의 temporary segment가 만들어지면서 
data를 저장한다. 그리고 Parallel coordinator 가 temporary 
segment를 병합한다.

(2) Temporary segment 의 free space 관리

Data의 loading 작업이 끝난 후에 각 temporary segment에서 
사용되지 않은 free space를 가진 extent가 tablespace level에서 
설정한 minimum extent 값보다 크면 parallel coordinator 가 
모든 temporary segment를 병합할 때 사용하지 않은 영역은 잘라낸다. 
잘려진 영역은 datafile의 free space로 돌려지며 다른 object에 의해 
사용될 수 있다. 그러나 연속된 영역이 아니므로 하나의 큰 segment로 
합쳐질 수 없다. (external fragmentation)
위의 경우에서 free space를 가진 extent가 minimum extent 값보다
작으면 parallel coordinator 가 모든 temporary segment를 병합할 때 
사용하지 않은 영역도 포함시킨다. 이 부분은 후에 data가 insert될 때 
사용된다. (internal fragmentation)

3. 제한 사항 

(1) UPDATE 와 DELETE 문은 non-partitioned table에서는 parallel 
하게 실행되지 않는다.

(2) Initial parameter Row_locking = intend 가 지정된 경우는 
Parallel insert, update, delete가 실행되지 않는다.

(3) Trigger가 걸린 table에 대해서는 parallel dml을 실행할 수 없다. 
trigger가 걸린 table에 사용할 때에는 trigger를 disable해야 한다.

(4) 같은 transaction 내에서 parallel DML 문장에 의해 update된 
table에 대해 다시 dml 문을 실행할 수는 없다. Parallel DML 문장 
이후에는 반드시 commit을 해야 한다.

(5) self-referential integrity, delete cascade 등에서는 parallel 
DML을 사용할 수 없다.

(6) Parallel DML은 object , LOB column 을 가진 table에서는 
사용할 수 없다. 

(7) Distributed transaction 에서는 parallel DML을 사용할 수 없다. 

(8) Clustered table에서는 parallel DML을 사용할 수 없다.

728x90
저작자표시 비영리 변경금지

'Oracle > oracle' 카테고리의 다른 글

Append 힌트를 사용하려면 테이블을 NOLOGGING으로 설정해야 한다는 논란  (0) 2011.07.06
sqlplus를 사용한 csv 파일 내려받기  (0) 2011.06.27
Isolation Level 비교  (0) 2011.06.12
읽기 일관성 - Multiversion Concurrency Control  (0) 2011.06.10
Range 파티션 테이블에 파티션을 추가할 때 확인사항  (0) 2011.06.10
728x90

Oracle Database Isolation Levels

Isolation LevelDescription

Read committed (default)

This is the default transaction isolation level. Each query executed by a transaction sees only data that was committed before the query (not the transaction) began. An Oracle Database query never reads dirty (uncommitted) data.

Because Oracle Database does not prevent other transactions from modifying the data read by a query, that data can be changed by other transactions between two executions of the query. Thus, a transaction that runs a given query twice can experience both nonrepeatable read and phantoms.

Serializable

Serializable transactions see only those changes that were committed at the time the transaction began, plus those changes made by the transaction itself through INSERT, UPDATE, and DELETE statements. Serializable transactions do not experience nonrepeatable reads or phantoms.

Read-only

Read-only transactions see only those changes that were committed at the time the transaction began and do not allow INSERT, UPDATE, and DELETE statements.



DB2 Isolation Levels

ANSI SQL Isolation LevelDB2 UDB equivalent
SERIALIZABLERepeatable read (RR)
REPEATABLE READRead stability (RS)
READ COMMITTED (default)Cursor stability (CS)
Currently Committed (CC)
READ UNCOMMITTEDUncommitted read (UR)


Informix Isolation Levels
http://www.database.sarang.net/?inc=read&aid=571&criteria=informix&subcrit=&id=947&limit=20&keyword=&page=70

728x90
저작자표시 비영리 변경금지

'Oracle > oracle' 카테고리의 다른 글

sqlplus를 사용한 csv 파일 내려받기  (0) 2011.06.27
PARALLEL DML 사용 방법  (0) 2011.06.18
읽기 일관성 - Multiversion Concurrency Control  (0) 2011.06.10
Range 파티션 테이블에 파티션을 추가할 때 확인사항  (0) 2011.06.10
CTAS를 통한 테이블 복제시 참고사항  (0) 2011.05.16
728x90

Multiversion Concurrency Control


Oracle은 트랜잭션 수준의 읽기 일관성을 제공한다. Oracle을 일관성 있는 데이터 조회를 제공하기 위해 롤백(=언두) 세그먼트에 정보를 저장한다. 롤백 세그먼트에는 커밋하지 않았거나 최근에 커밋한 트랜잭션에 의해 변경된 데이터가 보관되어 있다. 다음은 롤백 세그먼트의 데이터를 사용하여 일기 일관성을 어떻게 제공하는지를 도식화한 것이다.


Description of Figure 13-1 follows


쿼리를 실행하면 실행 시점 SCN(system change number)과 데이터 블록의 SCN과 비교하여 실행 시점 이전에 커밋된 데이터 블록만을 읽는다. 만일 SCN이 10024인 시점에 데이터가 변경되었다면 롤백 세그먼트로의 데이터로부터 SCN이 10023인 시점 이전에 커밋된 데이터 블록을 읽어와 결과값을 반환한다.


만일 다른 트랜잭션에 의해 언두 세그먼트의 데이터가 덮어씌워지는 경우 ORA-01555: snapshot too old 오류가 발생한다. 이러한 경우 롤백 세그먼트의 사이즈를 크게 늘리거나 트랜잭션이 처리하는 데이터 범위를 축소해야한다.


http://kr.forums.oracle.com/forums/thread.jspa?messageID=1698897

http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/consist.htm#BABIJEJI

http://ukja.tistory.com/178

http://en.wikipedia.org/wiki/Multiversion_concurrency_control

728x90
저작자표시 비영리 변경금지

'Oracle > oracle' 카테고리의 다른 글

PARALLEL DML 사용 방법  (0) 2011.06.18
Isolation Level 비교  (0) 2011.06.12
Range 파티션 테이블에 파티션을 추가할 때 확인사항  (0) 2011.06.10
CTAS를 통한 테이블 복제시 참고사항  (0) 2011.05.16
Windows Vista, Server 2008에 10g 클라이언트 설치  (0) 2011.05.14
728x90

Case 1) 테이블이 2009년, 2011년 월별로 파티셔닝 되어있다. 2010년에 대할 월별 파티션을 추가하려면?

2009년 12월. 2011년 1월 파티션이 각각 다음과 같은 형식으로 되어있다고 가정한다.


CREATE TABLE test

....

PARTITION P_200912 VALUES LESS THAN (20091232),

PARTITION P_201101 VALUES LESS THAN (20110132)

....


2009년 12월 이후 데이터는 2011년 1월 파티션에 저장되어 있으므로 2010년 데이터를 파티션으로 생성하려면 2011년 1월 파티션을 나눈다.


ALTER TABLE test SPLIT PARTITION P_201101 AT (20100132) INTO (PARTITION P_201001,PARTITION P_201101);



Case 2) 테이블이 2011년 4월까지 파티셔닝 되어있고 MAXVALUE 파티션이 존재한다. 2011년 5월, 6월 파티션을 추가하려면?

MAXVALUE 파티션이 있을 경우 파티션을 추가할 수 없다. SPLIT으로 MAXVALUE 파티션을 나누어야 한다.

728x90
저작자표시 비영리 변경금지

'Oracle > oracle' 카테고리의 다른 글

Isolation Level 비교  (0) 2011.06.12
읽기 일관성 - Multiversion Concurrency Control  (0) 2011.06.10
CTAS를 통한 테이블 복제시 참고사항  (0) 2011.05.16
Windows Vista, Server 2008에 10g 클라이언트 설치  (0) 2011.05.14
Static vs. Dynamic SQL  (0) 2011.04.21
728x90

CTAS 를 통한 테이블 복제시 제약 조건

 

   Local  Remote  
 Column Name, Type, Length    그대로 Copy 됨
 Column Default Value     No
 Index     No
 Constraint     No
 Not Null ( PK 에 의한 Not Null 포함 )    그대로 Copy 됨그대로 Copy 됨
 Grant     No
 Synonym     No
 Trigger     No

 

테스트 결과는 첨부 화일 참조 바랍니다.

 

1. CTAS 의 경우 Parallel Hint 는
   CT(Create Table) 에서는 사용하여도 의미가 없고(ORACLE 이 무시함),
   AS 에서만 Parallel Hint 를 사용한다.
   (As Select /*+ paralllel(a) parallel(b) */ * from tab1 a, tab2 b)
   식으로 사용한다.

2. 캐릭터셋의 다른 DBMS 간의 CTAS 사용시 Column Size 가 달리 될수 있다.
   [ 참고 - http://cafe.naver.com/prodba/2014 ]
   이 경우 _keep_remote_column_size=true 로 설정 후 DB Restart 후에 CTAS 를 사용하면 된다고함
   _keep_remote_column_size 파라미터의 의미 : remote column size does not get modified
   Default 값은 False 임  

3. 그럼 가장 빠르게 테이블 복제 [ Export / Import 제외 ]
  
  3.1 Create Table COPY_Table as select * from Source_Table@remote where 1=2
   를 통해서 테이블 껍데기만 Copy 후에
  3.2 alter session enable parallel dml;
  3.3 insert /*+ parallel(Copy_Table, 10) */ into Copy_Table nologging
      select /*+ parallel(Source_table, 10) */ * from Source_Table@remote ;
  3.4 Creae index ... Nologging PARALLEL ;
      CREATE INDEX XAK_COPY_Table ... NOLOGGING PARALLEL ;
  3.5 ALTER index ... LOGGING NOPARALLEL ;
      ALTER index XAK_COPY_Table logging noparallel ;
  3.6 통계정보 수집
     exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'COPY_Table ');
  3.7 기타 추가 작업
     Column Default Value, Index, Constraint, Grant, Synonym, Trigger 

4. CTAS 를 쓸것인가 ? Export/Import 를 쓸것인가 ?
   CTAS - Parallel 처리가 가능하다는 강점이 있다.
          단. DB Link 를 통해서 통신 하기 때문에, Network 로 근거리(한국 내 정도 ^^;)어야 하고,
              양쪽 DB 의 Characterset 이 동일 해야 한다. 기타 DB LInk 사용에 따른 제약이 없어야 한다.
   Export/Import - Dump 화일 생성을 위한 저장할 공간이 필요하다.
                   양쪽 DB 의 Characterset 이 다르더라도, Sub Set 에서 Super Set 으로 이관시 아무런
                   문제가 되지 않는다. 비교적 먼거리 시에는 Remote 에서 Dump 화일 생성, ftp 전송,
                   Local Import 수행이 가능하다.


[출처] CTAS 를 통한 테이블 복제시 제약 조건|작성자 타락천사


728x90
저작자표시 비영리 변경금지

'Oracle > oracle' 카테고리의 다른 글

읽기 일관성 - Multiversion Concurrency Control  (0) 2011.06.10
Range 파티션 테이블에 파티션을 추가할 때 확인사항  (0) 2011.06.10
Windows Vista, Server 2008에 10g 클라이언트 설치  (0) 2011.05.14
Static vs. Dynamic SQL  (0) 2011.04.21
베이스 테이블 없이 VIEW 강제로 생성하기  (0) 2011.04.21
728x90

Windows Server 2008에 Windows XP용 오라클 클라이언트 설치시 javaw.exe 오류가 발생한다.

이런 경우 silent 모드로 설치하면 굳이 Vista/Server 2008용 클라이언트를 받을 필요 없이 설치가 가능


silent 모드로 설치하기 위해서는 응답파일을 사용해야하며 이는 클라이언트 설치파일에 포함되어 있다.

클라이언트 파일 압축을 풀고 10201_client_win32\client\response 디렉토리에 clientadmin.rsp라는 파일을 열어 아래 내용을 수정한다.

 

#------------------------------------------------------------------------------

#Name       : ORACLE_HOME

#Datatype   : String

#Description: Complete path of the Oracle Home.

#Example    : ORACLE_HOME = "/product/10.2.0/client"

#------------------------------------------------------------------------------

ORACLE_HOME="c:\oracle\product\10.2.0\client"

#------------------------------------------------------------------------------

#Name       : ORACLE_HOME_NAME

#Datatype   : String

#Description: Oracle Home Name. Used in creating folders and services.

#Example    : ORACLE_HOME_NAME = "OraClient10ghome1"

#------------------------------------------------------------------------------

ORACLE_HOME_NAME="OraClient10ghome1"


수정이 끝나면 오라클 클라이언트 파일 압축을 풀어놓은 디렉토리로 이동하여 다음과 같이 수행한다.

setup.exe -silent -responsefile <응답파일위치>

728x90
저작자표시 비영리 변경금지

'Oracle > oracle' 카테고리의 다른 글

Range 파티션 테이블에 파티션을 추가할 때 확인사항  (0) 2011.06.10
CTAS를 통한 테이블 복제시 참고사항  (0) 2011.05.16
Static vs. Dynamic SQL  (0) 2011.04.21
베이스 테이블 없이 VIEW 강제로 생성하기  (0) 2011.04.21
Optimizing GROUP and ORDER BY  (0) 2011.04.13
728x90

Static vs. Dynamic SQL

  • 하드 파싱을 최소화하기 위해 Dynamic SQL 대신 Static SQL 을 사용하라는 표현을 흔히 사용하는데,
    용어를 제대로 사용하고 있는지 확인해 볼 필요가 있다.
    *_조건절에 바인드 변수를 사용하면 Static SQL, Literal 상수 값을 사용하면 Dynamic SQL 로 뷴류 하는 
    튜닝 교재들이 있어 이런 혼선이 빚어졌다고 생각한다{_}.*

(1) Static SQL

  • Static SQL 이란, String 형 변수(값을)를 담지 않고 코드 사이에 직접 기술한 SQL 문을 말한다.
    다른 말로 'Embedded SQL' 이라고도 한다.
    Init main()
\{ 
   Printf("사번을 입력하시오 : ");
   Scanf("%d",&empno);
   EXEC SQL WHENEVER NOT FOUND GOTO notfound;
   EXEC SQL SELECT ENAME INTO :ename
              FROM EMP
              WHERE EMPNO = :empno;
   Printf("사원명 : %s.\n",ename);
Notfound:
   Printf("%d는 존재하지 않는 사번입니다..\n",empno);
\}
  • 여기서 보듯이 SQL 문을 String 변수에 담지 않고 마치 예약된 키워드처럼 C/C++ 코드 사이에 섞어서 기술하고 있다.
    Static SQL 이든 Dynamic SQL 이든 PreCompile 단게를 거치고 나며 String 변수에 
    담기기는 마찬가지지만 Static SQL 은 런타임 시에 절대 변하지 않으므로 PreCompile 단계에서 
    구문 분석, 유효 오브젝트 여부, 오브젝트 액세스 권한 등을 체크하는 것이 가능하다.

(2) Dynamic SQL

  • Dynamic SQL 이란, String 형 변수에 담아서 기술하는 SQL문을 말한다.
    String 변수를 사용하므로 조건에 따라 SQL 문을 동적으로 바꿀 수 있고, 
    또는 런타임 시에 사용자로부터 SQL문의 일부 또는 전부를 입력 받아서 실행 할 수도 있다. 
    따라서 PreCompile 시 Syntax, Semantics 체크가 불가능하다.
    Int main()
\{
Char select_stmt\[50\] = "SELECT ENAME FROM EMP WHERE EMPNO = :empno";
// scanf("c", &select_stmt) ; ? SQL 문을 동적으로 입력 받을 수도 있음
EXEC SQL PREPARE sql_stmt FROM :select_stmt ;
EXEC SQL DECLARE emp_cursor CURSOR SQL sql_stmt ;
EXEC OPEN emp_cursor USING :empno ;
EXEC FETCH emp_cursor INTO :ename ;
EXEC CLOSE emp_cursor ;
Printf("사원명 : %s.\n", ename);
\}
  • JAVA, Delphi, Vaisual Basic 에서는 Static SQL 을 작성할 수 있는 방법이 제공되지 않는다.
    모두 String 변수에 담아서 실행하는 것이다. 따라서 이들 언어에서 작성된 SQL은 모두 Dynamic SQL 이다. 
    그런데도 Dynamic SQL 로 작성하지 말라고 한다면 어불성설이다.
  • Toad, Orange, SQL*PLUS 과 같은 ad-hoc 쿼리 툴에서 작성하는 SQL도
    모두 Dynamic SQL 이라고 보면 틀림없다.
  • Statics(=embedded)SQL 을 지원하는 개발 언어로는 Powerbuilder, PL/SQL, Pro*C, SQLJ 정도가 있다.

    (3) 일반프로그램 언어에서 SQL 작성법

    (4) 문제의 본질은 바인드 변수 사용 여부

  • 라이브러리 캐시 효율을 논할 때 초점은 바인드 변수 사용 여부에 맞춰져야 한다.
    Dynamic SQL 을 사용해 문제가 되는 것이 아니라 바인드 변수를 사용하지 않았을 때 문제가 되는 것이다.
  • 바인드 변수를 사용하지 않고 Literal 값을 SQL 문자열에 결합하는 방식으로 개발했을 때,
    반복적인 하드파싱으로 성능이 얼마나 저하되는지, 그리고 그 때문에 라이브러리 캐시에 얼마나 심한 경합이 발생하는지, 
    그 원리에 대해서는 앞에서 충분히 설명했다.
  • 다시 한번 강조하지만, 바인드 변수 사용 여부로 Static과 Dynamic 을 구분하는 것은 잘못된 것이므로 용어 사용에 주의하자.

참고 )

1.http://oracleclub.com/article/19612 
([강정식의 오라클 이야기]Dynamic SQL 사용방법 )

No.구분STATICDYNAMIC
1SQL 구성SQL을 CURSOR에 담아 사용SQL 을 CHAR 변수에 담아 만든 뒤 DBMS 에 완성된 SQL 구문을 날려서 데이터를 가져옴
2개발 패턴STATICS SQL 은 SQL 모양이 변경되지 않아야 하기 때문에 일반적인 개발 패턴은 STATIC SQL 을 CURSOR 절에 선언한 뒤 이를 BEGIN END 절 사이에서 LOOPING 구조로 데이터를 처리하는 방식임DYNAMIC SQL 은 SQL 모양이 변경될 수 있기 때문에 NVL()처리를 할 필요가 없음.
3컬럼 구성STATIC SQL 은 정적이기 때문에 컬럼 또한 고정이 되어서 사용되어야 함DYNAMIC SQL은 구문을 변수에 담아서 DBMS를 콜하기 때문에 변수나 칼럼등 모든 SQL을 로직으로 처리하여 자유롭게 SQL구문을 만들 수 있음
4실행 계획옵티마이져는 NVL()처리가 되어있는 조건을 처리하기 위해 IS NULL, IS NOT NULL 로 나누어 실행게획을 세움, 만약 6개 조건이 있다면 12개의 CONCATEMATION 으로 실행계획이 쪼개져서 나오고 결국 실행 계획을 만들기 위한 하드파싱 시간이 오래 걸림옵티마이져는 NVL()처리된 WHERE 조건이 없기 때문에 실행게획을 쪼깰 필요가 없고 그만큼 순수한 액세스 패스에 대해 실행계획을 작성하기 때문에 양호한 실행 계획이 작성되고 하드파싱 시간도 적어짐


  • 결국 STATIC SQL 이라는 것은 고정된 SQL 형태를 만든 뒤에 이 SQL 형태로 모든 조건들을 처리해야 하는
  • 반면, DYNAMIC SQL 은 여러 로직으로 조건에 해당되는 SQL을 변수에 담아서 만든 뒤에 DBMS를 콜하기 때문에 보다
    풍부한 SQL을 작성할 수 있습니다.
  • 이처럼 DYNAMIC SQL 이 STATIC SQL 보다 여러 장점이 있는데로 불구하고 잘 사용되지 않는건 개발 난이도도
    높고 개발시간도 현저히 늘어난다는 것입니다. 또한 STATIC SQL 이 직관적으로 볼 수 있는 반면 DYNAMIC SQL은 
    로직으로 SQL 을 만들어나가는 것이기 때문에 직관적이지 못하죠. 

문서정보


728x90
저작자표시 비영리 변경금지

'Oracle > oracle' 카테고리의 다른 글

CTAS를 통한 테이블 복제시 참고사항  (0) 2011.05.16
Windows Vista, Server 2008에 10g 클라이언트 설치  (0) 2011.05.14
베이스 테이블 없이 VIEW 강제로 생성하기  (0) 2011.04.21
Optimizing GROUP and ORDER BY  (0) 2011.04.13
오라클 캐릭터셋 확인  (0) 2011.03.08
728x90

view에서 참조하는 테이블이 없어도 뷰를 생성할 수 있다.


SQL> create or replace FORCE view <뷰이름> as select * from <테이블이름>;

Warning: View created with compilation errors.


SQL> select view_name from user_views where view_name='TEST';


VIEW_NAME

------------------------------

TEST


에러는 발생하지만 뷰는 생성되었다.


SQL> create table test123(a int);

Table created.


SQL> select * from test;


         A

----------

         1


테이블을 이후에 생성해도 뷰를 이용하여 데이터를 조회할 수 있다.


http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_8004.htm

728x90
저작자표시 비영리 변경금지

'Oracle > oracle' 카테고리의 다른 글

Windows Vista, Server 2008에 10g 클라이언트 설치  (0) 2011.05.14
Static vs. Dynamic SQL  (0) 2011.04.21
Optimizing GROUP and ORDER BY  (0) 2011.04.13
오라클 캐릭터셋 확인  (0) 2011.03.08
SQL SERVER HSODBC 설정 방법  (0) 2010.06.29
728x90

 

오라클 10.2를 시작할때, Group by와 Order by를 동일한 칼럼에 결합하여 사용할때, 성능과 관련하여 괄목할 만한 성능 절감을 발견하게 될 것이다. 

오라클 10.2에서는 Hash 기반의 Group By에 대해서 소개했다. 이전 Group by를 수행하면 연관된 칼럼의 소팅을 포함해서 처리했었고, 이러한 수행결과를 모아서 결과로 반환했다. Hash Group By 메소드는 소팅 없이 이러한 결과를 생성한다. 즉, 이 처리방식은 Sort-Based Group by보다 항상 나은 성능을 발휘한다. 불행하게도 Order By 절을 동일 칼럼에 Group By절과 함께 사용한다면 Sort-Based Group by로 변경하며, 응답속도는 떨어질 것이다. 그러나 요청한 순서대로 데이터를 획득하고 싶다면 Hash-Based Group by를 이용하면 조금 더 이점은 있다.

 

예를 들어 다음 단순한 샘플 구문을 확인해보자.

 



10.2 이전에서는 SORT GROUP BY 오퍼레이션을 이용하여 수행하게 된다.

 

 

 

10.2 버젼 이후부터는 HASH GROUP BY를 수행하는것을 확인할 수 있다.

 



Alex Gorbachev  noted에 의하면 새로운 GROUP BY 는 이전 버젼과 같이 수행할 수 있도록(11.1.0.7 혹은 10.2.0.4 버젼 이전 오라클을 말한다.) 수정할 수있음을 가르쳐 준다. 다음 파라미터인 _GBY_HASH_AGGREGATION_ENABLED 를 FALSE로 바꾸도록 설정하면 된다. 아래 예와 같이 OPT_PARAM 힌트를 이용하여 개별 SQL의 설정을 바꿔 보자. 혹은 ALTER SESSION이나 ALTER SYSTEM을 변경하여 세션이나 인스턴스 레벨로 동작하게 할 수 있다.
 

 

기억해야할 것은 GROUP BY의 결과가 정렬되어 나온다고 생각하면 안된다는 것이다. 10.2 버젼의 GROUP BY는 보통 정렬된 GROUP BY를 이용했고, 필수적으로 함께 ORDER BY 절이 추가 되었었다. 10.2에서는 쿼리의 결과가 갑자기 랜덤한 순서로 나타나는것을 보고 아마 깜짝 놀랐을 수 있다. Tom Kyte는 다음과 같이 이야기 하고 있다. : "기본 원칙은 결코 부분적인 정렬에 대한 다른 영향은 없을 것이다. 만약 정렬된 group 결과를 획득하고 싶다면 ORDER BY절을 항상 사용해야 한다."

 

일반적으로 말해서 새로운 HASH GROUP BY는 과거의 소트 방법보다 훨씬 향상된 결과를 가져온다. 아래에서 보는것과 같이 GROUP BY 알고리즘을 2백 50만건 로가 있는 샘플 데이터를 그룹바이 하여 20만 로의 통합된 로가 나온다면 수행결과 차이는 다음 그림과 같이 나타난다.



물론 결과가 다르게 나올수 있지만 HASH GROUP BY의 선능이 SORT GROUP BY 보다 우수하다는 것을 보게 될 것이다.

 

불행하게도 오라클은 몇몇 환경에서 HASH GROUP BY를 사용하지 않을 것이다. 공평하게 동일한 칼럼에 GROUP BY와 ORDER BY를 동시에 사용한경우에 그렇게 동작하며, 임의의 순서로 통합된 결과가 보이지 않을 것이다. 

 

오라클은 SORT GROUP BY를 이용할때, 그룹핑된 결과에 사이트 이펙트로 소트를 수행한다. 그래서 아래 예와 같이 SORT가 오직 한번만 일어나며, 결과적으로 GROUP BY와 ORDER BY를 모두 동시에 사용한 결과가 나타나게 된다. (주의할 것은 여기서는 HASH GROUP BY를 OPT_PARAM 힌트를 이용하여 기능을 꺼둔상태라는 것이다.)

 

 

상단의 플랜은 10.2 이전에서 주로 보이는 결과이며, HASH GROUP BY가 릴리즈되기 이전에 사용된 것이다.

 

그러나 11g나 10.2에서는 오라클은 여전히 SORT GROUP BY를 선택할 것이라는 것을 알 수 있다.

 

 

 

여기에 중요한 포인트가 있다.

 

만약 GROUP BY와 ORDER BY를 동일한 칼럼에 혼합해서 사용할경우,
오라클은 HASH GROUP BY옵션을 사용하지 않을것이다.

 

추측건데 옵티마이저의 생각은 그룹을 수행하는 동안 SORT GROUP BY를 수행하는 것이다. GROUP BY와 마찬가지로 ORDER BY 요청을 수행할때 SORT GROUP BY는 가장 좋은 방법인 것이다. 그러나 로직에는 중요한 하자가 있다. 보통 ORDER BY를 위한 입력은 GROUP BY의 입력보다 작은 로가 입력이 된다. 상단의 예에서도 GROUP BY는 ORDER BY가 20만건을 처리하는 동안, 2백 50만건이 처리되고 있다. 이것은 비싼 GROUP BY를 수행하여 상대적으로 싼 ORDER BY에 입력으로 이용하는것은 의미상 문제가 있는 반 옵디마이징이다.

 

그래서 오라클에서 ORDER BY를 해야하는 상황에서 강제적으로 HASH GROUP BY를 수행하도록 하면 어떨까? 하는것이 핵심이다. 그래서 오라클이 HASH GROUP BY를 수행할 수 있도록 서브쿼리에 GROUP BY를 수행하도록 만들었다. 그리고 외부 쿼리에서 ORDER BY를 수행할때 NO_MERGE 힌트를 지정하여, 외부 쿼리가 내부 서브쿼리에 통합되지 않도록 지정했다.
결과적으로 플랜을 보면, HASH GROUP BY를 수행하고, 그 결과를 SORT ORDER BY를 타도록 재설정 되었다.

 

 

 

생각하는것과 같이 상단의 내용은 HASH GROUP BY와 SORT ORDER BY를 함께 사용하여 SORT GROUP BY보다 훨씬 나은 수행을 하도록 하고 있다. 그러나 기억해야할 것은 SORT ORDER BY는 그룹된 결과를 소트한다는 것을 꼭 확인해야한다는 것이다. 즉 예제에서는 20만개의 로를 소트하는 것이고, 이것은 2백 50만개의 HASH GROUP BY를 수행한 결과로 처리한다는 것이다. 그래서 GROUP BY의 최적화는 중요함이 작은 두번째 소트처리를 피하는것 보다 더 중요한 사항이 되기도 한다.

 

 


이것은 2개의 성능을 비교한 결과를 보여준다.
응답 시간이 2/3으로 획기적으로 줄어든 것을 확인할 수 있다.

 

결론

GROUP BY를 ORDER BY와 동일한 칼럼에 결합해서 적용할때에는 오라클 옵티마이저는 더 효과적인 HASH GROUP BY를 사용하지 않고, SORT GROUP BY를 수행한다. SORT ORDER BY 계획을 피하기 위해서 SORT GROUP BY를 수행한 것이다. 그러나 성능은 형편 없다는것을 확인 할 수 있다.

 

더 나은 결과를 위해서 GROUP BY를 인라인 뷰에 작성하고, 외부 쿼리에서 ORDER BY를 수행하도록 하자.
여기서 두개의 쿼리가 머지되어 작동하지 않도록 NO_MERGE 힌트를 부여하는것에 주목하자.

728x90
저작자표시 비영리 변경금지

'Oracle > oracle' 카테고리의 다른 글

Static vs. Dynamic SQL  (0) 2011.04.21
베이스 테이블 없이 VIEW 강제로 생성하기  (0) 2011.04.21
오라클 캐릭터셋 확인  (0) 2011.03.08
SQL SERVER HSODBC 설정 방법  (0) 2010.06.29
Oracle 11g - ADRCI : IPS  (0) 2010.05.20
728x90

SELECT parameter,value FROM nls_database_parameters WHERE parameter like '%CHAR%';


SELECT * FROM sys.props$ WHERE name = 'NLS_CHARACTERSET';

728x90
저작자표시 비영리 변경금지

'Oracle > oracle' 카테고리의 다른 글

베이스 테이블 없이 VIEW 강제로 생성하기  (0) 2011.04.21
Optimizing GROUP and ORDER BY  (0) 2011.04.13
SQL SERVER HSODBC 설정 방법  (0) 2010.06.29
Oracle 11g - ADRCI : IPS  (0) 2010.05.20
NOT IN과 NOT EXISTS의 차이점  (0) 2010.05.03

+ Recent posts

티스토리툴바