Chap. 17  Recovery - Atomicity and Durability

*Textbook: Chapter 16(6판), Chapter 19(7판)

Overview

∙Transaction States: 5 states

Ex) p.444 Fig.13-1

①Active	: The initial state

⇒ The trans. stays in this state while it is executing.

②Partially committed: After the 'final' statement has been executed.

③Committed: After successful completion.

⇒ The actual output may still be temporarily residing in 'main memory'.

- Deferred writes

⇒ It is still POSSIBLE that it may have to be 'aborted' due to H/W failure.

⇒ The changes caused by an commited trans. have to be 'redone'.

④Failed: After the discovery that normal execution can NO longer proceed.

⑤Aborted: After the transaction has been 'rolled back' and the DB has been 'restored' to its prior state.

⇒ The changes caused by an aborted trans. have to be 'undone'.

∙Failures

Trans.

Disk

Trans. Failure

System Crash

Disk Failure

'Recovery' by DBMS from Redo Log File

'Backup/Restore' by DBA

from Backup Files

DBMS

OS

H/W

∙Objective of Recovery Scheme

- Fault-tolerance: How the DB can recover from failure?

- 원자성(atomicity) 보장: Failure후에 시스템을 재시작(restart) 시키면서 완결된/완결되지 않은(committed/uncommited) 트랜잭션의 작업들을 재시작/무효화 시킴.

⇒ All/Nothing

- 지속성(durability) 보장: DB에 반영된 트랜잭션의 결과가 system crash나 disk failure에도 살아 남을 수 있도록 함.

① Trans. Failure

□Logical Error: Some internal problem such as 'bad input', 'data not found', 'overflow' or 'resource limit exceeded'.

□System Error: Undesirable state such as 'deadlock'.

⇒ Recovery by DBMS

② System Crash: System halt such as 'H/W problem's, 'bugs in DBMS or OS'.

⇒ Fail-Stop Assumption: The disk contents are NOT corrupted when a system halts.

⇒ Recovery by DBMS

③ Disk Failure: Loss of disk blocks

⇒ Copies on 'other' disks or tapes are used to recover from the failure.

⇒ Backup/Restore by DBA

∙'Recovery scheme' consists of two parts.

① Actions taken during 'normal' trans. processing: Log file

⇒ A log contains info. on 'transactions'.

□Log ID: LSN (Log Sequence No.)

□Transaction ID

□Type of action (insert, delete, modify)

□The 'old' record value (required for 'undo')

□The 'new' record value (required for 'redo')

□Info. for recovery (e.g., a pointer to the 'previous' log record of the 'same' trans.)

□Trans. status (begin, abort, commit)

② Actions taken following a 'failure': Recover DB contents from the log file.

□commit되기 전에 abort된 trans. T: Undo(T)

□commit되었지만 DB에 반영이 되지 않은 trans. T: Redo(T)

□commit되고 DB에 반영이 된 trans. T: Restore(T)

⇒ Ensures DB consistency, trans. atomicity and durability.

∙Storage Structure

① Volatile(소멸성) Storage: Main memory, cache memory

⇒ Info. in this storage does NOT survive system crashes.

② Nonvolatile(비소멸성) Storage: Disk (online) and magnetic tapes (archives /back-ups).

⇒ Info. in this storage 'survives' system crashes (NOT disk failure).

③ Stable Storage: RAIDs or Remote site storage

⇒ Info. in this storage survives system crashes and disk failures.

⇒ Info. in this storage is 'extremely unlikely' lost.

Ex)	RAID Level 0: Mirrored disk

⇒ Always keeps two copies of each block on separated disks.

Remote site storage

⇒ Always keeps a copy of each block on disks in a remote site.

- RAID (Redundant Arrays of Inexpensive Disks)

□Using a large no. of "cheap" small disks may be more cost-effective than is using a smaller no. of expensive large disks.

□Improve the "reliability" of data by using data 'replication'.

□If the disks are operated in "parallel",

⇒ The R/W rate can be improved.

disk

disk

disk

disk

disk

disk

disk

disk

disk

channel

channel

channel

disk

controller

∙Data Access by DBMS

- Trans. Ti has a 'buffer' in main memory.

□A private work area for Ti.

□Copies of all the data items 'accessed' and 'updated' by Ti are kept in a buffer.

□The buffer is 'allocated' when Ti is initiated.

It is 'removed' when Ti either commits or aborts.

Ex) Data 'buffer cache' in Oracle

- 'Block' transfer between main memory and disk.

① Input(B) : Transfers the 'physical' block B to main memory.

② Output(B): Transfers the 'buffer' block B to the disk.

⇒ Two cases of Output(B):

□Reflect the change to B on the disk at the 'END' of trans.

□The buffer manager needs the memory space.

Ex) p.515 Fig.15-1

- Ti interacts with the DBMS server using two operations.

① Read(X): When Ti needs to access a data item X for the 'first' time, it must execute Read(X).

⇒ If the block Bx on which a data item X resides is NOT in main memory, then Input(Bx) is executed.

