1 An oracle server consists of an oracle instance ans oracle database
2 startup nomount????? (allocate physical memory)
? alter database mount (control file)
? alter database open? (data file and log file)
3 instance memory strusture
?????????? background process
? database--data file
??????????? log file
??????????? control file
4 an oracle instance
? is a means to access an oracle database
? alwary opens one and only one database
? consists of memory and process structures
5 show sga
? select * from v$bgprocess;
? select * from where v$bgprocess where paddr<>'00'列出必須的后臺(tái)進(jìn)程
6 connection to an oracle instance consists of establishing a user connection and creating a session
?user process?? server process
7 an oracle database is collection that is treated as a unti
? consist s of thress file types data file control file log file
8 the physical structure of an oracle database is determined by the orpertion system file
? that provide the actual
?
?select * from v$controlfile(列出所有控制文件)
?select * from v$datafile?? （列出說數(shù)據(jù)文件）
53
?select * from v$logfile??? （列出所有日志文件）
9memory structure
?oracle menory structure consists of two menory areas known as
?sga and pga(程序全局區(qū)2)
10 The SGA consists of several menory structures:
???? shared pool
???? Database buffer cache
???? redo log buffer
?there are two optional menory structure than can be configured within the SGA
??? large pool
??? java pool
11 SGA
?? show parameter shared (列出系統(tǒng)全局區(qū)的參數(shù)設(shè)置)
?? show parameter db_cache(列出系統(tǒng)數(shù)據(jù)庫緩存參數(shù)設(shè)置)
?? SGA is dynamic an d sized using SGA_MAX_SIZE,調(diào)整各個(gè)部分值的總合不能超過SGA_MAX_SIZE
??
?? alter session set nls_lanage=american
?? alter system set db_cache_size=64m
??
12 Share Pool
? Share pool is used to store the most recently executed SQL statement s and the the most recently
? used data definitions
??? it consists of two key performance related memory structure
???? －library cache
???? －Data dictionary cache
??? it sized by the parameter share_pool_size
??? alter system set share_pool_size=64
13 libray cache
?? The libray cache stores information about the most recently used sql and pl/sql statement
????? 1)Enable the shreing of commonly used statments
??????? is managed by ta least recently used LRU
????? 2)consists of two structures
??????? shared sql area
??????? Shared Pl/SQL area
????? 3)Has its size determined by the shared pool sizing
14 Data Dictionary Cache
? The data dictionary cache is a collection of the most recently used definitions in the database
15 Database Buffer Cache
? 1)The database buffer cache stores copies of data blocks that have been retrieved form the data files
? 2)it enable great performace gains when you obtain and update data
? 3)it is managered through a LRU
? 4)DB_BLOCK_SIZE determines the primary block size
? 5）show parameter db (列出db 中所有的參數(shù))
? 6）Consists of indepent sub-cache
??? db_cache_size
??? db_keep_cache_size
??? db_recycle_cache_size
? 7)Database buffer cache can by dynamically resize to grow or shrink using alter system
?? alter system set db_cache_xiae=96m
? 8)DB_CACHE_ADVICE can be set gater statistics for predictin different cache size behavior
??
?? alter system set db_cache_advice=on ?
16 The redo log buffer
? The redo log buffer cache records all changes made to the database data block
? 1)its primary perpose is recovery
? 2)change record within are called redo entries
? 3)redo entries contain information to reconstruct or redo changes
? 4)size is defined by log_buffer
17 large Pool
? the loarge pool is an optional area of memory in the SGA
? 1)it relivere the burden placed on the shared pool
? 2)this configured memory area is used for sesion memory(UGA) ,
?? i/o slavers and backup and restore operation
? 3) the large pool does not use an LRU list
? show parameter log_buffer
18 Process Structure
? Oracle takes advantage of various types of
processes:
? 1) User process :Started at the time a database user requests connection to the oracle server
? 2) Serer process connects to the oracle instance and is started when a user establishes a session
? 3) background process available when an oracle instance is started
19 user process
?A suer process is a program that requests interaction with the oracle server
?? 1)it must first establish a connection
? 2) it does not interact directly with the oracle
20 Server Process
? Aserver process is a program that directly interacts with the oracle server
?? 1)it fulfills call generate and returns results
?? 2) can bi dedicated or shared server
21 DBWn write s when checkpoint, dirty buffers threshold reach, no free buffer,timeout ,tablespaxe
?offline,tablespacee read only ,table drop or turncate,tablspace begin backup
22 LGWR write
?at commit,went one third full,when threre is 1mb of redo ,every 3 seconds ,before DBWn writes
24 System Moritor,PMON
25 Checkpoint
?? Responsible for signalling DBWn at checkpoints
?? Updating datafile header with checkpoint information
26 Archiver ARCn
?optional background process automaticall archives online redo logs when ARCHIVELOG mode is set
?Preserves the record of all changes made to database
27 Lpgoca; Structure
? The logical structure of the oracle architecture dictates how the physical space of a database is to be
? sued
? A hierarchy seists in this structure that consists of tablespaces ,segments ,extents and blocks