Oracle database architecture
everytime instance started
-SGA (Shared Global Area) is allocated + background processes started
Database
-physical structures
- can be managed without affecting access
to logical storage
-logical structures
Connection
user process<--communication --> instance
communication pathway established/ network software
Session
-state of current user login to db
-multiple sessions can be created
Interacting with oracle db
1) instance started at host/db server (where oracle db installed)
2) users starts application (user process)
connection can be local, client/server, ,3 tier connection
3) server runs listener (has appropriate Oracle Net Services handler)
listener
-detected the connection request, create server process (on behalf of user process)
4) users run SQL and commit
5) Server process receives statements.
Check shared pool (an SGA component) - see if there is identical SQL
IF SQL area found, server process checks user’s access privileges to requested data
(existing shared SQL are will be used)
else IF SQL area not found,
(new shared SQL area is allocated)
6) Server process retrieves any necessary data values (from table or values in Database buffer cache)
7) Server process modifies data in SGA
LogWriter process (LGWR)- records transaction in redo log file.
Database Writer process (DBWn)- writes modified blocks permanently to disk
8) IF transaction is successful
server process sends a message to application
else IF transaction is not successful
error message
9) Throughout entire process, other background processes run.
Db server
-manages other user’s transactions
-prevents contention (between transactions that request the same data)
Oracle database memory structures
-Group of shared memory structures (SGA components)
-data & control information
-e.g data stored in SGA includes cached data blocks and shared SQL area.
-SGA includes the following data structures:
- Database buffer cache :caches blocks of data retrieved from database
- Redo log buffer :Caches redo information (instance recovery)
- Shared pool : caches various contructs (shared among users)
- Large pool : optional area , for large memory allocation
e.g oracle backup & recovery, I/O server processes
Initialization parameters - create and manage memory structures.
Simplest way to manage memory - allow memory to auto tune
How?
-set MEMORY_TARGET : target memory size initialization parameter
-MEMORY_MAX_TARGET: maximum memory size initialization parameter.
Database Buffer Cache
-part of SGA
-data blocks - read from data files
-shared by all concurrent users
-1st time Oracle Db user process requires data, it searches from Db Buffer Cache
IF data in the cache (cache hit), data read directly from memory.
ELSE cannot find data in cache (cache miss), it must copy the data block into buffer in cache. Access data from cache hit faster than cache miss.
Redo Log Buffer
- circular buffer in SGA
- store information about changes made to database. (stored in redo entries)
- Redo entries - info for reconstruct/redo, used for db recovery if necessary.
Shared pool
Data dictionary - collection of db tables and views which contains info about db, structures and users. Often accessed at 2 locations, Library cache and dictionary cache
p.s Oracle db will know when 2 users run the same SQL (uses the shared SQL area)
Shared SQL area - contains the parse tree and execution plan