Oracle : Steps for Update Statement Process in SGA

Steps for Update Statement Process in SGA:

 

update processes

>update emp set sal=5000 where empno=7903;

step 1

when user fires update statement first it will start user processes

step 2

user processes to server processes with the help of user processe interface

step 3

server processes to sga with the help of server processes interface

step 4

it will check wheter this statement already executed or not

step 5

parsing will be done in library cache

> hard parsing or soft parsing

hard parsing first time execution

soft parsing second time execution

step 6

after parsing symantical checking will be done in ddc

step 7

after symantical checking here comes the oracle optimizer

oracle optimizer will choose the best execution plan to fetch the data from the server

step 8

with the help of best execution plan server processes will fetch the data from datafiles and it will store before image and new image in to database buffer cache.

step 9

before image will also store in undo why because we cannot guarantee the data which is available in the database buffer cache because it’s a memory

step 10

when user says commit the new image will be written into coresponding datafiles by dbwr

if user says rollback then previous image will be written into corresponding datafiles from the memory, if previous image is not available in the memory then it will be taken from undo.