DML Processing in an Oracle Database - DBArch Video 8

[@Bassim88]

After searching youtube about Oracle DML processing for a VERY long time (You can say years!!), this is -by far- the BEST . REALLY appreciate this exceptional explanation. Your explanation style is indeed simple and up-to-the-mark. Please upload more about Oracle Architecture, Tuning, Query optimization etc.

[@kv5481]

Hi Ram Sir, I hope you would read my comment. I just want to appreciate how wonderfully you have explained such a complicated stuff. Only an experienced guy like you can do this. It really helped me being in the industry. Please accept my deepest regards!

[@ridamujeeb2917]

You explained it in such a simple manner. This is what every learner is looking for.

[@mujeebrahman4968]

I cannot keep quite... I am an experienced DBA... loves your presentation style and very useful to refresh my knowledge.

[@RamkumarSwaminathan]

+Mujeeb Rahman thanks a lot for your feedback

[@kemokourouma2522]

Thx u teacher for what u are doing for us

[@muruganandamramasamy]

Superb explanation .. waiting for more videos from you.

[@sauravyuvraj]

Hi Ram, I know you from when u came to my previous organisation and u came to give us RAC training. I can say that training was superb and as expected this is also. Looking forward for videos in oracle in diff topics.

[@RamkumarSwaminathan]

THanks Saurav, your feedback surely inspires me to do more.. Will be uploading new videos also... Just out of curiousity.. May I know when and where you attended my training...

[@ankitasingh5514]

Learning Oracle in lockdown and yr videos are just awesome... enjoying a lot

[@ArulselvanDharmalingam]

You are Excellent teacher Sir. Thank you.

[@m162534]

Quite detailed and an excellent peek under the bonnet. Continue the endeavor Ram

[@RamkumarSwaminathan]

Mahadevan Meikum Perumal Thanks Maha... Sure... will be working on more such videos..... Shall keep you posted when I put them online....

[@tumusiimejevunail4474]

You are the best. God bless you

[@maharajanrpillai6049]

Very well explained sir.. your patience is the major key for your success.. thankfully one of your youtube student

[@RamkumarSwaminathan]

Thanks... Enjoy learning

[@GyanaranjanMishra]

well explained. very nice. One of the best video. Appreciated

[@rotrose7531]

Thank you very much! Your explanation is the best!

[@bhuvaneshwarisekar8390]

Such a fantastic explanation sir..thanks much..

[@valiantvimal]

This is very good article Sir, very helpful. I thank you very much 👌

[@akshitachaudhary2244]

Hi Sir, i have searched alot about good explanation for Oracle architecture but couldn't find it any .Three days before I got your video in suggestions and could not be more happy to say it's best choice to just see it. Indeed your way of explanation is the best of best . Please keep posting such videos and also please make a video on RAC and Dataguard

[@bhargavsai8078]

Awesome sir, Crystal clear explanation please countinue this and please do videos on data gaurd as well Thanks a lot sir

[@robh115]

Thanks for the excellent video. Question though, does undo take place in the buffer cache? Or, is it on disk?

[@satishkrajendran8703]

Very nicely done Sir. Super narration and explanation.. thank u very much

[@RamkumarSwaminathan]

Thanks , enjoy learning

[@AnkurSharma-oz6dp]

Very well explained in simple words. Please make more such videos.

[@anoopkumarpatil5434]

Very well explained. looking out for more videos of yours on Oracle DB.

[@RamkumarSwaminathan]

Thanks... I already have more than 100 videos.. explore the playlists in my channel...

[@senthilkumarsivam8849]

Superb!!! Simple and great explanation Ramkumar. Really worth watching. Will be great if you post some videos on explain plan, performance tuning.

[@RamkumarSwaminathan]

+Senthil Kumar Sivam Thanks Senthil for your feedback. We are working on it... We are building the videos... shall keep you posted once its ready...

[@sadanandamkanaparthi6368]

Nice explanation Sir, simply superb, how server process put the data into buffer cache from data block if committed value not written to data file ,other user issued same update with other value.

[@srinivasavula7268]

very good explanation..is the process is same for SQL server architecture or not..

[@gunjansharma3260]

Best i have ever seen.

[@dimwit4759]

