Why Oracle Works the Way it Does (6) - The SGA

Please see my note at the bottom of this post about the new IntelliTXT feature ITtoolbox is testing (those green links with double underlines).---------------------------------------------------------

The System Global Area (SGA) is powerful set of memory components that help you make Oracle very performant and scalable. ----------------------------------------------------------I say that it helps you because it won't necessarily keep you from shooting yourself in the foot if you really want to. The most important component of any database is the DESIGN. If you design your database (logical and physical models) poorly, no amount of database configuration will make up for it.

If you've ever uttered the phrase "performance is a hardware issue", you're probably not going to get a lot out of this series. Performance is always a design issue and hardware is only one piece of the puzzle.----------------------------------------------------------

The most obvious and immediate benefit the SGA provides is keeping you most active data blocks (rows) in memory. Even if that's all it did, it would probably be worth it. If you don't believe me, do this:

- run some commonly used queries (SELECTs) against your database - shut down your database and start it back up - run those same queries and see how much slower they are

That is because Oracle memory structures kinda work the way other parts of Oracle do (see how consistent and elegant Oracle is starting to look?).

You'll (hopefully) remember that when we create a datafile, the space is allocated and the OS will not try to use that space for another file (even another Oracle datatfile), but the datafile has nothing in it. The OS says "yep, that's a 100 meg file", but Oracle says "I've got 100 megs of free space to put things in."

The SGA works kinda the same way. When the Oracle "instance" is started, some memory is allocated from the OS for the exclusive use of Oracle. System memory is reduced, but Oracle says "I've got a bunch of RAM to put things in."

Oracle immediately starts putting some important things in memory, but it does not put rows of your data into memory.* It doesn't know which rows you want in memory until you start asking for them.

As you start asking for rows to read or manipulate, Oracle puts them into memory block-by-block. Remember from an earlier post that the smallest unit Oracle really messes with is a data block. So even if you ask for a single row from a data block, Oracle will load that entire block into memory. This is a good thing, but you might think it's wasteful. On its own, it is not; however, you may see the effect picking a block size bigger or smaller than you need can have on memory usage (you're smart).

So, you've just restarted your database and issued your first SELECT * FROM SOMETABLE WHERE SOMETHING=SOMETHING.

After parsing your query, Oracle first checks to see if the blocks it needs to check are in memory.-----------------------------------------------------Make sure you understand that point. Oracle doesn't magically know where your row is, it uses your FROM clause to find the general area of where the row is (the table(s)) and the WHERE clause to limit those rows to the one(s) you're seeking.

If you did not have an index and wanted one row out of a possible 10,000 rows from a table, Oracle has to read all of the datablocks into memory to find the one row you want.

In that situation, your query wouldn't result in one row sitting in memory, but 10,000. It would simply give you back the single row you were looking for.

More on this when we discuss the LRU and SQL.------------------------------------------------------

Database Buffer Cache

The specific place in memory it searches is called the database buffer cache(dbc). Since we just started our database, Oracle won't find any of our rows in the dbc. So Oracle goes to disk to start sucking up blocks (rows) and pumping them into the dbc.

Now, when the next user comes along with the exact same query we can avoid a couple of things:

- generating a new execution plan (more on this in a bit) - reading these data blocks from disk

You know, now, why you don't have to reread those blocks from disk--they're in the dbc.

The execution plan, on the other hand, is not a block-based object. It is an instruction set that tells Oracle the best way Oracle thinks the data can be retrieved.--------------------------------------I'm intentionally over using "Oracle" as a uber process because I don't want to get into those details yet. We'll discuss all these individual processes soon enough. It can just a little confusing to keep track of right now.---------------------------------------

SHARED SQL AREA

Since the execution plan doesn't really quite match the structure of a data block, we need some place else to put it in the SGA. That area, convienently enough, is called the shared SQL area.

The shared SQL area stores things like the SQL statement and its execution plan. Hopefully other people will make the same request and we'll save a little time and a little memory by reusing the work we've already done.

User Global Area

Whether anyone else makes the same request or not, there's some information that will never be useful to anyone but us. This includes "bind information and runtime memory structures." Oracle needs a place in memory for this information and it uses a structure called the User Global Area (UGA) (there's that word "global" again, but you can see again that Oracle is persitently consistent).

So, if the UGA holds session (or "user") specific information, where does it reside? Remember in the last post where I talked about connecting to Oracle via shared servers or dedicated servers?

If you use shared servers (formerly MTS) to connect to Oracle, the UGA is stored in the SGA. That's because when you connect via shared servers, you are not locked into a specific (or "dedicated") shared server. One of your requests could be handled by one shared server and another of your requests could be handled by a different shared server. That's the whole point of using shared servers. BUT, your individual session still MUST have some private space that holds information only relevant to your specific session. By putting the UGA in the SGA, any shared server has access to it and can find your private information.

If you use dedicated servers (one server per user), then the UGA is stored in the PGA and is the biggest chunk of the PGA.

Ok, I will come back to the SGA because there is a lot more to go over, but let me quickly address the PGA before we do.

--------------------------------------------------------OK, ITtoolbox is testing this new context-based ad placement called IntelliTXT. Just mouse over one of the highlighted words and you'll get a little pop-up message that you can click on to open a new window at the advertisers site.

What do you think?

