Hambone Blues Jam

Home Decoration Tips
Oracle Architecture in less than 10 minutes

Oracle Architecture in less than 10 minutes


Hello, My name is Stephane Faroult, I am a database
performance consultant. The first Oracle database I ever installed
was a beta version of Oracle 5, an awful number of years ago. I have published two books with O’Reilly,
“The Art of SQL” and more recently “Refactoring SQL Applications”. For me, there is a clear link between performances
and the understanding of what happens inside the database. I’ve had the opportunity to witness that
for many developers the “persistence layer” is kind of cloudy. I’d like to take you behind the scene, and
explain without getting too much into details how everything
works. I’ll start by explaining what happens when
you start the database, something which is most often
automatically performed by scripts when a machine boots. I’ll talk more precisely
about the Unix implementation of Oracle, but it’s not massively
different under Windows. An administrator who wants to manually start
a database (called a server by some other products) must
have at least two important variables set in his environment or in the registry : a
variable that says where the distribution of Oracle has been
installed, called ORACLE_HOME, and a variable that identifies
the database, called ORACLE_SID. You can have on the same machine several different
databases running, that may be running under different
versions of Oracle. The administator will run the sqlplus program,
and “connect” – actually the connection is dummy at this
stage, it’s just a check that the program is invoked from a Unix or Windows account
with the right privileges. You can then run the magical startup
command. The program starts by reading a file called
spfile.ora that contains the parameters for
the database identified by ORACLE_SID. It’s a binary file, and you may hear about the
init.ora file that is a text file that historically predates
the binary file. With a binary file, parameters are
modified using sqlplus, and many of them can by dynamically changed when the database is
up and running. Database parameters will give the size of
a shared memory area that is allocated at this stage and which
is called the System Global Area, or SGA. This is where Oracle works. A number of utility
processes will also be started. The number increases with
each version of Oracle, some of them are mandatory, others
are optional. Under Windows, they arent’ processes but threads. All the processes are running the same oracle
program and have the SGA in their address-space. It sometimes look like they are consuming
a lot of memory, but actually most of this memory is shared. When memory is allocated and processes started,
SQL*Plus displays the total SGA size as well as the
size of four subparts, the fixed part, the variable part, the database buffers and the
redo buffers. At this point, Oracle is going to open and
read a file that is so critical that it’s always duplicated :
the control file, found at a location that is specified in the parameter file. This is
where Oracle finds the names of all the data files, temporary
files and log files that together make up the database. The control file will also allow to check
global consistency, whether something needs to be restored and
whether the database was properly shutdown or if some unfinished transactions will have
to be rolled back. Without a control file, your database is dead. After passing this stage, SQL*Plus will display
that the database is mounted, which mostly means that
all files are identified. The only step that remains is the opening of the various files
for read/write operations. When files are opened, then the database is
open and users who aren’t database administrators can connect.
That is, they can connect if they are logged onto the machine that hosts the database. If you want to enable users or an application
server to connect from another machine, which is the most
common case, you have to launch with the lsnrctl start command another program called tnslsnr,
which is the “listener” that waits for incoming
connections. After displaying a lot of intimate details about its
configuration, found in the listener.ora file, the program runs in the background and everything
is in place. Let’s now contemplate a client program that
runs on another machine. If it wants to access the database,
it needs to provide, first of all, three pieces of information: the name of the host on which
Oracle runs, the port on which tnslsnr is listening, and
the service, that is in practice the identifier of the
database you want to connect to. There are several ways to provide these pieces of information.
You can directly provide everything in the function that performs the connection request;
this is for instance what you do with JDBC and a pure
java driver. If your client is using the Oracle client libraries,
you can also provide an alias, and your client will transparently fetch the associated information
from a file called tnsnames.ora. These two methods are by far the most common
ones, but there are other possibilities, such as using
an LDAP directory or system utilities such as the Network Information Services as a substitute
to the tnsnames.ora file. Obviously Oracle will not give you uncontrolled
access to the data. You must sign-on, so that you are
identified and the database clearly knows what you are allowed to do. Usually, you will
provide a username and a password. Authentification
through an LDAP directory is also sometimes possible. In practice, your client issues a connection
request that is handled by the listener. The listener
will either spawn a sub-process that will run the oracle code and become the client’s
dedicated server, or redirect the connection to an already existing
shared server, which is a much less common configuration. The server process is, so to speak, the client’s
proxy on the Oracle side. It can access the SGA,
that is shared between all processes. It will also allocate a private memory area called
PGA, where it stores what is private to the process
and to the session. But let’s take a closer look to the
various parts in the SGA. There isn’t much to say about the fixed system area. But the
variable area contains the shared pool, which holds compiled
versions of queries, as well as various buffer pools
that will contain for instance data dictionary information. The data buffer contains data from the tables
but also index structures and various work areas.
Everything is split in pages or blocs; the block is the Oracle basic storage unit, and
a typical size for a block is 8K. Whenever you query or change
the content of a table, you operate in memory on blocks
that are in this cache. Finally, a fourth area is used to store information
before it is written to log files to ensure transactional consistency even in the occurrence
of a crash. The parts that really matter to understand
how Oracle works are the shared pool, the data buffer and
the log buffer. This is where the dedicated server process will operate, as well as the
main utility processes started with the database.
Let’s see what happens when the client issues an SQL query. The query arrives as text to
the server process that will start by computing a hash
value that is used as a checksum. Then the server will
check whether this query has already been recently executed, and will inspect the shared
pool. If the query is found, the analysis stops
here, this phase is called soft-parsing and the server process
can execute the query. If a prior query with exactly the same text
cannot be found, then we have hard-parsing that is CPU
intensive and locks some shared resources. SQL syntax must be analyzed, the objects that
are referred to in the query must be identified, some synonyms
may have to be translated, and the best execution plan must be determined among sometimes a
very large number of possible execution plans. It may cause recursive queries on the data
dictionary and input/output operations. This is why you
shouldn’t concatenate to the text of a query identifiers that change with every call, but
you should pass them as parameters, as if the query were
a function. Once the query is analyzed and compiled, it
is going to be loaded into the shared pool and executed.
It will then reference pieces of data that may or may not be in the data buffer. If data
blocks aren’t in memory, the server process fetches them
from the data files and loads them. Blocks that have been
modified are written asynchronously to the datafile by one or several database writer
processes. When you update data, mechanisms for analyzing
the statement and uploading data blocks into memory
are the same ones. But before modifying data, initial values are copied to a work area
called “undo segment”. Another process that wants to read data being
modified isn’t blocked, but reads data from the undo
segment instead of reading from the table block. Initital and new value are also copied
to the redo buffer; if the redo buffer fills up, the utility process
LGWR, or log writer, dumps its content to a log file. When you commit the transaction, Oracle records
that the initial value is no longer required for
rolling back the change, it records that the change is validated and the LGWR process writes
the transaction to the log file. This time the
operation is synchronous, and the server process waits for
the go ahead before the call returns to the client. That means that if you commit every single
update in a loop, you will waste a lot of time just waiting
for acknowledgment. This is a very quick introduction, but I hope
it will allow you to understand better what happens. Thank you for your attention.

