what is an instance?
instance constitutes of both memory structures and background processes
Memory stuctures
SGA:shared global area
sga will be allocated to the instance when started and releases the memory automatically if instance disabled
sga consits of different buffer caches, majorly we'll discuss about three
db buffer cache
redolog buffer
sharedpool area
largepool
javapool
streamspool
streamspool
to view the sga : sql> show sga
to fing the maximum size of the sga : SQL>show parameter sga_max_size
Background processes:
DBWR-dtabase writer : it's responsible for writing the data from db_buffercache to datafiles
LGWR-logwriter : it's responsible for writing the data from log buffer to online logfiles
PMON-mainly responsible for user failed processes and releases the locks.and frring the resources
SMON-performs the instance recovery at the time of startup in the event of system failures,it cleans up the temp segments that are no longer in usecombine contiguous free extens into one large constant known as coalesing.
MMAN-when sga_target is set to nonzero value this process is enabled----simply in automatic memory mangement mode
ARC-to write online redolog files into offline relogfiles writes in the from of batch
CKPT-all the modifed info in db_cahe are written to the datafiles using DBWR.this event is called as checkpoint,it's responsible for signaling the DBWR in signaling at the checkpoints to update datafiles and control files
DB buffer cache:
it consits of both modified and unmodified blocks
it consits of most recently used data and deletes the least recetly used data
it uses a backgroung process dbwr to write the data from db cache to datafiles
to view the db_cache
show parameter db_buffer_cache;
Relog buffer:
all the redo entries are stored in redologs
a background process LGWR is used to write is used to write from log buffer to redolog files,it writes in the form of batch
an online redo log is a set of two or more files that record all changes made to the oracle datafiles and control files
to view the log buffer
sql>show parameter log_buffer
Sharedpool area:
it consits of
library cache -stores the user executed sql statements for faster access if it's repeated in the future by other user
dictionary cache-stores data dictionary definitions in the database like
info about the database
datafile name
segment name
extent location
table descriptions
userr priveleges
to view shared pool area
sql>show parameter shared_pool_reserved_size;