How does Update Statement Works in Architecture Level?

Answered

By: Sugandh | 03 Mar 2015 5:42 pm | 3 Comments

Hello Experts,

I have this query

How does update statement works in architecture level?

please share your valuable thoughts.

Comments

Guest |

| 21 Jul 2015 12:25 pm

Best answer

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.

Abhijeet Mudgal |

| 03 Mar 2015 5:53 pm

Helpful answer

First you read DB Architecture..In Architecture read carefully Redo & Undo role...Rest thing you can understand..If you fire Update statement

1.background process check permission of user,statement syntax.

2. update database as u fire command.

3. save old value or New Value with time stamp.

4. after commit command save your updated data in your Physical storage..I hope u under stand.

ArunKumar A |

| 21 Jul 2015 12:26 pm

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.

SAP HANA Database Architecture Overview
Compared to other databases, the architecture of SAP HANA is exclusive and very distinct. The SAP HANA database is built on C++ and operates on SUSE Linux Enterprises Server. The aim of the SAP HANA...
How to Explain Oracle Architecture?
Hello Experts,I am going to face an interview and preparing for that and as I am preparing I met with a problem that how to explain oracle architecture in interview? I am unable to explain it if...
What is SAP R/3 Architecture ?
SAP uses the industry specific 3-Layer Architecture and named it as R/3 Architecture. It consists of three 3 Layers 1...
Enterprise architecture
SAP's new Extended Business member program is designed to create an ecosystem for SAP's nonpartner ISVs, consultants and integrators. The program lets SAP channel partners host other VARs that are...