'1. IT Story'에 해당되는 글 397건

• 자신이 접속해 있는 세션에만 트레이스를 거는 방법.

    

화면 캡처: 2011-12-26 오전 9:15

화면 캡처: 2011-12-26 오전 9:15

화면 캡처: 2011-12-26 오전 9:17

• 그러고 나면, 지정된 서버 디렉토리에 트레이스파일(.trc)이 생성됨

  

화면 캡처: 2011-12-26 오전 9:16

하지만, 트레이스파일을 직접분석하기에는 보기 힘들다.

화면 캡처: 2011-12-26 오전 9:18

• 그러므로 TKProf유틸리티를 사용하여, 분석하기 쉽도록 보기 쉽게 포맷팅해서 보면 됨.

• gbpr_ora_4692.trc 파일을 보기 좋게 포맷팅해서 report.prf으로 만들어주고, SQL을 파싱하는 과정에서 내부적으로 수행되는 SQL은 제외한다(sys=no).

화면 캡처: 2011-12-26 오전 9:21

Report.prf 파일

화면 캡처: 2011-12-26 오전 9:22

오랜지를 통해서 트레이스 보기

화면 캡처: 2011-12-26 오전 9:24

화면 캡처: 2011-12-26 오전 9:39

화면 캡처: 2011-12-26 오전 9:39

화면 캡처: 2011-12-26 오전 9:39

화면 캡처: 2011-12-26 오전 9:39

• 그리고, 보기 좋게 만든 트레이스파일 분석하기

• Parse : SQL문의 파싱되는 단계에 대한 통계, 단 PL/SQL 내에서 반복수행(LOOP)된 SQL이나 PL*SQL에서 보존커서(Hold cursor)를 지정할 경우 한 번만 파싱

• Evecute : SQL문의 실행 단계에 대한 통계, INSERT, UPDAT, DELETE 문은 여기서 수행한 결과를 보여줌

                   전체범위 방식으로 처리된 결과가 여러 건인 경우 주로 여기에 많은 값이 나타나며 fetch에는 아주 적은 값이 나타남

• Fetch : SQL문이 실행되면서 fetch된 통계

               부분범위 방식으로 처리된 SELECT 문들이나 전체범위 처리를 한 후 한 건을 추출하는 경우 주로 여기에 많은 값들이 나타나고

execute에는 아주 적은 값이 나타남.

               ex) AGGREGATE, 전체집계, COUNT 등

• count : SQL문이 파싱되는 횟수, 실행횟수, fetch가 수행된 횟수

    

• cpu : pares, execute, fetch 가 실제로 사용한 CPU 시간(1/100초 단위)

    

• elapsed : 작업의 시작에서 종료 시까지 실제 소요된 총 시간

    

• disk : 디스크에서 잃혀 진 데이터 블록의 수 disk가 0이면 메모리 ACCESS

    

• query : 메모리 내에서 변경되지 않은 블록을 읽거나 다른 세션에 의해 변경되었으나 아직 Commit되지 않아 복사해 둔 스냅샷 블록을 읽은 블록의 수, SELECT 문에서는 거의 여기에 해당하며, INSERT,UPDATE,DELETE 시에는 소량만 발생

• current : 한 세션에서 작업한 내용을 Commit하지 않아 오로지 자신에게만 유효한 블록(Dirty Block)을 액세스한 블록의 수

                  주로 INSERT, UPDATE, DELETE  작업 시 많이 발생, SELECT 문에서는 거의 없으나 아주 적은 양인 경우가 대부분이다.

• row : SQL문을 수행한 결과에 의해 최종적으로 액세스한 ROW의 수

               서브쿼리에 의해서 추출된 ROW는 제외됨, SUM, AVG, MAX, MIN, COUT 등의 그룹수를 사용한 경우라면 큰 의미가 없음

• Auto Trace의 실행통계 항목과 비교하기

10046 Trace 사용 방법

• 오라클은 오래전부터 다양한 종류의 이벤트 트레이스를 제공해 왔고, 이를 통해서도 SQL트레이스를 걸 수 있다.

10046 Trace LEVEL-1

SELECT DISTINCT TO_CHAR(CREATION_DATE ,'MM-YYYY')

FROM

CBO_PA_BUDGET_FIRST_V WHERE PROJECT_ID = 508

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 2 0.00 0.00 0 0 0 0

Execute 2 0.00 0.00 0 0 0 0

Fetch 4 0.01 0.05 11 290 0 2

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 8 0.01 0.05 11 290 0 2

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: 60

Rows Row Source Operation

------- ---------------------------------------------------

1 HASH UNIQUE (cr=145 pr=11 pw=0 time=49717 us)

5 FILTER (cr=145 pr=11 pw=0 time=49581 us)

25 HASH GROUP BY (cr=145 pr=11 pw=0 time=49570 us)

125 NESTED LOOPS (cr=145 pr=11 pw=0 time=31455 us)

125 NESTED LOOPS (cr=143 pr=10 pw=0 time=19627 us)

25 NESTED LOOPS OUTER (cr=116 pr=10 pw=0 time=19484 us)

25 HASH JOIN (cr=116 pr=10 pw=0 time=19405 us)

25 NESTED LOOPS (cr=101 pr=1 pw=0 time=7877 us)

25 NESTED LOOPS (cr=24 pr=1 pw=0 time=7594 us)

5 TABLE ACCESS BY INDEX ROWID PA_BUDGET_VERSIONS (cr=7 pr=1 pw=0 time=7525 us)

5 INDEX RANGE SCAN PA_BUDGET_VERSIONS_U2 (cr=2 pr=1 pw=0 time=7473 us)(object id 140787)

25 TABLE ACCESS BY INDEX ROWID PA_BUDGET_LINES (cr=17 pr=0 pw=0 time=139 us)

25 INDEX RANGE SCAN PA_BUDGET_LINES_N3 (cr=12 pr=0 pw=0 time=63 us)(object id 141887)

25 TABLE ACCESS BY INDEX ROWID PA_RESOURCE_ASSIGNMENTS (cr=77 pr=0 pw=0 time=219 us)

25 INDEX UNIQUE SCAN PA_RESOURCE_ASSIGNMENTS_U1 (cr=52 pr=0 pw=0 time=126 us)(object id 146724)

236 TABLE ACCESS FULL PA_RESOURCE_LIST_MEMBERS (cr=15 pr=9 pw=0 time=10276 us)

0 TABLE ACCESS BY INDEX ROWID PA_EXPENDITURE_TYPES (cr=0 pr=0 pw=0 time=51 us)

0 INDEX UNIQUE SCAN PA_EXPENDITURE_TYPES_U1 (cr=0 pr=0 pw=0 time=25 us)(object id 133736)

125 INDEX RANGE SCAN PA_BUDGET_VERSIONS_N6 (cr=27 pr=0 pw=0 time=139 us)(object id 140788)

125 INDEX UNIQUE SCAN PA_EXPENDITURE_CATEGORIES_U1 (cr=2 pr=1 pw=0 time=11225 us)(object id 141547)

10046 Trace LEVEL-4

Bind변수 값 확인

  아래 그림과 같이 바인드변수 값을 확인 할 수 있다.

원본 위치 <http://www.oracleclub.com/lecture/2131>

10046 Trace LEVEL-8

SQL> ALTER SESSION SET EVENTS
'10046 trace name context forever, level 8';

SQL> SELECT *
FROM EMP E
WHERE E.EMPNO = 9999999
AND E.DEPTNO = 10

Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.002 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 1 0.000 0.028 3 4 0 0
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 3 0.000 0.031 3 4 0 0

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
0 TABLE ACCESS BY INDEX ROWID EMP (cr=4 pr=3 pw=0 time=28400 us)
1 INDEX UNIQUE SCAN EMP_U1 (cr=3 pr=2 pw=0 time=22438 us)(Object ID 6485271)

-- Waiting 정보
Wait Event Name Count Wait(sec) Max Wait
-------------------------------------------------- ------- ---------- --------
db file sequential read 3 0.027 0.010
SQL*Net message to client 1 0.000 0.000
SQL*Net message from client 1 0.003 0.003
--------------------------------------------------- ------- --------- --------
Total 5 0.03

원본 위치 <http://www.oracleclub.com/lecture/2131>

10046 Trace LEVEL-12

(2) 다른 세션에 트레이스 걸기

• 성능문제가 발생한 SQL이 확보되지 않았다면, 커넥션 Pool에 놓인 세션 또는 시스템 레벨로 트레이스를 걸어 SQL정보를 수집해야한다.

(3) Service, Module, Action단위로 트레이스 걸기

• 최근의 n-TIer 주고의 애플리케이션은 WAS에서 DB와 미리 맺어놓은 커넥션 풀에서 세션을 할당받으므로 특정 프로그램 모듈이 어떤 세션에서 실행될지 알수 없고, 한 모듈내에서 여러 SQL을 수행할때 각각 다른 세션을 통해 처리될 수도 있다.
• 그러므로 한모듈만 트레이스를 거는것도 어렵고, WAS에서 맺은 세션에만 크레이스를 건 다음에 모든 트레이스 파일을 뒤져 찾는것도 어렵다.
• 10g부터 service, module, action별로 트레이스를 설정하고 해제할 수 있는 dmms_monitor패키지가 소개되면서 위의 불편함이 사라진다.

(4) 트레이스문서 분석

4-1) SQL 문장의 파싱 정보

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: 60

• 'Misses in library cache during parse'의값이0이면 소프트 파싱, 1이면 SGA Cache에 없어서 하드 파싱이 발생한 경우이다.
• 옵티마이저 모드 정보 및 파싱 스키마 정보를 확인할 수 있다.
• Recursive SQL일 경우 Recursive Depth 정보도 나타난다.(되풀이해서 쓰이는 SQL??)

4-2) 실행 계획 정보

Rows Row Source Operation

------- ---------------------------------------------------

0 HASH GROUP BY (cr=4 pr=0 pw=0 time=284 us)

0 VIEW (cr=4 pr=0 pw=0 time=213 us)

0 HASH GROUP BY (cr=4 pr=0 pw=0 time=209 us)

0 TABLE ACCESS BY INDEX ROWID PAY_INPUT_VALUES_F (cr=4 pr=0 pw=0 time=150 us)

1 NESTED LOOPS (cr=4 pr=0 pw=0 time=136 us)

