오라클 : NLS_LANG

[출처] http://ndba.egloos.com/2957626

Oracle에서는 많은 환경변수값을 사용하고 있다. 그렇지만 지금은 한글에 관한 이야기를 하고 있어서 NLS_LANG만 가지고 설명하려 한다.
내 가 주변에서 흔히 보는 경우가 Oracle DB 와 사용자의 Client간 NLS_LANG값을 동일하게 하여 사용하는 경우가 100에 99정도이며 사실 나도 그렇게 사용하고 있다. 이렇게 사용하는 이유는 Oracle Client 프로그램을 이용하여 직접 Oracle DB에 접속하여 작업하는 경우가 대부분 일것이다.
그러나 NLS_LANG 변수의 값은 Oracle DB 환경변수 값이 아니라, 사용자 자신이 속해 있는 환경을 Oracle DB 알려주는 역할을 하는 환경변수이다.
NLS_LANG = [언어]_[영역].[캐릭터셋]
언어 :현재 사용자가 사용하는 언어적 특성을 결정짓는 값
영역 :현재 사용자가 위치한 영역의 특성을 결정짓는 값
캐릭 터셋 :현재 사용자의 시스템이 인식할 수 있는 캐릭터셋의 값
만 약에 Windows Client에서 한국어 환경을 사용하는 경우 NLS_LANG 값을'KOREAN_KOREA.KO16MSWIN949'로 그리고, 유닉스 Client에서 한국어를 입출력한다면 다음과 같이 NLS_LANG을 'KOREAN_KOREA.KO16KO16KSC5601'로 설정 할 수 있다.

Oracle 캐릭터셋과 Client 캐릭터셋을 동일하게 설정해야 경우

위에서 이야기 했지만 NLS_LANG값은 Client값이기 때문에 Oracle DB와 동일하게 설정할 필요는 없지만 작업 및 그동안의 관습(?)에 따라 Oracle DB와 NLS_LANG 변수 값을 동일하게 설정하는 경우가 많다.
그러나 Oracle에서 밑에 나열한 작업이 필요할 경우 꼭 Oracle DB와 NLS_LANG값을 동일하게 하는 것을 권장하고 있다.

• 데이타베이스로부터 데이타를 export받을 때
• export 받은 데이타베이스와 같은 캐릭터셋을 가진 데이타베이스로 export된 파일을 import할 때
• 기타 다국어 지원 애플리케이션에서 목적에 따라 사용할 때

NLS관련 주요 변수

• NLS_TERRITORY : 영역 설정 - NLS_LANG 변수값에 의해 자동 설정
  • 설정 방법예 : ALTER SESSION SET NLS_TERRITORY = 'KOREA'
• NLS_LANGUAGE : 언어 설정- NLS_LANG 변수값에 의해 자동 설정 초기화변수
  • 설정 방법예 : ALTER SESSION SET NLS_TERRITORY = 'KOREAN'
• NLS_LANG : 언어,영역, 캐릭터셋 설정 - 기본값은 'AMERICAN_AMERICA.US7ASCII'
  • 설정 방법예 : OS 환경변수로 설정
• NLS_COMP : SQL에서의 비교 방식(<,>,=) 설정 - BINARY값으로 비교
  • 설정 방법예 : ALTER SESSION SET NLS_COMP = ''
• NLS_SORT : 문자열의 정렬방법 설정 - NLS_LANGUAGE값에 따라 결정
  • 설정 방법예 : ALTER SESSION SET NLS_SORT = 'KOREAN_M'

