[0:07]In this video, I'm going to tell you the primary components that are involved in an Oracle database and instance. We referred earlier that the database is a bunch of files which are present in the storage and the instance is made up of two components. One is called as the SGA and a bunch of processes. SGA stands for System Global Area. Sometimes it is also called as a shared global area because this place is shared by all users who connect to the database. So this is my SGA, a set of processes, and the database. So let me now list the important components of all these. Let's start with the database. So, database as I told you is a bunch of files, and we have to begin with data files. This is the place where all the data resides. It could be system metadata or user data that is put in. Then we have what is called as control files. Control file basically has information about what is the name of the database. What are the other files that are part of it? Meaning, where are the data files, and the other file, which I'm going to talk about redo log files, that is the information that is kept inside control files at a high level. Then we have redo log files. As the name suggests, it's like a log file.
[2:27]As and when you make changes to your data, it is logged and kept in the redo log. We will look at the structure of these files as we proceed. As of now, just remember there are three primary files that make up a database: data files, control files, and redo log files. Apart from this, we also have what is called as a parameter and a password file, which are used to start it up. So when we say there is a parameter file, this parameter file is used to actually create the instance. When we start a database, the instance is created, and the components in the instances are created based on the parameter configuration that you have. In other words, this is otherwise called as a P file or an SP file. I will tell you the details about these two at a later point in time. Right now, just know that there is something called as a parameter file, which could either be a P file or an SP file. P file is parameter file, SP file is server parameter file. We'll come into the details as we go ahead. Coming to now the instance, first we will look at the SGA. The primary components of the SGA are we have what is called as a shared pool, we have something called as the buffer cache. Then we have what is called as a redo log buffer. Then optionally, you could also have large pool, Java pool, and there are other components like stream pool and others. We will not go into that.
[4:31]Primarily, five major memory allocations: shared pool, buffer cache, redo log buffer, large pool, Java pool. So what is the purpose of these? Shared pool can be construed as the place where all the executables are available, or if I have to say, all the library of your PL/SQL code that you might write and the SQL that you execute are kept here. Plus, it also has a cache of the data dictionary. So these two can be set as library cache and data dictionary cache. That's what makes up a shared pool. The buffer cache contains whatever data you query upon. Any SQL that you run on your database will need to bring the data up. So from your data files, the data is brought into the buffer cache, where the processing happens. Redo log buffer, whenever you make a change to your data, entries are made in the redo log buffer. Large pool and Java pool are components, which are used for certain specific things. We will not get into the details now, I will talk about them later. So the primary components are shared pool. Shared pool is made up of library cache and dictionary cache. Library cache contains all your PL/SQL code and your SQL executables. Data dictionary cache has the system metadata used to process queries.
[6:18]Buffer cache contains data from your data files. Redo log buffer as and when you make changes in your data, log entries are made here. Now let's get into the next piece, which is about processes. We have what is called as Smon, Pmon, DB writer, Checkpoint, and Log writer. Five very important processes. There are many more processes, which we will get introduced to as we go into those specific functionality. But at a high level, these are the five important processes. To make it very simple, whenever you make changes to the data, remember the memory is only temporary. If you shut down, it is going to go away. So the DB writer is going to write down what is called as a dirty buffer. Now what is the meaning of a dirty buffer? Any block that is read into the buffer cache and changed, is a dirty buffer. And it is the responsibility of DB writer to write it back to the data files. Then log writer as and when you make changes in your buffer cache, log entries are made. Log writer's responsibility is to write it into the redo log file.
[7:41]Then we have Smon and Pmon. Smon is system monitor, which is generally coming in of help in case of a recovery. Now we will talk about what is recovery later on. Pmon is process monitor, which basically monitors all the processes, whether they are working fine, and it will take care if in case of any process failure, if the instance is going to crash, it's going to keep track of that. The last process here is checkpoint. Checkpoint ensures synchronization of your redo log and data files. In other words, writing the data buffer cache and redo log at the same time, so that all changes that are there in the memory are synchronized into the files.
[8:42]Just to summarize what we have discussed here, or what I have talked about here, database has three types of files: data files, control files, and redo log files. Data files contains all the data, control file contains the structure of the database in terms of where the files are located. Redo log files contains a log of all changes that you have made in your database.
[9:16]Shared pool is one of the primary components of your instance in your SGA, which is made up of library cache, which contains your executables, PL/SQL or SQL. Dictionary cache, which has the metadata used to process queries. Buffer cache contains data from your data files, which you want to process as a user when you write a SQL, it needs to process data, which is brought in here. Redo log buffer as and when you make changes to your data, log is made here, and we saw how the DB writer is responsible to write back to the data files, log writer is responsible to write back to the redo log files. Now in the next video, I will go through an example of a SQL statement, how it gets connected, and how it processes your request.