0 NESTED LOOPS (cr=4 pr=0 pw=0 time=132 us)

0 NESTED LOOPS (cr=4 pr=0 pw=0 time=132 us)

0 NESTED LOOPS (cr=4 pr=0 pw=0 time=130 us)

0 NESTED LOOPS (cr=4 pr=0 pw=0 time=128 us)

0 NESTED LOOPS (cr=4 pr=0 pw=0 time=127 us)

0 NESTED LOOPS (cr=4 pr=0 pw=0 time=127 us)

0 NESTED LOOPS (cr=4 pr=0 pw=0 time=125 us)

0 NESTED LOOPS (cr=4 pr=0 pw=0 time=123 us)

0 NESTED LOOPS (cr=4 pr=0 pw=0 time=122 us)

0 NESTED LOOPS (cr=4 pr=0 pw=0 time=121 us)

0 NESTED LOOPS (cr=4 pr=0 pw=0 time=120 us)

0 TABLE ACCESS BY INDEX ROWID PAY_PAYROLL_ACTIONS (cr=4 pr=0 pw=0 time=117 us)

0 INDEX SKIP SCAN PAY_PAYROLL_ACTIONS_N51 (cr=4 pr=0 pw=0 time=115 us)(object id 129927)

0 TABLE ACCESS BY INDEX ROWID PAY_PAYROLL_ACTIONS (cr=0 pr=0 pw=0 time=0 us)

0 INDEX UNIQUE SCAN PAY_PAYROLL_ACTIONS_PK (cr=0 pr=0 pw=0 time=0 us)(object id 129929)

….

• Total count 수 = 4, 사용된 CPU time = 0, elapsed time(실제 소요시간) = 0.002, Query 수= 2, Rows 수 = 1

• *cr

  -Consistent Read(읽기 일관성)의 약자로 Consistent Read Mode로 특정 블록을 Buffer Cache에서 읽는 것을 의미

• *pr

  -Physical Read의 약자로 특정 블록을 Disk에서 읽어 Buffer Cache에 적재하는 것을 의미

• *pw

  - Physical Write 총블록수

• *time

  - 전체 처리시간(마이크로초(1/1000000초) 단위)

• *us

  -microsecond – 1000000th of a second

* 위의 전체 내용이 실행계획(Execution Plan)이며 접근경로(Access Path)를 포함하고 있음

* SQL을 처리하기 위해서는 해당 데이터에 접근한 후에 연산을 수행함

* 실행 계획 : 데이터 접근 + 연산처리

• 접근 경로 : 데이터 내의 데이터에 접근하는 경로(방법)

  - Optimizer가 선택한 접근경로에 따라 성능 차이가 크게 발생하기 때문에 접근경로에 관심을 가져야 함

  - 접근 경로(Access Path)에는 Direct Access, Index Scan, Full Table Scan이 있음

    

• Direct Access

  -TABLE ACCESS BY USER ROWID EMPLOYEES : 실행 결과에서 다음의 내용으로 사용자가 직접 사용한 Rowid를

  통해서 테이블의 데이터에 접큰한 것으로 해석해 볼 수 있음

    

• Index Scan

  - INDEX UNIQUE SCAN EMP_EMP_ID_PK : EMP_EMP_ID_PK 라는 인덱스를 읽고 인덱스내의 Rowid를 통해서

  EMPLOYEES 테이블의 테이터에 접근한 것으로 해석해 볼 수 있음

  * Index Scan 의 종류 : UNIQUE SCAN, RANGE SCAN, FULL SCAN, SKPI SCAN, FAST FULL SCAN

    

• FULL TABLE SCAN

  -TABLE ACCESS FULL 부분으로(예시에는 없음) EMPLOYEES 테이블을 처음부터 끝가지 읽어서 해당 데이터에 접근

  * 테이블의 첫 Row가 들어있는 Block부터 마지막 Block=HWM(High Water Mark = 고수위선)까지 읽는다

  * 한번에 DB_FILE_MULTIBLOCK_READ_COUNT에서 정한 크기 만큼 읽는다 .

  * 한번에 여러 Block을 읽어들이는 MultiBlock I/O를 한다.

  * DB_FILE_MULTIBLOCK_READ_COUNT가 16이면 한번 i/o 할때 16 block 씩 scan 한다.

  * Parallel로 수행 가능하다

    

4-3) SQL 문장Wait 정보

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

---------------------------------------- Waited ---------- ------------

SQL*Net message to client 2 0.00 0.00

db file sequential read 1 0.00 0.00

SQL*Net message from client 2 0.14 0.15

********************************************************************************

• 이 부분은 10046 Event의8 또는12 레벨에 의해 생성되며, SQL 문장을 실행하는 데 발생되었던 Wait 정보의 요약 값을 나타낸다. 사용자가 느끼는 실행시간은SQL 문장의 실행시간과 Wait 시간의 합이므로 이들 Wait 시간을 주의 깊게 관찰할 필요가 있다.

    

• Times Waited : Wait Event가 발생했던 횟수

    

• Max. Wait : 최대로 길었던Wait 시간(초단위)

    

• Total Waited : 전체Wait 시간(초단위)

• 실행 계획

    

1. Set autotrace trace explain.