• NLS_TERRITORY 변수에 따라 그 값이 결정되는 변수
  • NLS_CREDIT(대차대조표 '대변'항목의 금액표기를 위한 기호. 보통 '공백'문자)
  • NLS_CURRENCY
  • NLS_DATE_FORMAT
  • NLS_DEBIT(대차대조표 '차변'항목의 금액표기를 위한 기호. 보통 '마이너스'문자)
  • NLS_ISO_CURRENCY
  • NLS_LIST_SEPARATOR(숫자를 가로로 나열할 때 각 숫자를 구분하는 기호로, 우리나라의 경우 콤마이다)
  • NLS_MOMETARY_CHARACTERS(금액 표기시 금액을 읽기 쉽게 나누는 문자로 우리나라에서는 3자리마다 ","를 추가한다)
  • NLS_NUMERIC_CHARACTERS(소수점기호와 숫자 그룹핑을 위한 문자 설정. 우리나라에서는 '.,'이다(dot와 comma)
  • NLS_TIMESTAMP_FORMAT
  • NLS_TIMESTAMP_TZ_FORMAT
  • NLS_DUAL_CURRENCY(유 로화 변경 기간동안의 혼란을 막기 위해 만들어진 매개변수. 9i Release 2과 그 이후로는 EU의 유로화 변경이 완료된 상태로 NLS_CURRENCY와 값이 동일하다. 다만 미래에 다른 지역에서도 통화기호의 변경이 일어나면 사용될 수 있다)
• NLS_LANGUAGE 변수에 따라 그 값이 결정되는 변수
  • NLS_DATE_LANGUAGE
  • NLS_SORT

SQL 튜닝

출처: http://cafe.naver.com/whatvoip/189

차례


1. 튜닝의 기본
2. INDEX
3. JOIN
4. 함수


Oracle SID  FATE 예로 설정

Oracle 제공하는 OPTIMIZER 라는 프로세스를 사용하여 튜닝 




튜닝의 기본


영향요소, 목적, 주체, 시기, 목표 설정 하고 시스템계획, 분석/설계(Table간 자동화 규칙, 적합한 인덱스 정의),  개발(옵티마이저, 최적화, 재사용 정립,표준 준수,테이블, 행단위 잠금설정, 유틸리티),  검수, 운영



1. Explain Plan


SQL 문을 분석하고 해석하여 실행계획(execution, SQL이 요구한 테이터를 추출하기 위해 오라클이 차례로 수행하는 작업 방법)을 수립, 실행계획 테이블(PLAN_TABLE)에 저장해주는 명령


1) 실행계획 테이블(PLAN_TABLE) 생성  

C:\Documents and Settings\Administrator>sqlplus /nolog
SQL*Plus: Release 8.1.7.0.0 - Production on 화 Apr 29 17:05:10 2008
(c) Copyright 2000 Oracle Corporation.  All rights reserved.

SQL> conn scott/tiger@FATE
연결되었습니다.

SQL> show user
USER은 "SCOTT"입니다

SQL> @D:\ORACLE\ora81\RDBMS\admin\utlxplan.sql테이블이 생성되었습니다.
SQL> DESC PLAN_TABLE 이름                                      널       유형
 ----------------------------------------- -------- ---------------------------
 STATEMENT_ID                                       VARCHAR2(30)
 TIMESTAMP                                          DATE
 REMARKS                                            VARCHAR2(80)
 OPERATION                                          VARCHAR2(30)
 OPTIONS                                            VARCHAR2(30)
 OBJECT_NODE                                        VARCHAR2(128)
 OBJECT_OWNER                                       VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(30)
 OBJECT_INSTANCE                                    NUMBER(38)
 OBJECT_TYPE                                        VARCHAR2(30)
 OPTIMIZER                                          VARCHAR2(255)
 SEARCH_COLUMNS                                     NUMBER
 ID                                                 NUMBER(38)
 PARENT_ID                                          NUMBER(38)
 POSITION                                           NUMBER(38)
 COST                                               NUMBER(38)
 CARDINALITY                                        NUMBER(38)
 BYTES                                              NUMBER(38)
 OTHER_TAG                                          VARCHAR2(255)
 PARTITION_START                                    VARCHAR2(255)
 PARTITION_STOP                                     VARCHAR2(255)
 PARTITION_ID                                       NUMBER(38)
 OTHER                                              LONG
 DISTRIBUTION                                       VARCHAR2(30)



D:\ORACLE\ora81\RDBMS\admin\utlxplan.sql rem
rem $Header: utlxplan.sql big_dev/0 11-aug-95.14:02:45 achaudhr Exp $ xplainpl.sql
rem
Rem Copyright (c) 1988, 1995, 1996, 1998 by Oracle Corporation
Rem NAME
REM UTLXPLAN.SQL
Rem  FUNCTION
Rem  NOTES
Rem  MODIFIED
Rem     mzait      02/19/98 -  add distribution method column
Rem     ddas       05/17/96 -  change search_columns to number
Rem     achaudhr   07/23/95 -  PTI: Add columns partition_{start, stop, id}
Rem     glumpkin   08/25/94 -  new optimizer fields
Rem     jcohen     11/05/93 -  merge changes from branch 1.1.710.1 - 9/24
Rem     jcohen     09/24/93 - #163783 add optimizer column
Rem     glumpkin   10/25/92 -  Renamed from XPLAINPL.SQL
Rem     jcohen     05/22/92 - #79645 - set node width to 128 (M_XDBI in gendef)
Rem     rlim       04/29/91 -         change char to varchar2
Rem     Peeler     10/19/88 - Creation
Rem
Rem This is the format for the table that is used by the EXPLAIN PLAN
Rem statement.  The explain statement requires the presence of this
Rem table in order to store the descriptions of the row sources.