② Write(X): After Ti accesses X for the 'final' time, it must execute Write(X).

⇒ Output(Bx) does NOT need to be executed 'immediately' after Write(X).

Why? Bx may contain other data items that are still being accessed.

⇒ 'Deferred' modification

Ex)	Oracle의 경우

Server processes: Input(Bx)

DBWR processes: Output(Bx)

Input(Bx)

Output(Bx)

D000    Dispatcher

⑥ database writer (DBWR)

□변경된 data block을 DB file에 기록함.

⇒ user process가 write를 했을 때, 이를 즉시 DB file에 쓰지 않고, 일단 buffer에 변경된 data를 기록하고, process가 끝나면 (a trans. commits) DBWR이 알아서 다른 data들과 함께 DB file에 기록함.

□또한, DB buffer의 빈 공간이 없는 상태에서 SGA로 새로운 data block을 읽을 필요가 있을 때 write가 행해짐.

⇒ Least Recently Used block

□server가 shutdown될 때, buffer의 내용을 DB file에 기록함.

□checkpoint(검사시점) 등과 같은 특정 기능을 수행할 때, data를 DB file에 기록.

□OS에 따라서 performance를 향상시키기 위해 9개까지의 DBWR을 사용할 수 있음.(initialization parameter의 DB_WRITER_PROCESSES의 값)

□User process의 Write operation

①data, index, rollback block을 SGA로 읽는다.

②update할 data를 위한 exclusive write lock을 확보한다.

③SGA 내에 있는 redo log buffer에 redo log entry를 기록한다.

(변경 전 data와 변경 후 data를 모두 포함)

④rollback segment(redo log file)에 rollback segment record를 기록한다.

⑤memory 내에 data block을 변경한다.

⇒ WAL(Write-Ahead-Log) Protocol: 변경된 block을 disk에 write하기 전에, 먼저 그 block에 변경을 기술하는 모든 갱신 log record들을 ‘먼저’ stable storge에 있는 log file에 강제 기록하여야 함.

Recovery and Atomicity

∙Overview

- ‘Simple’ recovery scheme

A system crash has occurred during the execution Ti.

Then, either 'Reexecute' Ti or 'Do not reexecute' Ti.

Ex) Balance: accnt. A - $1000, accnt. B - $2000

Ti: Transfer $50 from accnt. A to accnt B

Ti:	Read(A);

A = A - 50;

Write(A);

'System Crash'

Read(B);

B = B + 50;

Write(B);

① Reexecute Ti	: accnt. A - $900, accnt. B - $2050

② Do not execute Ti	: accnt. A - $950, accnt. B - $2000

⇒ ‘Inconsistency’ in the DB.

⇒ Both do NOT work.

- Why this scheme does NOT work?

□A failure occur after 'some' (NOT all) of the modifications have been made.

⇒ 'Either ALL or NO' DB modifications made by a trans.

□The DB is 'modified' WITHOUT having 'assurance' that the trans. will indeed 'commit'.

⇒ Trans.의 commit이 확실한 경우에만 DB가 수정되어야 함.

- To achieve atomicity,

① The modification info. must 'first' be stored in stable storage (e.g., a log file) BEFORE modifying the DB itself.

② Then, AFTER a trans. has been 'committed', perform 'all the modifications' to DB.

⇒ WAL(Write-Ahead-Logging) Protocol: DB의 변경사항들을 disk에 기록하기 전에 먼저 그 내역을 stable storage 상의 log에 기록해야 한다는 것.

- Two cases

① 'Serial' transactions recovery scheme: NO Locking

⇒ 1. Log-based recovery

2. Shadow paging

② 'Concurrent' transactions recovery scheme

⇒ 3. Log-based recovery with concurrent transactions

1. Log-Based Recovery

∙Log file

- Maintains the records of all the 'update' activities in the DB.

- Several types of log records.

<Ti, start>	Ti has started.

<Ti, Xj, V1, V2>	Ti has written on the data item Xj.

Xj had value V1 'before' the write,

and will have value V2 'after' the write.

⇒ 'update' log record

<Ti, commit>	Ti has committed.

<Ti, abort>	Ti has aborted.

<checkpoint>	Checkpoint(검사시점)

- Whenever Write(Xj) is performed, the log record for that write must be created in the log buffer 'before' Output(Bxj).

⇒ Redo/Undo is possible.

∙Log Buffer

- ① Log records are written temporarily in a 'log buffer' in main memory.

② 'Multiple' log records are gathered in the log buffer, output them in a log file by 'one' output operation.

⇒ Performance

Ex) LGWR in Oracle

- Additional requirements for transactions.

① 'Before' a data block in a data buffer can be output to the DB, all log records related to data in that block must be output to the log file.

⇒ 'Write-Ahead-Logging' (WAL)

② 'Before' outputting <Ti, commit>, all log records related to Ti must be output to the log file.

③ Ti should enter the commit state 'after' <Ti, commit> has been output to the log file.

⇒ For Redo(Ti)

∙Checkpoint

- The DBMS periodically performs checkpoints.

log

log

Checkpoint

log