1. 오랜지나 토드를 통해서 확인.

Statement

Table access by index rowid employees (cr=2 pr=0 pw=0 time=39 us)

Index unique scan EMP_EMP_ID_PK (cr=1 pr=0 pw=0 tion=19 us)of EMP_EMP_ID_PK (UNIQUE)

• Employees 테이블 -> EMP_EMP_ID_PK(UNIQUE)의 Employees 인덱스 EMP_EMP_ID_PK(UNIQUE)스캔 (ROWS는 1번째만 이용)

<참고문헌>

Oracle Database 11g : Adminstration Workshop

Oracle Database 10g Documentation Library - councepts

원본 위치 <http://www.oracleclub.com>

원본 위치 <http://blog.daum.net/_blog/hdn/ArticleContentsView.do?blogid=0LDC4&articleno=4653784&looping=0&longOpen=>

원본 위치 <http://www.gurubee.net/pages/viewpage.action?pageId=3900657&>

원본 위치 <http://blog.naver.com/PostView.nhn?blogId=showta3&logNo=104138454&categoryNo=63&parentCategoryNo=0&viewDate=&currentPage=1&postListTopCurrentPage=1&from=search>

• 테이블스페이스란?

  -테이블스페이스는 하나 또는 여러개의 데이터 파일로 구성되어 있는 논리적인 데이터 저장구조.

  -테이블스페이스는 크게 시스템(SYSTEM) 테이블스페이스와 비시스템(NON-SYSTEM) 테이블스페이스로 구분.

  -테이블스페이스는 사용자에게 공간을 할당할 수 있으며, 테이블스페이스 안에 저장되어있을 데이터의 가용성을

제어할 수 있다..

• 시스템 테이블 스페이스(System tablespace)

  -오라클 데이터베이스를 생성할 때 자동으로 생기며 오라클 데이터베이스의 기동을 위해 꼭 필요한 테이블스페이스 

  -모든 데이터 사전(Data Dictionary) 정보와, 저장 프로시저, 패키지, 데이터베이스 트리거등을 저장.

-유저데이터가 포함될 수 있지만 관리 효율성 면에서 포함 시키면 안 된다.

(데이터베이스와 함께 생성, 데이터 딕셔너리 포함, System undo Segment포함.)

• 비 시스템 테이블 스페이스(non-System Tablespace)

  -롤백세그먼트, 임시세그먼트, 응용프로그램 데이터, 그리고 응용프로그램 인덱스를 저장 할 수 있다.

  -공간관리를 쉽게 하기 위해서 생성.

  -유저에게 할당되는 공간.

(User Data TBS, undo TBS, temp TBS 포함.)

• 논리적 데이터베이스 구조에서의 테이블스페이스의 위치.

     

  

• 테이블스페이스의 기본구성요소들..

     

  

-테이블스페이스>세그먼트>Extent>Oracle 데이터블록

• 테이블스페이스의 종류

-SYSTEM : 오라클 커널에 관련된 테이블과 뷰 등이 저장된다. 서부적으로 Database Operation을 위한 공간, Data Dictionary 테이블, Stored Procedure, Functions, Triggers등이 저장된다.

-UNDO: 사용자가 DML문을 수행했을 때 읽기 일관성과 트랜젝션 관리를 위해서 변경 전 값을 Commit이나 Rollback 문을 수행하기 전까지 잠정적으로 보관하는 공간

-SYSAUX (system auxiliary): 10g 버전에서 추가된 테이블스페이스로, system auxiliary란 말 그대로 system 테이블스페이스의 보조 테이블스페이스로 dictionary외의 여러 시스템 관리.

-USERS : 유저들의 기본 테이블 스페이스로 사용되기 위한 Default Tablespace.

-TEMP : 많은 양의 데이터에 대한 sort작업에 사용되는 임시 저장공간.

• 테이블스페이스 관리

• 테이블스페이스와 데이터 파일 상태 조회.

-테이블스페이스 상태 조회

-테이블스페이스별 데이터파일의 상태를 조회.

-접두사가 'DBA_', 'USER_', 'ALL_'인 딕셔너리는 실제 시스템 테이블스페이스에 저장된 딕셔너리의 뷰이기 때문에 모든 테이블스페이스가 열리는 오픈 상태에서만 조회된다.

-'V$' 접두사가 붙은 것은 메모리에 만들어진 동적 성능 뷰인데, 이들 정보는 마운트 상태에서 읽혀진 컨트롤 파일에 기초하므로 마운트 상태에서도 조회가 가능하다.

• 테이블 스페이스 생성
  • 

• 테이블 스페이스 크기 증가

<참고문헌>

Oracle Database 11g : Adminstration Workshop

Oracle Database 11g Documentation Library

Oracle Database 10g Documentation Library

원본 위치 <http://www.oracleclub.com>

원본 위치 <http://www.dbguide.net/db.db?cmd=view&boardUid=12906&boardConfigUid=9&boardIdx=49&boardStep=1> 

• Undo Segment란?

     

  

• 세그먼트
  • Extent 다음 레벨의 논리적 데이터베이스 저장 영역으로 크게 4가지가 있다.

