• TO_CHAR

TO_CHAR 함수는 임의의 데이터를 문자형 데이터 타입으로 변경하는 함수로

변경 대상이 되는 데이터 타입은 다음과 같다

문자형 숫자형 --> VARCHAR2 날짜형

• 문자형 데이터를 VARCHAR2 로 변경

  ※ TO_CHAR(변경할 문자열) NCHAR NVARCHAR CLOB NCLOB --> VARCHAR2 로 변경하여 리턴함.

• 날짜형 데이터를 VARCHAR2 로 변경

※ TO_CHAR(변경할 날짜형 데이터) ※ TO_CHAR(변경할 날짜형 데이터 , 포맷) DATE TIMESTAMP TIMESTAMP WITH TIME ZONE TIMESTAMP WITH LOCAL TIME ZONE --> VARCHAR2 로 변경하여 리턴함..

날짜 표시 포맷 YYYY,YYY,YY,Y : 연도 MM : 01 ~12 형태의 월 DD : 1~31일 형태로 표시 D : 주중의 일(1~7) HH : 1~12시 형태로 표시 HH12 : 1~12시 HH24 : 0~23시 MI : 0~59분 SS : 0~59초 AM :오전 PM : 오후 DAY : 일을 서술형 이름으로 표시 / , - , : , .

ex)

SELECT TO_CHAR(COL_DATE,'YYYY-MM-DD) DSTR

FROM DATE_TABLE

DSTR --------------------- 1922-08-01 1 rows selected

• 숫자형 데이터를 VARCHAR2 로 변경

※ TO_CHAR(변경할 숫자) ※ TO_CHAR(변경할 숫자 , 포맷) --> VARCHAR2 로 변경하여 리턴함..

주요 숫자 포맷 , : 특정위치에 ,를 찍ㄴㄴ다 ex) : 9,999 9 : 한자리 숫자를 나타낸다.실제 숫자갯수와 크거나 같도록 표시한다. $ : 달러표시 SELECT TO_CHAR(1234,$9999) FROM DUAL --> $1234 리턴 L : 지정된 지역의 통화기호를 리턴 SELECT TO_CHAR(123,'L999') FROM DUAL --> \123 리턴

[출처] 오라클 변환함수 ( TO_CHAR )|작성자 하늘아이

오라클의 인스턴스로 접속을 가능하게 해주는 다음과 같은 세가지 영역이 존재한다.

사용자 프로세스는 데이터 베이스로의 접속을 시도하는 일종의 소프트웨어로서,

데이터 베이스와의 통신에 오라클 넷 서비스 를 이용한다.

오라클 리스너 (Oracle Listener)는 오라클 데이터베이스 서버에서 실행되는 프로세스이며, 클라이언트 어플리케이션에서 보내는 접속 요청을 청취하는 역할을 한다.

클라이언트는 청취자로 보내는 초기 접속요청에 서비스의 이름을 지정해야 한다.

이 서비스의 이름은 클라이언트가 접속하려는 데이터베이스의 인스턴스를 구별해주는 식별자이다.

※ 오라클 리스너 설정

오라클 리스너의 설정을 수동으로 변경하고자 한다면 listener.ora 파일을 찾아서 설정정보를 변경하면 된다.

Unix 의 경우 $ORACLE_HOME/network/admin 에서 찾을 수 있고,

윈도우의 경우 %ORACLE_HOME%\network\admin 에서 찾을 수 있다.

두 운영체제에서 모두 TNS_ADMIN 이란 환경변수를 만들고 오라클 넷 서비스 파일이 위치한 디렉토리를 가리키도록 할 수 있다.

서버와 마찬가지로 오라클 클라이언트 툴 역시 네트워크 상의 데이터 베이스와 통신 할 수 있도록 하는 설정이 필요하다.

클라이언트를 위한 설정파일은 tnsnames.ora 이다.

이 설정 파일의 내부에는 오라클의 툴이 데이터베이스에 접속하는 데 이용되는 접속 설정 리스트가 포함되며, 접속 설정은

서버의 호스트 이름과 서버와의 통신에 이용된 프로토콜 리스너와의 통신에 이용된 포트 등을 지정한다.

그림 참고 및 출처-http://www.oracleclub.com/lecture/1854

오라클의 SGA에는 위에서 보는 그림과 같이

- Share pool(공유풀) - Permanent Are

- Dinamic Area – Library cache

- Dictionary cache

- Database Buffer Cache(데이터 베이스 버퍼 캐쉬)