failure

Checkpoint

- The log file contains a 'complete' record of all DB activities.

⇒ As a result, the volume of a file may become unreasonably large.

⇒ When is it safe to 'erase' log records?

⇒ Checkpoint

- Why?

□Without checkpoint, we need to search the entire log for Redo(Ti)/Undo(Tj).

⇒ 주기적으로 checkpoint를 지정함으로써, recovery시에 DBMS가 해 주어야할 작업의 양 - Redo(Ti)/Undo(Tj) - 을 줄일 수 있음.

Ex) Oracle의 경우: CKPT는 '선택적'

①선택: checkpoint시 DB를 update.

②선택하지 않을 경우: LGWR가 DB를 update.

- A sequence of actions are executed on a checkpoint.

① Output to 'the log file' all log records currently in the log buffer.

② Output to 'the DB' all modified buffer blocks.

③ Output to 'the log file' a log record <checkpoint>.

⇒ Transactions are NOT allowed to perform any 'update' actions

WHILE a checkpoint is in progress.

- In the log file,

□Determine the most recent trans. Ti that started executing 'before' the most recent checkpoint took place.

□The set T denote those transactions such that Ti and all transactions Tj that started executing 'after' Ti.

□T must be 'maintained' in the log file, and the earlier part can be 'erased' WHENEVER desired.

⇒ T is used by the recovery scheme when a failure occurs.

⇒ T내의 trans.들은 아직 DB에 물리적으로 반영되지 않은 상태임.

(T 이전의 trans.들은 이미 DB에 반영되었음.)

Ex)

'failure'

<Ti, start>

<checkpoint>

<Ti-1, commit>

<Ti, commit>

<Ti+1, start>

'Earlier part from this point can be erased anytime'

'Later part from this point must be maintained'

Ex)	□T0, T1, … ,T100 are executed serially.

□The most recent checkpoint took place during the execution of T67.

□T = {T67, T68, … ,T100	}

□Recovery Scheme

WHEN the system comes back,

① 'Deferred' modification의 경우

If a trans. in T is committed, it needs to be 'redone'.

The 'undo' operation is NOT necessary.

② 'Immediate' modification의 경우

If a trans. in T is committed, it needs to be 'redone'.

Otherwise, it needs to be 'undone'.

Ex) Recovery in Oracle

Input(Bx)

Output(Bx)

D000    Dispatcher

⑦ log writer (LGWR)

□Trans.이 완료(commit)되었을 때 redo log buffer의 데이터를 redo log file에 기록.

⑧ checkpoint (CKPT)

□SGA의 모든 수정된 DB buffer는 일정 간격으로 DBWR에 의해 data file에 쓰여지는데 이 시점을 checkpoint라고 함.

□CKPT는 checkpoint 시점에서 DBWR에 '신호'를 보내고, DB의 모든 data file과 control file을 수정하여 최근의 checkpoint를 기록함.

□CKPT는 '선택적'이며, 선택하지 않을 경우에는 LGWR만이 수행됨.

⑨ archiver (ARCH)

□on-line redo log file이 꽉 찾을 때, 새로운 redo log file로 switch 되면서 사용된 redo log file을 이미 지정된 archive 저장소(i.e., 다른 disk file이거나 tape device)로 복사함.

□DB가 ARCHIVELOG 모드이거나 automatic archiving이 enable되어 있을 경우에만 동작하며, 10개까지의 ARCH를 사용할 수 있음. (initialization parameter의 LOG_ARCHIVE_MAX_PROCESSES의 값)

⑩ recoverer (RECO)

□Distributed 환경일 때만 필요한 선택적인 process.

□분산 trans.의 데이터 무결성을 위해 local RECO는 자동으로 local DB의 일시 정지 process나 fail된 trans.을 commit하거나 rollback시킴.

∙Recovery with Deferred DB Modification

- Idea

□Assume that transactions are executed 'serially'.

① All DB modifications are recorded in the 'log' buffer.

⇒ Write-Ahead-Logging

② 'Deferring' all modifications of a trans. until the trans. 'partially' commits.

⇒ Deferred Writes

⇒ Trans.이 write를 했을 때, 이를 즉시 DB file에 쓰지 않고, 일단 buffer에 변경된 data를 기록하고, process가 끝나면 - a trans. commits - DBWR이 알아서 다른 data들과 함께 DB file에 기록함.

- Scheme

① IF the system crashes 'before' Ti partially commits OR IF Ti 'aborts',

THEN, the info. on the log is simply 'ignored'.

⇒ NO need to 'Undo(Ti)' since NO 'actual' updates/modifications take place.

⇒ The log records of 'incomplete' Ti can be deleted from the log.

② During Ti partially commits, the deferred outputs are executed.

⇒ Prior to the start of these updates, all the log records are written out to the log file.

⇒ Since a failure may occur 'during' this updating process.

③ IF the system crashes 'after' Ti partially commits,

THEN 'Redo(Ti)' sets the value of all data items updated by Ti to the 'new' values by using the log file.

⇒ Only the 'new' value is required in the update log record.

