Introduction to Oracle Scalability Features

By Steve Karam, the world's youngest Oracle ACE and Oracle certified Master.

There is a great children’s classic by Watty Piper called The Little Engine That Could.  In this story, oodles of toys for all the good boys and girls need to get up a huge hill, but no engine is up to the task.  Finally, a little blue engine comes along and takes up the load, getting up the hill while chanting the hopeful mantra “I think I can, I think I can I think I can.”
Unfortunately, Oracle databases don’t work this way.  You can chant “I think I can” until you’re blue in the face, but the database will continue to burst, your performance will continue to drop, and the anxious pack of managers hovering behind you awaiting a fix will continue to grow.  In other words, the engine will fail before the top of the hill and your data will come to a screeching halt with naught but a puff of steam.  To keep your database running smoothly through any obstacle, you must tune.

This concept is known as scalability.  A scalable database is one that has room to grow, that has been tuned for growth, and one that will not buckle and break when unanticipated growth occurs.  All of these requirements together may sound near impossible, but they can be achieved with proper monitoring techniques.
The real key to scalability is to stay on top of how your database is going to grow.  If you know that many developers will be creating new queries against existing data, you can tune for additional queries and take action to make sure all new queries are properly tuned before going into production.  If you know that new products will be created on a regular schedule, and that these new products will require new tables and indexes, you can manage your storage in preparation for additions and develop scripts to monitor the growth.  Plan, plan, and plan some more.

Planning For Growth

But what makes a good plan?  It is not enough to look at a bunch of tables, and then add indexes to every column you find.  By doing this, you could hurt your database more than help.
In order to plan, there are a few tools that will help us.

·       Statspack or the Automatic Workload Repository (AWR) available in Oracle 10g
·       Monitoring scripts
·       Oracle Advisors
·       Excel or another spreadsheet program, especially one with graphing capabilities

This is one of the most important things you can do.  Saving and charting your important statistics not only helps you plan the future of your database, it also helps you  with one of the chief concerns of the DBA: accountability.
Let’s say you have a 100GB filesystem made for your Oracle datafiles, and that filesystem is at 70%.  Based on trends, you’ve seen that the filesystem grows at a rate of 5GB every two months.

You are now in a position to submit early reports and requests to management for new disks.  Just keeping metrics such as your current size at regular intervals allows you to proactively plan for the future.

Your management may ask you if you can simply clean up some old data; reorganize some indexes, shrink a tablespace or three.  At this point, you should be able to refer to your chart and show where you had done that in the past and carefully benchmarked it, and that on average it only frees up 1-3GB worth of space.

Now for accountability.  If you are not able to get the hardware you requested due to budget constraints or any other hang-ups, in eight months when your filesystem reaches 90% full, you can not only show your previous request for more disk space, but the research that you had and have kept up to date showing why the necessity was so great.  Being a good DBA means being on top of your game, and with a fast growing database, keeping growth trend information is a key component to that.

But what do we do when we have a high amount of data, high amounts of users and their queries, and performance starts to degrade?  Unfortunately, buying new hardware isn’t always the solution, as in the case above where we were running short on space.  In this case, we must tune for future eventualities, furthering our goal of not only being on top of our game, but mastering it.

Scalability Features

There are several features in Oracle that can really optimize your database for high amounts of data and transactions.  These features include:
·       Transaction Scaling
o      Shared Server
o      PGA Initialization Parameters
o      Profiles
o      System Resource Manager
·       Data Scaling
o      Materialized Views
o      Partitioning
o      Parallel Query
o      Segment Advisor
But what can we do with these features?  What exactly are transaction or data scaling, when are they required, and why?
The great thing is, we will know when by carefully planning the future of our database.  With the information we will be storing for future reference, we will also know why it needs to occur.

For instance, we may know from our metrics that users have been steadily increasing the amount of batch jobs they are running, thereby running up the load on the database.  This may tell us that if we don’t impose limits on the CPU consumption of these end users in the next two months, the server will no longer be able to handle the load.
In this single example, we see the what, the where, and the why.  In the future chapters we will flesh out the scripts and methods that can be used to collect extremely pertinent information that can be used to calculate all these things.
Most importantly, we will learn exactly how to use all the features at our disposal in order to make our database hum.

Summary of Scaling Features

Oracle’s many features can be very daunting to some DBAs.  When a DBA is placed in charge of a data warehouse or otherwise large database, this feeling can grow even worse.  For scalability purposes we can narrow down our feature list quite a bit.

Transaction Scaling

Transaction scaling is something a DBA must do in order to accommodate large numbers of users, transactions, and/or queries. 

On a UNIX or Linux system, Oracle generates processes for every client connection made to the database.  These are called “server processes,” and they are your agent for getting things done.  When a SQL*Plus connection is made, for instance, a server process will spawn on the server itself and carry out your queries, inserts, updates, etc.
Every server process requires a certain amount of RAM and CPU in order to start up and run.  When too many processes exist on a server, the server’s loads may rise.  In addition, the server may run out of physical memory and start swapping constantly, producing horrible response times.

In addition, these end users are performing queries, some good and some bad, that could be doing hundreds of “sort operations.”  When a sort occurs, Oracle must define a space either on RAM or on disk where the sort will take place; it is your job as a DBA to ensure that it happens in RAM if at all possible.

Some of these users may be running batch jobs, or large long running transactions that hog CPU, processors, and more.  In order to combat this, we may want to impose limits on the amount of CPU users can attain, as well as the amount of users that perform batch jobs logging in at any given time.

Read more about Oracle scalability features as this article continues:


Need a Health Check?

Oracle is the worlds most complex and robust database and there are hundreds of sub-optimal setting that can cripple your database performance.

BC has a great Oracle health check where we identify all database bottlenecks to ensure that your mission-critical system is running at optimal speeds.

Just call toll free 800-766-1884 to schedule your health check.


Need Oracle Training?

The very best Oracle training comes from Burleson Consulting, where you get an on-site visit by an experienced Oracle expert and author.  Whether it's one-on-one mentoring or getting a customized on-site Oracle training class, there is no substitute for BC Oracle training.  Just call me at 919-783-4133 for details, and check-out our on-site Oracle training catalog: