OCP 교육정리 - 오라클 튜닝(1)

<OCP 자격증 교육 정리 - 오라클 튜닝 (1)>

오라클 설치
SQL developer 
putty
오라클아키텍처
SQL동작원리
Tuning 포인트

# 다운로드
http://mango.adminschool.net/oracle -> 오라클서버
http://oracle.com sqldeveloper -> 개발도구
http://kldp.net/ putty.exe ssh -> 터미널 클라이언트

# 환경세팅
1. 개인 PC
VMNET8 : 192.168.100.2
1) putty.exe 연결
2) sqldeveloper 연결

2. VMWare서버
192.168.100.101 
1) 오라클 기동 확인 (엔진)
2) 리스너 기동 확인
3) em 기동확인
system, hr, sh 계정

3. putty 터미널 클라이언트
192.168.100.101 oracle/oracle
$export ORACLE_SID=orcl

$sqlplus / as sysdba

SQL> select status from v$instance; --상태확인 OPEN

SQL> shutdown immediate;
SQL> startup;


* vi ~/.bash_oracle
ORACLE_SID=orcl 변경
아니면 $export ORACLE_SID=orcl

set serveroutput on

4. sql developer

5.관리자
https://192.168.100.101:1158/em
sysman/oracle
system/oracle 관리자권한


# 오라클 데이터베이서 서버 구조
Instance = 메모리(SGA) + 프로세스
# 오라클아키텍처 
*PGA: 1521 포트 / 리스너 뜨면 세션 발생
-세션정보
-커서정보
-SQL 실행작업영역 
*SGA 
-Shared Pool: SQL 확인, DB object 확인, 실행계획 
1) 라이브러리캐시: SQL, PL/SQL 문
2) 데이터딕셔너리캐시: DB object
3) 결과캐시: 쿼리, 함수
4) 제어구조: lock
-Database buffer cache: 데이터파일에서 읽은 복사본을 보관
있으면 Logical Read, 없으면 Physical Read (Datafile에서 읽어옴)
DBWn(데이터베이스 기록자 프로세스)가 싱크 해주는 역할 / CKPt 
-Redo Log buffer: 데이터 변경한 후 commit 하면 Redo Log file에 쌓이고, Archive Log에 적재
LGWR(로그 기록자 프로세스)를 통해 Redo Log File에 적고
ARCn 프로세스를 통해 Archive Log 적는다.
-Java Pool: 서버 메모리에서 JVM의 세션별 Java 코드 및 데이터 사용 
-Large Pool: 백업 및 복원 / 대규모 메모리 할당 제공 / 병렬처리
-Systems Pool

*Background process
- CHPT, SMON, DBWN, ARCN 등 
*데이터베이스 저장 영역
-DataFile
-컨트롤파일
-파라미터파일
-패스워드파일
-온라인리두로그파일
-아카이브된 리두로그파일

* undo와 redo의 차이점 
1) undo: commit 이전 -> rollback / size 한계 있으므로 commit 오래 하지않으면 snapshot overflow 오류 발생
 -목적: 읽기 일관성 유지
 -관리: undo 세그먼트
 -동작: commit 전 기록
 -commit, rollback, savepoint 사용시
2) redo: commit 이후 -> Archive Log에 쌓임
 -목적: 복구
 -관리: Redo Log file
 -동작: commit 후 기록

* DML문 처리
1) SGA shared pool 라이브러리캐시에서 sql문 있는지 확인
2) 데이터베이서 버퍼 캐시에 데이터 있는지 확인
   없으면 데이터파일에서 가져옴
3) 리두로그버퍼
4) 사용자 프로세스

* commit 처리
1) 리두로그버퍼 쌓기
2) LGWR이 리두로그파일 쌓기
3) 사용자 프로세스

* 자동 공유 메모리 관리 : 고정SGA, 리두로그버퍼 제외한 나머지 메모리관리 자동으로 관리
-MEMORY_TARGET(11g)+ STATISTICS_LEVEL(통계) 
-SGA_TARGET+PGA_AGGREGATE)TARGET 
-각 메모리 구성요소 크기 수동 조정-> SGA 구성요소 및 집계 PGA의 메모리 할당 자동화


# 논리적 및 물리적 데이터베이스 구조
-논리적
데이터베이스 > 테이블스페이스(논리) > 세그먼트(인덱스, 테이블) > EXTENT > ORACLE 데이터블록
데이터베이스 > 스키마
-물리적
테이블스페이스 > 데이터파일 > OS블록
ORACLE 데이터블록 > OS블록               

* SYSTEM, SYSAUX 테이블스페이스
SYSTEM: 데이터딕셔너리테이블
SYSAUX: 보조, snapshot


# SQL 튜닝
-옵티마이저 통계 주기적으로 갱신

# 쿼리수행절차
parse -> execute -> fetch
*parse (SGA shared poll 영역)
1. 이전 parse 된 코드가 있는지 확인: 있으면 execute로 넘어감/ 없으면 체크
select * from v$sql;
2. syntax 체크
3. sementic check (object 점검)
4. 권한 체크
5. 실행계획 생성 및 선택

*execute (Database buffer cache 영역: buffer cache search)
1. Disk Read (physical read)
2. Memory Read (logical read)

*fetch: select 시 존재

*조회할 수 있는 정보: 테이블과 comments
select * from dictionary;

# 옵티마이저
Rule based: ~9i
Cost based: 10g~