④ After the actual updates occur, and Ti enters the 'committed' state.

- Redo() must be 'idempotent'(등멱).

⇒ Executing it 'several' times must be equivalent to executing it 'once'.

⇒ This characteristic is required to 'guarantee' correct behavior

EVEN IF a failure occurs 'during' the recovery process.

Ex)	p.518 T0, T1

Log	DB

<T0, start>

<T0, A, 950>

crash ①	<T0, B, 2050>	A=1000

<T0, commit>	B=2000

<T1, start>	C=700

crash ②	<T1, C, 600>

crash ③	<T1, commit>

A=950

B=2050

C=600

crash ①: T0 - partially committed

Log - p.520 Fig15-4 (a)

DB  - A=1000, B=2000, C=700 (after recovery)

crash ②: T0 - committed, T1 - partially committed

Log - p.520 Fig15-4 (b)

DB  - A=950, B=2050, C=700 (after recovery)

crash ③: T0 - committed, T1 - committed

Log - p.520 Fig15-4 (c)

DB  - A=950, B=2050, C=600 (after recovery)

'second' crash during recovery,

Recovery proceeds 'again'

⇒ Exactly the 'same' as in the above examples.

∙Recovery with Immediate DB Modification

- Uncommitted modifications: Allows DB modifications WHILE the trans. is still in the 'active' state.

- Scheme: 'Undo(Ti)' is necessary

⇒ In the restart from a failure, the recovery scheme must use the 'old-value' field of the log records.

⇒ To 'restore' the modified data items.

Ex)	p.521 Fig.15-5,

p.522 Fig.15-6,

p.523 Fig.15-7

2. Shadow Paging

∙Overview

- An improvement on the 'shadow-copy' technique

Ex) Shadow-Copy: p.445 Fig.13-2

□The DB is simply 'a file' on a disk. (i.e., Tables are stored in a file.)

⇒ Maintains a 'shadow copy' and a db_pointer.

□Assume that 'only one' trans. is active at a time.

① A trans. first creates a complete copy of the DB.

② All updates are done on the new copy,

⇒ Leaving the original copy (shadow copy) untouched.

③ The trans. is 'committed' AFTER all pages of the new copy have been written out to disk

⇒ db_pointer is updated to point to the NEW copy.

④ IF the trans. has to be 'aborted',

⇒ the new copy is merely 'ignored'.

⇒ the OLD copy has not been affected.

∙Scheme

Ex) p.527, Fig.15-9

∙Adv.:

- NO Undo/Redo is necessary.

⇒ Recovery is faster.

- NO storage 'overhead' such as a log file.

∙Disadv.:

- Commit overhead: Output 'multiple' blocks on commit

⇒ Actual data block,

Current page table (c.p.t.),

Disk addr. of the c.p.t.

(c.f.) A log-based scheme: Output ONLY the log records.

- Data fragmentation

- Garbage collection

- Hard to adapt to a system with 'concurrent' transactions.

⇒ It is relatively EASY to 'extend' the log-based scheme to allow concurrent transactions.

- Shadow paging is NOT widely used.

⇒ 초창기에 사용되었음. (IBM의 System R 프로토타입)

⇒ 현재는 대부분 WAL 기반의 복구기법(recovery scheme) 사용.

3. Log-Based Recovery with Concurrent Transactions.

∙Background

① Buffer Manager

- Assume that transactions are executed 'concurrently'.

① Ti가 buffer에 있는 객체 O의 page(P1)를 변경한 후에 이 값이 Ti가 commit하기 전에 disk에 기록될 수 있는가?

⇒ 다른 트랜잭션 Tj가 객체 O'의 page(P2)를 필요로 해서 buffer manager가 P1을 바꾸어 주는 경우에 허용됨.

⇒ 이러한 기록 연산을 허용하는 방식을 절취(steal) 방식이라고 함.

⇒ 즉, Tj가 Ti로부터 page 하나를 훔침.

② Ti가 commit되었을 때, Ti가 변경해 놓은 모든 page들을 ‘즉시, 강제로’ disk에 기록해야 하는가?

⇒ 이러한 방식을 강제(force) 방식이라고 함.

- 복구관리의 입장에서 보면 '비절취, 강제' 방식이 가장 간단함.

Why?

① 비절취이므로 abort된 트랜잭션의 변경내용을 무효화(Undo(Ti))할 필요가 없음.

⇒ Ti가 commit하기 전에는 DB에 변경내용이 반영되지 않음.

⇒ Abort된 트랜잭션의 변경내용은 무시(ignore)함.

Ex) Recovery with Deferred DB Modification의 경우

② 강제이므로 commit된 트랜잭션의 변경내용을 재수행(Redo(Ti))할 필요가 없음.

⇒ Ti가 commit한 후에 즉시 DB에 변경내용이 반영됨.

What problems we have?

① 비절취 방식은 트랜잭션들이 수정하는 모든 page들을 buffer에 수용할 수 있다고 가정.

⇒ 비현실적

② 강제 방식은 page I/O cost를 상승시킴.