- Redo log Buffer (리두로그 버퍼)

- Large pool(대형 풀)

- Java pool(자바 풀)<?xml:namespace prefix = o />

이 있다.

SGA 의 확인

- show sga

- select * from v$sga;

- select * from v$sgastat àiSQL*plus 로 확인 해 볼 것.

- select * from v$sga_dinamic_components; àiSQL*plus 로 확인 해 볼 것

위의 command 들로 SGA 확인

SGA 변경

[형식] alter system set 파라메터 명 = 변경 값;

이와 같은 형식으로 파라메터의 값을 변경할 수있다.

ex)

SQL> show parameter db_cache

SQL> alter system set db_cache_size = 24M;

SQL> show parameter db_cache

SQL> alter system set shared_pool_size = 28M;

Shared Pool(공유 풀)

여러영역으로 나뉜 Oracle 메모리영역에서 Shared Pool은 또 한번 Permanent Area 와 Dynamic Area 로 나뉘어진다.

- Permanent Area(고정영역) – 오라클이 SGA를 관리하는 메커니즘 및 오라클의 파라메터 정보가 저장이 되며 위의 SGA 확인명령들로 확이이 가능하다(Fixed Size).

(!! 고정영역의 크기는 파라메터 설정값 등을 고려하여 자동으로 할당 되며 사용자가 지정 할수 없다….. 고하는데 파라메터 설정값 등을 변경하여 할당 할수 있지 않을까??)

- Dynamic Area(동적영역) – 이 영역은 Library Cache와 Data Dictionary Cache로 구분되며 크기는 위의 ‘alter system set shared_pool_size = 28M;’ 명령으로 변경할 수 있다.

1) Library Cache – SQL 쿼리, 오라클의 recursive SQL, SQL 의 Pase Tree 및 Execution Plan 이 저장된다.

2) Data Dictionary Cache – Low Cache 라고도 불리우며 테이블, 인덱스 함수 및 트리거 등 오라클 오브젝트 정보 및 권한 등의 정보가 저장된다.

공유풀의 활용

오라클은 질의된 SQL 문의 문법이 올바른지 검색하고 문법이 맞다면 위와 같이 사원테이블에 대한 정보를 Data dictionary cache 로 저장하고 SQL 문, 분석정보 및 실행 계획을 Library cache 에 저장한다.

공유 풀에 저장 된 정보는 유저가 같은 SQL 을 다시 실행 하였을 경우 해당정보를 재사용 할 수 있게 한다.(단!!! 구문분석(parsing)은 대소문자를 구별하고 오브젝트 소유자가 일치 하여야 한다. 조건이 일치 하지 않을 경우에는 Hard parsing이 일어나게 되어 DISK 를 읽게 되므로 성능에 영향을 주게 된다.)

Pasing(구문분석)에 대한 내용은 text 105p 구문분석 참조

공유풀의 관리

오라클은 Shared pool의 Library cache 관리를 위해 LRU (Least Recently Used)알고리즘을 사용한다.

프로세스 스케줄링 기법(※ text 106p 참고… 프로세스 스케줄링 기법을 모두 적으려했으나…. OTL… ㅠ.ㅠ 기법은 너무 아서 text 에 적힌 것만)

LRU – 이상한 카드놀이?

FIFO – Que

LIFO – Stack 등과 비슷

Ora-4031 à 이 에러를 내고 싶다면 http://ukja.tistory.com/239 블로그 참고(누구 블로그 인줄 다아시지요 ^^;;)

공유 풀 예약공간

공유풀에 미리 공간을 예약함으로 인해 Ora -4031 에 대한 SQL 실행실패를 예방 할 수있다.

Alter system set shared_pool_size = 100M;

Alter system set shared_pool_reserved_size = 10M;

총 공유 풀 공간을 100M 로 설정하고 그중에 예약공간을 10M 로 설정

(show parameter shared_pool 로 공유풀과 공유 풀 예약공간을 볼 수 있다.)

위와 같이 공유풀 예약공간을 설정 하면

1^stSQL 은 공유풀 예약 공간의 메모리를 사용하게 되며 2^ndSQL은 부터는 공유풀을 사용하게 됨으로 써 Ora-4031 에러를 감소 시킬수 있다.(물론 이보다 더 큰 메모리를 요하는 SQL 같은경우에는 어쩔수 없다.. OTL 이다.. 이때는 공유풀의 내용을 초기화 하는수밖에..ㅠ.ㅠ)

