3. ORACLE 접속 과정, SQL 실행 과정, Shared Pool

[ 접속 과정 ]

1. client 서비스 요청

2. 리스너 서비스 확인/수락

3. 서버 프로세스 할당(수락한 요청에 대해)

 

       user process <----> server process

           단말기                   dbms 내 존재

           orange             v$process 뷰를 통해 조회 가능   

           sqlplus                  ps로 조회 가능

 

 

[ 명령어 전달 과정 ]

1. client 요청

 

2. 서버 프로세스 할당

 

3. sql 문장 체크(parse)

   1) 문법체크(syntax check)

   2) 구조체크(semantic check)

        - 객체 정보(테이블, 뷰, 컬럼) 확인

        - shared pool의 Data Dictionary cache에 저장 ( 다른 사용자가 사용할 때 빠르게 사용하기 위해 )

 

4. 실행 계획 작성(optimizer 계획 수립)

        - 최초 sql 실행 시 실행 계획을 cost 기반으로 작성 

        - shared pool의 Library cache에 저장 ( 저장이 많이 될수록 부하가 없이 빠르게 진행됨 )

           Library cache hit ratio 로 체크

      ** soft  parsing : 이미 저장된 실행계획 재사용

          hard parsing : 처음부터 다시 실행계획 수립

      ** hard parsing 발생 케이스

      1) 처음 수행되는 SQL

      2) 오래된 SQL

      3) DB restart 후 

      4) literal SQL : 변수처리되지 않고 상수가 직접 전달되는 형태의 sql

          ex) 

          select sal + comm from emp where empno = 7788; 
          select sal + comm from emp where empno = 7799; 

                          => 검색조건에 상수가 그대로 노출되는 경우 상수값이 다르면 다른 SQL로 판단, 

                            각각 hard parsing 유발

          select sal + comm from emp where empno = : emp_id;

      5) 표준에 맞지 않는 SQL

          ex)

           select sal + comm from emp where empno = 7788; 

          select sal + comm

             from emp

          where empno = 7788; 

         => 일반적으로 띄어쓰기, 내려쓰기, 대소문자 정확히 같을 경우 같은 SQL로 판단

 

 

oracle server = instance(memory) + database(disk)

 

[ instance 구조 ]

- instance = SGA + 백그라운드 프로세스

- SGA(System Global Area) 또는 shared global area 라고 부르기도 하지만 system이 정식 명칭

   : 모든 서버 프로세스들이 공유하고 있는 메모리 영역

   ** SGA = Shared Pool + DB Buffer cache + Redolog buffer cache

 

 

 

 

[ database 구조 ]

1) data file : 실제 데이터가 저장되는 디스크 영역

2) control file : DB 구조에 대한 정보를 가지고 있는 영역

3) redo log file : 모든 변경 내용을 기록하는 디스크 영역(복구)

 

 

 

 

[ SGA(System Global Area) ]

-  프로세스들이 함께 사용하는 공유 메모리 영역

- 동적 변경 가능(spfile 환경일때 가능)

 

oracle 홈 밑에 dbs 디렉토리에 initSID.ora / spfileSID.ora 가 있다.

init : 정적 파라미터 파일

spfile : 동적 파라미터 파일

둘다 있으면 spfile이 우선순위이다.

 

 

 

 

[ Shared Pool ]

- SQL  문장 수행 결과를 저장하는 메모리 공간

- library cache + Dictionary cache 영역으로 구성

  1) library cache : 실행계획을 저장

  2) Dictionary cache : 객체 정보를 저장

 

- 동적 변경 가능(DB운영중에 변경 가능)

- 수동으로 사이즈 변경 가능하지만 자동으로 관리(ASMM) Automatic Shared Memory Management

 

 

 

 

[실습]
1. 할당된 shared pool size 조회 방법
1) parameter file 확인
   (instance 기동과 관련된 파라미터 정보를 가지고 있는 설정 파일)
$ vi $ORACLE_HOME/dbs/initSID.ora
$ vi $ORACLE_HOME/dbs/spfileSID.ora    <- binary file이므로 열지않는것이 좋음

 

파라미터가 위치한 디렉토리

 

에서 

 

spfiledb1.ora 가 없으면 db는 올라가지 않는다. 그러므로 함부러 저 파일의 이름 또는 위치를 변경해선 안된다.

 

만약 저 위치나 이름을 변경하면 

다음과 같은 오류 발생

 

 

 

 

2) SQL prompt 확인
SQL> show parameter shared_pool_size    => orange 에서 수행불가능 / SQL PLUS에서 실행해야함

 

 

VALUE 0 <- ASMM(메모리 자동관리) 사용중 

 

3) v$parameter 

결과는

값이 0이라는말은 ASMM 사용중

 

2. 실시간 shared pool size 조회

결과는

 

 

[ hit ratio(적중률) 조회 ]

1. library cache hit ratio 조회

 

2. Dictionary cache hit ratio 조회