⇒ 자주 사용되는 page들은 buffer에 계속 남아 있어야 함.

Ex) 어떤 page가 20개의 트랜잭션들에 의해서 갱신될 때,

강제 방식 - 20번의 Output(Bx)

비강제 방식 - 마지막 1번의 Output(Bx)

- 실제로 DBMS에서는 절취, 비강제 방식의 buffer manager를 사용함.

① 절취: 어떤 page가 오손된 상태(dirty page)에서 이 page를 수정하고 있는 트랜잭션이 아직 active 상태에 있다고 하더라도 교체가 가능.

⇒ 오손 page: Buffer에 적재된 후에 수정된 page

② 비강제: 트랜잭션이 commit된 상태라 하더라도 그 트랜잭션이 수정해 놓은 buffer의 page들은 즉시, 강제적으로 disk에 기록되지 않음.

Ex) DBW0 in Oracle

- Additional requirements for transactions.

① BEFORE a page in the buffer can be output to the DB, all log records related to data in that page must be output to the log file.

⇒ 'Write-Ahead-Logging' (WAL)

② BEFORE outputting <Ti, commit>, all log records related to Ti must be output to the log file.

③ Ti should enter the 'commit' state AFTER <Ti, commit> has been output to the log file.

Ex) LGWR in Oracle

- 'Uncommitted' modifications: Allows DB modifications WHILE the trans. is still in the 'active' state.

⇒ 절취, 비강제 방식의 buffer manager 사용.

⇒ 'Undo(Ti)' is necessary

⇒ In the restart from a failure, the recovery scheme must use the 'old-value' field of the log records.

⇒ To 'restore' the modified data items.

- Objective

□Commit되기 전에 abort된 trans. T: Undo(T)

□Commit되었지만 DB에 반영이 되지 않은 trans. T: Redo(T)

② Concurrency Control

- Depends greatly on the Concurrency Control Scheme

⇒ Use Strict 2PL

□2PL + All 'exclusive' locks must be held until a trans. commits.

□Any data written by an uncommitted trans. are locked in exclusive mode until the trans. commits.

⇒ Prevents any other trans. from reading that data.

⇒ Prevents cascading rollback.

⇒ Generates 'recoverable' and 'cascadeless' schedules.

③ Transaction Rollback: Undo(Ti)

- By using strict 2PL, once Ti has updated a data item, NO other transactions could have updated the 'same' data item.

⇒ Locks held by Ti may be 'released' ONLY AFTER Ti has been rolled back.

⇒ Any other transactions must 'wait' UNTIL Ti finishes restoring the old value of the data item.

⇒ Atomicity and Consistency

- A 'failed' trans. Ti is rolled back using the log.

□The log is scaned 'backward'.

□For every <Ti, Xj, V1, V2>, the data item Xj is restored to its old value V1.

□The scanning 'terminates' WHEN <Ti, start> is found.

Ex)	<Ti, start>	"A is restored to 10"

<Ti, A, 10, 20>

<Ti, A, 20, 30>

'failed'

④ Recovery with Checkpoints

- 'Several' transactions may have been 'active' on the most recent checkpoint.

- Require

□The checkpoint log record be of the form

<checkpoint, L>,

where L: The list of transactions 'active' at the time of the checkpoint.

□Transactions do NOT perform updates

⇒ EITHER on the data buffer OR the log buffer WHILE the checkpoint is in progress.

- Idea

① Immediate Modification Case

□For all transactions Tk in T that have <Tk, commit>, execute Redo(Tk).

□For all transactions Tk in T that have no <Tk, commit>, execute Undo(Tk).

Ex) T = {Ti, Ti+1}

'failure'

<Ti, start>

<checkpoint>

<Ti-1, commit>

<Ti, commit>

<Ti+1, start>

'Earlier part from this point can be erased anytime'

'Later part from this point must be maintained'

Ti-1: No need to Redo().	; DB에 반영된 trans.

Ti : Redo().	; Trans.이 commit되었으나 DB에는 반영되지 않은 trans.

Ti+1: Undo().	; Trans.이 commit되지 않은 상태에서 중단된 trans.

② Deferred Modification Case

□For all transactions Tk in T that have <Tk, commit>, execute Redo(Tk).

□Do not need to Undo().

∙Recovery Scheme

WHEN the system comes BACK,

① Two lists are constructed

⇒	redo-list : transactions to be 'redone'

undo-list: transactions to be 'undone'

□The log file is scanned 'backward' UNTIL the first <checkpoint> is found.

For each record <Ti, commit>, Ti is added to redo-list.

For each record <Ti, start>, Ti is added to undo-list.

□Then, check the list L in <checkpoint, L>

For each trans. Ti in L,  if Ti is NOT in redo-list, add Ti to undo-list.

⇒ L: The list of transactions 'active' at the time of the checkpoint.

Ex) redo-list = {Ti, Tm}	L = {Ti, Tk}

undo-list = {Tn}	undo-list = {Tn, Tk}

'failure'

<Ti, start>

<checkpoint>

<Ti, commit>

<Tm, start>

<Tk, start>

