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

7. Internal Memory Structures Part2B

The shared pool is an area of memory, another chunk of Ram. That’s what we’re talking about in this whole section here are different chunks of Ram. Ram is very, very complex in the Oracle system. Very complex. That’s how it gets its speed, has all these things that it can do within Ram. Ram, the shared pool is the place where one, it stores compiled code. So SQL code, for example, once it’s stored, goes into the shared pool, goes into the shared pool section of Ram.

PL SQL also. Same thing. PL SQL is code. It’s Oracle’s kind of native programming language. Also gets stored in the shared pool. And you may be thinking, why if I select star from employees, why store that? And the answer to that is, the odds are if you run a piece of SQL code, you’re probably going to run it again. Somewhat counterintuitive, because once you’ve run it, you’ve run it right. Not true. Actually, statistically, SQL code gets run again and again and again. If people log into your online bookstore and they want to search for the number one bestseller, somebody logs in and their search query is, and you’ve programmed this in the background with SQL code.

But from them, they’re just clicking on a button that says search for the number one book that’s on the New York Times Bestseller list, or the top five or the top 20 or whatever, if they run that on a particular web page in your application. Yeah, sure, it’s a very reasonable thing to run. Chances are a lot of people are going to run that web page. And because of that, shouldn’t we keep that piece of SQL code in Ram?

Yeah, of course we should, because it’s going to be accessed a lot. And the area in Ram in which we keep that compiled piece of SQL code is the shared pool. So we’re going to keep compiled SQL and PL SQL code. That’s two examples of code that we’re going to keep in the shared pool. By the way, Java obviously has a lot to do with the Oracle system. If you have any interest in development on the Oracle system, I really recommend that you learn Java.

And why java? Well, Java was developed at Sun Microsystems. Oracle now owns Sun Microsystems. So that’s one of the reasons why Java plays a hefty role within the Oracle system. It did even before Oracle acquired sun. But I do recommend that if you’re going to be an Oracle professional, that you do learn the Java programming language. The Java code, java compiled code does not sit in the shared pool, but SQL and PL SQL does. Also, things that are returned by the data dictionary, by calls to the data dictionary, sit in the shared pool in a compiled state.

So we’ve got a lot going on here. We’ve got compiled code in here, all the sequel and PL SQL compiled code, all the information that’s returned by the data dictionary. And you may be thinking, by the way, I don’t use the data dictionary that much. I mean, I just pull a few things every once in a while. Yeah, you may not, but the Oracle system does. Anytime you’re running some kind of a join, those joins are connected through the data dictionary information. Matter of fact, anytime you do almost anything, you select star from Employees, oracle will say, what the heck is Employees? I better check the data dictionary.

And when it does, there goes that information into the shared pool. The data dictionary is extremely, extremely active, and Oracle uses it all the time. And why shouldn’t Oracle cache things in Ram just like you want things in Ram? Because you want the system to be faster. Oracle wants things to be in Ram so that it can be faster for itself. And the shared pool is the place where the data dictionary information is stored, not only to make things faster for you, but to make things faster for the Oracle database system itself. Okay, so that’s why the shared pool is very, very active. The shared pool just almost needs to sit on the throne of Ram. The shared pool is something that needs to be active. It needs lots of space. I mean, if your system I mean, compared to the other I don’t know whether it needs lots of space in your system, because your system may be small, but in terms of comparing to the other areas, it needs plenty of space. The shared pool is very important.

Let’s talk about the large pool. Now, as opposed to the shared pool, which is very, very important. The large pool may not be important at all to you. Matter of fact, you may never use the large pool. The large pool, in fact, is optional. It may be shrunken down to nothing, shrunken down to zero ramp in your database, the shared pool is used primary excuse me. The large pool is used by a couple of things. One, if you’re using shared servers, that is where you’ve got multiple servers sharing your database system. Your database system is shared across multiple servers.

Then the large pool will be used. And you’ll need to think about sizing the large pool before you use a multiserver architecture. The other time that it’s used is with a program called Rman. Rman is the recovery manager. R man is used to recover a database that’s been crashed. For some reason, our man is used to bring the database back up and recover the data, which may have been lost. It’s the first program, or it’s a program that you may use to try and recover data that may have been lost in a crash. It isn’t the only way to recover data. You may have just some kind of backup mechanism. You may just backup the server every couple of hours or something like that. Depends on how active the server is, how often you need it, how mission critical it is. But if you are using Rman as your backup system, then the large pool is helpful to you, critical to you, perhaps.

Otherwise the large pool is not that important a construct in the Oracle database system. But at least now you know what it is. The Java pool, another one of these pieces of memory which may be mission critical, or you may not use it if it’s not. By the way, it is a mandatory section of memory. It can’t be, it can’t be taken down to zero like the large pool can, because there are systems within Oracle itself that use Java. Some of the coding within Oracle is Java code, so it can’t be eliminated. But you may not need it a whole lot because a lot of your code may not be Java.

Then again, all of your code may be Java. Your system may just be a place where Java does everything. Everything is done in Java. All of your PL SQL, or excuse me, all of your SQL code may be passed in to the database through Java. So it may be very critical. The Java pool is the place where most of the data regarding Java will be stored. Java is an objectoriented programming language. So chances are if you’re a bookstore, let’s say you may have an object within your system called a book.

And if you do, then that book object and I won’t get much more into Java because this isn’t the course to learn about Java. But there are other courses about Java if you’re interested or have a need to learn it. But when you run Java code, if you’re doing it right, you’re creating objects. So if you’ve got an object called a book and then you want to store some data about that book, say name of the book is Mark Twain.

Or excuse me. Name of the book is Tom Sawyer. Author is mark Twain, et cetera, then you’ll be creating the data about that book in the Java pool because that’s where the object and we call that in Java, by the way. The instantiation of that object that will get stored in the Java pool and the data the rows about, for instance, that book will get stored in the table. Chances are then the data moves back and forth through the it moves back and forth to Ram through the Java pool. The rows also will get moved back and forth to Ram into a different global area. But the data about the objects in the Java language will get moved into the Java pool. Other pieces of the Java language may get placed into the Java pool also. So if you rely a lot on Java, then the Java pool will become important. If your database system is a heavy Java system, then you may think about having a larger Java pool. That way things don’t have to sit on the hard drive. Your system can constantly access those Java objects in Ram. Okay the streams pool. I won’t say a whole lot about the Streams pool.

The Streams pool is optional, and you may never, in fact, most people will not see the Streams pool. But here’s the gist of it. It’s possible to pass data back and forth between two database systems. They don’t even both have to be Oracle database systems. It may be Oracle and some other database system, the IBM system, or SQL Server, microsoft SQL Server, something like that. It’s possible to pass messages back and forth between two database systems. Oracle to Oracle. Oracle to Ibmdb two. Something like that. You can pass messages. It’s kind of like passing email back and forth, and the data is contained within those messages. Pull out this row from this table, pack it up into a message like an email, send the message over to the other database system, and then the message can be unpacked and stored in a row in that database. That’s what streams are all about. Data regarding streams is placed in the Streams pool. The streams pool is an oracle option. It’s not something that a whole lot of users are going to use, but just so you know, those messages are placed into the stream pool to be worked with by the Oracle system. This may be something you never use in your working career, or maybe something you use all the time, but this is an option within the Oracle database system. If you use it, that Ram space is what will be used to hold those messages, those packages of data, before they get passed on to the other database system. Lastly, we have the fixed SGA.

The fixed SGA is just what it sounds like. It’s fixed, meaning it can’t grow, it can’t shrink. It’s one chunk of ram. It’s used by the Oracle database system itself, and it’s used for the database system to store certain pieces of data about itself. And that’s really all you need to know. In other words, the Oracle database system has its own Ram that it covetously guards for storing its own data that it needs to operate. You do not need to size it. You do not need to worry about it. It’s like Microsoft Word, you know, it takes some ram. You know it needs some Ram, and that’s the place where it’s going to use its Ram. That’s going to go within the fixed SGA. And what’s SGA stand for? The shared or system global area. I like the term shared global area. So it’s the fixed shared global area. It’s not shared because it’s only used by Oracle itself. That’s a chunk of Ram that Oracle needs. Okay, so those are the major structures. There are others, but those are the major structures that version twelve of Oracle uses within the Ram. Those certainly I would expect to see on the exam. So be familiar with all of those. Be familiar with what they do, be familiar with what they store. And you should do pretty well. Thanks.

8. Background Processes Part1A

So we have talked about some of the Ram storage areas in the database. Now it’s time to talk a little bit about the Oracle system. It’s a little bit strange. Let me just digress for just a second. When we talk about a piece of software that runs on a computer, and I always use the example of Microsoft Word, we don’t don’t think, for instance, about all the different chunks of Ram that Microsoft Word uses. We just think, okay, Word must run somehow in Ram. I mean, it puts the document into Ram and I’m sure it puts some of it we’ve got Ms Word Exe that somehow runs in Ram. We know that that happens. That’s really all that we think about, probably all everything to do with Microsoft Word puts itself into Ram. I would assume so. And just occasionally calls some sort of process to back itself up because we know that Word has some kind of auto back feature to save itself occasionally every minute or so. It used to be in Microsoft Word that you could actually set how often, I don’t know if you still can because they’ve removed a lot of features from Microsoft Word. Anyway, not to be distracted, but there’s just basically one chunk of Ram for Microsoft Word. It’s not divided up into all these different pieces of Ram like we talked about in the last section, if you watched it. Same way with the hard drive, microsoft Word doesn’t divide itself into a bunch of different parts on the hard drive. There’s just the document that’s saved on the hard drive and that’s really in the program itself. Word Exe.

Ms Word Exe that’s saved on the hard drive and that’s really it. Oracle, however, is considerably more complex than that. So in the last section when we talked about all these different parts of Ram, there was quite a bit to talk about. There wasn’t just one little chunk of Ram into which a document is loaded and then we work off that document. No, not at all. Several different parts of Ram that are controlled by the Oracle database system. Well, this is also true of the actual program of Oracle. Oracle actually consists of a series of different programs. Some of those are completely independent of the Oracle program itself, the Oracle service, if you will, on Windows. And there are others which are actually part of the Oracle program itself. There are some which run independently of the Oracle program itself. In this section we’ll talk about some of those processes, in particular, the most important processes that run on Oracle. Oracle just doesn’t run. There are some database systems that do. You start up that exe program, for instance, on Windows and the database is running. Not true on oracle. Oracle runs several different programs, if you will, within itself.

And it actually calls out to a number of other programs that it runs in order to perform maintenance or just in order to keep itself doing tasks that are necessary to a large, somewhat complex database system. Actually, it’s a very complex database system if you were approaching it from scratch, if you weren’t taking a course like this. Oracle is a very, very complex program. Once it’s explained to you, as I’ve said many times throughout this course, once it’s explained to you, it’s not so bad, but just approaching it from scratch. Oracle is one heck of a program, okay? So Oracle doesn’t just run from one big piece of software. It actually is a series of separate pieces of software. And it’s important for us to learn a lot of these separate pieces of software for exam purposes or for work life purposes. And we are going to do that now. We’ll go through some of the separate threads, if you will, or programs if you will, that run within the Oracle system. Let’s get familiar with what some of them do, how they can affect our work life, these different processes. We could call them by different names. If we were running on different OS’s, we might call them by different names. We might call them a thread, or a task, or a job. I guess if we were in Windows we might call them a thread. Certain versions of Unix or Linux, we’d call them a job. So there are a lot of names for what we’re going to talk about in this section. But really what we’re going to talk about are the different programs that perform different tasks within the Oracle system. So what types, how can we divide these different programs up? And we’ll talk about these in this section. First there are client processes.

Then we’ll talk about some of the server processes, the processes which are obviously running on the server itself. And then there will be some background processes. Oracle runs a lot of background processes. In this section. We’ll talk about the most important ones, the client processes, also called the user processes. I prefer the term user processes because remember, Oracle isn’t really when you log into Oracle, you’re not really logging in as a client. You’re not logging in as yourself. It isn’t just you that’s using the your Oracle database system. Although it could be, you could be the only one logged in. But the point is, you are a login. You are a username and a password. You’re a user of the system. That’s why I don’t like the term client so much as I like the term user. You might log in as Scott with a password of Tiger, but I can also log in as you, because if I log in with your name, Scott and use your password Tiger, then I’m logged in as you. But it’s nothing personal. I didn’t mean to steal your password. I’m logged in as the user, Scott. It’s nothing. I’m not going to move into your house, I promise. I don’t know where you live. You can log in as Scott, the user Scott.

I can log in as Scott, the user Scott. So let’s talk about these user processes. The more technical term would be the client processes because they tend to run on the client side, not on the server side. So user processes typically run on the user’s machine. So this is the machine that some user has logged on through. They’re going to run on the local processor. Chances are they’re going to run on the local machine. Now that’s a bit of a concept because for instance if we have someone at their house logging onto a website using Internet Explorer or some other browser, none of these processes are going to actually run on their machine, except perhaps Internet Explorer. But Internet Explorer doesn’t have a whole lot to do with our system.

So they’re going to log on to some type of web server. But you know what I mean, they’re still logged on as a user. We’re not running on the local machine, at least not the machine that’s local to them. We’re running perhaps on a web server. Sometimes processes will run on an application server. An application server is a separate machine that does nothing but run programs and then in certain circumstances they may run on a server. And if you’re just running a local database, you’re just running Oracle locally on your own machine. Everything’s running on all three. The local user software, the application server software, and the database server software could be on all three. It’s all running on the same machine. So there are some concepts going on here but you get the idea it’s not uncommon in a large system for the user to be on his or her own machine, which then connects to an application server, which is going to be something like middleware, which is then going to connect to a series of database servers. That’s not at all unusual. It’s a typical architecture.

And the reason why I bring this all up is because we’re going to see that our processes are going to run in different areas and it could be very important for you to know where they run. Either way, user processes are going to run on some OS, they’re going to run on Windows, and if they’re true user applications, they probably will run on Windows or they’re going to run on Linux, they’re going to run on Unix or they’re going to run on Mac. They’re going to run on one of those I guess if we’re talking about user software, I guess they could run on some operating system other than one of those four. But almost always it’s going to be one of those four systems. And of course Oracle must run on one of those four systems in its latest incarnation in version twelve because it’s only written for those four systems now. So the user side is going to run on one of those systems. Probably windows. Maybe not, but probably Windows, and probably not necessarily, but probably it’s literally going to run on that local system. It’s going to run on the user’s local Windows machine. And an example that I give on this particular slide is SQL Plus. SQL plus is a local application. It runs on the user’s local machine, and then SQL Plus connects to a database, probably on the server or on some servers somewhere. Very typical example. Okay, so those are our client processes.

Let’s talk about our server processes. So what types of things will server processes be doing? Well, here are some examples that I give. They’re probably going to parse and run SQL SQL statements. They’re probably going to run some database related software like PL, SQL or Java. They’re probably going to read data blocks into the buffer cache, into the database buffer cache. And why do we put things into the buffer cache? Because it’s much quicker to read out of Ram than it is to read off of the hard drive. And they’re going to send some data to applications. So that’s just some typical examples of what’s going to happen on the server process. Those programs which run on the server itself, by the way, the database buffer cache. Something I’m just going to digress for just a second to talk about. If, for instance, you’re running Oracle on a machine like Windows, and let’s say Windows runs out of Ram, which is common in Windows, because like all operating systems, all versions of Windows are limited in the amount of Ram that’s accessible to the operating system. That’s why you can’t just load up Ram stick after Ram stick after Ram stick on the motherboard.

There is a limit for each version of Windows that you’re running at which Windows will say, put more Ram onto the motherboard. I don’t care, I can’t access it. There comes a point at which Windows just says, that’s it, that’s all I can do. And each version of Windows has a different maximum capacity. So think about this, by the way, when you’re running on a Windows server and on some Unix and Linux servers as well, in fact, on all of them. So let’s say we load data off of the database and we load it into Ram, which we know if you’ve watched some of the prior sections in this course that I’ve talked about, that’s a good thing to do. Absolutely. You want to put as much data as possible into Ram because of course Ram is a lot faster, easier to be served up to our users. Now, let’s say on our Windows system we can handle 64 megs of Ram. Okay, great. We got a whole lot of fast Ram going back and forth to our users. And let’s suppose, for whatever reason, our users pull up 100 megs worth of data. Some OS’s like Windows form a swap file. What happens with the swap file?

They dump extra Ram onto the hard drive. So now we have our beautifully tuned database, we think loading just the right amount of data into Ram so that it just purs like a kitten. And what’s happened? Windows has dumped data onto the swap file, which is a piece of the hard drive. So we’ve pulled data off of the hard drive from the Oracle database, which is what we wanted to do, put all that data into Ram, which is what we wanted to do, but now it’s too much. What does windows do? Puts it back onto the hard drive, into the swap file. We’ve moved it from the Oracle database hard drive to the swap file hard drive. And what have we done? We’ve just slowed the whole system down as if we’d never put the data in Ram to begin with. It’s just a side tuning issue. Sorry, I didn’t mean to go off our subject here in this particular section, but I wanted to make sure that I didn’t forget to tell you about that. And this just seemed like the place to do it, because we’re talking about these server processes, because there’s no server process to put things back onto the hard drive, but your OS may do it anyway. So you’ve got to be very careful about that when you tune the Oracle database and if you choose to take the Oracle performance tuning class. And good for you if you do that. There’s nothing in there about the swap file and dumping data back onto the hard drive when you thought you were doing such a great job by pulling it all off of the hard drive to put it in Ram to begin with. Just a little performance tuning tip that I thought was appropriate for this class because we’ve talked about it, we’ve talked about moving things onto Ram as much as possible. OK, back to these slides.

Let’s talk a little bit about some of the processes that run in the background. So we’ve talked about that there are some user processes like SQL Plus or one of those kind of or Toad, one of those kind of processes. Chances are that’s going to run in the user’s Ram because the user is using it on their local machine. Or maybe some application that you’ve written to access the data, some application you’ve written in Java or C Sharp, Visual Basic, something like that, that accesses the data, all that’s going to run on the local users machine. Chances are local process. We talked about some server processes, some things that run on the server that may take up some of its Ram. We also talked about what’s in the middle, an application server. That’s kind of the old client server architecture. That’s sort of old fashioned now because we’ve gone more to a grid architecture or an end tier architecture, but they’re all sort of based on the idea of client middleware server that’s not difficult to code. So you may see that an awful lot and you’ve got to decide where you’re going to put things. Let’s take a look at what’s going to run in the background. Background Processes let’s go through the most important background processes. These obviously are server processes.

So you should be aware of all these different background processes that run on the server. Some of them can be tuned. We haven’t talked about tuning yet in this course. So I want to talk about the major ones. So what are these background processes that run on the server? Well, first of all, they start with the instance when you start up Oracle. When you start up the Oracle database instance, these start automatically. They’re background processes, right? So they run in the background of Oracle. So these will start automatically. And by the way, there are a lot of these, but only a few that we really care about, and only a few that we can really do anything about. Only a few that we can tune. So I want to talk about the major background processes here. Like I said, they all start up with Oracle. They start up with the instance.