database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Tutorials 9280 게시물 읽기
 News | Q&A | Columns | Tutorials | Devel | Files | Links
No. 9280
SQL문 튜닝 방법에 관하여
작성자
정재익(advance)
작성일
2002-01-05 21:32
조회수
8,736

문제점을 발견한 SQL 문의 튜닝.....

 

1. 시스템을 파악해야 한다.

   - 데이터의 성격을 명확히 알아야만 한다.
   - 튜닝 방법을 선택하기 위한 체크리스트를 작성한다.
   - 사용하는 SQL 쿼리를 분석한다.
     1) SQL*PLUS 에서 "set autotrace on" 명령을 수행하고 TKPROF를 이용하여 분석한다.
     2) 문제가되는 SQL 문의 explain plan을 조회한다.
     3) "set timing on" 명령을 수행하여 실행 시간을 검사한다.

   ** 한 세션만 트레이스를 거는 방법
      1) select sid, serial# from v$session where username='SCOTT'; 
             해당 유저의 id를 알아낸다.
      2) execute dbms_system.set_sql_trace_in_session({SID},{Serial#},TRUE);

2. Analyze 를 한다.

   Optimizer의 파싱연산을 더욱 정확하게 수행토록 유도할 수 있다.

   -Analyze table [table 명] compute statistics;   테이블 전체 연산.
   -Analyze table [table 명] estimate statistics;  테이블이 큰 경우 1064row 까지만 연산.
   -Analyze table [table 명] delete statistics;    이전 연산결과 삭제.
   - 한 유저의 모든 테이블을 분석할경우
     exec dbms_utility.analyze_schema('SCOTT','COMPUTE');

3. Hint 를 사용한다.

 

- table Full Scan을 유도

select /*+ FULL(table_name) */ column1, 2 .. from table_name ... ;

 

- index 사용을 유도

select /*+ INDEX(table_name index_name1, 2 .. ) */ column1,2 .. from table_name ... ;

 

- table 을 순서대로 조인하도록 유도

select /*+ ORDERED */ column1,2 .. from table1, table2 ... ;

 

- Cost-based 연산시 또는 모든 row 를 캐쉬하도록 유도(인덱스를 사용 않하도록 유도)

select /*+ ALL_ROWS */ .... ;

 

- Cost-based 연산시 응답속도를 빠르게 하기위해 (인덱스 걸린 컬럼 쿼리시)

select /*+ FIRST_ROWS */ .... ;

[/pre]

4. 인덱스를 생성/삭제 한다.

   EX1) 인덱스가 항상 좋은가 ??
       EMP 테이블은 10만건의 데이터가 있고 DEPT_NO=10 것은 2만5천건
       존재한다고 할때 수행시간 비교.

    select ENAME from EMP where DEPT_NO=10;
       
   1) 인덱스가 없을때 : 약 55초 소요
   2) 인덱스가 있을때 : 약 70초 소요
   3) dept_no, ename 의 복합 인덱스가 있을때 : 약 0.1초 소요.
      (단, ename은 unique)

   ** 인덱스가 걸리는 컬럼은 중복값이 적은것이 좋다. 만일 전체의 25% 이상의 중복
      값을 가지는 값을 쿼리시 시간이 더 걸리게 된다.


   EX2) 인덱스와 힌트의 적절한 사용....
       EMP 테이블은 1만건의 데이터가 있고, DEPT_NO에 인덱스가 걸려있고,
       DEPT_NO>9990 것은 5천건 존재한다고 할때 수행시간 비교.

    select ENAME, DEPT_NO from EMP where DEPT_NO>9990;
     -> 약 53초 소요

    select /*+ FULL(EMP) */ ENAME, DEPT_NO from EMP where DEPT_NO>9990;
     -> 약 4초 소요


   EX3) join 테이블의 쿼리시 속도비교
        EMP 테이블은 10만건, DEPT 테이블은 10건의 데이터가 있을때.

    select ENAME, DEPT_NO from EMP, DEPT 
    where EMP.DEPT_NO = DEPT.DEPT_NO and EMP_NO=5 and DEPT_NO=10 ;
     -> 약 4분 소요 된다면

    select /*+ ORDERED */ ENAME, DEPT_NO from EMP, DEPT 
    where EMP.DEPT_NO = DEPT.DEPT_NO and EMP_NO=5 and DEPT_NO=10 ;
     -> 약 15초 소요
  
    select ENAME, DEPT_NO from EMP where EMP_NO=5 and EXISTS
             ( select 'X' from DEPT where EMP.DEPT_NO = DEPT.DEPT_NO 
                          and DEPT_NO=10 ) ;
     -> 약 8초 소요

     ** Exists, Union 등의 연산자는 속도를 개선시켜줌.

5. 테이블을 조정한다.

[Top]
No.
제목
작성자
작성일
조회
9283SQL*Net 설정과 튜닝 - SQL*NET Trace 하기
정재익
2002-01-05
5913
9282SQL*Net 설정과 튜닝 - SQL*NET을 ODBC로 연결할 때 속도를 빠르게 하기
정재익
2002-01-05
5389
9281init.ora 파일중 메모리 튜닝에 영향을 주는 파라미터
정재익
2002-01-05
6300
9280SQL문 튜닝 방법에 관하여
정재익
2002-01-05
8736
9279유용한 오라클 tuning scripts (2)
정재익
2002-01-05
8968
9278유용한 오라클 tuning scripts (1)
정재익
2002-01-05
11146
9276몇가지 유용한 SQL scripts
정재익
2002-01-05
10821
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.051초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다