V$SGA_TARGET_ADVICE – The Need for Speed

My brother Steve lives near Brands Hatch in Kent. This is quite appropriate really. He’s always been a bit of a thrill-seeker. After his motorbike accident a few years back, he seems to have decided to treat the wheelchair as merely a transfer from two wheels to four.

“I’ve got a new Nissan” he told me the other day. “It’ll probably go round Brands Hatch quite slowly”.

What’s this ? Has the daredevil spirit suddenly disappeared and been replaced by a Nissan Micra ?

Er…No.

Not a Nissan Micra

Steve’s latest mode of transport is a Nissan GT-R. It looks like it’s breaking the speed limit, even when it’s parked on the drive.
Steve assures me that hitting the throttle is not so much a means of increasing speed as a command to engage warp drive.

All of which speed-freakery brings me to the point of this post, namely sizing the SGA_TARGET so that your database will be just that bit faster.

The Problem

The database in question is running with a single block-size (8K). Automated memory management is enabled.
The DB_CACHE_ADVICE parameter is set to ON. This means that Oracle gathers information to enable it to predict the effect of changing the memory configuration of the database.
There are no objects using the RECYCLE or KEEP caches.

We know from monitoring that the application is waiting on buffer cache availability.
We want to increase the buffer cache without impacting negatively on other SGA components that are also being dynamically managed.
These include :

  • Shared Pool
  • Large Pool
  • Java Pool
  • Streams Poo

The server we’re running on is 32-bit. Having accounted for OS requirements and all other processes on the server, we know that we have physical memory available in which to expand the SGA. We also know, as it’s 32-bit, that the total memory avaialble to the database will not be able to exceed 4GB.

We know that dynamic memory management is enabled because the SGA_TARGET is set to a non-zero value ( in this case 584M).

If you want to check what memory is allocated to the SGA components in the database you can use:

SELECT component, current_size/1024/1024 as size_mb, min_size/1024/1024 as min_size_mb
FROM v$sga_dynamic_components
WHERE current_size > 0
ORDER BY component;

The results in our example are something like :

Component Size(MB) Min Size(MB)
DEFAULT buffer cache 452 452
java pool 16 8
large pool 4 4
shared pool 108 88

Obviously, this is a dynamic view and the results are not immutable as memory will be allocated between the various SGA components as and when Oracle thinks necessary. Having taken samples over a period of time and various workloads, the results above are fairly representative of the memory allocation overall.
However the memory is allocated, this query should return the same result –

SELECT SUM(current_size)/1024/1024
FROM v$sga_dynamic_components
/

In our case, 584 MB.

What will happen if I press this button ?

DBAs are by nature a cautious breed, especially when it comes to fiddling around with database settings. “Suck it and see” may be a useful approach in many walks of life, but does tend to accelerate the ageing process when vital production databases are concerned.
Fortunately, Oracle is on-hand to give us a hint as to the effects of various settings of the SGA_TARGET will have on performance. It does this by means of the V$SGA_TARGET_ADVICE view.
What we’re interested in here is, if we change the amount of memory available to the SGA, what will be the performance impact ?
There are three columns here that are particularly relevant, SGA_SIZE ( the size in MB); SGA_SIZE_FACTOR (the size relative to the current size); and ESTD_DB_TIME_FACTOR.
It does take a bit of digging around to establish exactly what DB_TIME is. The 10.2 documentation is a bit vague to say the least. However the 11.2 docs are a bit more specific.
In short, it’s probably easiest to think of the ESTD_DB_TIME_FACTOR as the amount of time required to process an operation which takes 1 second in the current configuration.

Let’s have a look at what it’s telling us at the moment :

SELECT sga_size, sga_size_factor, estd_db_time_factor
FROM v$sga_target_advice
ORDER BY sga_size ASC;

The results are :

SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME_FACTOR
292 0.5 2.2588
438 0.75 1.4914
584 1 1
730 1.25 0.4863
876 1.5 0.4265
1022 1.75 0.4255
1168 2 0.4255

From this, we can see that increasing the SGA size to 730MB will reduce the DB_TIME by over half. Increases above this mark will have a lesser impact to the point where Oracle predicts that there will be no difference in terms of performance, between having an SGA size of 1022MB and 1168 MB.

In summary, Oracle’s advice is to increase to 730MB at least, and 1022MB if you have the memory available to do it.

By contrast, I can guess Steve’s advice…

Just press the Loud pedal!

Given that Oracle is saying that increasing to 1168MB will have the same effect as increasing to 1022MB, and that I have the physical memory available to do the former, I’ll go with the Steve’s approach.

If you’re using an SPFILE in your database all you need to do to make the change is :

ALTER SYSTEM SET sga_target=1168M SCOPE=BOTH;

If you are just using an init.ora, then you need to change the sga_target setting there and re-start the database for the change to take effect.

You’ll notice at this point that I have not changed the SGA_TARGET_MAX_SIZE parameter.
This is because I want to see the effect of an SGA limited to the size we’ve set. If the SGA_TARGET_MAX_SIZE is set to a value lower than SGA_TARGET then Oracle will ignore it.

Another consideration at this point, especially if you’re running on a Windows server – if at all practical, then it’s probably worth re-booting the server itself, just to minimize the chances of it getting confused and leaking memory everywhere !

To check that the change has taken effect …

SELECT SUM(current_size)/1024/1024
FROM v$sga_dynamic_components;

This should now return the new SGA_TARGET size – in our case, 1168.

A little while later…

Fast forward a week and the database has been running the new configuration. The performance improvement has been noticed by end-users (always a good sign) and the batch runs are running that bit faster.
At this point, it’s probably worth checking what Oracle’s current advice is in terms of SGA size and whether it was in fact worth allocating more memory than it was recommending :

SELECT sga_size, sga_size_factor, estd_db_time_factor
FROM v$sga_target_advice
ORDER BY sga_size ASC;

The results are rather illuminating :

SGA Size Size Factor DB_TIME
292 0.25 9.0873
584 0.5 2.9462
876 0.75 1.0901
1168 1 1
1460 1.25 0.9494
1752 1.5 0.923
2044 1.75 0.6024
2336 2 0.6015

Remember, Oracle originally predicted a DB_TIME factor of 0.4255 for an increase in size to 1168MB. If this had held true, then the DB_TIME factor for an SGA size of 584 MB should now be reported as 2.35 (1/0.4255). In fact, the DB_TIME factor is 2.94.

Additionally, Oracle now claims that increasing the SGA size again will further benefit performance (e.g. a 2044MB SGA will reduce the DB_TIME factor to 0.6)

In light of this I think we can infer that increasing the SGA to the higher value was probably the way to go.

Steve has promised to pay us a flying visit in the next couple of weeks. If he’s driving the Nissan, I’m not sure what other type of visit it could be.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s