Oracle \ Data dictionary cache \ database buffer cache \ redo log caches add any others.
Oracle Memory Cache/Architecture: Oracle uses memory to store different pieces of information, like Program Code, Connected session, if it not currently active with any or different user, Information needed when program executive (Locking Information), and last Cached Data on peripheral memory.
Basic memory structure that associated with oracle database, System Global Area SGA (Shared with all server & his background processes, Program Global Area PGA Private for each process and his background processes.
Memory Structure/Shared SGA:Shared System Global Area is a group of shared memory structure containing data and constrain information for each (every DB) database system, this space of memory and information are shared between different & multiple user.
Shared Pool: Shared Pool Cache is a piece of memory in SGA that Oracle uses in order to store SQL (Structure Query Language) area statements and Data Dictionary (DD), DD Cache is a collection of database table & views containing references information about the database, they also help database administrator to track the location of various data also in the similar way in PL/SQL statements and objects, the total size of the shared pool is defined by the initialization parameter shared_pool_size, default value parameter 8MB on 32bit platforms and 64MB on 64-bit platforms.
Oracle DB Buffer Cache: They stores all the copies of DB locks containing rows from various table in database, they also read all the data file, if any change occur then they written data file, if all users connected to the database server access the database buffer cache, if any Oracle user access these data they are not in cache they called ‘Cache Miss’, on the other hand they called ‘Hit cache’.
Oracle DB Redo Log Buffer: Redo Log Buffer is a circular buffer that’s hold the information about any change occur made to the database, they also recovered and re-construct the database file, like (Insert, Delete, Update, Create, Alter and last Drop). Redo Log is copied by Oracle Db processes from user’s memory space to the Redo Log Buffer in SGA. The Redo entries continuous take up log in sequential style.
Data Dictionary Cache: Data Dictionary Cache is a collection of database table & views containing references information about the database, they also help database administrator to track the location of various data, DD take the data into two format one is DD cache and other is row cache because it holds data as row. All Oracle user processes share these two-cache formats for access data to DD information.
SQL lgwr/crpt/dbwr/pmon/smon what are all these and ad other’s if any,
Processes: A processes is thread of control in an operating system that can run a series of steps, a process normally has its own private memory area in which it runs. They are basically two types of processes, one is user process run the application (tool code), and other is server processes or background processes.
User processes is combination of two main types, one is called Connections and other are Sessions processes.
Connection Processes: Connection processes are communication pathway between a user process and an Oracle instance like (Network, means different computer run the DB application and Oracle, and communication through a network).
Session Processes: Session processes are a specific connection of a user to an Oracle instance through a user process session is basically the time period/frame between the user connection and disconnection the certain db application. For example user connect to any DB they must have a valid user name or password for entering in db application, and then a session is established for that specific user, session is basically the time frame between connection & disconnects/exits the databases application.
Oracle Instances: Consists of one or more control file, data files & redo file those all file name and record are saved in his control file.
Database Instances: Maximize performances and accommodate many users, a multi-process database system uses some processes called Background Processes. An instance is a combination of the background processes and memory area required to access on Oracle DB. Database application system required only one instance for one database; any database is mounted by his specific instances. Instances have four basic types that are given below.
PMON (Process Monitor), SMON (System Monitor), DBWR (Database Writer), LGWR (Log Writer).
PMON (Process Monitor): Cleans-up when user processes failed, PMON is responsible for cleaning the DB buffer cache & freeing database resources that the time user process was using, like delete/remove the process ID from the list of processes. PNOM is also time-to-time checks the status of dispatcher and server processes on network.
SMON (System Monitor): Cleans-up after an Oracle instance, they also performs recovery process, if necessary, SMON is also responsible for cleaning the temporary segments that are no longer in use with in DD managed table-spaces. SMON read offline error and checks regularly to see whether it is needed. Other processes can call SMON if they need help.
DBWR (Database Writer): Write modified block from the DB Buffer cache to data-file, the DBWR processes are responsible for writing modified (dirty) buffers in the DB buffer cache to the disk. When a buffer in the DB buffer cache is modified, it is marked dirty. A cold buffer is a buffer that has been recently used according to the least recently used algorithm.
LGWR (Log Writer): Write records of transactions from Redo-log buffer to Redo-log buffer file, the also record all the change in the database, LGWR writes and copied all redo entries into the buffer. Redo Log buffer is a circular buffer, LGWR normally very fast to writes & copied data to ensure that buffer space is available for new entries. LGWR continues checks & update logs error into the LGWR trace file system. If any file or group of file is damaged, or they are available for his backup. If anyone user in database application issue a commit statement, LGWR very fast to take record in the red log buffer & writes into the disk, this function is called fast commit mechanism. Oracle returns a success code to the committing transaction; through the data buffers have not yet been written to disk.
