1z0-062 Oracle Database 12c: Installation and Administration – Module 7: Internal Structures

5. Internal Memory Structures Part1B

So let’s talk about what goes into the SGA. Here’s just some on this slide of what goes into the SGA. And then we’ll go through this specifically and we’ll talk about what each of these areas are for. Each of these areas will possibly be tested on the exam. Okay, we start out with the database and I will go through each of these areas specifically with you. We start out with the database buffer cache. It’s an area where we’re going to store some data from our database, some area from our tables. The redo log buffer, if you’ll recall, the redo log allows us to reconstruct a database after we’ve attempted to make changes to it, probably through an insert or an update. The shared pool, an area that we’re all going to be able to see, an area that we all share. The large pool, which is an area where large pieces of data are going to be stored, as opposed to say, one small row that contains, say, just alphabetical data or something like that. The Java pool, which is an area where Java information, probably variable information, is going to be stored. The Streams pool, which is where messaging type applications hold off on that and I’ll explain that in a little bit, is going to be stored. And then the fixed SGA, which is an area for fixed type of data to be stored just to be used by the database.

So let’s go through each of these areas. We start out with probably the most important area, which is the database buffer cache. We know if you saw the section which goes chronologically just before this section that I talked about, data in the Oracle database is stored in blocks that’s areas of Ram, which is a preset size of Ram into which rows must fit. Those rows are copied from the table or whatever database structure we’re talking about, generally into Ram. And that’s done for speed purposes. Now, your Ram may be huge. Remember, Oracle was developed in a time when Ram was not so huge. And so we had to be very careful or the developers of Oracle had to be very careful about putting data into Ram. We definitely want our data into Ram. Why? Because it’s fast. We can get to data very quickly in Ram and serve it up to our user. Pulling data off the hard drive, as we know, is much slower. So we want to put as much data into Ram as possible. It would be wonderful if we could store the entire Oracle database system into Ram. And if you don’t have a whole lot of data and you’ve got a big bunch of Ram on your server, that may be indeed what’s happening.

So that’s great. But not many people have that kind of configuration. Not many companies have that sort of corporation. Normally, all the data is not going to fit into the Ram on the server. So for that reason data must be swapped back and forth between the hard drive and the Ram. So the question is, if all the data won’t fit into Ram, that place where we would love to have it, what data should we put into Ram? And the data that’s put into Ram is stored in the database buffer cache. And it’s stored not by the row, because that you would think would be the place to have it. You’d think you’d want to do it by the row, but we learned in that last section, rows are fictitious constructs to Oracle. Data is really stored by the block. And why is that? Because our OS is built in blocks. All of the Linux operating systems are built based on the blocks. Windows is built based on blocks. Everything in a hard drive or in Ram, in Windows or Linux or Unix, it’s all done by blocks.

So our data is stored in blocks. Oracle works on blocks, not by tables, not by rows. That’s all fictitious. That’s a great way to think about it, because our minds can conceive of tables and rows and columns. Yeah, that’s very easy for us to do. We don’t think of data as being in blocks because sometimes a table may be spread across hundreds or thousands or millions of blocks, terabytes of blocks. It’s certainly very possible. So that’s complicated for us to think about. We want to think about rows and columns in our tables. That’s a relational database system.

Tables relate to tables. Structures relate to structures. Okay? So it’s all done by the block. We think of it as tables and whatever structure Oracle thinks by the block. And so when data is ready to go to the users, the blocks are moved off the hard drive and onto Ram. That’s because it’s faster. That’s the only reason. It’s just faster to be pulled from Ram delivered up to the user, rather than pulling it off the hard drive and sending it up to the user. And you may think, by the way, well, hard drives are so fast these days, and you’re probably right. But remember, in large database systems and Oracle can run in the petabyte level, that’s really, really, really large. How large is it? Really large. Oracle can run into the petabytes with its system size. So when you’ve got that much data that has to be accessed, you don’t want to hit the hard drive all the time to grab data. It’s much, much easier to pull that data out of Ram. But Ram, as we know, is done by the block. So how do we know when we want to put those blocks of data into Ram, which is really, really fast, but then once we fill up the Ram, how do we know what to just let go and grab it off the table next time?

Grab it off the hard drive. It only fits so much into Ram. And the answer is, Oracle uses an algorithm for that, and the algorithm is least recently used. It’s the LRU algorithm. Least recently used. Least recently used says if it is the least no, it’s not. Last recently used. Don’t get that mixed up. It’s easy to confuse them. Least recently used means it’s been used the least. It’s also been used the longest to go. So between those two factors least used, it hasn’t been used that much and longest to go. It hasn’t been used that recently. And exactly what the algorithm is. Don’t worry about it for these purposes. Don’t worry about it for this course, don’t worry about it. For this test, least recently used says, haven’t used those data blocks in a long time. Let them go.

The ram is getting full. We got to dump something. All this data right here is being used all the time. Move it up front. All this data back here, hardly ever getting used. It’s least recently used. Nobody’s looking at this old data. It’s lonely. Let it go. That’s the least recently used algorithm. So Oracle will let go of the Ram that it’s not using and instead keep the Ram that it’s always using all the time. The buffer is the pointer in Ram, which is where all the data is. This data from this table is here in Ram. We’re going to point to it there. That’s how Oracle keeps track of what’s in the data in Ram. It points to it. It uses pointers throughout ram. If it’s pointing to this data, which is LRU least recently used, it tells that pointer to get rid of that data, and it’s flushed out of Ram.

