intro oracle

5/26/2018 Intro Oracle

1/52

Oracle Database Administrator

Oracle Architectural componentsOracle server 2 Oracle instance Oracle database

Oracle instanceSystem Global Area ( SGA ) !" startinstance background process Oracle server #"database$%"&kernel

1 Oracle Instance

System Global Area (SGA) &%- Database buffer cache &"!!' Data file $&(")*!+!#,Databasebuffer cache ( ! - parameter D!"O#$!SI%& " D!"O#$!'&

- Shared pool#&"! data dictionary "!#%shared pools+parameter S*A&D!+OO"!SI%&parameter file .shared pool 2 -Library cache(Shared SQL Area)."ibrary cache"&!

$%"S," statement !#%#-e.t of statement /+arsetree /&.ecution plan (#"/#$!$ Statement

-Data Dictionary cache& information data dictionary!+ ,uery optimi0ation $!/)/0$% +"

- edo log buffer"&!/'" & D1" statement & # old value/ ne old /instruction /user id/

1


2/52


timestamp redo log buffer +"OG!'&parameter file

SGA

shared pool

database buffer

cache

Redo Log Buffer

library cache

Data Dictionary

cache

Large pool

Java pool

2 System Global Area

- "arge +ool-set instance &Shared Server !#&!Shared pool $&Dedicated Server !#ackup and recovery ecovery 1anager '&"!I3O (Input3Output Server +rocesses)- 4ava +oolackground +rocess &process #SGA /user "!&$$"+process&$$"$$"+$-5 $"&!$"$$start instance - Database 6riter (D6) 7! Database buffer cache

Data file

- "og 6riter ("G6) 7!edo log buffer edo log filetransaction user "commit- System 1onitor (S1O8) 7$.!

recovery $start database- +rocess 1onitor (+1O8) 7""process fails- #heckpoint process (#$+-) 7"(/# update database

2


3/52


3 ackground +rocesses

Oracle database! 1# Database (D!8A1&) #1.(+hysical structures) file 2/"$/ file Database file $"Oracle Databasedatabase file

- Data file"&data dictionary / user ob9ect $%/'"transaction$: data fileedo log file "& data , record /

database buffer cache "3 instance failure $ recover data +""" ( commit ) $ data ""+!"&data file ( roll forord )$2 redo log file

- #ontrol file !"3$%&!"4.database

""file $%database file #- +arameter file#"&$",-"4-$%Oracle instance- +assord file $/)/0#- Archived redo log files "redo log file /,&backup "recover /"#&!

3


4/52


D a t a b a s e

D a t a f i l e s

C o n t r o l f il e

R e d o l o g f i l e s

P a r a m e t e r fi le

P a s s o r d f il eA r c h i v e d l o g

f i l e s

! r a c l e D a t a b a s e

4Oracle Database

Query process:; " 'ser process


5/52


:; !!data file"&database buffer cache2; $!$Statement data dictionary cache>; redo log file"&ne value /old value /instruction /userid /timestamp

redo log entry

@; "&!+ database buffer cache@

server processdatabase buffer

cacheredo log buffer

data dictionary

cache

data file

D$L statement

5

2

:

>

6D1" Statement +rocess

ne value old value "!database buffer cacheuser"commitCommit process ""commit:; "G6 /!redo log buffer redo log file2; message 'user "&/>; "$%!lock+!"user %#

!!+

server processdatabase buffer

cacheredo log buffer

redo log fileuser

:

@

> 2

#O11I- +rocess

5


6/52


!Oracle process and memory structure

6


7/52


"tartup and "hutdo#n Database"#startup database

S-A-'+ BO#&C B&S-I#-C B+I"&


8/52


#heck the synchroni0ation information; ( datafile / redo

logfile )

O+&8

O4-I?&

Allo an application access;

8eed a admin; -ask ; ( create user / create table/ drop

tablespaces )

S-A-'+

Start an oracle instance ( background process F SGA )

#heck hether the control file ( s ) is valid and all control

file ( s ) must be .AL/D;

ead content of controlfile ( db!name/ physical structure

( location and filename of data file and redo log file ) ;

#heck the synchroni0ation information; ( datafile / redologfile ) and all oracle database file must besynchroni0ed;

8


9/52


8O1O'8-

read init;ora

idintify control file

create and initiali0e SGA

start background process

%ree stages during database startup

1O'8-

open control files

mount database

O+&8

open and lock datafiles

open online vedo logs

recovery if nescessary

Open 1ount 8omount

Startup +rocess"#Shutdon database S*'-DO68 B8O1A" E -A8SA#-IO8A" E I11&DIA-& EAO- C

Shutdown normal+,3$connect /',session

disconnect "'stamp file $%shutdonShutdown transactiontransaction "/!/,""disconnect1"$1"$/'shutdonShutdown immediaterollback transaction "/!"disconnect1"$1"$/'shutdon

9


10/52


Shutdown abort rollback data file shutdown!!"startup #recovery instance

%ree options for shutting don database

Stop access to database

ait until users e&it

flush DB buffer and redo log caches

drop file loc's

complete ongoing tracsaction

update file headers

close thread

drop DB instance loc'

synchroni(e control files and DB files

cancel active

transaction and rollbac'

immediate normal abort

no recovery re)uired re)uire crash recovery

10


11/52


1 Shutdon +rocess

/nstance reco)ery:; oll forard " data +!"& data file $+"& online redo log

2; Opening the databade instead of aiting for all transaction to berolled back before making the database available

>; olling back uncommitted transactions by S1O8 and by theindividual server processes as they access locked data

reco)ery

- :- 2

- >

- @

- 5

# h e c k p o i n t a i l

11 System don

:; data!"& disk (#check point &" ! disk # am

2; transaction+""commit /server processcommit recordS#8 to transaction synchroni0e date "& redolog file-: !data file -2 / -@ ! vedo log file "+ disk system failure !

roll forard database start '"!!-* -> / -5 data ! rollback segment / data ! redo logbuffer system failure ! rollback segment "

11


12/52


Control ile&file binary &start database ,%"mounts instance!control file !"&control file+

database name

#!data files redo log files #tablespace time stamp of database creation

current log seHuence number