<Tm, commit>

<Tn, start>

② The recovery proceeds as follows:

□Rescan the log file 'backward' from the most recent record.

For each Ti in the 'undo-list', perform Undo(Ti) BACKWARD.

□Scan the log file from the most recent <checkpoint, L>.

For each Ti in the 'redo-list', perform Redo(Ti) FORWARD.

⇒ 손상시점에 '활동' 중이었던 트랜잭션의 단위작업들을 무효화한 뒤,

'완결'된 트랜잭션들을 다시 수행.

⇒ When the above recovery has completed, trans. processing resumes.

□It is important to perform undo's BEFORE redo's.

Ex)

<Ti, start>

<Ti, A, 10, 20>

①Ti aborted,

Restore A = 10

②'Failure'

<Tj, start>

<Tj, A, 10, 30>

<Tj, commit>

If we perform Redo(Tj) → Undo(Ti), A=30 → A=10 (Wrong!!!)

Performance of DBMS

∙Objective

- Quantify/Compare the performance of different commercial DBMSs.

⇒ The performance is measured by 'various types' of trans.'s.

⇒ Performance Benchmark

Ex)	Two types of trans.'s, T1 and T2

Measure 'throughput' as the no. of trans.'s run in a given time.

System A -	T1 : 99 trans.'s/sec  (1초에 99개 처리)

T2 :  1 trans.'s/sec	(1초에 1개 처리)

Avg. throughput = (99+1)/2 = 50 trans.'s/sec

System B -	T1 : 50 trans.'s/sec  (1초에 50개 처리)

T2 : 50 trans.'s/sec  (1초에 50개 처리)

Avg. throughput = (50+50)/2 = 50 trans.'s/sec

Does above two systems have 'equal' performance?

Run an 'equal mixture' of T1 and T2.

100 trans.'s - T1 : 50 trans.'s/sec,  T2 :  50 trans.'s/sec

System A - Time to completion = 50/99+ 50/1 = 50.5 sec

System B - Time to completion = 50/50+ 50/50 = 2 sec

- A simple measure is 'misleading' if there is ‘more than one’ type of trans.

⇒ The right way to 'average' is to take the 'TIME TO COMPLETION'

rather than the 'THROUGHPUT'.

Ex)	① AVERAGE time/trans.

System A - 50.5/100 = 0.505 sec/trans.

System B - 2/100    = 0.02 sec/trans.

② AVERAGE throughput

System A - 1/0.505 =  1.98 trans.'s/sec

System B - 1/0.02  = 50 trans.'s/sec

⇒ System B is 25 times faster on a workload consisting of an 'equal mixture' of the two types of trans.'s.

- The 'correct way to 'average' out the throughput assuming that trans.'s of all the types are 'equally likely'.

⇒ The 'harmonic' mean of n throughputs t1, … , tn	(, where ti trans.'s/sec)

= n/(1/t1 + … + 1/tn)	(, where 1/ti sec/trans.)

Ex)	System A - T1 : 99 trans.'s/sec,  T2 :  1 trans.'s/sec

Avg. throughput = 2/(1/99 + 1/1)

= 2/1.02

= 1.98 (trans.'s/sec)

System B - T1 : 50 trans.'s/sec,  T2 : 50 trans.'s/sec

Avg. throughput = 2/(1/50+1/50)

= 2/0.04

= 50 (trans.'s/sec)

- The harmonic mean of throughputs should be used ONLY IF the trans.'s do NOT interfere' with each other.

∙TPC Benchmarks

- Transaction Processing Performance Council (TPC)

□Organization that has defined a series of 'benchmark standards' for DBMSs.

□www.tpc.org

- Types of Benchmarks

① Online Trans. Processing (OLTP): Trans. processing queries

Ex) TPC-A, TPC-B, TPC-C

② Online Analytical Processing (OLAP): Decision support queries

Ex) TPC-D, TPC-H, TPC-R

③ E-Commerce on the Web

Ex) TPC-W

- TPC-A

□Measures performance on 'update-intensive' environment.

Ex) Typical bank application

- Cash Withdrawal/Deposit on several Balance relations

□Must ensure full support for the ACID properties of trans.'s.

□Models realistically the 'end-to-end' performance.

⇒ Incorporates 'communication with terminals'

□The metric used.

①throughput - trans.'s per sec (TPS)

②price per TPS

⇒ A system may be larger and 'expensive'.

- TPC-B

□Measures the 'core' performance of the DBMS on the O/S.

⇒ NOT end-to-end performance

□Removes the parts of TPC-A that deal with users, comm., or terminals.

⇒ Focuses on the back-end DB server

□Relatively less widely used.

- TPC-C

□Models 'more complex' system than that of TPC-A.

⇒ 실제와 같은 정보처리시스템 사용

⇒ 입력폼/보고서 포함

□Concentrates on the main activities on an 'order-entry' environment.

Ex)	p.658 Fig.19-7, p.659 Fig.19-8

Entering/Delivering orders,

Recording payments,

Checking status of orders,

Monitoring levels of stock, etc.

- TPC-D

