Flow of data:

When a data base started there are three part

                                      1)Logical area
                                      2)Background process.
                                      3)Physical area.

Instance: In our database logical area and background process together we called it as a instance of database, when a user got connect its connect in a instance of database through server process. #Parsing: When any sql query execute by user, server process first break it small small maybe per word, per character as per requirement then stored it into a sql point of library cache as # code.

There are two types Hard parsing and soft parsing. If all those process followed by server process then it called as hard parsing. if sql query already exist there in library cache not followed all the steps already execution plan are available that called sot parsing.

How a DQL get execute:

After logged in database, If any user run a sql query then user process carry the query to instance of database , there server process take that query and parsing it in very small part (maybe by letter, character etc... as per requirement) and create one # code. After that server process carry that # code and stored in sql are of library cache. There check any kind of syntax error are there or not, if there then sever process back to user for correction, if everything is fine then server process carries to data dictionary. In data dictionary all the meta data getting stored over there. In data dictionary checked any kind of semantic error are there or not like table name, column name exists or not like that, if there also any error found server process back to user for correction. If everything fine then server process carries to optimizer. In optimizer server process execute the optimum plan for that query, means where less efficiency, less space, and stored it into physical data file as a data block, prior all the process was going on all are virtual. After that server process take one copy of data block and carry it to database buffer cache and stored over there. From database buffer cache to again back to user that reflect as a result.

How a DDL or DML get execute: As per DQL all the process till remain same server process follow all the steps, if everything is fine server process come to data file check the data block for that DML and copy the data block and store into the data base buffer cache, whatever changes is required do it in data base buffer cache, from there again it change value get stored into the data file. After that the entire statement stored into redo log buffer cache only this (DDL, DML, DCL). From redo log buffer cache LGWR write all the entities to redo log file, there are such kind of criteria are there when LGWR will write 1) Every three second. 2) If 1/3 redo log buffer cache full. 3) If user Commit. 4) 1MB full the space. In redo log file so many statement getting stored how it get identified which one commit or not, as in every nano second there are creating SCN no (system change no) from that no its get identified and stored accordingly.

CKPT: It plays a vital role in our data base, there are two type of check point 1) Incremental checkpoint 2) Full checkpoint. Incremental checkpoint occurs in every three second and sync all this three files data file, control file and redo log file. In every three second it check whatever new SCN no is there in redo log file update in control file and also update heard of the data file. One more thing its activate the DBWN to write. Full check point occurs two times when data base got shutdown and log switch happen, its ensure that all the dirty blocks write in to the data file.

DBWN: We called it as a lazy writer as its not activate by own, its get activate by CKPT. It writes all the dirty blocks from data base buffer cache and stored into data file. If DBWN not able to write all the dirty block if frequency is high then full check point responsible for that and its get stored.

PMON: We called it as performance monitor, its monitor performance of our database maybe 10 user connected in the data base for any reason 1 user got disconnected maybe network or hardware issues, pmon check that user is not there kill that process as if user not there not required for that server process, pga whatever created at that point of time as its wastage. We also called it cleaner of our database.

SMON: System monitor mainly worked for instance recovery, when data based got shut down immediately maybe for any hardware or any issues at that point of time it work. After start up the database at first its come to redo log file check whatever new SCN no is there which get commit that not written in data file its roll forward all those and written in to the data block in data files.