First of all many thanks for posting useful materials. Had a question, if you could pls answer. Scenario- If the uncommitted(5000) data from log buffer is written to redo log file because of 'SCN' from another session & buffer cache entry of 5000 is written to data file. Before user1 issues a rollback instance goes down. In case there is a db recovery on this SCN/checkpoint (from session2) wouldn't Oracle incorrectly have value 5000 instead of 500 from redo/archive logs? I am sure 'm missing something or Oracle has some magic wand to handle this case

[@rameshthamizhselvan2458]

fantastic Explanation this is information what i need ...

[@dalipkhurana7348]

Undo tablespace(tablespace a logical entity) has underlying datafiles, datafile a physical entity (storage) which u mentioned in one of previous video is slow, did u mean that the instance writes the undo data to datafile associated with undo tablespace or undo value is also kept as dirty buffer in buffer cache itself nd is written to datafile(undo) along with others by dbw1 ???

[@lingeshmaster3113]

Hello Sir. Your videos are very simple to understand and helpful. Even after years of being a developer only after your videos I'm able to understand the flow. I have a small doubt in this. When user1 executes an update on the emp table with value 5000 until he commits user 2 will be displayed with value 500 only. When user 1 commits then user 2 will be displayed with value 5000. Here u have mentioned a point that not necessary that the changes will be written to data files for each commit. Then how come user 2 will be displayed with 5000 immediately.

[@RamkumarSwaminathan]

Because the data is accessed from the memory in the sga for user 2 and that is having the updated value...

[@gtmgtm8312]

Wow.. very well demonstrated

[@RamkumarSwaminathan]

+gtm gtm thanks

[@mahendrababu5516]

Hi Ram Very good explanation... I have a question here,when another user tries to select the same row which we are updating,from where the user will get the data before committing the data? 1)Data file or 2)Undo file

[@RamkumarSwaminathan]

It reconstructs a read only copy from the old value in the Undo

[@mahendrababu5516]

So another user in different session will read the old data from undo block not from the Data File? Please correct me if am wrong.

[@RamkumarSwaminathan]

@@mahendrababu5516 it may not go to data file... From buffer cache itself

[@mahendrababu5516]

Thank you so much Ram!!!

[@kashtrophobic]

very nice explanation.. would like a video on how a DDL processing is done in Oracle DB

[@RamkumarSwaminathan]

HI, Apologies for the delay in the upload... I have created a video for this kzbin.info/www/bejne/mHS6nZ16bJmsqJY . Trust it helps you...

[@st8vlogs542]

SQL*PLUS checks the syntax on client side. If syntax is correct the query is stamped as a valid sql statement and encrypted into oci (oracle call interface) packets and sent via lan using tcp to the server. Once the packets reach the server the server process will rebuild the query and again perform a syntax check on server side. Then if syntax is correct server process will continue execution of the query. The server process will go to the library cache. The library cache will keep the recently executed sql statements along with their execution plan. In the library cache the server process will search from the mru (most recently used) end to the lru (least recently used) end for a match for the sql statement. It does this by using acontd... www.orahow.com/2018/05/how-update-statement-works-internally.html

[@RamkumarSwaminathan]

Yes you are right, sqlplus checks syntax but the server cannot rely on client side validation, as not all sql is going to come from Sqlplus right? It can come from any supported client ( java program, c program etc).

[@melkitkaurpinder5868]

Very well explained. Im looking out for more videos of yours on Oracle DB. Do you have other link I can refer to? Im going bck to work after 3 years of break. Looking forward to brush up on my skill set.

[@RamkumarSwaminathan]

Thanks... Will be publishing more videos... In the meanwhile there are videos on GoldenGate also you can go through...If you want the entire bunch of videos on GoldenGate... its available at USD 50

[@manzoor5554]

Good Explanation ...DONE...

[@prabhas972]

Very much informative..thanks a lot

[@RamkumarSwaminathan]

Enjoy Learning...

[@prasadd1324]

Fantastic sir 👌👌👌👌👌👌

[@ANKIT2502953]

hi Swaminathan, thanks for the video. If my sga size 40GB and my table emp size is 800GB, and If I fire select * from emp; what will happen and will the wuesry execute or it will get hung and throws some error? Kindly help

[@PGExcellenceClasseswecan]

In this case direct Path read happen query will go to the PGA instead of shared memory

[@mahmudurkhan5754]

You are the best

[@ramramaraju2221]

Thanks Ram.

[@satya3233]

great..........really appriciate

[@RamkumarSwaminathan]

Thanks... enjoy learning

[@sandip250382]