□Measures the performance of the ‘decision-support’ queries.

□Consists of a set of 17 queries modeling an OLAP system.

Tuning

∙Overview

- Selecting design choices and adjusting various system parameters.

⇒ Improving the performance of the application.

Ex)	DB Design

schema/indices design,

transaction optimization,

etc.

System parameters

buffer sizes,

no. of disks,

etc.

∙Tuning Process

단계	Tuning
Analysis	① User Requirement Analysis/Business Logic Tuning
Design/ Development	② Data Design Tuning ③ Application Tuning ④ Data Operation Tuning
System	⑤ Memory Tuning ⑥ Disk I/O Tuning ⑦ DBMS Parameter Tuning

⇒ Finding/Removing a 'bottleneck'

각 단계에서 병목점을 찾아서 개선해야 함.

① User Requirement Analysis and Business Logic Tuning

□새로운 application 개발에 앞서 현재 업무에서 개선되어야 할 business logic/data flow를 찾아서 개선.

Ex) 업무 수행 방법의 수정/통폐합

새로운 업무흐름 구축

□Application 개발자, 업무 담당자, 의사 결정권자 등의 협력이 필요.

② Data Design Tuning

□불필요/불합리한 disk access를 제거.

□Normalization/Denormalization, Indices 사용.

③ Application Tuning

□C/S 환경 하에서 client와 server 사이에 business logic 분담.

□3GL(C, C++ 등), 4GL(PL/SQL, PowerBuilder, Delphi 등) optimization.

④ Data Operation Tuning

□SQL optimization.

⑤ Memory Tuning

□Very important tuning point.

□Caching: Minimizes disk accesses.

Ex) 자주 사용되는 데이터(index 등)/SQL/stored procedure들을 buffer cache에 상주시킴.

⑥ Disk I/O Tuning

□Distributed access: 여러 개의 disk에 동시에 access 되는 data들을 분산해서 저장.

Ex) 데이터와 index를 분산시킴.

□Clustering

⑦ DBMS Parameter Tuning

□DBMS/OS Parameter Optimization

∙Tuning Point

- OLTP

① 특징

□적은 양의 data(200～500 Bytes)가 네트워크에서 심하게 이동

⇒ 시간당 Insert/Update가 많이 발생

□대용량의 DB를 많은 user들이 동시에 사용

□DB의 성장속도가 빠름

□네트워크로 연결

□짧은 response time 요구

□신속한 복구

② Tuning Point - 아래의 부분을 check/optimize

□Rollback Segment	□Index, Cluster, Hashing

□Discrete Transaction	□Size of Data Block

□Dynamic Allocation of Table/Rollback Segment

□TP Monitor, Multi-Threaded Server

□SGA Shared Pool	□SQL Statement

□Integrity Constraints	□C/S Structure

□Procedure, Package, Functions

*Discrete Transaction

Application developers can improve the performance of short, nondistributed transactions by using the procedure BEGIN_DISCRETE_TRANSACTION. This procedure streamlines transaction processing so short transactions can execute more rapidly.

During a discrete transaction, all changes made to any data are deferred until the transaction commits.

⇒ Of course, other concurrent transactions are unable to see the uncommitted changes of a transaction whether the transaction is discrete or not.

Oracle generates redo information, but stores it in a separate location in memory. When the transaction issues a commit request, Oracle writes the redo information to the redo log file (along with other group commits), and applies the changes to the database block directly to the block. Oracle returns control to the application once the commit completes.

⇒ This 'eliminates' the need to generate 'undo' information, since the block actually is not modified until the transaction is committed, and the redo information is stored in the redo log buffers.

- OLAP

① 특징

□과거에서부터 현재까지의 대량의 data를 가공/저장

⇒ 미래의 사실을 유추

□많은 동시 user의 지원보다는 수명～수십명 user의 요구 처리

□Response time은 수분～수시간

□Read와 Join이 중심 operation

② Tuning Point - 아래의 부분을 check/optimize

□Index, Cluster, Hashing

□Size of Data Block

□Parallel Query Option

□SQL Statement

□PL/SQL Function 사용

□Bit-mapped index, Materialized view 사용

- Scientific Application

① 특징

□복잡한 대규모의  SQL 연산

Ex) 통계, 기상

□시스템의 자원을 심하게 사용

② Tuning Point - 아래의 부분을 check/optimize

□SQL Statement

□PL/SQL

□Parallel Query Option

- C/S Application

① 특징

□client와 server를 network으로 연결 - 분산수행

□2-tier, 3-tier

□network 부하

② Tuning Point - 아래의 부분을 check/optimize

□Stored Procedure	□Database Trigger

□Integrity Constraints	□Array Processing

□Sequences

Ex) Creates a sequence used to 'generate' employee no.'s

⇒ for the EMPNO col. in the EMP table

CREATE SEQUENCE emp_sequence

INCREMENT BY 1

START WITH	1

NOMAXVALUE

NOCYCLE

CACHE	10;

∙Tuning for Oracle9i

- Monitoring Shared Servers