• 데이터 세그먼트: 클러스터화되지 않은 각각의 비인덱스 구성(non-index-organized)테이블에는 데이터 세그먼트가 있다. 단, External Table, Global 임시 테이블(Temporary Table), Partition 테이블은 예외.

• 인덱스 세그먼트: 각 인덱스는 해당 데이터를 모두 저장하는 인덱스 세그먼트를 가짐, Partition인덱스의 경우 각 Partition은 인덱스 세그먼트를 가짐.

• 언두 세그먼트: 각 데이터베이스 Instance당 하나의 UNDO테이블스페이스가 생성됨.

테이블스페이스에는 언두 정보를 임시로 저장하기 위해 다수의 언두 세그먼트가 포함되어있음.

언두 세그먼트의 정보는 데이터베이스 Recovery 중 유저에게 커밋되지 않은 트랜잭션을 롤백하기 위해

읽기 일관성 데이터베이스 정보를 생성하는데 사용.

• 임시 세그먼트: 임시 세그먼트는 SQL문에서 실행을 완료할 임시 작업 영역이 필요할 때 오라클 데이터베이스에 의해 생성됨.

• Rollback 세그먼트와 Undo세그먼트의 관계

-Rollback과 Undo는 기본적으로 동의어임.(Rollback=Undo)

-Oracle 9i 이전에는 Rollback Segment라는 용어를 사용하다가, Oracle 9i 이후 버전부터는 Undo Segment

라는 용어를 사용.

-추가된 기능-

-자동관리 모드와 수동 관리 모드를 선택할 수 있음

-트랜잭션 처리를 위한 알고리즘 개선.

-생성, 할당 및 튜닝을 오라클 서버가 관리함으로, DBA는 더 이상 몇 개의 Rollback 세그먼트를 생성 할 것

인지, 크기는 어떻게 할 것이며, 트랜잭션에 따라 어떻게 할당할 것인지를 결정하지 않아도 됨.

• Undo 세그먼트의 목적

  -Undo Segment는 Transaction Rollback. 읽기 일관성 유지(Read Consistency), Transaction Recovery를 위해 존재함.

     

  ※ Transaction Rollback란 Oracle 서버에서 Undo Segment에 지정된 값을 이용해서 원래의 값으로 복원하는 기능을 말함.(Transaction Rollback이 실행되는 경우는 사용자가 Rollback command를 실행하거나, Transaction이 비정상 종료되어 PMON이 작동 Rollback처리하는 경우이다.)

※ 읽기 일관성이란 Transaction이 진행되는 동안 Database의 다른 사용자는 이 Consistent Read에 의해 Commit되지 않은 변경 사항을 볼 수 없는 기능이다.

※ Transaction Recovery는 Transaction이 진행되는 동안 Instance가 실패한 경우 Database가 다시 열릴 때 Commit되지 않은 사항은 Rollback되어야 하는데 이때 Undo Segment정보가 사용됨(Undo Segment의 변경사항은 리두 로그 파일로 보호되므로 복구가 가능함.)

• Undo 세그먼트의 종류

• SYSTEM

  -시스템 테이블스페이스에 존재하는 객체에서 사용.

• Non-SYSTEM

  -시스템 테이블스페이스가 아닌 테이블스페이스에 존재하는 객체에서 사용

  -Auto Mode : 지정된 Undo테이블스페이스에서 자동 생성 관리됨.

  -Manual Mode

  가)Private : 하나의 인스턴스에만 독점적으로 사용 가능

  나)Public : 다중 인스턴스 환경에서 어떤 인스턴스라도 사용가능.

• Deferred

  -테이블스페이스를 offine immediate명령으로 offline시켰거나, recovery가 진행 중일 때 사용.

• Undo Segment 관리

• Undo 테이블스페이스 생성

  - 자동 Undo 관리에는 Undo 테이블스페이스가 필요함.

- Undo 테이블스페이스는 데이터베이스에 두 개 이상 가능하지만, 그 중에 하나만 활성화되어 있음.

  - Undo 테이블스페이스를 생성하는 방법

①CREATE DATABASE문에 절을 추가하여 데이타베이스와 함께 생성하는 방법.

②데이터베이스가 생성된 후에도 CREATE UNDO TABLESPACE 명령을 사용하여 생성 가능.

• 데이터베이스 생성시 지정방법

     

  Sql> Create database db01 (데이터베이스 생성시)

  …….

  Undo Tablespace (이름명)

  Datafile '파일 위치/ 파일명' Size (크기지정)

  Autoextend (옵션 선택);

     

  (데이터 베이스 생성 방법에 대한 부분에 대해서 부족한 점이 있어 실습은 하지 못함.)

• 데이터베이스 생성 후 별도 생성.

     

  Create Undo Tablespace (이름명)

  Datafile '파일 위치/ 파일명'

  Size (크기기정);

• Undo 테이블스페이스 변경

• Undo 테이블스페이스 전환

- 현재 사용하고 있는 Undo 테이블스페이스를 다른 테이블스페이스로 변경 할 수 있음.

(활성 Undo 테이블스페이스에 있는 Undo 세그먼트를 오프라인 상태로 변경할 수는 없다.)

  - 인스턴스당 하나의 Undo 테이블스페이스만 활성 Undo 테이블스페이스로 지정 가능.

  - ALTER SYSTEM 명령을 이용하면 됩니다

SQL> ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2

- Undo 테이블스페이스를 전환하면 새로운 트랜잭션은 새로 지정한 UNDO 테이블스페이스를 사용.

  - 하지만 모든 현재 트랜잭션, 즉 이미 존재하던 UNDO 테이블스페이스에 할당된 트랜잭션은 완료될 때까지

계속해서 기존의 Undo 테이블스페이스를 사용.

• Undo 테이블스페이스 삭제

     

  - UNDO 테이블스페이스 삭제는 인스턴스에서 현재 사용되지 않을 때 , 커밋되지 않은 트랜잭션이 포함되어 있지 않아야 가능.

 - 삭제하려는 UNDO 테이블스페이스가 데이터베이스의 현재 Active한 UNDO 테이블스페이스인 경우는 먼저 새 UNDO 테이블스페이스를 설정해야 함.

- UNDO 테이블스페이스를 삭제하려면, 테이블스페이스내의 모든 트랜잭션이 완료되어야 함.

※ 쿼리 결과 만약 PENDING OFFLINE 상태의 Undo 세그먼트가 존재한다면 이 UNDO 세그먼트에는 Active 트랜잭션이 아직 포함되어 있는 것입니다. 

• Undo 테이블스페이스 크기 결정

• Undo 세그먼트 통계조회

- V$UNDOSTAT 뷰를 사용하여 Undo에 대한 공간 할당과 사용을 모니터함.

(각 행은 10분 간격으로 인스턴스에서 수집된 통계가 저장됩니다. 시간 간격은 10분이라고 하였지만 10분 미만의 시간이 반환 될 때도 있습니다. )

- 이 뷰를 사용하면 현재 작업 로드에 필요한 Undo 공간의 크기를 예측할 수 있으며 Undo 사용을 튜닝 할 수 있다.

(자동 모드와 수동 모드에서 모두 사용할 수 있습니다.)

Undo 세그먼트 통계

• 크기 결정에 필요한 정보

-UNDO 테이블스페이스의 크기를 조정 하려면 세 가지 가 필요 합니다.

  -두 가지는 초기화 파일에서 얻을 수 있는 UNDO_RETENTION과 DB_BLOCK_SIZE이며, 세 번째는 초당 생성되는 Undo block의 수를 V$UNDOSTAT에서 얻을 수 있습니다.

※ (UR) UNDO_RETENTION (초)

※ (UPS) 초당 생성되는 Undo 데이터 블록 수

※ (DBS) 익스텐트 및 파일크기에 따라 달라지는 오버헤드 (DB_BLOCK_SIZE)

(크기를 결정하는 SQL문장에 대한 이해 부족으로 인하여 아직 실습을 하지 못하였습니다;;;)

• Undo 세그먼트 정보 조회

<참고문헌>

Oracle Database 11g : Adminstration Workshop

Oracle Database 11g Documentation Library

Oracle Database 10g Documentation Library

원본 위치 <http://www.oracleclub.com>

원본 위치 <http://www.dbguide.net/db.db?cmd=view&boardUid=12906&boardConfigUid=9&boardIdx=49&boardStep=1>

• 리두 로그 파일

     

  

     

※ 리두 로그 버퍼: 데이터베이스에 대한 변경 사항 관련 정보가 포함된 SGA의 순환 버퍼.(중복되는 내용에 대해서는 재활용을 통해서 DB성능을 개선.)

• 온라인 리두 로그파일-
• 데이터베이스의 Instance Recovery를 허용함, 데이터베이스 서버가 손상되었지만 해당 데이터 파일은 손실되지 않는 경우 Instance는 이러한 파일 안에 있는 정보를 사용하여 데이터베이스를 Recovery할 수 있다. (데이터 백업 및 복원)

① 데이터베이스에서 생긴 모든 변화를 기록하는 파일.

② 인스턴스 실패 같은 상황에서 데이터 파일에 쓰여지지 않은 커밋된 데이터를 복구하기 위해 사용.

③ SGA내의 리두 로그 버퍼 캐쉬에 저장된 데이터들은 리두 로그 버퍼가 일정수준 이상 채워지게 되면 LGWR에 의해서 리두 로그 파일로 저장됨.

④ 리두 로그 파일은 적어도 두개 이상의 그룹을 가지며, 한 그룹내의 각 맴버들은 모두 동일한 데이터를 가짐.

• 온라인 리두 로그 그룹-

  -동일한 리두 로그 파일의 복사본.

  -LGWR은 그룹내의 모든 리두 로그 파일에 동시에 동일한 정보를 기록

  -최소한 2개 이상의 온라인 리두 로그 그룹이 필요함.

• 온라인 리두 로그 맴버-

  -그룹내의 각 온라인 리두 로그 파일

  -각 멤버는 동일한 로그 시퀀스 번호와 동일한 크기를 갖는다.

  - 현재의 로그시퀀스번호(Log Sequence Number)는 콘트롤 파일과 모든 데이터 파일 헤더에 저장됨

  -> 로그 스위치를 일으킬 때

     

  ※시퀀스

  -유일한 값을 생성해주는 오라클 객체

  -기본키와 같이 순차적으로 증가하는 컬럼을 자동적으로 생성가능

  -보톤 primary key 값을 생성하기 위해 사용됨.

  -메모리에 Cache되었을 때 Sequence 값의 액세스 효율이 증가함.

     

     

