개발자연습생

함수란 코드 작성 시 필요한 각각의 기능을 개별적으로 나눠 둔 것을 말한다.

다중 행 함수란 WHERE 절에서 만족하는 데이터들을 검색한 후 연산하여 하나의 행으로 표시하는 것을 말한다.

SQL에서 지원하는 다중행 함수로는 대표적으로 5가지가 있다.

1) SUM()

2) AVG()

3) MAX()

4) MIN()

5) COUNT()

이번에 사용하는 테이블은 전과 같은 BOOK 테이블을 사용한다.

사진 설명을 입력하세요.

1. SUM()

SUM() 함수는 합계를 구해주는 기능을 수행한다.

테이블에서 책의 가격의 총 합을 출력하고자 하면 다음과 같이 쿼리를 작성한다.

사진 설명을 입력하세요.

2.AVG()

AVG() 함수는 평균을 구해주는 기능을 수행한다.

테이블에서 책의 가격 평균을 출력하고자 하면 다음과 같이 쿼리를 작성한다.

사진 설명을 입력하세요.

3. MIN()

MIN() 함수는 최솟값을 구해주는 기능을 수행한다.

테이블에서 가장 저렴한 책을 출력하고자 하면 다음과 같이 쿼리를 작성한다.

사진 설명을 입력하세요.

4. MAX()

MAX() 함수는 최댓값을 구해주는 기능을 수행한다.

테이블에서 가장 고가의 책을 출력하고자 하면 다음과 같이 쿼리를 작성한다.

사진 설명을 입력하세요.

5. COUNT()

COUNT() 함수는 위의 함수들과 조금 달리 행의 갯수를 구해주는 역활을 한다.

사진 설명을 입력하세요.

현재 INSERT 한 데이터가 5개이므로 쿼리 실행시 5가 출력되는 것을 확인할 수 있다.

만약 NULL 값이 있다면 어떻게 처리될까 싶어 만화책이라는 데이터를 추가하였다.

사진 설명을 입력하세요.

그리고 다시 COUNT() 함수를 사용하여 실행해본 결과 다음과 같이 나온 것을 확인하였다.

사진 설명을 입력하세요.

즉, NULL 값은 COUNT() 함수에서 카운팅되지 않는 다는 것을 확인할 수 있다.

1. 계정 추가

Developer 에서 워크시트 창에 다음과 같이 입력한다. ([]는 생략)

사진 설명을 입력하세요.

오라클 버전이 12C로 넘어오면서 위 질의문에 ORA-65096 에러가 발생한다면 다음과 같이 해결한다.

1) 유저이름 앞에 c## 을 붙인다.

create user c##cookie identified by 1111;

사진 설명을 입력하세요.

2) 계속해서 c##을 붙이기 싫다면 아래 문장을 실행한다.

alter session set "_ORACLE_SCRIPT"=TRUE;

사진 설명을 입력하세요.

위 문장을 입력 후 실행하여 정상적으로 계정이 생성된 것을 확인.

2. 권한 설정

위에서 사용자를 추가하였다면 해당 유저에게 DB에 대한 권한을 부여한다.

권한은 보통 접속을 허용해주는 CONNECT 와 객체를 삽입, 수정, 삭제 등의 권한을 주는 RESOURCE 가 있다.

이러한 권한을 ROLE 이라 하며 ROLE 에 대해서는 추후 정리한다.

(보통 기능에 대해 그룹화 시킨 형태를 ROLE 이라 칭하는 듯 하다.)

사진 설명을 입력하세요.

RESOURCE의 경우 객체(table,trigger,index,cluster,sequence 등)를 생성, 삽입, 삭제하거나 컬럼을 추가, 삭제하는 등 강력한 권한을 부여하므로 신중하게 선택해야 한다.

3. 유저의 권한 부여를 완료했다면 접속을 시도해본다. 아래와 같이 성공으로 뜨면 접속에 성공한 것이다.

사진 설명을 입력하세요.

정상적으로 접속했다면 상태창에 다음과 같이 생성한 계정이 나올 것이다.

사진 설명을 입력하세요.

1. User Process와 Server 간 처리과정

아래 그림은 User의 요청이 발생하면 처리되는 순서를 나타낸다.

사진 설명을 입력하세요.

① User Process를 이용하여 SQL 명령 실행

② Server Process에 SQL문 도착

③ Server Process는 이전에 한번 실행된 적 있는지 Library Cache에서 검색

한 문자씩 모두 비교 (대소문자, 띄어쓰기에 따라 다른 문장으로 인식)

④ 실행된 기록이 있다면 Library Cache에서 Parse-Tree와 QEP를 이용하여 SQL을 실행 후 ⑧단계 진행.

실행된 기록이 없다면 Server Process 구문분석 과정을 시작.

⑤ Data Dictionary Cache의 데이터 정의, 통계정보를 이용하여 SQL 문법과 권한, 속성 존재 여부를 확인 후

최적의 QEP 생성

⑥ , ⑦ Parse-Tree와 QEP를 Library Cache에 저장하여 다음에 동일 요청이 올 경우를 대비