create table PLAN_TABLE (
 statement_id  varchar2(30),
 timestamp     date,
 remarks       varchar2(80),
 operation     varchar2(30),
 options        varchar2(30),
 object_node   varchar2(128),
 object_owner  varchar2(30),
 object_name   varchar2(30),
 object_instance numeric,
 object_type     varchar2(30),
 optimizer       varchar2(255),
 search_columns  number,
 id  numeric,
 parent_id numeric,
 position numeric,
 cost  numeric,
 cardinality numeric,
 bytes  numeric,
 other_tag       varchar2(255),
 partition_start varchar2(255),
 partition_stop  varchar2(255),
 partition_id    numeric,
 other  long,
 distribution    varchar2(30));





* 실행계획(Execution Plan)


SQL 문이 요구한 데이타를 추출하기위해 DMBS가 차례로 수행하는 방법을 말하는데, 오라클 옵티마이저에 의해서 SELECT, UPDATE, INSERT 그리고 DELETE문이 테이블 인덱스등에 대해서 ACCESS 하는 경로를 보여줌

옵티마이저가 DMBS를 Access 하고 경로를 작성한 계획으로서 어떤 Table 또는 Index 를 제일 먼저 Access 하고 다음으로 어떤것을 Access 하고 하는 등등의 계획, 반드시 PLAN_TABLE 이 있어야 가능하고 분석은 가장 우측의 것이 최 우선, 우측의 Depth 같은 경우 상위것이 우선함



2) EXPLAIN PLAN 실행


EXPLAIN PLAN  [SET STATEMENT_ID = 'Description']
                        [INTO TABLE_NAME]
                        FOR SQL_Statement 
EXPLAIN PLAN SET STATEMENT_ID = 'TEMP_1'
  INTO PLAN_TABLE FOR
  SELECT A.EMPNO, A.ENAME, NVL(A.SAL,0), B.DNAME
  FROM EMP A, DEPT B
  WHERE A.EMPNO = 10
  AND A.DEPTNO = B.DEPTNO

SQL> EXPLAIN PLAN SET STATEMENT_ID = 'TEMP_1'
  2  INTO PLAN_TABLE FOR
  3  SELECT A.EMPNO, A.ENAME, NVL(A.SAL,0), B.DNAME
  4  FROM EMP A, DEPT B
  5  WHERE A.EMPNO = 10
  6  AND A.DEPTNO = B.DEPTNO
  7  /
해석되었습니다.



3) PLAN_TABLE 확인


SELECT ID, PARENT_ID P_ID,
  LPAD('',2*(LEVEL-1)) || OPERATION OPERATION,
  OPTIONS, OBJECT_NAME
  FROM PLAN_TABLE
  START WITH ID = 0 AND STATEMENT_ID = 'TEMP_1'
  CONNECT BY PRIOR ID =  PARENT_ID AND STATEMENT_ID = 'TEMP_1'


출력 결과 한줄에 보기좋게 설정

COLUMN  OPERATION FORMAT A25
COLUMN  OPTIONS FORMAT A20
COLUMN  OBJECT_NAME FORMAT A20
COLUMN  ID FORMAT 999
COLUMN  P_ID FORMAT 999


SQL> COLUMN  OPERATION FORMAT A25
SQL> COLUMN  OPERATION FORMAT A25
SQL> COLUMN  OPTIONS FORMAT A20
SQL> COLUMN  OBJECT_NAME FORMAT A20
SQL> COLUMN  ID FORMAT 999
SQL> COLUMN  P_ID FORMAT 999


SQL> SELECT ID, PARENT_ID P_ID,
  2    LPAD('',2*(LEVEL-1)) || OPERATION OPERATION,
  3    OPTIONS, OBJECT_NAME
  4    FROM PLAN_TABLE
  5    START WITH ID = 0 AND STATEMENT_ID = 'TEMP_1'
  6    CONNECT BY PRIOR ID =  PARENT_ID AND STATEMENT_ID = 'TEMP_1'
  7    / 

  ID P_ID OPERATION                 OPTIONS              OBJECT_NAME
---- ---- ------------------------- -------------------- --------------------
   0      SELECT STATEMENT
   1    0 NESTED LOOPS
   2    1 TABLE ACCESS              BY INDEX ROWID       EMP
   3    2 INDEX                     UNIQUE SCAN          PK_EMP
   4    1 TABLE ACCESS              BY INDEX ROWID       DEPT
   5    4 INDEX                     UNIQUE SCAN          PK_DEPT