#heckpoint information

"og history

ackup information of the recovery manager utilityADD Control ile:; Shodon database normal

2; copy control file OS command>; /#!fileparameter #O8-O"!I"&parameter file@; start up database control file "&+disk "2 file #+backup control file$,""Database

%edo lo* ile"&data ,record /databasebuffer cache"3instance failure $recovery data

+""commit $data ""+!"&data fileedo log file2 group""$"$"'$&1+$"'$group member &mirror""/"media "!

12


13/52


Mana*in* $ablespace and Data 7iles-ablespace (3#"&!&,logical ""$datafile #"&/%1: -ablespece data file +$" $$ : data file "database

tablespace: $" System tablespace #&data dictionary

table space

segment segment

e&tent e&tent

data bloc'

data bloc'

bloc'

bloc'

e&tent

e&tent

12 -ablespace Structure

$ablespace &ses "$user " "&!!disk ".#disk !

" //tablespace $"backup recovery "&!"4-(read only) !

(readrite) tablespace'"tablespace "Data ile: tablespace +data file $: data file&tablespace"data file "+database!1parameter 1ADA-AI"&Sparameter file

13


14/52


%elationship 8et#een "e*ments 9:tents and Data bloc;s

Database

%ablespace

Segment

*&tent

!rable bloc'

Data file

!+S Bloc'

Database Structure

Logical Physical

13 elationship eteen Segments /&.tents and Data blocks

Segment 7+//64 logical storage

structure ithin a table space #/ segment & -able segment Inde. segment

-emporary segment

ollback segment&.tent 7 &set lock $"Data lock 7 ( data ) Oracle database !& datablock Data lock ! +arameter D!"O#$!SI%&parameter file

$ype o $ablespace 2 #/ "ystem tablespece "& data dictionary information / definition

of stored procedures / packages database trigger / System rollbacksegment +"&system tablespace

14


15/52


on


16/52


Oline tablespace $ data file , offline - file fail offline > " 8ormal ( check point) -emporary (+ check point ) Immediate (+ check point )- table offline +', Ob9ect ,! tablespace" Oracle check point tablespace offline tablespace + offline online , $,-*

!"' data dictionary control file tablespace is offline shutdon a database + !check reopen database

# datafile tablespace $$"$":; offline tablespace

2; #OS copy 3 move file " physical vie>; e.ecute " Alter tablespace rename datafile@; online tablespace5; # OS delete file +&$#

$# Data file +offline +:; shutdon database2; #OS copy 3 move file>; 1ount database@; e.ecute "5; Alter tablespace rename data fileR; Open database

" e.tent !$/"&""A"-& -A"&S+A#& tablespace!name #OA"&S#&

" tablespace " tablespace "& rollbacksegments1""

create rollback segment rbsM: tablespace rbsstorage ( initial :MM$ ne.t :MMk

optimal @1 mine.tents 2M ma.e.tents :MM )

$emporary $ablespace""!+"&permanent ob9ect !!temporary tablespace!/++#&tablespace &temporary1option -&1+OA $tablespace Default storage -emporary tablespace "

16


17/52


Initial < ne.t < (multiple of sort!area!si0e ) FD!"O#$!SI%&

pctincrease < MData file"&disk "$"data tablespace

17


18/52


Create and Mana*in* &serCreate user create user $$!$%"user" user name and authentication mechanism

tablespace ""!user temporary tablespace ""! tablespace &$# /)/0'$%user +"#create user#&A-& 'S& userID&8-II&D Jby passord E &-&8A""KB D&A'"- -A"&S+#& tablespace CB -&1+OA -A"&S+A#& tablespace C

B ,'O-A J integer B $ E 1 C E '8"I1I-&DK O8 tablespace CB +ASS6OD &+I& CB A##O'8- J "O#$ E '8"O#$ KCB +OI"& J profile E D&A'"- K C

+arameter

'S& "3#(!#Jpassord3&-&8A""3G"OA"" ASPDistinguish8ame(D8)QK

,/)$$"$(!#1 passord &$Database Authentication $,"("&-&8A"" &$$"$(!#&.ternal Authentication

G"OA""

&$$"$(!#Global Authentication

BD&A'"--A"&S+A#&tablespaceC

7$*/8"&&$*(!#-++,/8-&$*

B-&1+OA-A"&S+A#&tablespaceC

7$*/8#"

B,'O-AJinteger

KB$31C3'8"I1I-&DK O8tablespaceC

/8(!##+"$

",9/8,"$/8#"+,

B+ASS6OD&+I&C

(!#$"(&/!"

BA##O'8- &&&"3#(!#

18


19/52


J"O#$3'8"O#$KC

B+OI"&Jprofile3D&A'

"-KC

1*+7*","(+Oraclei#

1*+7*#TD&A'"-U ""3#(!#1*+7*!T1*+7*U

%ole &,/)/0$"(!#"(!#&,$"4-$$,"#/ )/ 0#&A-& O"& role!name B8O- ID&8-II&D 3 D&8-II&DC J passord 3 &-&8A"" 3 G"OA"" 3 'SI8G packageKC

+arameter

ole!name #8O-I-&8-II&D

#+$$$"$(!#

ID&8-II&D #$$$"$(!#8'/)#$@

:; +ASS6OD2; &-&8A"">; G"OA""@; 'SI8G +A#$AG&

passord "( &$$"$(!##"((!#$#$,"($

'SI8G package &&-(!#"#"/#"T/#"(Application ole)U (!#&&,3$#"/#"+1&&,3$#+&$"(./#"-++,&&&Oraclei &&!(!#"

&-&8A"" #(!#$!$(/.#2/"$/G"OA"" #(!#$!$(Oracle

Internet DIrectory

'ri)ile*es&/)/0$%+"$user"#/)/0

19


20/52


GA8- Jsystem E roleK -O Juser E role E publicK 6I-* AD1I8 O+-IO8

6I-* AD1I8 O+-IO8 user +"/)/0/)/0+"$"user +

14 'ser 1anagement

20


21/52


8ac;up Method&.port and Import utilities &!oracle database" !$" ! "$ user tablespace #"backup data reorgani0e database

:port &tility!5!"&!data dictionary &file OS &binary file &+disk tape!"#&+ $&6OD


22/52


$eyord Default )/I8D&S & e.port inde.

"OG 8'"" #+7*&"

e.portO68& 'sername user $e.port+AI"& #+7*&!"parameter

import$eyord Default )/OD"&8G-* record e.portO6 & data

e.port S-A-IS-I#S &S-I1A

-&/)#import

-A"& Schema;table

e:p scott=ti*er tables + (dept9dept- ile + emp>dmp lo*+e:p>lo* compress+ndirect+y recordlen*th + 326!

/mport &tilityfile +&.port 'tility !Oracle database!"#I1+ $&6OD


23/52


$eyord Default )/'"" 8 & $import

"GA8-S & $ import

grant *&"+ 8 &parameter)/

IG8O& 8 & + error/'& 8 error/'!import

I8D&S & import inde.

I8D&I"& 8'"" #+7*&inde."OG 8'"" #+7*&"

import

$""imp scott3tiger tables


24/52


Oracle /nstance $unin*'% Oracle instance ##1emory I3O ($+"$/)/.$!+!)/+U!++/

"3"$+U

$""$$""'(+$/+&+$"+&$/)&("&++"% (++/'+ /"$/"3 /"$ /$*/$#+#,&,/$ "$$,-/!#1emoryOracle Instance $!#I3O /1#*&/&,&"$+/)/.,/$*&!&, #+' #ache $System memory I3O#'!memory &" $,$#memory/)/.,#I3O +""$'!"Disk I3O131##ache1emory /)/.#I3O'"&'!1emory +##+' "%- I3O ",/)/.1##aching #System #+' /)/. /!1emory$&I3O ,.Oracle MemoryOracle Instance !!&!"1emory Disk 8'1emory&!$#!$$"Disk ,!+!$#"memory1emory +&'#'!&++$!/-#"""'$##/"#1emory /)/.$,*1emory !#1Oracle #aching 1emory (!#$

24

8ote7 "$Oracle Instance +""$Application13'"!'!# 1emory Application Application($"Instance


25/52


"$1emoryOracle Instance""$"%" -he Operating System

+rivate S," +"3S," areas -he Share +ool

-he edo log buffer

-he uffer #acheOperatin* "ystem/"$1emory "Oracle Instance $",-+1emory +!"Oracle+//+aging Sapping 8'&($/)/."$2/"$/"Oracle &$""Oracle +2/"$/$"$"$+ 1emory 8'"SGA "1ain 1emory

1emory 8'"'ser "1ain 1emory paging Sapping 1emory 8'"/2/"$/ 1emory 8'"2/"$/Oracle #&!

'!backup %'ri)ate "QL and 'L="QL+rivate S," &!1emory ,!!untimeuffer ,%#"S,"+rivate S," "($(!#+rivate S," +&2 - +ersistent Area#,!"!Input !+memory'!"!+"

column ,Statement- untime Area#,!"S," untime

Area '!"8"8#/"S," os #(1emory!"+(&/!1emory!"!"+!+#

""'$"$ 'ser +"

#ursors #//)/.1$+-*#&$/$"TO+&8!#'SOSUOracle1emory $"$'ser !1Application $"#O+&8!#'SOS 8'1emory!1"$1"$/2/"$/1emory !$'ser !,1$"

25


26/52


Application 1emory #& 8'$//$*$" $/System am Sapping +aging"hare 'ool

Share +ool#, library cache data dictionary cache & !Session "$$!$Share +ool"ibrary #ache,!S," +"3S," #" //)/.1/#achehit ate"ibrary #ache 1/&'"ibrary#ache &"S," #% #ache,8'"S," #"4-":; $""4"S,"$""+#'6hite space 2; 'schema ob9ects"S," $+("*&Ob9ect ">; #$""$##"1#/"@; "S,"$!Optimi0e1#/)"#-#ostase$#"#Store +rocedure&""+# memory"(&Store +rocedure&!+$+$$,"-$?W"IA#A#*,!/$/#"ibrary #ache 8'"$+!#ache+"$+!"ibrary #ache / 1emory1"/$"TS*A&!+OO"!SI%&U Oracle

$/ #ursor" Session1//$*O+&8!#'SOS ""/ 1emory /+($ 1emory / & 1 / $*#'SO!S+A#&!O!-I1&&-'& 8',Share S," Area+!"#ursor "!/+Data Dictionary #acheData Dictionary #ache ,!-able ?ies 8'Oracle#"'DatabaseData Dictionary !"$+- !(!##/)/#- Integrity #onstrains !'"-ableDatabase- ##/!,%#olumnDatabase -able- !#Schema Ob9ectData Dictionary "!#1Oracle $"S," 8'$/)/. Data Dictionary #ache/$/#8'&+$?WO6#A#*&

26


27/52


# Data Dictionary #ache "$+!+/ :M ' :5*8&$*"$ +1/ 1emory " DataDictionary #ache / $ * $" " "ibrary #ache TS*A&D!+OO"!SI%&U

Shared Session!Session 8'+""+rivate S," Area +/" +1(1emory !1Session "!,1emory !$Session$?WS&SS-A- !$+&1emory !#1'GA('ser Global Area) 1+GA (+rogram Global Area)8uer Cache"3,#ache&uffer #ache 8'&"Oracle SGA !#",%,uery 'pdate$"Data lock !"!copy++ uffer #ache

$"!!uffer #ache !/$!"!SGA +&'!SGA $+copy !Data ile++SGA 'pdate+!Data lock ufferSGA "!Disk'!"Database 6riteruffer #ache !#""'+/$/#uffer #ache !&+$?WSSS-A-#achehit ate $ XM YM *8&$* &$/ Database

uffer #ache / / )/ . 1 " / / $ *TD!"O#$!'&SU 8','Database lock uffers !""!#-+"uffer Si0e < D!"O#$!'&S L D!"O#$!SI%&

-#ache hit +Database lock uffer '$ W$#* $ ! # / $ *D!"O#$!"'!&-&8D&D!S-A-IS-I#S , os$W$#* 8',-/)/.uffer #ache 3' Data ile Database lock uffer &"$ I3O"&$3$+&(Share pool '+&&"3"S,"

27

7 D!"O#$!'&S#1emory /++#&(/Sapping+aging/%


28/52


,%"D!"O#$!'&S /$*%Oracle"+$I3O ateOS "$#achehit ateOracle $unin* $he /=O "ubsystem

"I3O &'&"3$(!#Oracle%-/)/.!"1Disk I3O-Disk !+&I3O bound Disk oundDisk "$$8'+",+"""Disk I3O&$'"Disk """ !""4- Disk#+#"I3O ",/'#$"!I3O $"'$$"+!'"$1emory '"$Disk I3O

,)*""$Disk I3O "4,Drive !.$"#&//)/.Disk !!%Disk !!Thot spotsU hot spot + *ot spot /'+&$,#ontention /+Disk !,DiskDis; ContentionDisk #ontention /''". Disk Drive !8'!+"/$/ile I3O Oracle /$/2/"$/8''$/$/Oracle "

'/$/Oracle.!$#I3O "Data file $+'&/Disk "/%/'$Oracle Data ile "+hysical Disk %"!"'!Disk!&!$?WI"&S-A- 8'"")*"$?WDA-AI"& ,ile #1'ile"$&.#I3O Data ile#I3O ""$Data ile &(.$"I3O+/".$Disk /"3"I3O Disk !'"#'!"!+!Disk "Dis; Contention,"3Disk #ontention$/*I3O ate $DiskDrive#Disk &!Disk Array /*$$"Disk &2/"$/ thirdparty -ool$#I3O $/$Disk &'

28


29/52


I3O ate $Drive 8'+/5M I3O $/$Driveandom Access :MM I3O $/$DriveSeHuential Access"Disk Array &&2/"$/thirdparty -ool$#I3O #"$$$I3O $/$Disk

8'Disk Array!Disk !8'Disk Array &/",""$I3O ate %"$Disk"$+&"3,-"4- I3O ,-#

I3O +2 ."e?uential /=O.3!/*$"'!redo log file "4-"I3O !%andom /=O!!!"+/*'!&,

,$I3O rate /*+7*2 /-+

"e?uential /=OS#SIII /*+7*",+:MM ':5MI3O "$/%andom /=OS#SIII /*+7*",+ 5M 'RM

I3O ,$/

"3/*:-;*#"3/*":; Isolate seHuential I3Os seHuential I3O /'

+/*+7*&

2; Spread out random I3Os as much as posible ,-$!1#Oracle striping/ OS striping *ardare stripig>; Separate data and inde.ed $#"1#/&8*

$/+"$#!/&8*/*$%!%"

@; &liminate nonOracle disk I3O from disk that contain database filesDisk I3O %/*"Oracle (#!Oracle #

/solate "e?uential /=Os

Isolating seHuential I3O#'!"/*"$$!'!/*,/)+1redo log file ++/*$

$ redo log file !/*" 19mirrored log file ,-mirroring1#OS *ardare mirroringredo log file++$?olumes "$ log file !"

29


30/52


/*$""/Seek rite "og file 8'&$,+erformance $

"&/"3$"redo log file(/1#mirroring 8'Oracle +#OS #,-* *ardare

fault tolerance &+Speared Out andom I3Os

1)#$/random I3O '!$%7!Oracle !'.random I3O1//*+"$+"/*$%,-+1Striping !++/*%$"$"%&1$$++$+7*

Striping &!3%+!&!&Stripting +1#Oracle #OS #*ardaredisk arrays

Oracle StripingOracle striping "$!+&&%1

"tablespace "#&A-& -A"&S+A#& stripedtablespace#""$+S,"= #&A-& -A"&S+A#& mytablespace

2 DA-AI"& Pfile:;dbfQ SI%& 5MM$/> Pfile2;dbfQ SI%& 5MM$ /

@ Pfile>;dbfQ SI%& 5MM$ /5 Pfile@;dbfQ SI%& 5MM$ -ablespace created;

-ask !-*,-table .tablespace datafile @ $"$+&"$#data file @ $""S,"= #&A-& -A"& mytable

2 ( name varchar (@M)/> title varchar (2M)/@ office!number number(@) )

5 -A"&S+A#& mytablespaceR S-OAG& (I8I-IA" @5 8&- @5$X 1I8&-&8-S @ +#-I8#&AS& M )

-able created;

$"$7!5MM$ 8'Stripe si0e $3Stripe &38'Stripe 31#*

30


31/52


,-!3$ #"Os1,$*O"-+ 3Stripe &I3O +1#*

7!'"$"&"

"$$7!"&: 7!$diskvolum $'$5!,-:M G ,-:M disk volume7!,-: G

,-/7!&%+'I3O ,-+8'"7!

Oracle striping !##$"OS hardare striping+

OS Striping'!"$"OSStriping"OS $6"

1,$*#OS striping!$$/$"OS "%OS disk striping !16"/*2 logicaldisk 3"stripe$"$"$/*

:/ /*2 /*>+$"+1$strip '"OS 87**#stript

'!"87**,-#Strip /* OS 1onitoring facilitiesdisk I3O rate$disk perlogical disk basic /$"3!&,-I3O rate $/*+

%87**OSstriping$","#1#*11AID 8'$,-"$/"(/+

OSstriping $"&/"8'"3+/#*ardare striiping

31

!!"%A/DAID (edundant Array of Ine.pensive Disk) !"

$"&!""1"++AID M !""1"+ 8o ault -olerance (8-) !!

+/*$$"AID : 1irroring !!"&+7*"4-88

" AID 2 itlevel striping "//8'++#/

AID > Sectorlevel striping "+#/AID @ +arity disk; /*: @ $"!#&/$+7*"fault tolerance !"4-AID MAID 5 Distributed parity /$+/*$volume (n:)L Disk Si0e n +7*"


32/52


*ardare Striping*ardare striping "4-%"OS striping1*areare fault

tolerance"4-disk array 8'disk array#/*%$"&/*: $"logicle

*ardare fault tolerance 1#*+/#+' #"OS striping *ardare striping #1#*11AID fault tolerance+

evie of Striping Options,-#Oracle striping/ OS striping *ardare striping,,&

I3O"4-,+/*%$"&++-,-I3O $/$!"+hysical Disk

,-#Oracle stripting OS striping ,-$!/)/.$Disk+,-# *ardare striping+OS monitor

"1$/&Disk volume &: "ogical disk#!$$+-I3O rate $+7*I3Os per disk < ( 8umber of I3Os per second per volume ) 3 ( 8umber of

drives in the volume)$/,-Disk array @ $":2M I3O $/I3Os $/$

/*-+"I3Os per disk < :2M 3 @ < >M I3So per second per disk"data volume !',-/*$

-+!$

8umber of disk < I3Os per second needed 3 RM I3Os per second per disk1,$*,-$7!",5MM I3Os $/,--/*$+!$

8umber of disk < 5MM I3Os per second 3 RM I3Os per second per disk < :R23> disks :X disks

-#,--data volume+'

Separate Data and Inde.es/*7!/&

8*+$,/*188*#""7!/&8*+$/*#/*$#7!#(%""7!/&8*+'

32


33/52


&liminate 8onOracle Disk I3Os+&""notOracle I3Os I3Os .#

//)/.Oracle+13"$1"3"throughput response time

nonOracle I3Os #/!$$/)/.Oracle (?WI"&S-A-) 7!Oracle /*/$/$%$!$

%educin* &nnecessary /=O O)erheadI3O overhead +/throughput"+

Overhead +&chaining migrating ros hurts performancemigrating chaining /'#"'+DA-& /

ro +"data block /$,-*#Oracle"roblock Oracle""++block

migrating $ block+ Oracle ro &%&+ data block %8'chaining

Migrated and hained !owsmigrated ro &$,/ overheadOracle#

#+' time "ro copy ro "++ datablock8'/ "#+' time I3O ""#"'+DA-& 8'&$,/migration ($/)/.

#hained ro &$, Overhead+$ ro

""/$access $"chained ro $I3O : "read +Oracle disk block $"access ro %block!++SGA

,-$ chained ros1! "IS- #*AI8&D O6O+-IO81# " A8A"%& ,-# " S,"$chained migrated ro+"

S,"= emS,"= #&A-& -A"& chained!ros ( 2 oner!name varchar2(>M)/ > table!name varchar2(>M)/@ luster!name varchar2(>M)/5 head!roid roin/

R timestamp date)