- 공유 풀의 예약공간을 설정하지 않았다면 자동으로 공유풀의 5% 크기의 예약공간이 설정된다.

- 공유 풀 예약공간은 공유 풀 크기의 50% 이상 설정할 수 없다.

- Ora-4031 에러를 감소시키기 위한 방법으로 공유풀의 예약공간을 크게 한다면 메모리의 낭비를 초래 하게 되어 성능에 영향을 미치게 된다.

Data buffer cache

데이터 버퍼 캐쉬 – 데이터를 읽고 가공하기 위해 디스크에 존재하는 데이터를 읽어 저장하는 메모리공간 이다. 이 데이터 버퍼 캐쉬 또한 LRU 알고리즘에 의해 관리된다.(이거 보시면 아시겠지요.. ㅎㅎ) 이것 또한 LRU 알고리즘에 의해 관리되다 보니 데이터 버퍼 캐쉬에 데이터가 없으면 DISK 에서 데이터를 읽어 가공을 한다. 하지만 Disk 보다 메모리가 더 빠르다는 것은 다들 알 것이다. 그렇기 때문에 필요한 데이터를 가급적 메모리에서 읽어오게 하는 것이 좋다.

많은 데이터를 가급적 메모리에서 읽어오도록 하기 위해서는 어떻게 하는 것이 좋은가.

오라클은 데이터를 읽거나 쓸 때 블록단위로 실행한다.

많은 데이터를 읽기오기 위해서는 블록을 크게 하면 된다.

(OTL.. 워드에서는 ‘블럭’ 이 블록으로 변환되네요.. 알아서 보시길.. ^^;;)

하지만!!!! 하나의 DB에 여러 user 가 질의를 하고 있다고 가정할 때 단순히 블록의 크기만 크게 하게 되면 그 블록에 경합이 발생을 하게 된다. 단순히 몇 명 뿐이면 이해가 쉽게 가지 않을 수 있다. 만약 우연히 증권을 거래하는 HTS에 같은 application 을 최소 몇 만명에서 1000만 이상 쓰게 되었을 때 같은 블록내의 데이터를 보려고 SQL로 질의 하게 된다면. 될 것인가… 결과는 불을 보듯 뻔하다… 시스템이.. 멎던가… 아니면.. 경합으로 인하여 조금 오래 걸릴 것이다. 하지만 이런 결과를 조금이나마 해소하기 위하여 오라클은 9i 부터 여러 개의 데이터 블록의 크기를 설정할 수 있게 되었다.

Text의 115p 처럼 Show parmeter cache_size 명령으로 볼수있고 또한 116p 처럼 테이블 스페이스에 블럭 size 를 설정할 수 있다.

따라서 데이터 버퍼 캐쉬 설정에는 정해진 크기가 없다.. 각 시스템 마다 알아서 설정을 해줘야 하는 것이다.

또한 이러한 경합을 줄이기 위해 8i 부터 다중 데이터 버퍼 캐쉬를 설정하는 것이 가능해졌다.

다중 데이터 버퍼 캐쉬란

파라미터에서 설정할 수 있는 일반 데이터 버퍼 캐쉬에서 재활용이 많이 되는 캐쉬 의 내용을 LRU 리스트에서 조차 삭제 하지 못하도록 하는 Keep 할수 있는 기능과 재활용이 거의 되지 않는다고 판단되어 짧은 시간안에 제거되게 만드는 Rcycle 기능 이 있다.

데어터 버퍼 캐쉬의 Keep 과 recycle에 대한 설정은 text 120p에 잘 나타나있다.

리두 로그 버퍼

오브젝트가 변경되거나 DML 작업에 의해 데이터가 변경될 때 변경에 대한 로그를 저장하는 SGA메모리 공간이다. (이 에대한 자세한 사항은 8장 리두로그 파일과 아카이브 로그파일을 발표하는 사람에게.. ㅋㅋ^^;;)

오라클의 로그 기법은 선로그 기법을 쓴다.(Log Ahread 기법)

변경전 데이터를 리두로그 버퍼에 미리 저장을 하여 변경시 장애가 생겨도 해당 작업에 수행을 할수 있도록 하는 기법

리두로그 버퍼의 크기 는 1~10M 이내이면 운영하는데 이상이 없다고 한다.

(리두로그 버퍼를 설정한 이후 V$SYSTEM_EVENT 데이터 딕셔너리 뷰를 통해 Log Buffer Space 대기 이벤트가 많이 발생한다면 리두로그 버퍼를 크게 설정 해 주어야 하며 그렇지 않다면 더 이상 크게 설정 할 필요는 없을 것이다.)

