The Buffer Cache

The Buffer Cache is a memory area in which the data physically stored in the database is read and modified. When you want to see the employee records in the EMP table, they are first read from disk and loaded into the Buffer Cache. Why not simply fetch the rows off disk and straight back to you and your client PC? Someone else might want to read the EMP records just after you. If we loaded the rows into Buffer Cache first time round, the second person won't have to read the rows from disk at all: they'll be able to access the copy of them direct from the Buffer Cache. Reading rows of data off disk involves performing a physical read. Reading rows out of the Buffer Cache copy of them involves performing a logical read. Logical reads don't require physical disk access and so are much quicker to perform than physical reads. The job of the Buffer Cache can be said to be, therefore, to minimise physical reads and to help maximise logical ones.

It's not just reading data, though. If you want to update a record, the modification is made to the version of the data stored in the Buffer Cache. Only later is this modified buffer (known as a dirty buffer) then saved back down to disk. When we write a modified buffer back down to disk, the contents of the in-memory buffer become the same as the on-disk data, and we therefore say that the buffer is now a clean buffer.

Oracle never reads individual rows, though. If you want to update Jim's phone number in the EMP table, we never just get Jim's record and let you at it. The minimum unit of reading and writing is an entire "collection" of rows -usually 2K, 4K, 8K or 16K in size. The idea is that if I am modifying Jim's phone number, you will probably want to update Bob's phone number similarly in just a moment or two... so my request to update Jim's record helps you out, because it causes the entire collection of rows around Jim's row to be loaded into memory at the same time. Your subsequent update to a different row hopefully therefore only involves logical I/O, not physical I/O -and you therefore benefit from my earlier work.

This 2K, 4K, 8K or 16K collection of data is known as the Oracle Block and is the minimum unit of I/O in an Oracle database. Request to see just one byte in a block, and the entire block is read into the Buffer Cache. The term "block" is actually used to describe the collection on disk; when a block from disk is read into memory, we call it a "buffer" instead. Essentially, though, a block and a buffer are the same thing, except that one physically exists and the other is only an in-memory thing. Some other database products (such as SQL Server) call the same thing a "page" of data. Blocks, buffers, pages... they're all really just different words for the same idea: a minimum 'chunk' of data read from disk into memory, and stored in memory for other users to find without having to re-visit the physical disk.

The Shared Pool

When you issue a query such as select * from emp, the database must work out what that means. Do we have a table called EMP? What are its constituent columns? Where, physically, on disk, does the table live? Are there any indexes built on the table which might help us get the results back more quickly? Do you, the user, have rights to query the table? The process of answering all these sorts of questions is known as parsing a SQL statement, and it involves a lot of work going on 'behind the scenes'.

To work out whether a table exists at all, for example, we have to query an in-built system table called TAB$. To work out where it physically exists on disk, we have to query more in-built system tables including one called TS$ . To check if you have the rights to view the table, yet another query is made of a table called USER$ (amongst others). You issue a simple select statement, in other words, and the poor database has to issue a dozen queries of assorted system tables before it can even work out what your statement means. We call these 'System SQL statements issued in response to a user SQL statement' recursive SQL, and performing recursive SQL means performing lots of preparatory work before actually answering your SQL requests.

Parsing is therefore very expensive... so we'd really like to only have to do it once and have the results of the parse stored for you and other users to make re-use of every time you re-issue the same basic SQL statement in the future. The result of a parse is known as an execution plan, and it represents a package of pre-worked-out instructions as to how to answer a SQL statement. Each new SQL statement causes a new execution plan to be constructed and stored... and they are stored in a sub-section of the Shared Pool known as the Library Cache. If a second user issues exactly the same SQL statement as someone else happened to issue earlier, therefore, the second user can simply nip into the Library Cache and make use of the execution plan previously worked out for the first user.

If you have to go through the entire rigmarole of working out an execution plan from scratch, that's called 'doing a hard parse'. If you can merely re-use execution plans previously created by other users, that's called 'doing a soft parse', and soft parses are a lot cheaper and quicker to carry out than hard ones.

You can fairly say, therefore, that the job of the Library Cache is to help stop us having to do hard parses.

Incidentally, fetching the contents of system tables like TAB$, USER$ and so on is expensive, too: these tables are collectively known as the data dictionary, and the data dictionary is physically stored on disk in the system datafile. Forever having to access these tables off disk would not be a good idea: lots of disk access generally means "slow performance". Therefore, once we've read some rows from these tables, we store them for future access in another component of the Shared Pool called the Data Dictionary Cache. The data dictionary cache is where in-memory copies of the data dictionary tables, needed to carry out parsing operations, reside.

The job of the dictionary cache is therefore to stop us having to visit the system data file on disk every time we parse.

Since both the Library Cache and the Data Dictionary Cache are both sub-components of the Shared Pool, and since both are designed to minimise parsing activity, or at least make it an in-memory affair if it does have to happen, it is reasonable to conclude that the job of the Shared Pool overall is to make parsing SQL statements as cheap an affair as possible.

Oracle Server Components

What is an Oracle Server?

When you edit a document in Word (or whatever word processor you happen to prefer!), you don't type documents directly into a disk file, because doing so would be painfully slow. Instead, documents are initially 'typed into' and created in memory -your computer's RAM- because that works at nanosecond speed and is thus very fast and responsive to work with. Unfortunately, most of today's RAM is volatile: switch off the computer's power supply and you lose the lot -and if you've ever been typing a long, complicated document and had a power cut before you remembered to save it, you'll know the sinking feeling of your brush with volatility!

To protect against data loss like that is why you're supposed to do a periodic 'File -> Save', of course. Hard disks might be slow and painful to use for constant access, but they make a fine permanent store of things which have initially been created and worked on in RAM. Saving a document in Word simply means 'transfer a copy of it out of volatile memory and onto permanent disk storage'. In this way, your word processor lets you combine the speed of RAM with the permanence of hard disk storage. You therefore end up getting the best of both worlds: speed and safety.

In fact, that goes for any program you care to mention, be it a word processor, a video editing package, a media player, whatever: data is read from its permanent store on hard disk and manipulated and worked on in memory, because one is fast but volatile and the other is slow but safe.

The Oracle Relational Database Management System (RDBMS) is just another program in this respect, because it does exactly the same thing. The data it manages is stored permanently on disk but when someone wants to update it, view it or delete it, it is loaded from disk into memory and it is in memory that all the data manipulation takes place. The permanent store of data on disk is what we call the Oracle database. The chunk of RAM where the database's data is actually worked on is called the Oracle instance.

The relationship between an instance and a database is one-to-one: if an instance has accessed the data from database X, it cannot also access or work on the data from database Y. If you want to work with the data from database Y, you will need to use another instance entirely. This relationship -one instance manages only one database- is always and completely true.

The relationship the other way around is not always like that, though. In an advanced configuration of Oracle known as a Real Application Cluster (or RAC for short), it is possible for one database to be accessed by two or more instances, simultaneously. That's usually done to speed things up and get more work done. But even so, each of the participating instances in a RAC can only be used to store and work on data from one database. So the rule can be more fully stated as:

One instance can only work on one database,
but one database may be opened by many instances

An Oracle Server is nothing more, therefore, than a computer onto which the Oracle software has been installed; on which an Oracle instance is running in memory (providing fast but volatile data management capabilities); and on which an Oracle database has been permanently stored on disk.

Users connect to the instance (not the database, note) and make requests to see or to manipulate data from the database by issuing SQL statements. In response to these SQL statements, the Oracle program loads the requested data into the instance. The user continues to work on the data within the instance. Occasionally, to make sure a power failure doesn't cause the loss of all the work users have been doing, the Oracle software performs, in effect, an automatic 'File -> Save' and writes the data stored in the instance back to the physical database on disk. In this way, users of an Oracle database get all the performance advantages of working with things in fast RAM, but all the permanence and safety advantages of having saved stuff to disk.

The Structure of the Oracle Instance

An Oracle instance is merely an allocation of memory in which to do work plus a bunch of background processes which automate a lot of that work for us. The memory areas are collectively known as the System Global Area (or SGA for short). The constituent 'pools' of memory, which in the aggregate make up an SGA, are called The Shared Pool, The Buffer Cache and The Log Buffer. These three SGA components are compulsory and every SGA on the planet will have all three of them. There are various additional 'pools' which your SGA might or might not have configured, depending on how you are using your database, what software options you've enabled (and paid for!) and so on. You will commonly see, for example, a Java Pool and a Large Pool. You will less commonly see (in 10g and above) a Streams Pool.

No matter what fancy names these pools of memory are given, they are all merely chunks of memory in which different sorts of data are stored. The principle function of all of them is to try and 'cache' that data in memory so that you do not have to fetch it back off disk or work it out from scratch (both of which options are relatively slow and expensive).