-able created;

33


34/52


S,"= emS,"= em Analy0e the -able in ,uestionS,"= emS,"= A8A"%&

2 -A"& scott;emp "IS- #*AI8&D O6S

-able analy0ed;

S,"= emS,"= &1 #heck the esultsS,"= emS,"= S&"- L from chained!ros8o ros selected

ro%!/ chained migrated ro "3migrated ro1copy ro+#$#"ro $/$ ro "+$$9" temp table "chainedro "9#hained ro ,-&chained ro"Oracle database block si0e &+,-$!databaseblock si0e 3'

,-+chain ro19$ "O8Gcloumn long #*A ?A#*A2 column ,-!column 3"1#*"database block #

database/block ($/)/.block 3/+8'&/block 1$"/$*D!"O#$!SI%&+#"data base block buffer+

1emory used < D!"O#$!'&S (number) L D!"O#$!SI%&(bytes)

""1+aging sapping 8'&$,SGA+"

Dynamic :tensions/I3O !18&&$*+8&$*"!

"!$data base #"/'$!/"/'"

Dyanmic e.tension+&$,/I3O ""&$,/"S,"//$/!""recursive call #"/I3Os "/)/.

34


35/52


,-$ recursive call + " $ diynamicperformance (?WSSS-A-)1#""$+