• Redo log file 관리

     

  

     

  -현재 내가 가지고 있는 리두 로그 파일에 대한 정보이다.

  -그룹의 개수와 그룹에 속하는 멤버들의 수, 크기 등을 알 수 있다.(과제5 참고)

     

• 온라인 리두 로그 그룹의 추가

     

     

  

     

  • Alter database add logfile group * ('생성할 파일위치/ 파일명'+추가파일지정가능),
  • 사이즈 지정의 형태로 리두 로그 그룹의 생성.

     

  

     

  • 그룹형태로 만들어지는 리두 로그형태를 볼 수 있다.(실습에서는 1개의 로그 파일만 생성함.)

     

     

• 온라인 리두 로그 그룹의 삭제

     

  

     

  • Alter database drop logfile group * 형태로 해당되는 리두 로그 그룹을 삭제가능.

     

  

     

  ※ 위 그림은 그룹이 삭제될 때의 모습을 보여준다.

     

• 온라인 리두 로그 멤버의 추가

     

  

     

  • Ater database add logfile member '생성할 파일의 위치/ 파일명' 형태로 멤버를 추가할 수 있다.

     

  

     

  • 한 그룹, 다양한 그룹 안에 동시에 많은 멤버를 추가할 수 있다.(실습에서는 1개를 추가함.)

       

     

• 온라인 리두 로그 멤버의 삭제

     

     

  

     

  ※ Alter databae drop logfile member '리두 로그 파일 위치/ 파일명' 형태로 멤버를 삭제할 수 있다.

  (online 중의 리두 로그파일은 삭제 할 수 없기 때문에 DB를 shutdown 한 뒤 삭제나, Alter system switch logfile 필요)

     

  

     

     

  

     

  ※리두 로그 안의 멤버를 좀 더 섬세하게 삭제할 수 있다.

     

     

<참고문헌>

Oracle Database 11g : Adminstration Workshop

Oracle Database 11g Documentation Library

Oracle Database 10g Documentation Library

원본 위치 <http://www.oracleclub.com>

원본 위치 <http://www.dbguide.net/db.db?cmd=view&boardUid=12906&boardConfigUid=9&boardIdx=49&boardStep=1>

1. listener.ora

2. tnsnames.ora

3. sqlnet.ora

(Server쪽에서 필요한 파일로는 listener.ora, sqlnet.ora가 있고 Client쪽에서 필요한 파일은 tnsnames.ora파일이 필요하다.)

• listener.ora 설정방법

- 서버에 생기게 될 리스너에 대한 정보를 담고 있다.

SID_LIST_LISTENER =  // SID_LIST_뒤에 위에서 정의한 listener의 이름 ex)SID_LIST_AAA

   (SID_LIST =           //접근시킬 DB가 여러개인 경우 SID_LIST안에 SID_DESC를 여러개 만든다

       (SID_DESC =

(SID_NAME = orcl) //오라클 SID

          (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) //오라클의 실제 홈 경로

(PROGRAM=extproc)

        )

    )

LISTENER =                    //사용할 listener의 이름이 listener가 아니어도 된다.

  (DESCRIPTION_LIST =

      (DESCRIPTION =

          (ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(key=kwan)

              (ADDRESS = (PROTOCOL = TCP)(HOST = 아이피주소/도메인이름)(PORT = 1521))

//서버측의 접속할 프로토콜, IP주소, PORT번호를 적는다

(ADDRESS = (PROTOCOL = IPC)(key=EXTPROCO)

           )

       )

    )

• tnsnames.ora 설정 방법

  - Client가 서버에 접속하기 위해서 사용되는 파일이다.

     

ORCL =                // @ORCL <-접근할 때 사용할 수식어

   (DESCRIPTION =

      (ADDRESS_LIST =

         (ADDRESS = (PROTOCOL = TCP)(HOST = 아이피주소/도메인이름)(PORT = 1521))

      )

      (CONNECT_DATA =

         (SERVER = DEDICATED)    // shared서버로 접근할지 dedicated서버로 접근할지를 설정

         (SERVICE_NAME = ORCL) // 서버의 접근하려는 DB의 SID를 적는다

      )

   )

• sqlnet.ora 설정 방법

- 클라이언트가 오라클 서버로 접근 할 때, 어떻게 접속 할지 기술해 놓는 파일이다.

# SQLNET.ORA Network Configuration File: /u01/app/oracle/product/10.2.0/db _1/network/admin/sqlnet.ora

# Generated by Oracle configuration tools.

-2가지의 형태로 나뉜다.

SQLNET.AUTHENTICATION_SERVICES=(NTS)

- 클라이언트가 오라클 서버로 접속할 때, 오라클이 어떠한 인증서비스를 사용할 것인가?

 NAMES.DIRECTORY_PATH=(TNSNAMES)

- 클라이언트가 접속 시 사용하는 alias를 어디서 해석하느냐?

<참고문헌>

Oracle Database 11g : Adminstration Workshop

Oracle Database 11g Documentation Library

Oracle Database 10g Documentation Library

원본 위치 <http://www.oracleclub.com>

원본 위치 <http://www.dbguide.net/db.db?cmd=view&boardUid=12906&boardConfigUid=9&boardIdx=49&boardStep=1>

1. Instance 시작

      

   

   1. NOMOUNT
      1. Parameter 시작

         

      2. SGA 할당
      3. 백그라운드 프로세스 시작
      4. Alert<SID>.log 파일 및 Trace file 열기

            

   2. MOUNT
      1. 이전에 시작된 Instance의 정보를 바탕으로 컨트롤 파일 열기
      2. 해당 컨트롤 파일을 찾아서 열기(데이터 파일과 온라인 리두 로그 파일의 이름 및 상태를 확인)
      3. 해당 상태에서 변경가능한 내용
         1. 데이터 파일 이름 바꾸기
         2. 온라인 리두로그, 파일 아카이브 옵션 활성화 및 비활성화,
         3. 전체 데이터베이스 Recovery수행)
   3. OPEN
      1. Instance의 컨트롤 파일에서 기술한 대로 모든 파일 열림
      2. 데이터 파일 열기
      3. 온라인 리두 로그 파일 열기

            

   4. Test1 단계별로 열기

         

      