Sir Interested Transaction List also contains Transaction ID and SCN and it is stored in block header of a segment but in this video you have explained that Transaction ID and SCN is stored in Log Buffer, so I am a bit confused, can you kindly clear this doubts of mine ??

[@RamkumarSwaminathan]

Its there in the Blocks also... but any change is also recorded in the redo... When a recovery has to happen... its easier to read the redo from the last checkpoint... rather than reading all the blocks in the datafiles...

[@Nick-ui9dr]

Okay guru ji... I am lil confused abt this undo and logging thing of yours here. I jus cant comprehend overall logic of your whole process... the way u say it is done. I mean whats the logic of creating a log entries on update in log table before creating undo entries for the same in buffer cache? Won't it messup the whole logging process a bit...a more complex I mean? :) Lets start from basic..lil more detail.. but still pusedo code like... maybe someday later if I stick to this oracle things of yours we can talk in all the details at actual record content formats...wherether of database file or redo log files. But for now concept level will do I suppose....1st I am not a programmer... jus a hobist as far this comp thing is concern ...so as that only I learn these things sometimes... that I came here also to learn oracle or whatever. So forgive me if not talking sense...jus using common sense of mine u know. Okay! So as far as my intelligence level allows.,. I think this UNDO facility is to simplify the logging(redo log) process... cause here in oracle u provide a facility to user to rollback or commit the results at his own lesuire than instantly. So keeping a temp undo entry sort of thing in buffer cache can simplify log entries. So that unnecessary log entries won't be created there.. which are not required actually if user chooses to roll back his doing. Or in other words, simply cancelling the whole current editing/update things. Right? :) That make sense to me!... I mean it'll simplify the logging process or else it'll be lil complicated though not impossible. Yeh toh sirf aapke kaam karne ke tarike ki baat hai...the way u like. Ab aap bol rahe ho ki log entries pehle ban ja rahi hai before undo entries. Toh yeh process Redo log writer ke process ko complicate nahin kar dega? Which u ..I dunno why u say database performance bhi increase karta. Now it had to deal with the rollback and commit logic per entry basis in log area on commit. Otherwise, it just have to simply generate the new SCN number and put all the records whatever they maybe be in Redo log files on commit with that new SCN number. Logging is overhead already on system... why adding more to it ...especially when there is undo area in buffer cache or datafile to eliminate that very overhead. :) Okay lets get lil deeeper if all this confusing u ...I mean not getting what I am saying... lets start with update statement firing.... Note:... wbb to complete this post gtg this time...watch out will be coming soon for sure guru ji. ;) Ok me back! So lets start from firing UPDATE statement from user.... So first server process...bt wait I think we shouldn't be wasting time on this... I already made my point clear ...concern I mean... maybe later videos will clarify it. So lets not waste time and watch other videos of yours... gotta complete all your videos today. Phir aayega na woh oracle boss humara delhi wala parsu net conference call mein... lega humaari farzi mein nahin toh... So lets complete the overview first....then we can talk abt confusion later on anytime... not u not me going anywhere soon! ;)

[@jayakrishnachanumuru]

Thank you so much sir

[@sarahshrivastava7926]

New n old vales r not stored in the redo log buffer but the transaction record. Redo buffer never stores user data values.

[@chitithali]

hi sir what will happen if we commited the transaction and suddenly poweroff to server before checkpoint asks dbwr to datafiles

[@RamkumarSwaminathan]

During next startup instance recovery will happen based on what is available in Redo Logs...

[@ashishtiwari4775]

Hi, can you please let me know Why old value is stored in redo buffer if it is already there in undo block for rollback purposes?

[@RamkumarSwaminathan]

The Undo buffer is only present in buffer cache... And will be later written to data files... Log buffer is like a log... Will be written to redo log files.... On commit that is were guaranteed write happens

[@nanikumar5176]

hi sir, thz r excellent videos for learning vth great explanation like cake walk , but i have query like i would like take learn full training ,so could u plz forward contact details for further discussion,

[@RamkumarSwaminathan]

Please use the link below to see our online training offering and our contact email. docs.google.com/spreadsheets/d/1qKpKf32Zn_SSvbeDblv2UCjvtHIS1ad2_VXHh2m08yY

[@vishnukhare8981]

if cursor is open and fetching data from a table ,and at the same time someone in another session delete the records and commit the same table what will happen?

[@palanivelu.a]

Read about consistency, you come to know. when is opened first will close first with commit/rollback then only the second can able to commit.. else it will hang.

[@pradeepmedikonda9084]

if any other person have done the same update operation on the same row. how this mechanism will handle

[@204subhash]

Hello Sir, I have a query. My buffer cache size is 1GB, but i have updated 3GB data. now, how update process works?

[@RamkumarSwaminathan]

The SGA does not hold all the data at any given time. When the Buffer Cache is full, DBWR will write down dirty blocks/buffers and make way for new blocks to be read from the Data files.... Thus the entire DML will go through...

[@204subhash]

thank you very much sir

[@vishalsankpal5201]

Ramkumar Swaminathan if a dbwr writes uncommitted data to disk then what happens when rollback is issued in this case?

[@RamkumarSwaminathan]

Those will also have to be written back later

[@naseemshaik32]

nice explanation

[@CVenture]

Can you please explain Global temporary table architecture? And how it perform DML operations?

[@RamkumarSwaminathan]

Hi Chandru, I have created a video to explain about Global Temporary Tables in Oracle Database... Hope this helps you... kzbin.info/www/bejne/p6e7Xp-qgN19gNE

[@CVenture]

Thank you Ram.. It really helps me a lot..I couldn't get the point, you were saying about standby database..Can you demonstrate GTT with standby database? It helps to understand it better..Thanks again..

[@RamkumarSwaminathan]

HI Chandru, It enables DML (to do computations while generating reports ) in a Standby Database ( till 12c this was not possible). I will be uploading videos about Data Guard and Standby databases later... where in you can learn about those concepts

[@CVenture]

Ok..Ram..Awaiting for that..Thanks in advance..

[@RamkumarSwaminathan]

Hi Chandru, I have created a new playlist for Oracle Data Guard... This will have the concepts of Standby databases... Hope this helps...kzbin.info/aero/PL3Hc8S1SG2ASVFXYO5EM3Y-QXyCueF1Mr

[@biggboss3946]

what would happen for the data blocks if the buffer cache became full and the user didn't issue a commit ?

[@RamkumarSwaminathan]

DBWR ( Database Writer) will write down changed blocks ( Dirty Blocks) from Buffer Cache to the Data Files from time to time ( on various threshold events)... It does not wait for Commits to happen. Commits are managed by SCN ( System Change Number) that are maintained in the database blocks and redo.

[@biggboss3946]

yes , but this means that the data files now contain uncommited data what will happen to this data ? is it going to stay there ? where can i read more about this ?

[@RamkumarSwaminathan]

Yes you are right... Uncommited data can reside in datafiles... and commited data may not be present in datafiles.... Rollback / Rollforward from Redo will take care if there is a crash. You can read through more about this in Oracle Documentation.... Go through the Concepts Guide... Hope this helps...

[@biggboss3946]

if a datafile contains uncommitted data (which it will do), and then you have a crash, we can use the redo logs to work out what was committed and what was uncommitted at the time of the crash, and roll the committed stuff back. what if there is no crash happened ? is it going to stay in data files ? thank you for responding

[@biggboss3946]

finally i found the answer to my question , there is something called undo tablespace Every Oracle Database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo. Undo records are used to: Roll back transactions when a ROLLBACK statement is issued Recover the database Provide read consistency Analyze data as of an earlier point in time by using Oracle Flashback Query Recover from logical corruptions using Oracle Flashback features When a ROLLBACK statement is issued, undo records are used to undo changes that were made to the database by the uncommitted transaction. During database recovery, undo records are used to undo any uncommitted changes applied from the redo log to the datafiles. Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it.

[@syedafatima320]

I am confused on sql area and shared pool...What is happening actually here...

[@RamkumarSwaminathan]

SQL Area is a memory allocation for each SQL that gets executed in a Database, and it is located in the Shared Pool in the SGA which is part of the Instance....

[@shorifulislam923]

awesome

[@rajasekharbellapu1491]

Sir can we say Select statement is a transaction?

[@RamkumarSwaminathan]

It's a statement

[@RamkumarSwaminathan]

Transaction is a set of statements with a commit

[@baizeedrony]

Thanks

[@hasanfahad5292]

Best

[@SaurabhRelia]

Respects

[@ashishpachpor6734]

thanks sir

[@Nick-ui9dr]

Ainh??? ...Everybody seems to be getting everything crystal clear ...am I the only one with lil brain here? :(

[@اشرفعمر-ع8ع]

دمل

[@elmokhliss4852]

Thanks