6 개의 행이 선택되었습니다.
4) TRACE FILE


위와 같이 하나의 SQL 문장에 대한것이 아닌 시스템 전체 혹은 하나의 세션에 발생하는 모든 SQL 문에 대한 실행 계획을 알고자 할때 Trace File 을 만들어 분석하고 tkprof 라는 툴을 사용해서 분석


 시스템 전체 적용

  D:\Oracle\ora81\database\initFATE.ora   (initSID.ora)

    SQL_TRACE = TRUE
    TIMED_STATISTICS = TRUE
    USER_DUMP_DEST = Directory
    MAX_DUMP_DEST = number

  종료후 TRUE 를 FALSE 수정후 DB Restart


 세션별 적용

 전체 적용 불가능시 해당 세션만 적용

  ALTER SESSION SET SQL_TRACE = TRUE
  ALTER SESSION SET TIMED_STATISTICS = TRUE



  trace 파일은 보통 $ORACLE_HOME/admin/$ORACLE_SID/udump 에 생성



5) TRACE FILE 분석

tkprof tracefile outfile [explain= ] [table= ] [print= ] [sys= ] [sort= ]

explain : explain=userid/passwd 로 connect 시 사용된 user 및 password
sys      : sys=no  system에 관련자료 미포함



6) SQLPLUS 를 이용한 Trace 분석

  SET AUTOTRACE TRACE EXPLAIN  : 실행하지는 않고 통계정보만 보여줌
  SET AUTOTRACE ON                   : 실행하여 결과물과 통계 정보 다보여줌
  SET AUTOTRACE OFF






2. OPTIMIZER


SQL 문에 대하여 이를 해석하고 실행계획(Execution Plan)을 수립하는 오라클 프로세스로 최적의 경로를 이용하지 않았을 경우 최적의 경로로 Access 할수 있도록 하는것


Rule Based Optimizer(RBO, 규칙 기반 최적화)

DB 에 접근하는 경로를 찾는데 사전 정의된 규칙을 사용, 예측, 통제 가능하고 가장 낮은 순위의 실행 계획을 항상 사용하고, 통계정보는 사용하지 않고  비용 계산도 고려 하지 않고 규칙 우선

Rank	Access Path
1	Single row by ROWID
2	Single row by cluster join
3	Single row by hash cluster with unique or primary key
4	Single row by unique or primary key
5	Cluster join
6	Hash cluster key
7	Indexed cluster key
8	Composite key
9	Single column indexes
10	Bounded range search on indexed columns
11	Unbounded range search on indexed columns
12	Sort merge join
13	MAX or MIN of indexed columns
14	ORDER BY on indexed columns
15	Full table scan

Cost Based Optimizer(CBO, 비용 기반 최적화)

DB 정보를 활용, 생성된 테이블을 ANALYZER 명령으로 분석, 과부하 요소를 찾고 cost가 적은 실행 계획을 선택 하는데,  Cost 란 각각의 소요시간(Elapsed time)을 말한다


Optimizer Modes 설정 방법


시스템 전체 (InitSID.ora)

Optimizer Mode =  CHOOSE, ALL_ROWS, FIRST_ROWS, RULE

세션에서 지정

ALTER SESSION SET OPTIMIZE_MODE =  CHOOSE, ALL_ROWS, FIRST_ROWS, RULE

SQL 문장에서 지정(Hint 사용)

SELECT /*  CHOOSE, ALL_ROWS, FIRST_ROWS, RULE   */



통계정보 만들기(ANALYZER)

ANALYZER 란 테이블, 인덱스에 대한 통게정보를 DBMS가 저장함으로써 Optimizer가 CBO Mode 에서 참고할수 있는 정보 제공하고  이를 이용하여 각 Object 의 구조를 확인 하고 Chain 생성 여부를 확인 할수 있으므로 시스템의 저장공간 관리를 효줄적으로 함


ANALYZE  TABLE(or INDEX) COMPUTE,ESTIMATE,DELETE STATISTICS 


SQL_Trace

 SQL_TRACE 설정 -> 세션별 실행통계->TRACE 파일 생성-> TKPROF->출력
 Init.ora 의 파라미터를 이용하여 설정, 세션단위도 가능

TKPROF

 TRACE 파일을 분석 가능한 파일 형식으로 출력,  SQL 문장분석, 시스템 이상 분석

Access Path

SQL문에서  WHERE 조건문의 형태를  분석하는 자료
주요 컬럼 조사 기록, 인덱스 검정및 클러스트링 검토 중요 자료