1. Instance 종료

      

   

      

   1. ABORT
      1. 종료하기 전에 최소한의 작업을 수행
      2. 일반적으로 다른 형태의 종료 모드가 작동하지 않거나 Instance를 시작할 때 문제가 있는 경우
      3. 현재 Oracle 서버가 처리하고 있는 SQL문이 즉시 종료
      4. 현재 데이터베이스에 연결하고 있는 유저의 연결이 해제될 때까지 대기 하지 않는다.
      5. 커밋되지 않은 트랜잭션은 롤백되지 않는다.
      6. 데이터베이스 및 리두 버퍼가 디스크에 기록되지 않는다.
      7. 다음 번 시작 시 Instance Recovery가 필요하며, 이는 자동으로 수행된다.

            

   2. IMMEDIATE
      1. 가장 일반적으로 사용되는 옵션
      2. 현재 Oracle 서버가 처리하고 있는 SQL문이 완료 되지 않는다.
      3. 현재 데이터베이스에 연결하고 있는 유저의 연결이 해제될 때까지 대기 하지 않는다.
      4. 커밋되지 않은 트랜잭션은 롤백됨.
      5. 다음 번 시작 시 Instance Recovery가 필요하지 않다.

            

   3. TRANSACTIONAL
      1. 트랜잭션이 완료되는 즉시 데이터베이스가 종료됨.
      2. 현재 작업 결과를 포함하는 데이터 손실이 일어나지 않는다.

            

   4. NORMAL
      1. 기본 종료모드
      2. 세션의 연결이 끊길 때까지 대기함
      3. 데이터베이스 및 리두 버퍼가 디스크에 기로됨.
      4. 백그라운드 프로세스가 종료되고 SGA가 메모리에서 제거됨.

            

   5. Test1 단계별로 종료

         

      

         

<참고문헌>

Oracle Database 11g : Adminstration Workshop

Oracle Database 11g Documentation Library

Oracle Database 10g Documentation Library

원본 위치 <http://www.oracleclub.com>

원본 위치 <http://www.dbguide.net/db.db?cmd=view&boardUid=12906&boardConfigUid=9&boardIdx=49&boardStep=1>

• 오라클이 구동 할 수 있게 설계되어 있는 도면

     

  

     

1. Pfile(정적 파라미터)
   1. 파일명 : InitSID.ora
   2. 오라클 모든 버전에서 사용가능
   3. 텍스트 형식으로 OS Edit를 이용하여 변경 가능
   4. 정적 파라미터의 종류는 적음

         

2. Spfile(동적 파라미터)
   1. 파일명 : spfileSID.ora
   2. 오라클 9i 버전부터 사용가능
   3. 바이너리 형식으로 OS Edit를 이용하여 변경 불가능(vi를 이용해서 나오는 결과는 바이너리가 아님.)
   4. 동적 파라미터의 종류는 많음.

         

3. Pfile과 spfile의 차이점

      

1. Pfile은 Os에서 vi 편집기를 이용하여 파라미터의 값을 변경가능
2. Pfile은 데이터베이스가 NOMout단계 이상이라면 다시 Pfile을 사용하지 않음.
3. 결과적으로 파일을 지워도 상관없다(잠깐 사용되고 버려짐)
4. Spfile은 Os에서 vi편집기로 절대 수정하면 않된다.
5. Nomout단계 이상에서 spfile을 다시 쓰기 때문에 제거하거나 이동하면 않됨.

      

1. Test1 Pfile 내부

      

1. Test2 Pfile, Spfile 확인

1. Test3 Pfile, Spfile 변경

      

   

   6-1 Spfile->pfile

      

   

      

   

      

   

      

   

      

   6-2 Pfile->Spfile

      

   

      

   

      

   

      

   

      

   

      

      

<참고문헌>

Oracle Database 11g : Adminstration Workshop

Oracle Database 11g Documentation Library

Oracle Database 10g Documentation Library

원본 위치 <http://www.oracleclub.com>

원본 위치 <http://www.dbguide.net/db.db?cmd=view&boardUid=12906&boardConfigUid=9&boardIdx=49&boardStep=1>