| |
 |
|
Oracle Tips by Burleson |
Oracle 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
Col. John Garmany
http://www.rampant-books.com/book_2005_1_easy_sql.htm |