Large pool (대형 풀)

SGA 영역중 Shared pool, Data buffer cache, redo log buffer 는 필수 SGA 영역이며 Large pool 은 Optional 이다.

MTS(공유 환경) 환경에서는 UGA 가 공유 풀을 사용하는데 이를 방지 하기 위해서 대형 풀을 설정한다.

RMAN 사용시 파라미터 설정으로 대형 풀을 설정하게 되며 대형풀의 공간이 부족하다면 경고 메세지를 alret log 에 저장 하며 백업 및 복구시 I/O 슬레이브 프로세스를 사용하지 않게 된다.( p123)

병렬 프로세스의 정보저장을 하는데 쓰이며 설정을 하지 않으면 공유 풀을 사용한다.

I/O 슬레이브 프로세서의 정보저장 (p123 의 설명과 앞의 RMAN 의 상관관계 가 있다.)

(여기 까지 봤을 때…. 대형 풀에 대한 나의 생각은.. 대형 풀은….. 필수 SGA 영역이다… OTL 설정안 하면 ORA-4031 에러 나온다고 협박 당한 느낌….;; 꼭 설정하도록 하자…)

앞과 마찬가지로 alter system set large_pool_size = 10M; 이렇게 간단히 설정할 수있다.

JAVA pool(자바 풀)

자바풀 또한 필수 SGA 영역은 아니다. Optional 한 영역인 것이다. 하지만 JAVA 명령을 Parsing 할 경우 지정이 되어 있어야 하며 필요 없을 시 size 를 0 로 하여 효율을 높일 수 있다.

참고 및 출처 - http://blog.naver.com/luckij?Redirect=Log&logNo=50080316073

오라클 메모리 관리

공유 메모리 자동관리 (ASMM)

(튜닝시간에 배울 듯 하므로 일단 교재의 내용을 거의 그대로 베꼈음… OTL.. ㅠ.ㅠ)

- 오라클 10g 의 새로운 기능으로 옹라클이 업무 부하를 판단하여 공유풀, default 데이터 버퍼, 대형 풀 및 자바 풀의 크기를 조정하는 기능이다.

장점으로는 - 데이터 베이스 관리자가 매번 수행해야 했던 SGA 구성요소의 크기를 조정을

오라클이 자동으로 수행함으로써 업무 부하에 따라 동적으로 SGA 구성요소의 크기가 동적으로 조정 되기 때문에 메모리 사용 효율 증가와 이에 따라 메모리 부족현상이 감소 하였다고 한다.

구성방법으로 써는 http://rhrlfdud.tistory.com/entry/자동-공유-메모리-관리-ASMM의-사용-방법 을 보는 것이 가장 빠를 것이다.

정적 파라미터파일과 동적 파라미터 파일

데이터 베이스가 재 시작하게 되면 파라미터 파일(Pfile, initdb명.ora)에 설정된 값을 재적용 하게 되는데 이때 변경 값을 유지하기 위해서 동적 파라미터(Spfile, Spfiledb명) 파일을 쓰게 된다. 그이유는 정적 파라미터 파일인 pfile은 machine 운영 중 적용한 변경 값이 oracle 메모리에만 적용이되고 파라미터 파일에 적용이 되지 않으므로 초기 파라메터 그대로 남아있다. 하지만 동적파라미터 파일인 spfile 은 machine 운영 중에 적용한 변경 값이 scope=BOTH 옵션을 추가 하게 되면 변경 된 값이 메모리와 동적 파라미터 파일에 동시에 적용되므로 DB 재시작후에도 변경된 값이 적용된다.(!!!만약 machine이 정적 파라미터로 운영중이면 파라메터 값이 SGA 에 적용이 되기 때문에 몇 개월 후 DB를 재 시작 했을 때 초기의 파라메터로 돌아갈 수 있다. ※ 정적 파라메터로 시스템을 운영 하다 정기 점검등의 이유로 인하여 오라클을 내렸다가 올렸을 때를 생각 해보라. ㅎㅎ;; 지옥이 보일것이다.. ^^;;)

공유 메모리 자동 관리 사용시 고려사항

1. MMAN 백그라운드 프로세스가 관리하지 않는 SGA 구성요소

- 공유 풀의 고정영역

- 기본 데이터 버퍼캐쉬를 제외한 Db_2k_cache_size 와 같은 파라메터로 지정되는 다중 블록 데이터 버퍼 캐쉬

