Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy




Oracle Tips by Burleson 

Inside the Oracle Database

The Oracle database is made up of a large shared chunk of memory and multiple processes that support that memory.  The memory contains the buffer cache, library cache, data dictionary , and some other items.  The buffer cache is where all the work on the data is done.  Before data is read, updated, created or deleted it is moved to the buffer cache.  Also, in this chunk of shared memory is a pool called the shared pool.  The shared pool contains the library cache and the data dictionary cache.  The library cache is where the database stores plans to execute SQL that it has received.  If the same SQL is resubmitted, it can use the plan in the library cache to quickly execute the request.  The data dictionary cache (also in the shared pool) is where the database caches database metadata  (data about the data that makes up the database).  Figure 1.15 shows these memory structures.

There are actually a number of other structures in that shared memory, but these are the ones we are interested in.  All of the caches in the database use some form of “least recently used” caching algorithm.  If the database needs to read data into the buffer cache and it is full, it will remove the data that has not been used the longest and read in the new data.  Likewise, if you submit a SQL request and there is no room in the library cache, you will force the least use SQL to age out of the cache to make room for your statement.

One buffer that we have not yet discussed is the Log Buffer.  This buffer, along with the undo log files, holds information about changes to data.  They are used to provide users with a consistent view.

Consistent View

A consistent view is a very important concept, and how it works is different depending on which database software you are using.  Oracle implements a consistent view that basically says that a user will see only committed data.  It sounds easy, but it is actually one of the features that sets Oracle apart from other database systems.  The key to understanding a consistent view is understanding what and when data changes are committed.

If I was in charge of the payroll and needed to give everyone a 10% raise, I could create a query that will do that.  Once the query was run, everyone’s pay rate would equal (payrate * .10).  If I queried your pay rate, I would see your new pay rate.  This change is not yet permanent.  To make it permanent, I need to issue a commit.  This tells the database to make the change permanent.  If I made a mistake, I could issue the command to rollback and the data would be returned to the point before I gave everyone the raise.  The thing to note is that once I commit, I can no longer rollback. 

Now, where does the consistent view  come in?  Let’s go back to the example.  I give everyone a pay raise but have not committed.  When I query the data, I will see everyone having the new pay rate.  When you query your pay rate, it will be the original pay rate.  Why? Because I have not committed the change, so Oracle will not show the changes to anyone but me.  Once I commit, your query will return the new pay rate.  This is the key point; Oracle will never show uncommitted data to anyone other than the user that made the change.  What happens when I change your pay rate (add 10%) and you change your pay rate (+15%) and neither one of us commits?  We both changed the same piece of data.  I will see the 10% raise while you will see the 15% raise.  If I commit, my change will become permanent, but you will still see the 15% raise.  If someone else queries the data, they will see my 10% raise.  If you then commit, everyone will see the 15% pay raise.  Thus, you will see your own changes but no one else will see them until you commit.  When multiple users are changing the same data, he who commits last wins!  Some database programs will allow you to see others’ uncommitted data.  This is called a dirty read.  Oracle will not allow dirty reads.  How Oracle implements this is complicated, but basically, the database records changes in the buffer cache, log buffer and undo logs .  When I make a change, the change is stored in the buffer cache and the undo is stored in the undo logs.  If I rollback the change, the data in the undo logs are used to return the data to its original state.  If I have not committed my change and you read the data, Oracle sees that the data has changed but is not committed, and it uses the undo data to recreate the original data for you.   Bottom line is that you will never see someone else’s uncommitted data.

The above book excerpt is from:

Easy Oracle SQL

Get Started Fast writing SQL Reports with SQL*Plus

ISBN 0-9727513-7-8

John Garmany

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster

Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   



 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks