Gain in-depth knowledge for passing your exam with Exam-Labs 1z0-062: Oracle Database 12c: Installation and Administration certification video training course. The most trusted and reliable name for studying and passing with VCE files which include Oracle 1z0-062 practice test questions and answers, study guide and exam practice test questions. Unlike any other 1z0-062: Oracle Database 12c: Installation and Administration video training course for your certification exam.

Module 2: Memory Structure

3. Memory Structure Part2A

The next area in memory we need to talk about is Remember I said Oracle has a lot of different sections of memory? Most pieces of software don't even care about memory. People just write software, be it Windows software or Linux software. Just write the software and care only if it works. That's the way software is written. I've been writing software for most of my life. You don't generally care about memory. There's enough RAM; the computer's going to run. That's all there is to it. and you write your software. Your concern is whether the software runs, and it runs without errors. You're generally not concerned about RAM or how much space you take up on the hard drive. And what's the difference? Well, to begin with, Oracle's pretty huge. I mean, it's a big piece of software, but it's also a client-server piece of software. Now that it's a cloud-based piece of software, especially with 12 C, we don't have to worry as much about the user's hardware. We can run this thing in the cloud, access it across the internet, or something like that. So it's not that much of a concern anymore. But Oracle is big and Oracle moves big data. So when you've written software in the past, if you have, and you were writing, say, a word processor or something like that, whatever piece of software you were writing ran on one local computer, and you knew the computer was going to have enough RAM to run it. Oracle is big, though, but what's more important is that the data can be big. So the question is, can all the data fit? You've got to worry not only about the software you're rewriting, but all the data it's going to handle. So you really do care about how that RAM is broken up within the program. You really do care about what sections of theOracle process used, how much of the memory. Why? because memory may be scarce. All these different processes have to work within memory, and all the data has to fit within memory. And the data could be in millions of rows. We want to put as much of Oracle as we can within Ram. Why? because it's a big programme and it may run slowly if you're writing a word processing program. That is a tremendous accomplishment, and you should be proud of yourself, but you are more concerned with the software working than with the RAM. If the software doesn't work well on a particular computer, then the user needs to upgrade. That's all there is to it. Sometimes it's not easy to upgrade an Oracle server. That can be tens of thousands of dollars. So we want Oracle to run as well as it possibly can. Have they accomplished that? Oracle Enterprise Edition can be run on any old PC as long as it is relatively new. It has a good recent processor and has decent RAM. It'll run, so that's a pretty good job. They've done a pretty good job of designing this software. That's why we're not just talking about Ram. Guess what Oracle needs—x gigs of RAM on your machine. We're talking about all the little pieces of RAM that this software is going to use. We're going to talk about all the little pieces because you, as a DBA, may have to make changes to all these little pieces of RAM. So Oracle has tremendous abilities built into it that actually allow you to control how the RAM is used. You can dedicate certain portions of the RAM to different processes that are within Oracle. You have total control over all of this. You have control over how much RAM users can access. You have control over what processes are allocated and what amounts of RAM. Now that's fairly powerful. That's something that you as a developer, if you are one, wouldn't be able to write into your software. Chances are, this is one of the reasons why Oracle is such a fast database system. It doesn't just move things into RAM. You have the ability as a DBA to control how much of what process moves into RAM. You can move things that are slower into larger parts of RAM to give them the ability to offload things from the hard drive into RAM. And all these different processes that we're talking about are the things that you can load into different sections. In fact, they're already set up in RAM for you. It's just a question of how you're going to allocate the RAM. Let's go to the next one, the Program Global area. The programme global area is a storage area in RAM, and it's a storage area for users. It allows users to have a place to storevariables or code or things along those nature, alongthat nature in the Ram on the server. Again, just another way to speed up Oracle. This is one of the reasons why I think Oracle's claim that it's the fastest database system in the world can probably be proven. In fact, I've seen many tests, and perhaps you have as well, in which Oracle competes with other database systems and wins. There may be other tests where other companies put themselves against Oracle, and I just haven't seen them. So the programme Global Area is another section of RAM, and it holds data per user and gets created each time a user logs on. It's divided up into two different areas. One is the SQL database. I said SQL. Some people, I think most people probablysay SQL, but I say SQL. One is the SQL work area. That's an area where the sequel is stored once it's compiled. It's also an area where errands are completed. So when data is being sorted, it's going to be sorted in the SQL work area. And it's also a place where SQL queries are stored in a compiled format. The other area is the private SQL area, and that holds the SQL statement. I know this sounds confusing. I'll clear it up in just a second. Because I know I just said SQL is held in the sequel work area, and now I'm saying sequel is held in the private sequel area. The private sequel area holds sequel statements and some variables. OK, the difference between the SQL in the SQL work area and the private SQL area is that the SQL in the SQL work area is compiled. The sequel in the private SQL area is uncompiled. So it's your code. That's the difference between those two areas. The SQL work area—in fact, both of these areas—will hold your SQL for as long as it can. The SQL work area will hold on to compiled SQL statements for as long as possible. And the reason it does that is just in case the sequel needs to be rerun. If it needs to be rerun, it's already compiled and good to go. So no compilation needs to occur. The whole process is going to be that much quicker. If we don't have to recompile, then that's going to save a little bit of time. So the most recently used compiled SQL is going to sit in the SQL work area, and the uncompiled code, which is the pure code version, is going to sit in the private SQL area. That way, you can access your code to make changes. Alternatively, if the compiled SQLgets were bumped out of the SQL work area due to RAM exhaustion, For example, the SQL can easily be recompiled from the private SQL area. Why? because SQL code is held in the private SQL area. If this sounds like "oh my gosh, this is so much," well, it's not that much to memorize, so don't worry about it so much for exam purposes; learn it and then it will probably be tossed out of your memory. Do learn this stuff, but don't worry, it's not that difficult. Think of the whole idea here. What are we trying to do? Oracle is trying to fit what it can into RAM memory. Because obviously that's so much quicker than hard-drive memory or some other kind of memory. Fit what you can into RAM memory. And we all know that the more RAM a computer has, the faster it runs. And why not just load up your computer with Ram memory? Yeah, do it. There's no reason not to put as much RAM into your server as you can into your server.You can only put so much in because operating systems can only access so much RAM at once. And sometimes you can actually overload a server with RAM. So we don't want to do that because then we're going backward. If a computer or a CPU is spending so much time organising RAM, then it can't do what you want it to do, which is return data from the databases, so this is all a delicate balance. Fortunately, this is not as much of a delicate balance as it used to be. 15 years ago, when hard drive space was at a premium and RAM was very small, this was a really big deal. Now that quad-core processors are cheap and they can access tonnes and tonnes of RAM, which is incredibly cheap, This is not so big as it used to be, but still, it's important to know this stuff. But that's why we're going through it all, because this is one of the main ways that Oracle makes itself faster by making very, very careful and specific access to RAM memory available. And when Oracle makes the claim that it's the fastest database system in the world and you see all these different ways of handling RAM, you kind of start to believe them. The next area of RAM that we want to talk about is the system global area. The system global area is its technical name. I think most people call it the shared global area. Either way, same thing. System global area, shared global area two names for the same thing. What's the point about this area of Ram? Well, this area of Ram has a lot of stuff. First of all, it holds the shared pool. The shared pool is the pool of all of the most recent compiled SQL statements. Remember, we had an area of RAM where we held your most recent compiled SQL statements. Now we have an area for everybody's most recent sequel statements. And I know you're thinking that sounds dumb. We're storing compiled SQL in two places. Okay, well, let's think about this. You and I are both operating on the database. I just ran "select star from employees," so that's stored in my area. You just ran "select all" from the employees that are stored in your area. Wait a second. Why don't we put both of them in just one area if we're both going to be running these SQL queries a lot? And that's what's happening here. Oracle is taking a query that perhaps an awful lot of people use. Assume that all of our Internet users, all of those who log in and search for book titles, a specific book title, are not in separate areas of memory. Let's have a big global area of memory store for the recently used and generally used queries, and that way we don't have to waste all that. Ram got a shared area called Ram. That's what's going on here. the database buffer cache. Now this is really important. Yeah, it is good to keep compiled and uncompiled SQL statements in certain places. This is going to save us from going to the database and recompiling things over and over again. That's important. But you know what's really slow? Retrieving 1000 rows of data off the hard drive And if we do this again and again and again, we are talking about a really slow database. Why don't we take big chunks of data if it's something we're accessing all the time, like the most popular books, and move all that data into RAM and let the database try and do searches against RAM rather than always doing searches on the hard drive? There's an idea. It turns out that the system globalarea does this for you. The most recent data from the database gets loaded into the system global area, and it stays there. It stays there as long as it can, unless more recent things bump it out. Chunks of data will actually sit in RAM rather than in the database on the hard drive. Another way of really speeding up the Oracle database is the redo log buffer. "Redo" is when we need to undo something. It's kind of like an undo log. It's when we need to, maybe. We've made all kinds of changes to the database. All of our customers that live in Florida—we've just moved them to New York accidentally. Now we need to fix that. So we're going to keep track of how all that happened, and then we're going to undo it. It sits in Ram because Ram is the quickest place. The Java Pool in Java is not something that you'll have to deal with a whole lot as a DBA. That's more of a developer concept. But Java uses a lot of large objects. Java has the ability to read big chunks of data and store that data within data types and variables within Java. Java will use a big chunk of that RAMSO that it can hold those types of variables. If you weren't using Java with your database, by the way, Oracle uses Java all the time. But if you want, then you could shrink that part of the database. But this part of the shared RAM is someplace where large Java objects can be stored. The large pool stores large objects. Large objects like, say, binary data or photographs are much, much larger than, say, the results of most simple SQL queries. The result cache also stores your most recent SQL query results. Why store the most recent SQL query results, though? Because, according to the theory, if you've done a search, the next search will most likely be the same as the most recent search. In other words, if some user, maybe an Internet user, says, "Give me a list of all the best sellers," there's a better chance than anything that the next user will run the same query. I mean, what's the next query going to be? It could be some wild, random query. But if the most exciting Anne Rice book just came out, chances are you're the next user. And I know the chances aren't great, but Oracle has got to do something. So if somebody searches for Anne Rice, all right, there's a slightly greater chance that the next person is going to search for Anne Rice. And if a bunch of people are searching for Anne Rice, then why not store that in RAM and keep it there for as long as it's needed? the most recent searches for Anne Rice. Chances are, the next search that comes along will be for Anne Rice. You may think it's a foolish assumption, and maybe you have a better one, but this is the one that Oracle went with when they were designing these pieces of software. the database buffer cache. We'll talk about that later in this course.

4. Memory Structure Part2B

Okay. When data is retrieved from the database, it's returned in what are called buffers. Buffers are blocks of data. There's a reason why these are in blocks. These are in big chunks. And the reason for that is because hard drives store data in blocks. You can think of them as rectangles into whichwe put data consecutively stored along the hard drive. And chances are, we want to set up blocks within the Oracle database. We'll talk about blocks a little later. We want to set our blocks to be the same size as the hard drive blocks. Data is retrieved in blocks. And you might be thinking, "Well, what if I just want a little data that's just a little piece of the block?" Doesn't matter. Data is retrieved in blocks. That's just how Oracle works. It receives the data in blocks. What the user sees may have nothing to do with a block. It may just be the title of the latest Anne Rice book. Nevertheless, Oracle gets the whole block. It works in blocks. If you really want to tune Oracle, well, chances are you'll set the size of the Oracle block to be the exact same size as the hard drive block. In other words, that size in bytes or K or whatever will be exactly the same. Matching blocks is a good idea. It's faster and better for Oracle to take a whole block of data off the hard drive than to take a piece of a block off the hard drive. just works better. It may use up more RAM still. It's just much better on the hard drive. So these blocks are going to be lifted off the hard drive and taken into RAM whenever we search for the data. And then the blocks are sorted by buffers. And here are the three kinds of buffers. Dirty buffers. These are blocks of data. Dirty buffers contain data that needs to be changed. Data needs to be changed when it's committed to the hard drive and saved to the database, and Oracle will save when it can. That's all I can say about it. Oracle does. It helps when it gets around to it. There is a process, a thread, if you will, within Oracle that just runs around saving things, and it does it when there's time. If this is a lot of saving going on in the database right now, it gets backed up. There's very little data being saved. Maybe it's 300 in the morning. It sits around and waits for its turn. Hey, anybody need any saving? No? All right, I'll be here when you need me. If it's a heavy point, maybe in theday, say, it's 03:00 in the afternoon. Hey, we need some saving over here. Can you get over here and save us? Look, I'll get to you when I get to you. I'm busy. I'm saving data. So it's in process. A block or a buffer—they're not the same thing. By the way, a block will hold dirty buffers if it needs to be saved. And it's waiting for that process, that database writing process, to come along and save it to the hard drive. Free buffers are empty, so nothing's going on with them. Pinned buffers are the ones that are currently active—the ones that are in the process of being saved. Those are the three kinds of buffers. They're always in one type of state whenever they've got data in them to be saved. Well, I guess in the case of free buffers, they may not. So they're free, which means no problem; everything's good. Nothing needs to be saved. They're not screaming for work. There's just nothing going on with them. The pinned buffers are the ones that are currently being written to the hard drive. Then the dirty buffers are the ones that are waiting to be pinned. Those are the three states of data waiting to be written by a certain process, the database writing process, to the hard drive. The way that Oracle works is that it uses at least a recently used algorithm. Last recently used So, for instance, if Anne Rice was the last recently used thing, that's what's going to stay in the ance, if AnBecause, like I talked about a few minutes ago, chances are Anne Rice will get used again. It's just human nature. Chances are greater than any if someone has recently ordered a book by Anne Rice. Maybe not high, maybe the odds aren't high. But of all the books in the database, the one most likely to be ordered again is that one by Anne Rice. Because if somebody just ordered it, chances are somebody else is going to order it next. just a theory. just the theory that Oracle came up with. They had to store something in the buffers and keep something in the buffers. So their idea was, whatever was just accessed, let's assume it's going to get accessed again. So let's keep that in Ram. Let's keep that in the buffers; that's the LRU algorithm, the most recently used, will stay in the buffer so it can quickly stay in RAM and be accessed again. One last concept we'll talk about beforewe get out of this section. What is the process called that takes those dirty buffers and writes them to the hard drive? By the way, it isn't actually the hard drive. It could send them to the ram, but let's put that aside for a moment. Let's just pretend it's always to the hard drive. Things are always being saved to the hard drive. Just pretend for a moment. What's the process called that writes this changed data to the hard drive? That's called the database writer. This is written with a dbwitalicized N. The italicised N means there's more than one of them. So DBN something: DBN one, DBN two, DBN three. A database writer is the process that takes dirty data and writes it into the database so that it becomes the current database writer. There are several of them. That's why we say "database writer n." Okay, so those are the structures that occur within the Oracle instance. That's a general, basic overview of the Oracle instance, how it works, and all these different things that are going on within the Oracle system itself. It's good for exam purposes to have a pretty good idea of what all of them do. It's a pretty good idea to not have to memorise them all but to be able to identify them on the exam. And so now you know how all these processes are working in the background. Now you have a general idea of how complicated Oracle is. It's not just saving data, it's storing data in different places. It's saving data at different times. All of this put together makes Oracle probably the quickest and most efficient database system out there.

Module 3: Tables

1. Tables Part1A

The fundamental structure within a database, especially within a relational database management system, is the table. In fact, this is one definition, not Oracle's definition.So don't write this down and don't memorise it. But one definition of a relational database management system is that it is a collection of related tables. Okay, that's just a layperson's definition. Like I said, don't memorise it. But essentially, that's what we're doing with databases. And we've seen how we can link databases together. What we do is usually take one numbered column and match it to, in a table, another numbered column. We certainly don't have to match numbered columns. As long as the data in one column is equal to the data in another column, and as long as there's some data that's in a primary key that matches some data in a foreign key, then we can use the system that most relational database management systems use. We don't even have to do that. I mean, we can certainly relate all the last names and the last name table to all the addresses in another table. We can relate things however we want to, but essentially we're going to relate numbers to numbers. That's the easiest thing to do. The basis of a database system is a collection of tables. Excel is a spreadsheet. They don't relate. But in Excel or worksheets, I guess,is what they're called in Excel. But that's what Excel is. Excel has one or more worksheets. So you could have a database with just one table in it, but then that would sort of just be a spreadsheet. So in a sense, a database is a collection of tables in a sense.Although we know that's not a good definition for Oracle because it's a whole lot more than that, That's the basic idea. That's what a database is. It has related tables. A table is the fundamental structure of any database system. Let's spend a little while and talk about tables. How we build them, how we kill them, how we change them, how we want to use them Let's talk about all of that. So first of all, we know that tables are collections of rows and columns. The columns are the type of data that we need, and the rows contain all the data that we're going to insert into the database in Oracle. The columns have data types, so they're all going to be theoretically of the same data type. So the first column may be a number data type because, for instance, maybe it's our primary key column. A string, which is a collection of letters, is a possible second data type. A string data type exists in most modern programming languages, so that may be a string of, say, the first name. By the way, we don't use the term "string" for that in Oracle. String is not a data type that we'll use in Oracle, but it's very similar to another type of data. The following column could be last name and some string data type. Okay, so our columns will be data typed.That's how it's done at Oracle. That's how they're done, as far as I know, in every relational database management system. What data types can we give our columns? And when we create a table, we're going to find out that we must give our columns data types. So there are lots of data types available within the Oracle database system. I would suggest that you go to the online documentation for Oracle and search under the term "datatypes" or otherwise find it online somewhere on the web, on the Oracle website, and just look at all the different data types that are available. And there are, and I don't know exactly how many of them there are, but there are a couple of dozen of them, and many of them are very alike. They just have slightly different characteristics. Maybe one type holds more bytes of data in it than another type—something like that. So go through and get familiarised with the data types. Data types are important. The right kind of data must fit within the right data type. although Oracle lets you fudge a lot on that. Oracle will accept the wrong datatype into its columns in many instances. In fact, more often than not, it doesn't. But don't fudge. We want to do things right. We want to put the right types of data into the right data type columns. So it's important for you to know all the data types within Oracle. One, for purposes of the test, obviously, but two, just to make sure that you put the right data types into the right columns. If you don't, if you put a number into, say, a stringtype column and you try and do math on it, you may get a crash or your function may take longer because Oracle is going to try and figure out what you've done. Remember, faster is smaller, and smaller is faster. We want our database to be fast—as fast as possible. So put data types in the right places. What are some of our most common data types—the ones that you probably ought to memorise other than just looking at them on the web page and getting a good, strong familiarity? Let's talk about some of the ones that you're probably going to memorise first. The varchar two The varchar varchar, by the way, stands for variable characters. In other words, it can have different numbers of characters stored within it. the bar chart, too. This is what we might call the string variable type if you're coming to us from another programming language. Bar chart 2 holds different numbers of strings. When you define it, you may define the largest number of characters it may have. In other words, if you want this column to hold up to 38 letters, you would define it as VAR. Chart two open perenn 38 close par Another type is the number type of variable; that's kind of self-explanatory: the float variable. It floats because it has a floating decimal point. That's why they call it a float. So it allows storage of non-integer, non-hole numbers. The long is used for handling long character types. I forget exactly how long it's a numeric data type, by the way, and how long it can be, but it will hold an astronomically large number within it. The date character type stores dates. By default, it stores dates in the Oracle Internal format, which counts the number of seconds. You probably will not need that kind of precision, so you'll probably want to convert the date to a different type or format the date in a certain way. And then we have Char, which is sort of like a baby version of VarChar varchar two.So those are just, what, six different data types that are available within the Oracle database system. They're also the six most likely used data types. I think that you'll find Take a look in the documentation, find out what those are, how large the data can be, whether they store negative numbers or decimal numbers, and just get generally familiar with those. And while you're at it, look at all the other data types. There are sometimes only subtle differences between data types, but take a look at them all and get a good idea so that in case you meet with any of them on the exam, you'll be ready for them. Columns in the Oracle database can also store large objects. We just talked about the long A closely related data type is the longRaw, which would be like a binary. There's also the Lob data type. Lob stands for large object. The large object, as you can see here, stores up to four gigabytes of data. The difference between the long, which stores up to two gigabytes of data, is that the long is stored inline, and the lob is stored out of line. What I mean by that is that the log is stored inside the database itself. It's actually put into the database tables in the LOB. And there are many kinds of lobs: binary, lobster, the lob, and some other types that are out there. Lobs can be huge. Four gigs. Lobs are not stored within the database. Only a pointer to somewhere outside the database on the hard drive is stored. So they don't grow the database, they're big. So the database just says, "This is where they are outside the database on the hard drive, someplace they're stored." And you'll never know the difference because, as a user, you can't tell when you select from the database whether the data is stored in lines or as an outline of lines. But to save space within the database itself, lobs are stored somewhere else on the hard drive outside of the Oracle database system. Yeah,

Pay a fraction of the cost to study with Exam-Labs 1z0-062: Oracle Database 12c: Installation and Administration certification video training course. Passing the certification exams have never been easier. With the complete self-paced exam prep solution including 1z0-062: Oracle Database 12c: Installation and Administration certification video training course, practice test questions and answers, exam practice test questions and study guide, you have nothing to worry about for your next certification exam.

Hide