As promise in Oracle 9i Architecture post here is new post for how SQL statement processe internaly. This is for them those whi are planning to start career as Oracle DBA. If they want to make clear picture of Oracle Architecture then first of all they need to understand how sql statement procees in oracle.These are the statement which we are going to run.
Sqlplus scott/tiger@prod
SQL>select * from emp;
SQL>update emp set sallary=30000 where empid=10;
SQL>commit;
So we will understand what is happening internaly
- Once we hit sqlplus statement as above client process(user) access sqlnet listener.
- Sql net listener confirms that DB is open for buisness & create server process.
- Server process allocates PGA.
- ‘Connected’ Message returned to user.
- SQL>select * from emp;
- Server process checks the SGA to see if data is already in buffer cache.
- If not then data is retrived from disk and copied into SGA (DB Cache).
- Data is returned to user via PGA & server process.
- Now another statement is SQL>Update emp set sallary=30000 where empid=10;
- Server process (Via PGA) checks SGA to see if data is already there in buffer cache.
- In our situation chances are the data is still in the SGA (DB Cache).
- Data updated in DB cache and mark as ‘Dirty Buffer’.
- Update employee placed into redo buffer.
- Row updated message returned to user
- SQL>commit;
- Newest SCN obtained from control file.
- Data in DB cache is marked as ‘Updated and ready for saving’.
- commit palced into redo buffer.
- LGWR writes redo buffer contents to redo log files & remove from redo buffer.
- Control file is updated with new SCN.
- Commit complete message return to user.
- Update emp table in datafile & update header of datafile with latest SCN.
- SQL>exit;
- Unsaved changes are rolled back.
- Server process deallocates PGA.
- Server process terminates.
- After some period of time redo log are archived by ARCH process.
Hope you guy’s enjoy this post.Very soon I wil come up with Parsing & execution as well.
Experts are always welcome for their valuable comment or suggestion for the above post.
Related Post:
https://samadhandba.wordpress.com/2011/02/17/oracle-9i-architecture/
bro as u mention in point no. 15 nothing is written to disk,because we have not committed but it is not case LGWR not only write after commit the data in some more cases it write data to disk. like if,
1.redo log bufer is
2.1/3 full or when 1mb of redo generated or
3. at every 3 sec. so its not tat LGWR write only committed data LGWR write at Evey one of above condition so log file contain both committed and uncommitted data at at start and after rollback it remove uncommitted data.
m i right?
Dear Amit,
Bro u r right, though it is not commited but it will be written into log file. And rest of the LGWR points u mention these are standard. Thanks for correcting me.
Thanks & Regards,
Samadhan
Bro i surprise by seeing ur comment. Good keep it up…. keep reading the blogs and commenting / suggesting …. Looking forward for encouraging responce… 🙂
Thanks & Regards,
Samadhan
Hi Samadhan,
Again many many thanks for the blog . If the data is not found in the SGA then it reads from the datafile, i want to what changes occour in SGA during that period (like explain plan ,parsing and in which component it occcur).
Hi Dear , in above post i just give overall picture , I believe you must be knowing the detail still will tell you …. if data is not in DB cache it get the data from datafile store in DB cache and data return to user as well that SQL statment is store in libyary cache ….. well we can disscus on this deeply as much as we can ….