- Useful Views for Monitoring Shared Servers.

□V$DISPATCHER: Provides information on the dispatcher processes, including name, network address, status, various usage statistics, and index number.

□V$DISPATCHER_RATE: Provides rate statistics for the dispatcher processes.

□V$QUEUE: Contains information on the shared server message queues.

□V$SHARED_SERVER: Contains information on the shared server processes.

□V$CIRCUIT: Contains information about virtual circuits, which are user connections to the database through dispatchers and servers.

□V$SHARED_SERVER_MONITOR: Contains information for tuning shared server.

□V$SGA: Contains size information about various system global area (SGA) groups. May be useful when tuning shared server.

□V$SGASTAT: Detailed statistical information about the SGA, useful for tuning.

□V$SHARED_POOL_RESERVED: Lists statistics to help tune the reserved pool and space within the shared pool.

Ex) The following statement dynamically changes the number of dispatcher processes for the TCP/IP protocol to 5, and adds dispatcher processes for the TCP/IP with SSL (TCPS) protocol. There was no DISPATCHERS initialization parameter for the TCPS protocol (the only DISPATCHERS parameter was the one for the TCP protocol), so this statement effectively adds one.

ALTER SYSTEM

SET DISPATCHERS =

'(PROTOCOL=TCP)(DISPATCHERS=5) (INDEX=0)',

'(PROTOCOL=TCPS)(DISPATCHERS=2) (INDEX=1)';

If there are currently fewer than five dispatcher processes for TCP, Oracle creates new ones. If there are currently more than five, Oracle terminates some of them after the connected users disconnect.

Ex) Shutting Down Specific Dispatcher Processes

It is possible to shut down specific dispatcher processes. To identify the name of the specific dispatcher process to shut down, use the V$DISPATCHER dynamic performance view.

SELECT NAME, NETWORK FROM V$DISPATCHER;

NAME  NETWORK

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

D000  (ADDRESS=(PROTOCOL=tcp)(HOST=rbaylis-hpc.us.oracle.com)(PORT=3499))

D001  (ADDRESS=(PROTOCOL=tcp)(HOST=rbaylis-hpc.us.oracle.com)(PORT=3531))

D002  (ADDRESS=(PROTOCOL=tcp)(HOST=rbaylis-hpc.us.oracle.com)(PORT=3532))

Each dispatcher is uniquely identified by a name of the form Dnnn.

To shut down dispatcher D002, issue the following statement:

ALTER SYSTEM SHUTDOWN IMMEDIATE 'D002';

Ex) Changing the Minimum Number of Shared Server Processes

After starting an instance, you can change the minimum number of shared server processes by using the SQL statement ALTER SYSTEM. Oracle will eventually terminate servers that are idle when there are more shared servers than the minimum limit you specify.

The following statement dynamically sets the minimum number of shared server processes to two:

ALTER SYSTEM SET SHARED_SERVERS = 2;

If you set SHARED_SERVERS to 0, Oracle terminates all current servers when they become idle and does not start any new servers until you increase SHARED_SERVERS. Thus, setting SHARED_SERVERS to 0 may be used to effectively disable shared server.

- Monitoring Processes and Sessions

□V$PROCESS: Contains information about the currently active processes

□V$SESSION: Lists session information for each current session

□V$SESS_IO: Contains I/O statistics for each user session

□V$SESSION_LONGOPS: This view displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution. More operations are added for every Oracle release.

□V$SESSION_WAIT: Lists the resources or events for which active sessions are waiting

□V$SYSSTAT: Contains session statistics

□V$RESOURCE_LIMIT: Provides information about current and maximum global resource utilization for some system resources

□V$SQLAREA: Contains statistics about shared SQL area and contains one row for each SQL string. Also provides statistics about SQL statements that are in memory, parsed, and ready for execution

□V$LATCH: Contains statistics for non-parent latches and summary statistics for parent latches

- Monitoring Locks: The utllockt.sql script displays, in tree-structured fashion, the sessions in the system that are waiting for locks and the locks that they are waiting for. Using an ad hoc query tool, such as SQL*Plus, the script prints the sessions in the system that are waiting for locks and the corresponding blocking locks. The location of this script file is operating system dependent; see your operating system specific Oracle documentation. A second script, catblock.sql, creates the lock views that utllockt.sql needs, so you must run it before running utllockt.sql.

The following view can be used for monitoring locks:

□V$LOCK: Lists the locks currently held by the Oracle server and outstanding requests for a lock or latch

DB Back-up/Restore

∙Overview

①Back-up: ‘DB’ 문제 발생시 recovery를 위해 disk나 다른 storage에 복사본을 저장하는 작업.

②Restore: Backup data를 사용하여 문제가 발생하기 이전 상태로 DB를 recovery하는 작업

∙Backup in Oracle

- Archive mode: Book p.287

- Backup and Recovery Manual

Security

∙Objective

- The data in DB need to be protected from

① 'unauthorized' access

② malicious destruction/alteration

③ accidental introduction of inconsistency.

∙Security in Oracle

- Oracle white paper in otn.oracle.com