80 comments found

  1. @mxsunayv

    This is why the same video is ALSO available in French, where my accent is flawless. I suggest you watch the French one.

  2. @mxsunayv I appreciate the free classes in this complex topic. I understand very well. Listen to the logic. It will be easy to follow.

  3. @sallampa

    > strings control.dbf | grep temp
    /usr/lib/oracle/xe/oradata/XE/temp.dbf
    /usr/lib/oracle/xe/oradata/XE/temp.dbf

    It's there.

  4. @mxsunayv , dude why u say bad accent. U dumb ass do not forget French is his first langauge. English is my 4th langaugage, I do not have problem understanding him and you are complaing. Sometime Crappy American are really dumb. They do not like to do hard work. They do not want to take extra effort to unstand something. All I have to say get out of ur way of thinking pay attention to others & learn the hard way. We have to learn English while English is not our first lanague. So do some work.

  5. @seekknowledge2010 "Crappy American" should be replaced with "crappy people" Not all Americans think this way nor dislike to do hard work. Nor do you know if he is actually an American because there are other countries that speak English, and the Americas speak Spanish and Portuguese as well as English…. Your generalization is discriminatory.

  6. Greate tutorial! Thanks. I do not find the accent so bad as some say. I understood every single word and hence it is even better and more understandable than native english speak.

  7. good tutorial, but you have to do more pauses and be more expressive, not simply read from your notes, otherwise is very difficult to follow you.
    anyway thank you for your effort.

  8. Subtitles are a Youtube feature. You upload either a plain text file or a text file that includes timing indications.

  9. I just realized that there is a transcript of the video, so i don't have to turn on the speakers very loud to understand what's being said !
    very useful !
    Thanks !

  10. So far I have been only posting on Youtube but I am right now considering a few more "work friendly" options (I guess that the question is motivated by Youtube being blocked at work?)

  11. I was hoping you have blogs as well, since your explanations of core concepts are easy to understand and concise while other video's and blogs are not user friendly.
    I actually want to create a blog of how to do a small piece of work in mssql, and then how to do the exact same thing in oracle on the same page, for myself as a learning tool and reference guide.

  12. I'm not too comfortable with blogs; I have either too little or too much to say. Add to this that 24-hour days are already too short for what I'm doing …

  13. Video and contents are awesome, but the accent is not easy to digest "VERY VARD HARD ACCENT' he would have used other auto speech type of options.

  14. This is exactly what I have been looking for since I struggling with oracle architecture.
    Thanks a bunch.
    Is it possible to have access to more in depth videos on oracle architecture even if ti cost some money?

  15. Hello dear, I appreciate a lot this video, but it's from 2009, still update this structure nowadays? I mean, is it the same?

  16. The video was great up until the last two minutes. It's great to explain advanced technical terms at a basic level for people to understand if the people actually understood what the guy is saying. And then let's have CAPTIONS AVAILABLE IF I FEEL LIKE IT at the top just to tease everyone. "Full transcript available for a small fee" Like sorry you're not french and I half assed my english pronunciations, if you want to understand me give me money. What a fucking joke.

  17. If two queries are functionally the same (returning same resultset) but not litterally the same, they get parsed as different queries? (for example: differences in whitespace between tokens, and differences in case of keywords in queries, or alternate syntax forms which are functionally the same).

  18. Excelente video, ficou claro sobre a arquitetura do banco Oracle.
    ———————
    Excellent video, it was clear about the architecture of the Oracle database.

  19. Great video. Your accent is great. I used the automatic youtube captions and they worked perfectly as well. Thank you for the information.

  20. Extremely useful for beginners. Great explanation. Now I can do better on my exams today.
    I just don't understand why would anyone dislike this video?

Leave comment

Your email address will not be published. Required fields are marked with *.