S,"= S&"- name/ value

2 O1 vWSSS-A-> 6*&& name < Precursive callsQ8A1& ?A"'& recursive calls 5@@M

$recursive call "Application ,-!""-:5 '2M !",-recursivecall

ecursive call /$,"$+- ".Data Definition "anguage- ".S," .store procedure/ function/ package

anonymous +"3S," block- ""referential integrity constraints- (/data dictionary cache

#,-!+%&$,/ recursive call+'$,-"e.tents dynamically $$DA!&-&8-S ,-& e.tents""'

,-$e.port !$!"+8&$*,-1#1"+$$/1$

'C$7% and 'C$&"D Command Options//)/.Overhead #+#-&&

+#-'S&D option S-OAG& " #&A-& #"'S-&/#&A-& -A"&/ #&A-& I8D& #&A-& S8A+S*O- #+#-&& +#-'S&D,-,data block+$%-!"1,$*!,- #//)/.1+,-#+#-&& +#-'S&D "%,*"2 Option/)/."")*"+#-&& +#-'S&D ($&'data block $$#"++#1#*,-+" ( "3% +#-&& +#-'S&D chaining

35


36/52


+#-&& "!+#-'S&D &"$ data block +#-&& +ro%/+block++#-'S&D (+#-&& +#-'S&D ++/:MM &/ +#-&&

!:MM +#-&& (/:MM""!$+#-&& +#-'S&D

+#-&&+#-&& ($data block "1$$ro

/'ro/+data block"!data block*8&$*+#-&&

+#-&& ($- 31$"ro :-

//)/.block "1+- //)/.chaining - $/$block !#+/)/.

+#-&& ($"- #"1$ro !- /)/."- /)/.ro/chained '/#+'(

#"/I3O- !#/)/.!3

#+#-&& #+1,$*/ ro% +#-&& option !# " #&A-& #"'S-&/#&A-& -A"&/ #&A-& I8D& #&A-& S8A+S*O- "&!,-,-!$ table/ cluster inde. $"/(/'"$+#-&&

36

$"$/,-+#-&& +#-'S&D "+#-&& < 2M+#-'S&D < @M$",-/ ro+data block"data block & YMN (:MMN 2MN free) /$,#+/ro%+data block+!+$/1$ro !

,-/ro+ data block *8$*#+ block '@MN $,(&"/chaining


37/52


+#-'S&D+#-&& !+ro%/+data block

+block$$+#-'S&D ,-"$/%+#-'S&DOracle&data block "+#-'S&D $

block+#-'S&D ($- /)/./migrating chained ro - #$&

+#-'S&D ($- /)/.'/migrating chained ro- #/)/.++#

#"+#-&& $"$Application

##/)/.'A evie of the +#-&& and +#-'S&D Options

#""$38'$#""#/)/.$$+&/$'',-$"$+#-&& +#-'S&D

- (+#-&& +#-'S&D $+/:MM- (+#-&& +#-'S&D ":MM Oracle

&-

+#-&& /##+' time- "3block overhead++!-+#-&&+#-'S&D

- $$(:MM+#-&&) +#-'S&D

$+&"$+#-&& +#-'S&D- 'pdate Activity / *igh o Groth Application ,-

'pdate 8'$ro+#-&&!+#-'S&D "#/"data block "1$ro$"

'C$7% + 2 @ 25+#-'S&D < >5 Z @M( :MM Z +#-&& ) Z +#-'S&D < >5 to @5

- Insert Activity/ Small o Groth / ro 3'pdate +#-&&+#-'S&D/chaining ro"ro/ + data block"',/

37


38/52


migration ,!+/ro%++block+#-&& < 5 Z :M+#-'S&D < 5M Z RM

( :MM Z +#-&& ) Z +#-'S&D < >M to @5- +erformance +rimary/ Space Abundant /)/.&/"3",-,-$migration chaining+/''+#-&&!%+#-'S&D %

'C$7% + 3+#-'S&D < >M( :MM Z +#-&& ) Z +#-'S&D < @M

- Space #ritical/ +erformance Scondary ,-$3/*"+#-&&$%+#-'S&D !%"##,,$

/)/./chining migration 'C$7% + 5+#-'S&D < M( :MM Z +#-&& ) Z +#-'S&D < 5

#$%$"& /=OI3O &/"3$/)/.I3O &

'(/$*# ###/&'!"/* ,-&+,-//)/.I3O1I3O "I3O ,""'!/&8* ,-&+/& I3O1/#dynamic e.tension migrated chained ros

#"/,-&I3O /*,-###I3O &++

ollback Segmentollback Segment & #$ -ransaction

+rocessing #ollback Segment&("/)/."ollback Segment T"'!ollack -ransaction "U ollback Segment !#,!%"(ead #onsistency) "D6#O11I- !+!5!(Database ecovery) -uning ollback Segment+/)+#/ollback Segment "$$ollback Segment

38


39/52


"Data iles18#"!+ollback Segment &"3!ollback Segment$!$$#!!

$"-ransaction "ollback

Segment -ransaction "2 "4-:; Automaticollback Segment"DD" D1" "-ransaction1"$1"$/

2; 1anual /#"#ollback Segment +1#"S&- -A8SA#-IO8 ,parameter 'S& O""A#$ S&G1&8-$"#O11I-'!ollack -ranscation "

ollback Segment+$"++"$#&!,%" ( ead #onsistency ) / ollback Segment&T#ircular ufferU 1ollback Segment$ 2&.tents (1$/ ) -ransaction#&.tents $&&"+#&.tents "$+$"

-ransaction#&.tents ",&+&.tents ++&&.tents "' ollback Segment#"

#&A-& +'"I# O""A#$ S&G1&8- rsname-A"&S+A#& tsnameS-OAG&(I8I-IA" :MM$8&- :MM$O+-I1A" :5MM$1I8&-&8-S :5

39


40/52


1A&-&8-S :MM)$parameter "

- I8I-IA" /$&.tents :MM$- 8&- &.tents "$+ :MM$ ( "

I8I-IA")- O+-I1A" YMM$ &,-&(,

&.tents+#&.tents3'+- 1I8&-&8-S +Y &,&.tents

+$"$/&.tents- 1A&-&8-S :MMDynamic Alocate $##&.tents $&$1I8&-&8-S &$+%$+/1A&-&8-S +/&.tentsO+-I1A" ++#&"

-uning ollback Segmentollback Segment$

ollback Segment '" -ranscation %"

(#oncurrent -ranscation)1 " + : ollback Segment " @#oncurrent -ranscation -ransacion#ollback Segment ""& -ransaction $#" $ &3! ollbackSegment $+$"?W6AI-S-A- 8'$%"

- '8DO *&AD&ait buffer & rollback header block- '8DO "O#$ ait buffer & rollback block

rollback header block

40


41/52


- SS-&1 '8DO *&AD& '8DO *&AD& $#"SS-&1rollback Segment

- SS-&1 '8DO "O#$ '8DO "O#$ $#" SS-&1rollback Segment

$#S," !$"?W6AI-S-A- $+SQL" S#L#$ class %count

2 7%OM .A/$"$A$

& '#!# class *

+ (,undo header-%-undo block-%-system undo header-%-system undo

block-)

"D '& G&-S #OSIS-&8- G&-S$"?WSSS-A-

SQL" S#L#$ S.M(value) /Data !e0uests1 2 7%OM ."B""$A$

& '#!# name *

+ (,db block gets-%-consistent gets-) ollback Segment lock ollback Segment

*eader :N -ranscation "/ ollbackSegment +

ollback SegmentOL$'&-ransaction$

3"!&%ollback Segment &%%-:M$2M$ &2 Z @ &.tents ( ollback Segment&%#"SGA ')

Lar*e Queries ollback Segment":MN -able 3,( S," 3(":MN !!-able ")

Large .pdates $ % & & % % !ollback Segment '234 % $able %(( SQL $()'234 %%*&*$able )

#Dynamic Groth &/Over*ead"-ransaction /'+$+?WO""S-A- 8'parameter $%"

41


42/52


- &-&8-Sollback e.tents- SSI%& &byte ollback Segment- O+-SI%& &Optimal- A?&A#-I?& &9Active &.tents-

A?&S*I8$ free &.tents shrink- &-&8-Sollback Segment /&.tents- S*I8$S ollback Segment shrank- *61SI%& &*ight ater mark ollback Segment Si0e

SQL" S#L#$ substr(name%2%+3)% e5tents% rssi6e% aveactive%

aveshrink% e5tends%shrink 2 7%OM .%OLLAM rn9.rollstat rs

& '#!# rn7usn8rs7usn

,/ -uning ollback Segment:; #default ollback SegmentSS-&1 -able Space

ollback Segment $"$2; : ollback Segment "@ #oncurrent -ransaction >; :M Z 2M &.tents "$ollback Segment"O"-+@; ollback Segment":MN -able 3

,""arge -ransaction ( S," 3(":MN!!-able ")

5; "arge 'pdates ollback Segment":MN -able 3,

R; Dynamic &.pansion ollback Segment16"1onitoring %

edo "og uffer #ontention$contention edo "og uffer +1$

?WSSS-A- +&M process #edo "og uffer ""/edo "og uffer

SQL" S#L#$ name% value2 FROM V$SYSSTAT3 WHERE name = redo log space reqes!s"#

42


43/52


-uning #heck +oints#heck +oint5!Oracle &,""PDirty lockQ

"SGA!+disk D6 $Algorithm"' ("east ecently 'sed) /check poin "4-"

-

/"og Sitch #heck +oint- $"$"/$"OG!#*$+OI8-!I8-&?A" ""/checkpoint 'redo log block !

- $"$"/$"OG!#*$+OI8-!-I1&O'- ""/checkpoint '&/

- /#heck +oint -able Space ackup- /#heck +oint Instance Shutdon1"

S*'-DO68 8O1A"/S*'-DO68 I11&DIA-&- /#heck +oint Admin "/

check point& 2 "4-PormalQDatalocks

+1 D6 / PFas!QDatalocks1 D6(Database 6riter)1lock !-agged+$/D6 "performance $/"G6 ("og 6riter) ,/+'pdateile *eader #$+- file header #"G6redo " + $ " / $ #$+- ! initiali0e parameter#*$+OI8-!+O#&SS&-'&

Optimi0in* Archi)in*Archiving &$/,"$-une +erformance

,(A#*I?&"OG 1OD& :; ,3$/ ecovery -ransactionackup ",',Instance ail

2; ,3$/Online ackup " ! A#*I?& 1OD& + 1 $" $""OG!A#*I?&!S-A- &-'&1$/ A#*I?& 1OD&+("Database +erfoamance 5!3&"ogfile+hysical("/)/.""$ parameter"OG!A#*I?&!'& "OG!A#*I?&!'&!SI%&

Optimi0ing SortsSort /1emory Disk $+

?WSSS-A- sort 2 "4-- Sorts (1emory)Sort +"1emory- Sorts (Disk)Sort $6"-emp Space*arddisk

SQL" S#L#$ name% value

43


44/52


2 O1 ?WSSS-A-> 6*&& name I8 (Psorts(memory)Q/Qsorts(disk)Q)

sort # -emp Disk & /

SO-!A&A!SI%&"/SO-!A&A!&-AI8&D!SI%&1inimi0ing ree "ist #ontention

ree list &/)datablock uffer #ache+&'reelist& link list lock Segment $ree list+1$?WSSS-A-

SQL" S#L#$ class% count2 O1 ?WSSS-A-> 6*&& class < Pfree listQ

free list : percent eHuest "/free list parameter &&"IS-S $Data eHuest+

SQL" S#L#$ S.M(value)% count2 O1 ?WSSS-A-> 6*&& name I8 (Pdb block getsQ/Qconsistent getsQ)

'Optimi0er Oracle&.ecute "S," (/$."-;*Oracle &.ecution+lan1" S," ! &.ecute &.ecution +lan!1 Optimi0erOracle1#,)*Optimi0ation ,$+arameter /$

/) /.( &.ecute'"/)Optimi0ation 8'/)Optimi0ation /,)*ulease #ostase'8',)*#'" Application Data "

Optimi0er (!Optimi0er +"""1#*$"Optimi0er Optimi0e "S," +"S," !$"+-*+$"Optimi0er /$+/'

:; "S," !/*/-$&/"S," !$1$"Optimi0er

44


45/52


2; "S," !1odify1$"Optimi0er "S," 8"8$" Optimi0er " S," /)/.,(&)

>; 1erge ?ie!2/"$/ ""Access ?ie "

$"Optimi0er Oracle 1eage ,uery""",uery ?ie Optimi0ation@; /)Optimi0ation !' $"Optimi0er

,)*ulease #ostase 5/*!8'+Ob9ect *int $%

5; Access ! $"Optimi0er '%"$-able '"S,"

R; 4oin Order ! '4oin "S," OracleOrder ,4oin /'

X; 4oin Operation ! $"Optimi0er Operation

,#2/"$/ 4oin,-&$"$$8'$" Optimi0er &.ecution+lan ,""S," "8"8 $"&!!&.ecution +lan ,!"

(#)Optimi0ation ModeOptimi0ation 1ode "ulease #ostase !1

#/)""$+ /$* O+-I1I%A-IO8!1OD& $/$

/$*O+-I1I%A-IO8!GOA" " A"-& S&SSIO8,/)Optimi0ation /)/$/)" A"-& S&SSIO8 ,-,$""$+

COO"$"Optimi0er Oracle Optimi0ation 1ode 5/$/8'+ -able /#luster Inde.19 /$/+% -able !Access ."S," ,)*#ostase!#",-hroughput , +/$/8'+-able ,)*ulease!#,)*ulease&Default ,)*Optimi0ation+!,

%&L$"&$,Optimi0er Oracle#,)*ulease +

&$/$/$%-able !Access

45


46/52


ALL%O"$"&$,Optimi0er Oracle#,)* #ostase "S,""+/$/8'+-able !Access ,)*&-hroughput ,"#,

7/%"$%O"$"&$,Optimi0er Oracle#,)* #ostase "S,""+/$/8'+-able !Access ,)*&esponse -ime ,

8O-& ,)*Optimi0ation #ostase !#+/$/.8'+-able !Access Information %(#Data lock-able)!#/#ost Operation $%

$*Optimi0ation1"%+ ,)*#ostase &,)*+# #ase ,)*#ostase &$"&.ecution +lan ,)* ulease+&$-une "S," 1anual +"/)/.Optimi0ation ulease #ost Zase

,)*ulease &1#* -une applicationOracle ?ersion #,)*ulease /+#/$/&$,,)*ulease /)/.,)*#ost Zase + &$ /$ /5 ! 1igrate+ & #ostbase

optima0aton

"*+%ule


47/52


Y #omposite key Singlecolumn inde.es:M ounded range search on inde.ed columns:: 'nbounded range search on inde.ed columns

:2 Sortmerge 9oin:> 1a. or 1in of inde.ed column:@ Order y on inde.ed column:5 ulltable scan

,)*ulebase &5"S," "+&$/$/$%"Database -able ,)*ulebase $Step &.ecution +lan :; &.ecution +lan &++2; ank (8'$$"$$#ost Access

"ule Z ased Optimi0ation>; ,)*ank $,/),)*Optimi0ation ule Z ased /)/.

+ $+&$/$/8'+ -able /#lusters Inde.es ,)*#ost Z ased /)/.

"*+Cost


48/52


Optimi0ation #",$"Optimi0er &.ecution +lan 8'#",

Optimi0ation esponse -ime ,

$"Optimi0er &.ecution +lan 8'esponse -ime ,

'AALBE,-#" A8A"%& /$/",-8'

#"$"Optimi0er #ostbase "#+/$/$#"$,*%"A8A"%& #"$+,$$" A8A"%& #/$/ " -able/#luster Inde.es # Optimi0e #ostbase&.ecution +lan ,",-Data inte*rity"A8A"%& #$ integrity1-able /inde. #luster,$$Chained


49/52


-able "#luster+!Scan !!#/$/#!!,,-#"A8A"%&

'AALBE #)%,$$,

#"A8A"%& -/$/!$$#"A8A"&"$+

"-ableA8A"%& -A"& table!name

#O1+'-& S-A-IS-I#S" #luster

A8A"%& #"'S-& cluster!name#O1+'-& S-A-IS-I#S

" Inde.esA8A"%& I8D& inde.!name

#O1+'-& S-A-IS-I#S

# A8A"%& $" #O1+'-& S-A-IS-I#S scan "-able /#luster Inde. -/$/!$,--/$/!$!("*!$(&1-/$/+&$,-#"+"Information ,--/$/"-able#luster ,-$#""oad ""-able #luster ,-+&$##""Inde.es

'AALBE #)%,$$,-#"A8A"%& -/$/Oracle

##"un "A8A"%& "-/$/+"

"-ableA8A"%& -A"& table!name

&S-I1A-& S-A-IS-I#S

" #lusterA8A"%& #"'S-& cluster!name&S-I1A-& S-A-IS-I#S

" Inde.esA8A"%& I8D& inde.!name

&S-I1A-& S-A-IS-I#S

49


50/52


,-#A8A"%& $"&S-I1A-& S-A-IS-I#S Oracle scan-able /#luster Inde. --/$/,-,!Scan #"/$/1'/$*

SA1+"& .... O6SSA1+"& yy +&8-

/$*SA1+"& .... O6S $"A8A"%& "$+A8A"%& -A"& table!name

&S-I1A-& S-A-IS-I#SSA1+"& :MMM O6S

/$*SA1+"& yy +&&8- $"A8A"%& "$+A8A"%& -A"& table!name

&S-I1A-& S-A-IS-I#SSA1+"& @M +&&8-

AALBE"A8A"%& 1#*"/$/

"/*1integrity -able /#luster Inde.es #"A8A"%& /$//)/. Optimi0er #ostbase /'

Data Dictionary Statistics,-#"A8A"%& /$/" #ostase Optimi0er+#/$/!/.$/)/. Oracle !.8'-able !,uery (?ie $% '?ie+Information "$$'"?ie ,-19$Information & ?ie $+!$""4"$+

./ "&'S&! ?ie Information " Ob9ect 'ser&A""! ?ie Information " Ob9ect !Access1'ser

/& Ob9ect 'ser & Ob9ect Access+ublicDA! ?ie Information Ob9ect ".

50


51/52


?ie + Information "$$" #-able /#lusters /Inde.es #olumn %?ie !"$+Information /)/.

-able vie 7 'S&!-A"&S/A""!-A"&S/DA!-A"&S

#luster vie 7 'S&!#"'S-&S/A""! #"'S-&S/DA!#"'S-&S

Inde. vies7 'S&!I8D&S/A""! I8D&S/DA! I8D&S

#olumn ?ie7

'S&!-A!#O"'18S/A""!-A!#O"'18S/DA!-A!#O"'18S

Information ,!-able .(internal table) !/1?ie!#1$" Optimi0er $"/"&.ecution +lan $ " / " &.ecution +lan ! 5

Information " Ob9ect !, Ob9ectInformation ,-able $$$+

&"$able&"%$A8L"9ALL$A8L"9D8A$A8L"

#olumn Description of contents

A?G!S+A#& 9-ableA?G!O6!"&8 9o"O#$S lock-able#*AI8!#8- #hained o&1+-!"O#$S lock +!#8'1!O6S o-able

&"Cluster&"%CL&"$%"9ALLCL&"$%"9D8A CL&"$%"


A?G!"O#$S!+&!$& 9lock 8'ros #$ey"#"'S-&!-+& #/#luster 7 Inde. #luster *ash#luster '*AS*$&S *ash $ey "&*ash #luster

&"/nde:es&"%/DF"9ALL/DF"9D8A /DF"


51


52/52


A?G!"&A!"O#$S!+&!$& 9"eaf lock $$ey

("eaf lock "Inde. lock $,)A?G!DA-A!"O#$S!+&!$& 9Data lock $

$ey"&?&" "L -ree#"'S-&I8G!A#-O "+"-A"& I8D&

'DIS-I8#-!$&S Distinct $eyInde."&A!"O#$S "eaf lockInde.'8I,'&8&SS Inde. 'niHue +'niHue '

&"Column&"%$A8COL&M"9ALL$A8COL&M"9 "D8A$A8COL&M#olumn Description of contents

D&8SI- #olumn (o $Data lock)*IG*!?A"'& Second*ighest#olumn -able"O6!?A"'& Second"oest#olumn -able8'1!DIS-I8#- +8"(Distinct ?alue)#olumn -able

Information$Optimi0er &.ecution +lan /)/.!5!"'19+!$,,-un "A8A"%& $"/$/-+%-(")*/-&!/

52

intro oracle

Documents