Please leave feedback, because I know they are interested in hearing from you the readers more than from us the authors. I'm not going to stop writing just because I have green words popping up in my posts. I'd kinda like to see the same technology for the links I do embed so you can get a preview of the link before going to it, but I doubt that will be available any time soon.

I don't really like it or dislike it, but it's certainly better than that Microsoft ad that loads over the whole page anytime you accidentally mouse near it (I wonder if mentioning Mycrowsoft in that sentence will pull up that ad!)

So, please give me some feedback on the content of my post and also leave a comment about the "mouse-up" ads. I'd be interested to hear what you think as much as ITtoolbox would. Specifically mention if it would make you less likely to read blogs here or if you find it better than pop-ups.

I hated them at first (I fear change), but I'm already starting to tune them out. I'm going to check some of the other blogs here and see if I find ads that make me want to click them.

Related White Papers

18 Comments

Nice post, would like to have this in a book where I think you could still keep it short and simple, but expand it a bit beyond the constraints of a blog post. Certainly makes it easier to understand than the books that just reproduce the Oracle manuals verbatim.
The ad links? Eh, whatever. I might mouse over one if I see a word that interests me highlighted. Wonder what links words like PORN comes up with and if it does those links in comments.
I jumped on Google and found this that gives an explanation of this "service". I like the name of the site, too.

I am being pedantic I know but "Oracle is persitently consistent" ??? Maybe persistently?
I didnt see the green words on your blog but I have seen them on another tree swingers blog.
You posts are very good and informative and I was starting to get withdrawal symptoms when there was no action for a couple of days.

Having been a DBA on RAC for 3+ years, I really enjoy the=
simplicity with which you explain things=2E Oracle manuals -=
while detailed - are not for beginners=2E They assume you already=
know a lot and want details=2E Oracle doesn't usually put out=
good books for people just learning=2E
Oh, and I have ScriptBlocker (FireFox extension) running=2E The=
intellitxt appears to be a javascript app that gets blocked by=
default by the extension=2E I bet it would also work on those=
nasty fill-your-screen ads that also appear on Yahoo!=
sometimes=2E

Oracle, oracle, oracle (just seeing if the intelliTXT works in comments).
Thanks for the feedback, let me ask another question.
I have enough material to put all of these posts (fleshed out and with a few visuals (sorry, Murali, I don't know if I can add graphics to these posts) into a book, but I would want to keep it as a small book.
I fully understand the authority that a 1,000 page book conveys, but I would prefer to keep this small and handy. And since I'm not including scripts, I think a book version would come in around 200 pages or a little more.
The whole point is trying to keep this stuff as simple as it is and empower readers to actually understand the Oracle documentation by understanding what the architecture really is.
Of course, I could keep expanding the concepts until it was a 1,000 page book. But would anyone be interested in a fairly easy to read book that didn't weigh 20 lbs.?
hmmm, if I had 24 chapters, I could finish my 24 series and lead each chapter with that to add pages (just kidding).

Hi Dratz,
Keep it going, if I can easily understand your blogs then anyone can :)
I like the simplicity and casual style.
The green words are fine. I don't mind them as long as they don't disturb my reading and I don't run my mouse on every word that I read.
Cheers,
Ganesh

The intellitext is now working for me=2E Why it didnt before I dont=
know=2E I dont care about the size of the book or the number of=
pages=2E As long as it is easy to read, easy to understand (more=
important I think) then however many pages are needed to=
correctly convey your concepts are the correct number of pages=2E=
If it is good enough and well enough written (which I believe=
you could do) then the book will sell itself=2E
My 2 YTK (Yeni Turk Kurus :)

The post are great. I am not a DBA but I am a sys admin for our servers that are running Oracle so I think that understanding Oracle will help me in my job. I think the simplicity is the best part.
As far as the little green mouse over things, I don't really care one way or the other.

Of course, I could keep expanding the concepts until it was a 1,000 page book. But would anyone be interested in a fairly easy to read book that didn't weigh 20 lbs.?
Yes ! definitely ! it would be very useful in some of my courses.
Alain

Even though I have been a DBA for 13 years and read the concepts manual several times, this is a very refreshing read. Easy to grasp and nicely laid out in chunks. Really like the stuff about structures on disk. Well done.

i wanted to thank you for this extrordinary series. It makes complicated things simple and really makes sense. I am s student in my last semester on Comp. Sc. Tech and this tutorial really helped me to get a better understanding of Oracle's internals.Thank you, great work

Dratz just keep on posting.......its really difficult to find concepts xplained amazingly simple........
Well an warning on a client system >>>
Tablespace full xtend tablespace by some GB
and den i used to add or resize the datafile...
Nobody was able to xplain the relatn betn datafile and tablespace....Thanks to u i got the reason>>>>>>
AS there r no new data blocks to allocate and dats why tablespace is full

May 29, 2009

Very Nice Post... Revise whole Process. Very Nice :)

Jul 13, 2009

Hi Dratz, have you published any books related to oracle working, I have become your disciple :) after reading ur blogs......its fantastic and you made me to understand the concepts very easily......

Disclaimer: Blog contents express the viewpoints of their independent authors and
are not reviewed for correctness or accuracy by
Toolbox for IT. Any opinions, comments, solutions or other commentary
expressed by blog authors are not endorsed or recommended by
Toolbox for IT
or any vendor. If you feel a blog entry is inappropriate,
click here to notify
Toolbox for IT.