Transcript

Demonstration – What Does Oracle do when DML is Executed?

Oracle Instance Recovery Tutorial

 

Module 5 – Demo – What Oracle Database does when DML is Executed

 

>> John:  I want to run through what actually happens when we execute DML, what happens on disc, what happens in memory?

 

There’s some assumed knowledge here by the way. I assume you are all database administrators. This is some assumed knowledge. What I think I’ll do at this stage is demonstrate what happens first and then return to the slide.

 

[pause]

 

This is actually standard 11g 11203 database by the way. Nothing special about it at all. I’ll connect to my database as user Scott and I’m going to create a little tablespace. Create tablespace. Now I have a small tablespace. 

 

[pause]

 

Datafile.

 

[pause]

 

I’ll call the datafile

 

[pause]

 

Small1.dbf*size 128k. It can’t get much smaller than that.

 

You’ll see why I’m creating a tablespace specifically for this demonstration in a second. Then within that tablespace, I’ll create a very simple table.

 

[pause]

 

A very simple table t1 with one column c1 of varchar2 type.

 

[pause]

 

Now, insert a row into the table. Insert into t1 values and I’ll insert myself.

 

[pause]

 

I’ll even commit myself. So I am now committed. But what’s actually happened on disc? Let’s take a look.

 

[pause]

 

There’s my datafile small1.dbf. I’ve inserted myself and I’ve committed myself – is there anything on disc at this stage? I’ll use the strings utility to extract just the text strings from a binary file and I’ll string small1.dbf and grep out john. 

 

[pause]

 

It’s not there. That’s the first example of data corruption. I’ve got a committed transaction and it does not exist in the database. It simply isn’t there.

 

[pause]

 

Right now my database is missing a committed transaction. That’s straightforward corruption. But it gets worse. I’ll insert another row. I shall insert a row. In this case, I’ll put in Susan. Susan can join my database. Nice.

 

[pause]

 

Uncommitted transaction. I’ve got a committed transaction – apparently it’s not in the database – and uncommitted transaction as well. Is that in the database? It certainly shouldn’t be. You see if Susan exists in the datafile and sure enough she doesn’t. But what if Oracle decides to write some data to disc? I’ll go through the algorithm with you shortly.

 

Let’s just assume – I’m going to force Oracle to write to disc at this point. Alter system, checkpoint. Force a writer buffers to disc. What’s happened now? Susan is there.

 

[pause]

 

She exists in the datafile. This is bad a corruption as you can get. You think, “What’s happening?” My committed transaction that inserted john was not on disc. My uncommitted transaction that inserted Susan is in the database. This is a pretty serious state of affairs. Is it a problem? SQL*Plus disconnects from another session.    

 

[pause]

 

If I select * from t1, everything is fine. I’m getting proper transaction isolation. My transaction has inserted John. We don’t know how durable it is, but apparently just pretty durable. It’s there. It’s visible even though it is not in the datafiles and my uncommitted transaction is insert Susan is totally isolated so nobody else can see her. Of course, from the session that did the insert that session is of course allowed to see the uncommitted transaction.

 

[pause]

 

Take a look at the next stage. What has happened within the redo log? I’ll go through the redo log contents and then we’ll see how this is being implemented.

 

Select * from v$log

 

I want to see which is my current log file group. My current log file group is group 1. Group 1 consists of that file there, redo01.log. Let us see what there is in redo01.log. 

 

[pause]

 

I’ll grep out John. Does he exist?

 

[pause]

 

Yes, he does. So the change vector that inserted John does appear to be in there.

 

What about the change vector that inserted Susan?

 

[pause]

 

She’s in there too.

 

Maybe you can make things a bit more complicated. Build up a bit more of an idea of what’s happening on disc and then run through the theory. 

 

[pause]

 

Let’s say in this session, I shall update t1 set c1 =. I’ll put Dave in my database instead of me. C1 = john. I do that update.

 

[pause]

 

Uncommitted.

 

[pause]

 

What do we now see in the redo logs, things get a bit more complicated. 

 

[pause]

 

John has two entries now in the redo string. One will be the entry when he was inserted into the table. The other will come from the undo segment when I updated the row, which we’ll come to in just a second. Of course, Dave is in there too.

 

[pause]

 

What’s on disc? Right now, if we look at John as the person who exists in the datafile.

 

[pause]

 

That’s a checkpoint to force a disc write. Alter system checkpoints.

 

[pause]

 

What’s in the datafile now?

 

[pause]

 

John’s gone. He’s no longer in the datafile, but Dave is. Now I have my uncommitted update that’s apparently wiped John out from the database. 

 

[pause]

 

What’s going to happen at this point if we have a problem and we grep the database? 

 

[pause]

 

What I’ve demonstrated, we have the log writer, we have the database writer. The log writer writes on a very, very aggressive algorithm. Change vectors are written to the online log in very nearly real time. When you say “commit,” it is real time. As you’ve seen, pretty much as soon as I did any change, the change vector immediately went to the online log – immediately.

 

So I’ll just commit that transaction, stop any more confusion. Do another update.

 

[pause]

 

I’ll change “Dave” to “Lynn.”

 

[pause]

 

That’s uncommitted.

 

[pause]

 

We get this ready in advance.

 

[pause]

 

Grep Lynn. No mention of course. Do my change. Set c1 = lynn where c1 = dave.

 

[pause]

 

In virtually real time, the change vector of the update is there. Alter the change vector to Dave.

 

[pause]

 

He was there as well. Right. What’s going on?

 

Log writer, as demonstrated, writes in a very aggressive algorithm. Your change vectors go to the log file in nearly real time. When you say commit, believe me, it is real time. The session hangs when you say commit until the vectors have gone to the online log.

 

[pause]

 

Note the online log change vectors are both committed and uncommitted changes.

 

[pause]

 

Database writer by contrast, also as demonstrated, writes in a very lazy algorithm. I’ve changed a row to Lynn. But those Lynn exist in the database, not at all.

 

[pause]

 

Database writer writes it on a lazy algorithm as little as possible, as rarely as possible. Why? Because disc are always back to performance. Blocks are written only if they conform to two criteria. They’ve been changed – there’s no point in writing data that hasn’t been changed – and they’re idle, i.e. no one is using them. 

 

This choice of which blocks to write is not related to commit. End result? Your database is always corrupted as demonstrated. The datafiles on disc may be storing uncommitted changes. They’ve been missing committed changes.

 

[pause]

 

Does this matter? No. It doesn’t, unless the instance crashes.     

 

Copyright SkillBuilders.com 2017

×
Free Online Registration Required

The tutorial session you want to view requires your registering with us.

It’s fast and easy, and totally FREE.

And best of all, once you are registered, you’ll also have access to all the other 100’s of FREE Video Tutorials we offer!

 

×
Podcast
×