※ show parameter cache_size 명령으로 볼 수 있다. (기본 데이터 버퍼 캐쉬인db_cache_size 를 제외한 모두라고 보면 된다.)

- Db_keep_cache_size 또는 db_recycle_cache_size 로 지정되는 고정 또는 재사용 데이터 버퍼 캐쉬

- 리두 버퍼 캐쉬

공유 메모리 자동관리는 위와 같이 MMAN 백그라운드 프로세스 비관리 메모리에 대해서는 자동관리를 하지 않는다. (이 메모리를 제외한 나머지 메모리(SHARED POOL, LARGE POOL, JAVA POOL, DB CACHE (DB_BLOCK_SIZE)에 대해서만 자동으로 관리를 한다.)

2. 사용자가 MMAN 백그라운드 프로세스에 의해 관리되는 SGA 영역의 값 설정

- 만약 ASMM을 사용시 사용자가 MMAN 백그라운드 프로세가 관리하는 메모리들(SHARED POOL, LARGE POOL, JAVA POOL, DB CACHE (DB_BLOCK_SIZE))에 대해 값을 0 으로 설정 할 경우 MMAN 백그라운드 프로세가 자유롭게 값을 증가시키고 감소 시킬 수 있다. 4가지 메모리영역을 설정하지 않아도 0 이 된다. 따라서 설정하지 않아도 ASMM 이 구성되는 것이다.

- 만약 사용자가 최소한 어떠한 영역에 어느 정도의 메모리를 써야 한다면 사용자가 그 값을 지정해 주게 되면 지정한 값 이하로는 감소하지 않고 그 값보다 조금 더 큰 범위에서 놀게 되는 것이다.

1.오라클 내부 구조 (Oracle Architecture)

오라클은 크게 인스턴스(Instance)와 데이터베이스로 나눌 수 있습니다. 인스턴스는 크게 SGA (System Global Area) 등 메모리 영역과 Background Process 등의 서버프로세스로 구성됩니다.
SGA영역은 오라클이 사용하는 메모리 영역으로 $ORACLE_HOME/dbs/inin$ORACLE_SID.ora 파일에 정의된 값에 따라 세팅됩니다. SGA 영역을 세분화 하면 Database buffer cache와 Redo log buffer, Shared pool 영역으로 구성됩니다. 각 영역마다 저장되는 데이터가 다른 것이죠. Background Process들도 각 작업마다 분담하여 수행하게 됩니다. 기본적으로 DBWR, LGWR, PMON, SMON이 있고 네트워크환경의 데이터의 분산기능과 일관성, 백업기능을 지원하기 위해 추가로 RECO, LOCK, ARCH, Dispatcher 등이 있습니다.

오라클 데이터베이스는 데이터를 저장하는 부분으로 물리적인 구조를 보면 데이터파일, 데이터 변경에 대한 히스토리를 저장하고 데이터의 안정성을 위한 로그 파일, 데이터베이스의 전체적인 구조를 저장하고 있는 제어 파일로 구성됩니다. 그리고 Background Process와 SGA영역의 크기에 대한 값을 저장하는 파라미터 파일이 있습니다.

1-1 오라클 메모리 구조

오라클이 사용하는 메모리 영역을 보면 크게 Software Code Area, PGA(Program Global Area), SGA(System Global Area)로 나눌 수 있습니다.

1-1-1 Software Code Area

오라클의 실행 모듈이 할당되는 메모리 공간입니다. 수행중이거나 수행 될 수 도 있는 모듈이 대체로 stack에 올려져 있으며 소프트웨어가 update 되거나 재 인스톨 될 때 변경되어 질 수 있습니다. 일반 유저 프로그램이 접근할 수 없고, 크기는 O/S에 따라 약간씩 다르지만 Virtual Memory에 위치하기도 합니다.

1-1-2 SGA(System Global Area)

오라클 인스턴스의 한 부분으로 인스턴스가 Startup 할 때 메모리에 할당되는 작업 영역입니다. 이 영역은 오라클이 기동중에 사용되는 데이터나 각종 정보들 그리고 일반 유저들의 모든 SQL 문장들이 이곳에서 실행됩니다. init$ORACLE_SID.ora 파라미터 파일의 메모리변수들에 의해서 SGA영역의 크기가 정해집니다. SGA 영역은 클수록 성능상 이점이 있지만 Real Memory 영역에 위치할 수 있도록 적절히 조절해야합니다.