Oracle Database Memory Structures
It consists of two types :
- Memory Structure
- Back Ground Process
Oracle Database Memory Structures
The SGA is a group of shared memory structures, known as SGA components, that contain data and control information for one Oracle Database instance. The SGA is shared by all server and background processes. Examples of data stored in the SGA include cached data blocks and shared SQL areas.
It consists of
- Shared Pool
- Database Buffer Cache
- Redolog Buffer Cache
- Large pool
- Stream pool
- Java pool
here we can go to see each components in detail
- It's parameter is shared_pool_size
- It's consists of Library cache and Data Dictionary Cache
- It stores information about recently used sql and Pl-sql Statements
- Here it checks some of the followings
1)Semantic checking - it checks the privilege issued commands by user
2)Syntax checking - it checks the syntax of user issued commands
3)Soft parse - Already Executed Sql statements command
4)Hard parse - New Sql Statements
- It stores the collection of most recently used definitions in the databases includes dbfiles,tables,indexes ,columns etc.
- It has the information about database and it is read only.
- It stores copies of data block that have been retrieved from the database datafiles.
- Its parameters are
show parameter db_block_size =8kb is default size, show parameter db_cache_size
- It maintains records of modification database blocks.
- Primary purpose is recovery.
Show parameter log_file
- Parallel execution allocates buffers out of the large pool only when sga_traget
- It works to release the burden of the shared pool
show parameter parallel_automatic_tuning
- Parsing requirement of java commands
- Requires installation of java based projects
Show parameter java_pool_size
- It's Cache "Oracle Stream" Objects
- Oracle Stream means to allow data multiplication between on oracle databases or oracle and non-oracle databases,It can be used for Replication,Message Queuing,Loading data into a Data Warehouse,Event Notification,Data Protection Automatic Shared Memory Management (ASMM ) was introduced in Oracle 10g.
its taking care by oracle and allocates SGA components size ASMM taking care of
3)Database buffer cache
A PGA is a nonshared memory region that contains data and control information exclusively for use by an Oracle process. The PGA is created by Oracle Database when an Oracle process is started.
One PGA exists for each server process and background process. The collection of individual PGAs is the total instance PGA, or instance PGA. Database initialization parameters set the size of the instance PGA, not individual PGAs.
- It reserved memory for each user process connecting to an oracle database
- Allocates memory when a process is created
- De-allocates memory when a process is terminated
The UGA is memory associated with a user session.
The UGA is session memory, which is memory allocated for session variables, such as logon information, and other information required by a database session. Essentially, the UGA stores the session state.
User Global Area (UGA)
Software code areas are portions of memory used to store code that is being run or can be run. Oracle Database code is stored in a software area that is typically at a different location from user programs , a more exclusive or protected location.
- A program that request interaction with oracle server
- It must first establish a connection
- It doesn't interact directly with oracle server
- It directly interacts with oracle server
- It can be a dedicated or shared server
- It always responds to user requests
- It enforces the relationship between memory structure and database
- To view all background process
!ps -ef | grep databasename
Its some of components are
Each components are
- Time Out Error
- Tablespace offline
- Tablespace Read only
- Tablespace Drop or Truncate in above situations, Data 'll be flushed from database buffer cache into data files
- At commit
- Every 3 sec
- When there is full 1MB reached
- Redolog Buffer reached one-third full
- Before DBWR writes In above situations, redolog writes through LGWR from redo log buffer
- Monitoring the system is called system monitor
- Instance recovery
- Rolls forward changes into redologs
- Open database for user access
- Rolls back uncommitted transactions
- Taking Care of All background Process
- Cleaned up after failed process
- Rolling Back
- Updating the control file with checkpoint information.
- It's a process of writing by DBWR ,all modified buffers in SGA cache into Data files
Alter system checkpoint;
The Database is a collection of data which contains data files ,control files ,redolog files and Archive log
- It is a portion of an oracle database ,it stores the data which includes user data and undo data
- It's extension ".dbf"
- The default location is " $ORACLE_BASE/oradata"
- To view the location in database use this command
Select name from V$datafile;
- It's heart of the database
- It holds the information of data file ,redo log file locations and backup information starting time and ending time
- It's extension ".ctl"
Show parameter control_files
- By default oracle has copied the control files into flash_recovery_area
- It's part of an oracle database
- It's the main purpose is to recover the database
- It's extension ".log"
- When transaction is committed that details in redo log buffer are written to a redo log file
select * from V$log; or Select * from V$logfile ;
- It's a group of redo log files to one or more offline destinations, known collectively as the archived redo log
- Its Default location is Flash_recovery_area
- Must enable archive log mode in the database then only ll be saved on archive log folder other wise the log buffer overwrites on redo log files through Lgwr.
If you like dEexams.com and would like to contribute, you can also write your article here or mail your article to firstname.lastname@example.org . See your article appearing on the dEexams.com main page and help others to learn.
Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.