So one of the main reasons why Oracle is perhaps probably is, but claims to be, and I think they’re right, the quickest database system. Very, very quick database system, especially given how much data it can hold. So remember, one of the most important things to remember about this section, about this whole chapter that we’re talking about here, is that Oracle uses a least recently used algorithm. The data which is older and has hardly been touched, is going to be flushed out of the Ram earlier to make room for data that’s being accessed all the time. And that’s done purely for speed purposes. That’s the database buffer cache. The database buffer cache is where the data is stored, which is most recently used. Most recently used data goes into the database buffer cache, which is in Ram. We want to get to that data as quickly as we possibly can.

 

6. Internal Memory Structures Part2A

Let’s next talk about the redo log buffer. Before we talk about the buffer, let’s talk about the redo log, because we haven’t talked about it a whole lot. So here’s what the redo log is. The redo log is an area of memory which keeps, let’s call it the diary of everything that’s been been done. And by everything that’s been done, I mean to the tables, to the database structures, everything that’s been done to the data, particularly the DML insert, update, delete. Every single bit of insert, update and delete is stored in the redo log. It’s the DML statements doesn’t store the data, it stores the DML statements. So if you’ve done insert whatever, insert whatever, insert whatever, that’s all stored in the redo log, line by line by line. That’s what the redo log consists of. It is every single statement that’s occurred in order. There’s no algorithm here, nothing at all like that. There’s nothing magical, nothing special about the redo log. It is step by step by step in order of the DML that’s occurred. If it’s a select statement, for instance, does not go into the redo log, we don’t care about that.

And here’s why. Why is it only DML? Why is it only insert, update and delete? Because the purpose of the redo log is to redo everything that you’ve done, every change that’s been made to the database. So if it’s an insert statement, that insert statement goes into the redo log. It’s an update statement. Same thing. A delete statement, same thing. Insert, update, delete, those three statements go into the redo log. And why might that be? Why do we need a redo log? And the answer is, it’s a backup mechanism. So let’s suppose you issue 1000 statements and none of the statements have been committed yet. In other words, they’re in that nebulous area where you as a user, can see them, but no other user can see them because there’s been no commit.

They haven’t been written to the hard drive yet. They’re in that area where they could be rolled back. So here is this. 1000 statements and for whatever, that’s a transaction. Let’s say that one grouping of 1000 statements is in and of itself a transaction. So it can’t commit until number 1000 finishes. And we’re at number 999. Someone trips over the plug, system goes down. I don’t know why. I always think someone’s going to trip over the plug. It’s not like there’s no backup. It’s not like there’s no electricity backup, but whatever, there isn’t.

Meteor hits the server, takes the whole thing out. No wait, that would take out the redo logs too. Anyway, you know what I’m talking about. Something happens and the system goes down on row number 999. Transaction has not committed. Fortunately, you know every single statement from one to 999. And how do you know? Because you’ve got the redo log. The redo log is nothing but all those statements. So in other words, if you rerun the redo log, you can rerun all of those statements one by one, just exactly in the order in which you have entered them. And all that data is going to commit just like you had run them. That’s what the redo log is. When the redo log, when you reboot the server and the redo log sees that there are uncommitted transactions, actually it’s not the redo log that even looks for it, but another process that checks to make sure that the whole system is being booted up correctly.

It says, we’ve got this DML here all in a row that didn’t commit. That process runs over to the redo log and says, hey, redo log, go start that whole thing again because it didn’t commit. So let’s rerun it again, and this time, let’s try and commit. Let’s see what happens. So that’s what the redo log is. One more thing you should probably know. Oh, by the way, also, I guess two more things you ought to know. The redo log not only stores DML, that is insert, update, update, and delete our DML statements. Insert, update, and delete, but our redo log stores DDL, data definition, Language. So creating tables, dropping sequences, all of that also gets stored in the redo log. In case, for example, you run a create table statement and it fails because of loss of power or whatever, then that statement can be rerun again. So the redo log keeps both of those types of statements. The other thing that I wanted to talk to you about was that the redo log is circular. So it’s kind of like this picture of the clock face. So the redo log starts writing here and continues all the way around until it’s full. And then it starts writing again over top of the old data. So it rewrites over itself.

A redo log therefore needs to be pretty big, needs to be as big as you can reasonably make it, because once it runs out of space, it’s got nowhere to go except over top of itself. Now you can have multiple redo logs. In fact, it’s somewhat foolish not to have multiple redo logs. And in another section, we’ll talk about how to make multiple redo logs. So you don’t have to worry too much about this happening, but just know that if the redo log runs out of space, it’ll begin overwriting itself and it’ll go from line number one to line number whatever, and it’ll just start again at line number one and start erasing itself.

That’s how that works. Okay, the internal program within the Oracle database that writes to the redo log is called Log Writer. That’s easy enough to remember. So just remember that Log Writer writes to the redo log. It’s one of the internal processes that runs within Oracle, and its purpose is to write to the redo log. There are a number of these internal processes that we need to talk about. And they all usually have like these all capitalized four letter abbreviations. And this one’s no exception. So you can see here on the slide, its abbreviation is LG WR. That’s the abbreviation for Log writer. Log Writer is the little internal program, the little sub program that writes out all this information to the redo log. Okay, good enough for the redo log for now.