⑧ Parse-Tree와 QEP를 이용하여 SQL을 실행 후 결과를 PGA에 전달 후 User에게 반환.

※ 용어 정리

QEP(Query Execution Plan)

- 쿼리 실행 계획으로 해당 데이터를 찾아가는 최적의 방법.

Parse-Tree

- SQL을 파싱(분석) 후 결과를 Tree 형태로 저장하여 실질적인 SQL 실행의 주체로 사용.

2. SQL 구문 처리 과정

사진 설명을 입력하세요.

* Parse

· 문법 검사

· 의미 검사

· 권한 검사

· 실행 계획 => 쿼리 성능에 결정적 영향을 미침

- 신규 쿼리 : Hard Parse(옵티마이저) 진행

- 기존 쿼리 : Soft Parse(커서공유) 진행

* Bind

· 실행 계획을 1개만 생성 후 바인드 변수에 값을 바꿔서 여러번 실행하는 것

* Execute

· 데이터파일에서 데이터가 들어 있는 블록을 찾아 Databuffer Cache로 복사

* Fetch

· 복사된 블록에서 사용자가 원하는 데이터 추출

3. Parse

- Hard Parse

Parse의 권한 검사 수행 후 Shared Pool의 Library Cache를 검사할 때 기존 실행문이 없다면 Hard Parse 수행

수행 순서는 다음과 같다.

(1). SQL문을 Shared Pool에 저장

(2). Syntax Parsing

(3). Semantic Parsing

(4). 쿼리 변환

(5). 최적화(Optimization)

(6). Dictionary Cache를 참조하여 실행계획 생성

- Soft Parse

Hard Parsing 에서 (1)의 과정을 생략한다.

만약 동일 기능을 수행하지만 쿼리의 문장이 달라지면 다른 쿼리로 인식하고 Shared Pool에 기록하는데

이 경우가 빈번하게 발생된다면 많은 부하가 발생됨.

예를 들어

두 쿼리는 같은 기능을 수행하지만 대소문자가 다르므로 다른 쿼리로 인식하여 Hard Parse를 진행한다.

다른 예로

위 쿼리에서는 EMPNO와 비교할 숫자만 바꼈지만 모두 다른 쿼리로 인식한다.

이런 경우에는 바인딩 변수를 사용하는 것으로 부하를 방지할 수 있다.

1. Oracle Server 구조 (Dev)

오라클 서버는 다음과 같은 구조를 갖는다.

Oracle Server 구조

그림의 DB Server는 크게 보면 INSTANCE 영역과 DATABASE 영역으로 나뉜다. INSTANCE의 경우 메모리에 생성되며 DATABASE는 HDD에 생성된다.

즉, 실제로 데이터를 저장하고 있는 DATABASE는 단순히 파일 개념으로만 존재하며 INSTANCE에서 사용자의

요청을 처리한다. 만약 SELECT 하여 데이터를 가져온다는 것은 INSTANCE가 요청을 받고 HDD에서 파일을 꺼내찾은 후 사용자에게 보내주는 것이다.

그림에서 각각의 중요 파트는 다음과 같은 기능을 갖는다.

*INSTANCE

DB 데이터에 대한 동기화 등 데이터를 보호하는 역활. 실질적 DBMS의 형태

*SGA(System Global Area)

데이터와 공유 정보를 갖는 공유 메모리 영역

*Shared Pool

SQL 공유 영역. SQL에서 변경이 잦은 곳을 바인딩하여 공통으로 사용할 수 있도록 실행계획을 저장.

즉, SQL을 실행하기 위한 SQL문장과 문장의 실행계획, 구문정보를 저장하는 공간

*Database Buffer Cache

데이터의 보호를 위해 원본을 메모리에 저장(백업)하는 공간.

*Redo Log Buffer

DB에서 발생한 모든 변화를 로그로 작성하여 저장하는 메모리 공간.

2. User - Oracle Server 의 구조

* Dedicated Server Process

· 1:1 구조

· 유저 프로세서의 수 만큼 서버 프로세서의 개수도 늘어나는 구조.

· 유저 프로세서가 idle 상태면 서버 프로세서도 idle 상태가 되므로 자원 낭비가 발생.

· 일반적으로 사용되는 구조

Dedicated Server Process

* Shared Server Process

· 1:N 구조

· 서버 프로세서들이 개별적으로 PGA 영역에서 가지고 있던 User Process의 정보를 Shared Pool에 캐싱 후

다른 서버프로세서들과 공유.

Shared Server Process

- Server Process

· DB 접속시 유저가 가진 접속 정보를 저장. (어느 DB에 접속하는 계정인지 등을 파악)

· 유저의 요청을 DBMS에 전달하며 처리된 요청을 유저에게 전달하기 위해 PGA를 생성.

- PGA(Program Global Area)

· DB에 접속한 모든 유저에게 각각 할당되는 각각의 서버 프로세스가 독자적으로 사용하는 오라클 메모리 영역.

· 따라서 다른 유저들과 공유되지 않음.

· 서버 프로세스에서 생성됨.