Memory Structures - Expert Oracle Database Architecture, Third Edition (2014)

Expert Oracle Database Architecture, Third Edition (2014)

Chapter 4. Memory Structures

In this chapter, we’ll look at Oracle’s three major memory structures:

· System Global Area (SGA): This is a large, shared memory segment that virtually all Oracle processes will access at one point or another.

· Process (or Program) Global Area (PGA): This is memory that is private to a single process or thread; it is not accessible from other processes/threads.

· User Global Area (UGA): This is memory associated with your session. It is located either in the SGA or the PGA, depending whether you are connected to the database using a shared server (it will be in the SGA), or a dedicated server (it will be in the PGA).

Image Note In earlier releases of Oracle, shared server was referred to as Multi-Threaded Server or MTS. In this book, we will always use the term shared server.

When we discuss memory management in Oracle, we have five modes to investigate:

· Automatic memory management (AMM), for the SGA and PGA, available only in Oracle 11g and above, the DBA sets just one parameter—the MEMORY_TARGET parameter—to allow the database to determine how to size all of the memory regions.

· Automatic shared memory management (ASMM), for the SGA, where the DBA sets a target size for the SGA (via SGA_TARGET).

· Manual shared memory management, for the SGA, the DBA manually sizes individual memory areas of the SGA (via DB_CACHE_SIZE, SHARED_POOL_SIZE, and so on).

· Automatic PGA memory management, for the PGA, the DBA sets a target size for the PGA (via PGA_AGGREGATE_TARGET).

· Manual PGA memory management, for the PGA, the DBA manually sizes individual memory areas of the PGA (via SORT_AREA_SIZE, HASH_AREA_SIZE, and so on). Oracle strongly recommends not using this method, but we will discuss it to provide a foundation for other memory management concepts.

Looking at the prior bulleted list, which memory management method should you use? In an ideal environment, everybody would be using automatic memory management, right? Just set one parameter (MEMORY_TARGET) and you’re done. However, in the real world it’s not so black and white. Sometimes there are aspects of your environment where you know best what the target sizes of memory should be, and therefore decide to use one of the less automated memory management methods. This chapter’s purpose is to help you become versed in all aspects of Oracle memory so that you can make intelligent decisions regarding how to enable memory management.

Image Note Some operating system configurations are incompatible with automatic memory management (for example, Linux HugePages). See the Oracle Database Administrator’s Reference for Linux and UNIX-Based Operating Systems manual for details.

We’ll attack all of the methods by first discussing PGA and UGA memory management, first manual and then automatic. We’ll then move onto the SGA, again looking at manual and then automatic memory management methods. We’ll close with a look at how to manage memory using just a single parameter to control both the SGA and PGA regions.

The Process Global Area and User Global Area

The PGA is a process-specific piece of memory. In other words, it is memory specific to a single operating system process or thread. This memory is not accessible by any other process or thread in the system. It is typically allocated via either of the C runtime calls malloc() ormemmap(), and it may grow (or even shrink) at runtime. The PGA is never allocated in Oracle’s SGA; it is always allocated locally by the process or thread—the P in PGA stands for process or program; it is not shared.

The UGA is, in effect, your session’s state. It is memory that your session must always be able to get to. The location of the UGA is dependent on how you connect to Oracle. If you connect via a shared server, the UGA must be stored in a memory structure that every shared server process has access to—and that’s the SGA. In this way, your session can use any one of the shared servers, since any of them can read and write your session’s data. On the other hand, if you are using a dedicated server connection, there’s no need for universal access to your session state, and the UGA becomes virtually synonymous with the PGA; it will, in fact, be contained in the PGA of your dedicated server. When you look at the system statistics, you’ll find the UGA reported in the PGA in dedicated server mode (the PGA will be greater than or equal to the UGA memory used; the PGA memory size will include the UGA size as well).

So, the PGA contains process memory and may include the UGA. The other areas of PGA memory are generally used for in-memory sorting, bitmap merging, and hashing. It would be safe to say that, besides the UGA memory, these are the largest contributors by far to the PGA.

Starting with Oracle9i Release 1, there are two ways to manage this other non-UGA memory in the PGA:

· Manual PGA memory management, where you tell Oracle how much memory it can use to sort and hash any time it needs to sort or hash in a specific process.

· Automatic PGA memory management, where you tell Oracle how much memory it should attempt to use system wide.

Starting with Oracle 11g Release 1, automatic PGA memory management can be implemented using one of the two following techniques:

· By setting the PGA_AGGREGATE_TARGET initialization parameter and telling Oracle how much PGA memory to attempt to use instance wide.

· By setting the MEMORY_TARGET initialization parameter and telling Oracle how much total memory the database instance should use for both the SGA and the PGA; the PGA size will be figured out by the instance itself from this parameter.

The manner in which memory is allocated and used differs greatly in each case, so we’ll discuss each in turn.

Image Note It should be noted that in Oracle9i, when using a shared server connection, you only can use manual PGA memory management. This restriction was lifted with Oracle 10g Release 1 (and above). Since that release, you can use either automatic or manual PGA memory management with shared server connections. If you’re using Oracle 10g Release 1 (and above) then you should be using automatic PGA memory management.

PGA memory management modes are controlled by the database initialization parameter WORKAREA_SIZE_POLICY and may be altered at the session level. This initialization parameter defaults to AUTO, which sets automatic PGA memory management when possible in Oracle9iRelease 2 and above. In Oracle9i Release 1, the default setting was MANUAL.

In the sections that follow, we’ll take a look at each approach.

Manual PGA Memory Management

In manual PGA memory management, the following are the parameters that have the largest impact on the size of your PGA, outside of the memory allocated by your session for PL/SQL tables and other variables:

· SORT_AREA_SIZE: The total amount of RAM that will be used to sort information before swapping out to disk (using disk space in the temporary tablespace the user is assigned to).

· SORT_AREA_RETAINED_SIZE: The amount of memory that will be used to hold sorted data after the sort is complete. That is, if SORT_AREA_SIZE is 512KB and SORT_AREA_RETAINED_SIZE is 256KB, your server process would use up to 512KB of memory to sort data during the initial processing of the query. When the sort is complete, the sorting area would “shrink” down to 256KB, and any sorted data that does not fit in that 256KB would be written out to the temporary tablespace.

· HASH_AREA_SIZE: The amount of memory your server process can use to store hash tables in memory. These structures are used during a hash join, typically when joining a large set with another set. The smaller of the two sets would be hashed into memory and anything that didn’t fit in the hash area region of memory would be stored in the temporary tablespace by the join key.

These parameters control the amount of space Oracle will use to sort or hash data in memory before using the temporary tablespace on disk, and how much of that memory segment will be retained after the sort is done. The SORT_AREA_SIZE-SORT_AREA_RETAINED_SIZEcalculated value is generally allocated out of your PGA, and the SORT_AREA_RETAINED_SIZE value will be in your UGA. You can discover your current use of PGA and UGA memory and monitor its size by querying special Oracle V$ views, also referred to as dynamic performance views.

For example, let’s run a small test whereby in one session we’ll sort lots of data and, from a second session, we’ll monitor the UGA/PGA memory use in that first session. To do this in a predictable manner, we’ll make a copy of the ALL_OBJECTS table, with about 72,000 rows in this case, without any indexes (so we know a sort has to happen when we use ORDER BY on this table):

EODA@ORA12CR1> create table t as select * from all_objects;
Table created.

EODA@ORA12CR1> exec dbms_stats.gather_table_stats( user, 'T' );
PL/SQL procedure successfully completed.

To remove any side effects from the initial hard parsing of queries, we’ll run the following script three times, but for now ignore its output. We’ll run the script again in a fresh session so as to see the effects on memory usage in a controlled environment. We’ll use the sort area sizes of 64KB, 1MB, and 1GB in turn, so save this script as run_query.sql (this script assumes the username and password are eoda/foo, you’ll have to modify it for your environment):

connect eoda/foo
set serveroutput off
set echo on
column sid new_val SID
select sid from v$mystat where rownum = 1;
alter session set workarea_size_policy=manual;
alter session set sort_area_size = &1;
prompt run @reset_stat &SID and @watch_stat in another session here!
set termout off
select * from t order by 1, 2, 3, 4;
set termout on
prompt run @watch_stat in another session here!

and then run:

@run_query 65536
@run_query 1048576
@run_query 1073741820

Just ignore the output for now; we are just warming up the shared pool and getting everything “even.”

Image Note When we process SQL in the database, we must first parse the SQL statement. There are two types of parses. The first is a hard parse, which is what happens the first time a query is parsed by the database instance and includes query plan generation and optimization. The second is a soft parse, which can skip many of the steps a hard parse must do, because it can reuse the result of that work. We hard parsed the previous queries so as to not measure the work performed by that operation in the following section.

I suggest logging out of that SQL*Plus session and logging back in before continuing, in order to get a consistent environment, or one in which no work has been done yet. Now, we will want to be able to measure the session memory of the session running the big ORDER BY queries from a second separate session. If we used the same session, our query to see how much memory we are using for sorting might itself influence the very numbers we are looking at. To measure the memory from this second session, we’ll use a small SQL*Plus script I developed for this. It is actually a pair of scripts; you’ll be told when to run them by the run_query.sql script. The one that resets a small table and sets a SQL*Plus variable to the SID we want to watch is called reset_stat.sql:

drop table sess_stats;

create table sess_stats
( name varchar2(64), value number, diff number );

variable sid number
exec :sid := &1

Image Note Before using this script (or any script, for that matter), make sure you understand what the script does. This script drops and re-creates a table called SESS_STATS. If your schema already has such a table, you’ll probably want to use a different name!

The other script is called watch_stat.sql, and for this case study, it uses the MERGE SQL statement so we can initially INSERT the statistic values for a session and then later come back and update them—without needing a separate INSERT/UPDATE script:

merge into sess_stats
select, b.value
from v$statname a, v$sesstat b
where a.statistic# = b.statistic#
and b.sid = :sid
and ( like '%ga %'
or like '%direct temp%')
) curr_stats
on ( =
when matched then
update set diff = curr_stats.value - sess_stats.value,
value = curr_stats.value
when not matched then
insert ( name, value, diff )
(, curr_stats.value, null )

select name,
case when name like '%ga %'
then round(value/1024,0)
else value
end kbytes_writes,
case when name like '%ga %'
then round(diff /1024,0)
else value
end diff_kbytes_writes
from sess_stats
order by name;

I emphasize the phrase “for this case study” because of the lines in bold—the names of the statistics we’re interested in looking at change from example to example. In this particular case, we’re interested in anything with ga in it (pga and uga), or anything with direct temp, which in Oracle 10g and above will show us the direct reads and writes against temporary space (how much I/O we did reading and writing to temp).

Image Note In Oracle9i, direct I/O to the temporary tablespace was not labeled as such, so we used a WHERE clause that included (and like '%ga %' or like '%physical % direct%') in it.

When this watch_stat.sql script is run from the SQL*Plus command line, we’ll see a listing of the PGA and UGA memory statistics for the session, as well as temporary I/O. Now, if you run the script “@run_query 65536” in a session, you’ll see output like this:

EODA@ORA12CR1> @run_query 65536
EODA@ORA12CR1> set serveroutput off
EODA@ORA12CR1> set echo on
EODA@ORA12CR1> column sid new_val SID
EODA@ORA12CR1> select sid from v$mystat where rownum = 1;


EODA@ORA12CR1> alter session set workarea_size_policy=manual;

Session altered.

EODA@ORA12CR1> alter session set sort_area_size = &1;
old 1: alter session set sort_area_size = &1
new 1: alter session set sort_area_size = 65536

Session altered.

EODA@ORA12CR1> prompt run @reset_stat &SID and @watch_stat in another session here!
run @reset_stat 23 and @watch_stat in another session here!
EODA@ORA12CR1> pause

We can see the SID of this new session (23), and we’ve set our PGA memory management to manual and our SORT_AREA_SIZE to 65,536 (64KB). Now the script tells us to run the two other scripts in another session, so we’ll do that:

EODA@ORA12CR1> @reset_stat 23
Table dropped.

Table created.

PL/SQL procedure successfully completed.

EODA@ORA12CR1> @watch_stat

6 rows merged.

-------------------------------------------------- ------------- ------------------
physical reads direct temporary tablespace 0 0
physical writes direct temporary tablespace 0 0
session pga memory 876 0
session pga memory max 876 0
session uga memory 334 0
session uga memory max 334 0

6 rows selected.

Image Note The watch_stat.sql script must be run in the same session as the reset_stat.sql script; reset_stat.sql sets the :sid bind variable necessary for the MERGE statement. Alternatively, you can execute exec :sid := <sid>, where <sid> is the SID you observe in your testing.

So, before we begin we can see that we have about 334KB of data in the UGA and 876KB of data in the PGA. The first question is, “How much memory are we using between the PGA and UGA?” That is, are we using 334KB + 876KB of memory, or are we using some other amount? This is a trick question, and one that you can’t answer unless you know whether the monitored session with SID 23 was connected to the database via a dedicated server or a shared server—and even then it might be hard to figure out. In dedicated server mode, the UGA is totally contained within the PGA, in which case we would be consuming 876KB of memory in our process or thread.

In shared server mode, the UGA is allocated from the SGA, and the PGA is allocated in O/S memory private to the shared server process. So, in shared server mode, by the time we get the last row from the preceding query, the shared server process may be in use by someone else. That PGA isn’t “ours” anymore, so technically we are using 334KB of memory (except when we are actually running the query, at which point we are using 1210KB of memory between the combined PGA and UGA). In this case, I used a dedicated server (it would be impossible to do the test accurately otherwise) and we are using a grand total of 876KB of memory in the combined PGA and UGA. So, let’s now run the first big query in session 23, which is using manual PGA memory management in dedicated server mode. We just have to go back to that session where we ranrun_query.sql and hit enter to start the query running:

Image Note Since we haven’t set a SORT_AREA_RETAINED_SIZE, its reported value will be zero, but its used value will match SORT_AREA_SIZE.

EODA@ORA12CR1> set termout off
EODA@ORA12CR1> prompt run @watch_stat in another session here!
run @watch_stat in another session here!
EODA@ORA12CR1> pause

The point where you see set termout off is the point where the large query is executing, we told SQL*plus to run the query but not print on the screen (it would take a while for over 70,000 lines to be printed). Now if we run our watch_stat.sql script again in the second session, we’ll see something like the following:

EODA@ORA12CR1> @watch_stat
6 rows merged.

-------------------------------------------------- ------------- ------------------
physical reads direct temporary tablespace 3000 3000
physical writes direct temporary tablespace 3000 3000
session pga memory 1196 320
session pga memory max 1260 384
session uga memory 654 320
session uga memory max 718 384
6 rows selected.

Notice this time that the session xxx memory and session xxx memory max values don’t match. The session xxx memory value represents how much memory we are using right now. The session xxx memory max value represents the peak value we used at some time during our session while processing the query.

Image Note In these examples, I wouldn’t expect that you’d see exactly the same numbers I’ve printed here. The amount of memory used is affected by many things, such as Oracle version, the operating system and its features and capabilities, the amount of data placed into the table T, and so on. You should expect variations in memory amounts, but the overall picture will be the same.

As you can see, our memory usage went up—we’ve done some sorting of data. Our UGA memory increased from 334KB to 718KB during the processing (max value) of our query. To perform our query and the sorting, Oracle allocated a sort area for our session. Additionally, the PGA memory went from 876KB to 1196KB. Also, we can see that we did 3,000 writes and reads to and from temp (since the data we sorted could not have fit into 64KB, our SORT_AREA_SIZE).

By the time we finish our query and exhaust the result set, we can see that our PGA has shrunk somewhat (note that in Oracle8i and before, you wouldn’t expect to see the PGA shrink back at all; this is a new feature with Oracle9i and later).

Let’s retry that operation but play around with the size of our SORT_AREA_SIZE by increasing it to 1MB. We’ll log out of the session we’re monitoring and log back in, following the directions to increase our SORT_AREA_SIZE to 1MB. Remember that in the other session we are monitoring from, you must run the reset_stat.sql script to start over. As the beginning numbers are consistent (the output of the first watch_stat.sql should be the same in a new session), I don’t display them here—only the final results:

-------------------------------------------------- ------------- ------------------
physical reads direct temporary tablespace 1043 1043
physical writes direct temporary tablespace 1043 1043
session pga memory 1196 320
session pga memory max 2732 1856
session uga memory 718 384
session uga memory max 1756 1422
6 rows selected.

As you can see, our UGA memory has grown considerably this time during the processing of our query. It temporarily grew to about 1,700KB (a bit more than 1MB, our SORT_AREA_SIZE), but the amount of physical I/O we had to do to sort this data dropped considerably as well (use more memory, swap to disk less often). We may have avoided a multipass sort as well, a condition that happens when there are so many little sets of sorted data to merge together that Oracle ends up writing the data to temp more than once. Now, let’s go to an extreme here and use a 1GBSORT_AREA_SIZE:

-------------------------------------------------- ------------- ------------------
physical reads direct temporary tablespace 0 0
physical writes direct temporary tablespace 0 0
session pga memory 1132 256
session pga memory max 11372 10496
session uga memory 654 320
session uga memory max 10631 10296
6 rows selected.

We can observe that even though we allowed for up to 1GB of memory to the SORT_AREA_SIZE, we really only used about 10MB. This shows that the SORT_AREA_SIZE setting is an upper bound, not the default and only allocation size. Also, we only performed one sort again, but this time it was entirely in memory; there was no temporary space on disk used, as evidenced by the lack of physical I/O.

If you run this same test on various versions of Oracle, or perhaps even on different operating systems, you might see different behavior, and I would expect that your numbers in all cases would be a little different from mine. But the general behavior should be the same. In other words, as you increase the permitted sort area size and perform large sorts, the amount of memory used by your session will increase. You might notice the PGA memory going up and down, or it might remain constant over time, as just shown. For example, if you were to execute the previous test in Oracle8i, I’m sure you’d notice that PGA memory does not shrink back in size (i.e., the SESSION PGA MEMORY equals the SESSION PGA MEMORY MAX in all cases). This is to be expected, as the PGA is managed as a heap in 8i releases and is created via malloc()-ed memory. In 9iand above, new methods attach and release work areas as needed using operating system-specific memory allocation calls.

Here are the important things to remember about using the *_AREA_SIZE parameters:

· These parameters control the maximum amount of memory used by a SORT, HASH, or BITMAP MERGE operation.

· A single query may have many operations taking place that use this memory, and multiple sort/hash areas could be created. Remember that you may have many cursors opened simultaneously, each with its own SORT_AREA_RETAINED needs. So, if you set the sort area size to 10MB, you could use 10, 100, 1,000 or more megabytes of RAM in your session. These settings are not session limits; rather, they are limits on a single operation, and your session could have many sorts in a single query or many queries open that require a sort.

· The memory for these areas is allocated on an “as needed” basis. If you set the sort area size to 1GB as we did, it doesn’t mean you’ll allocate 1GB of RAM. It only means that you’ve given the Oracle process the permission to allocate that much memory for a sort/hash operation.

Automatic PGA Memory Management

Starting with Oracle9i Release 1, a new way to manage PGA memory was introduced that avoids using the SORT_AREA_SIZE, BITMAP_MERGE_AREA_SIZE, and HASH_AREA_SIZE parameters. It was introduced to address a few issues:

· Ease of use: Much confusion surrounded how to set the proper *_AREA_SIZE parameters. There was also much confusion over how those parameters actually worked and how memory was allocated.

· Manual allocation was a “one-size-fits-all” method: Typically, as the number of users running similar applications against a database went up, the amount of memory used for sorting and hashing went up linearly as well. If 10 concurrent users with a sort area size of 1MB used 10MB of memory, 100 concurrent users would probably use 100MB, 1,000 would probably use 1000MB, and so on. Unless the DBA was sitting at the console continually adjusting the sort/hash area size settings, everyone would pretty much use the same values all day long. Consider the previous example, where you saw for yourself how the physical I/O to temp decreased as the amount of RAM we allowed ourselves to use went up. If you run that example for yourself, you will almost certainly see a decrease in response time as the amount of RAM available for sorting increases. Manual allocation fixes the amount of memory to be used for sorting at a more or less constant number, regardless of how much memory is actually available. Automatic memory management allows us to use the memory when it is available; it dynamically adjusts the amount of memory we use based on the workload.

· Memory control: As a result of the previous point, it was hard, if not impossible, to keep the Oracle instance inside a “box” memory-wise. You couldn’t control the amount of memory the instance was going to use, as you had no real control over the number of simultaneous sorts and hashes taking place. It was far too easy to attempt to use more real memory (actual physical free memory) than was available on the machine.

Enter automatic PGA memory management. Here, you first simply set up and size the SGA. The SGA is a fixed-size piece of memory so you can very accurately see how big it is, and that will be its total size (unless and until you change it). You then tell Oracle, “This is how much memory you should try to limit yourself to across all work areas” (a new umbrella term for the sorting and hashing areas you use). Now, you could in theory take a machine with 2GB of physical memory and allocate 768MB of memory to the SGA and 768MB of memory to the PGA, leaving 512MB of memory for the OS and other processes. I say “in theory” because it doesn’t work exactly that cleanly, but it’s close. Before I discuss why that’s true, let’s take a look at how to set up automatic PGA memory management and turn it on.

The process of setting this up involves deciding on the proper values for two instance initialization parameters:

· WORKAREA_SIZE_POLICY: This parameter may be set to either MANUAL, which will use the sort area and hash area size parameters to control the amount of memory allocated, or AUTO, in which case the amount of memory allocated will vary based on the currently allocated PGA memory to the instance. The default and recommended value is AUTO.

· PGA_AGGREGATE_TARGET: This parameter controls how much memory the instance should allocate, in total, for all work areas used to sort or hash data. Its default value varies by version and may be set by various tools such as the DBCA. In general, if you are using automatic PGA memory management, you should explicitly set this parameter.

Image Note In Oracle 11g Release 1 and above, instead of setting the PGA_AGGREGATE_TARGET, you can set the MEMORY_TARGET parameter. When the instance uses the MEMORY_TARGET parameter, it decides how much memory to allocate to the SGA and PGA respectively. It may also decide to reallocate these memory amounts while the database is up and running. This fact, however, doesn’t affect how automatic PGA memory management (described later in this chapter) works; rather it just decides the setting for the PGA_AGGREGATE_TARGET.

So, assuming that WORKAREA_SIZE_POLICY is set to AUTO (the default) and PGA_AGGREGATE_TARGET has a nonzero value, you will be using automatic PGA memory management. You can “turn it on” in your session via the ALTER SESSION command or at the system level via the ALTER SYSTEM command.

Image Note Bear in mind the previous caveat that in Oracle9i, shared server connections will not use automatic memory management; rather, they will use the SORT_AREA_SIZE and HASH_AREA_SIZE parameters to decide how much RAM to allocate for various operations. In Oracle 10gand up, automatic PGA memory management is available to both connection types. It is important to properly set the SORT_AREA_SIZE and HASH_AREA_SIZE parameters when using shared server connections with Oracle9i.

So, the entire goal of automatic PGA memory management is to maximize the use of RAM while at the same time not using more RAM than you want. Under manual memory management, this was a virtually impossible goal to achieve. If you set SORT_AREA_SIZE to 10MB, when one user was performing a sort operation that user would use up to 10MB for the sort work area. If 100 users were doing the same, they would use up to 1,000MB of memory. If you had 500MB of free memory, the single user performing a sort by himself could have used much more memory, and the 100 users should have used much less. That is what automatic PGA memory management was designed to do. Under a light workload, memory usage could be maximized as the load increases on the system, and as more users perform sort or hash operations, the amount of memory allocated to them would decrease—to reach the goal of using all available RAM, but not attempting to use more than physically exists.

Determining How the Memory Is Allocated

Questions that come up frequently are “How is this memory allocated?” and “What will be the amount of RAM used by my session?” These are hard questions to answer for the simple reason that the algorithms for serving out memory under the automatic scheme are not documented and can and will change from release to release. When using things that begin with “A”—for automatic—you lose a degree of control, as the underlying algorithms decide what to do and how to control things.

We can make some observations based on information from MOS notes 147806.1 and 223730.1:

· The PGA_AGGREGATE_TARGET is a goal of an upper limit. It is not a value that is preallocated when the database is started up. You can observe this by setting the PGA_AGGREGATE_TARGET to a value much higher than the amount of physical memory you have available on your server. You will not see any large allocation of memory as a result (one caveat, if you’ve set MEMORY_TARGET, and then set PGA_AGGREGATE_TARGET to a value larger than MEMORY_TARGET, on instance startup Oracle throws an ORA-00838error and won’t let you start your instance).

· The amount of PGA memory available for a given session is derived from the setting of PGA_AGGREGATE_TARGET. The algorithm for determining the maximum size used by a process varies by database version. The amount of PGA memory a process is allocated is typically a function of the amount of memory available and the number of processes competing for space.

· As the workload on your instance goes up (more concurrent queries, concurrent users), the amount of PGA memory allocated to your work areas will go down. The database will try to keep the sum of all PGA allocations under the threshold set byPGA_AGGREGATE_TARGET. This is analogous to having a DBA sit at a console all day, setting the SORT_AREA_SIZE and HASH_AREA_SIZE parameters based on the amount of work being performed in the database. We will directly observe this behavior shortly in a test.

OK, so how can we observe the different work area sizes being allocated to our session? By applying the same technique we used earlier in the manual PGA memory management section to observe the memory used by our session and the amount of I/O to temp we performed. I performed the following test on an Oracle Linux machine with four CPUs using Oracle and dedicated server connections. We begin by creating a table to hold the metrics we’d like to monitor (the following code is placed in a file named stats.sql):

create table sess_stats
select name, value, 0 active
select, b.value
from v$statname a, v$sesstat b
where a.statistic# = b.statistic#
and b.sid = (select sid from v$mystat where rownum=1)
and ( like '%ga %'
or like '%direct temp%')
union all
select 'total: ' ||, sum(b.value)
from v$statname a, v$sesstat b, v$session c
where a.statistic# = b.statistic#
and ( like '%ga %'
or like '%direct temp%')
and b.sid = c.sid
and c.username is not null
group by 'total: ' ||

The columns in this table we’ll be using for the metrics represent:

· NAME: the name of the statistic we are gathering (PGA and UGA information from V$SESSTAT for the current session, plus all of the memory information for the database instance as well as temporary tablespace writes).

· VALUE: the value of the given metric.

· ACTIVE: the number of other sessions doing work in the instance. Before we start, we assume an “idle” instance; we are the only user session right now, hence the value of zero.

I then ran the following SQL*Plus script (stored in a file named single_load.sql) in an interactive session. The table T had been created beforehand with about 70,000 rows in it.

connect eoda/foo
set echo on
l_first_time boolean default true;
for x in ( select * from t order by 1, 2, 3, 4 )
if ( l_first_time )
insert into sess_stats
( name, value, active )
select name, value,
(select count(*)
from v$session
where status = 'ACTIVE'
and username is not null)
select, b.value
from v$statname a, v$sesstat b
where a.statistic# = b.statistic#
and b.sid = (select sid from v$mystat where rownum=1)
and ( like '%ga %'
or like '%direct temp%')
union all
select 'total: ' ||, sum(b.value)
from v$statname a, v$sesstat b, v$session c
where a.statistic# = b.statistic#
and ( like '%ga %'
or like '%direct temp%')
and b.sid = c.sid
and c.username is not null
group by 'total: ' ||
l_first_time := false;
end if;
end loop;

This script sorts the big table T using PGA automatic memory management. Then, for that session, it captures all of the PGA/UGA memory settings as well as sort-to-disk activity. In addition, the UNION ALL adds system-level metrics about the same (total PGA memory, total UGA memory and so on). I ran that script against a database started with the following initialization settings:


These settings show I was using automatic PGA memory management with a PGA_AGGREGATE_TARGET of 256MB, meaning I wanted Oracle to use up to about 256MB of PGA memory for sorting.

I set up another script to be run in other sessions to generate a large sorting load on the machine. This script loops and uses a built-in package, DBMS_ALERT, to see if it should continue processing. If it should, it runs the same big query, sorting the entire T table. When the simulation finished, a session could signal all of the sorting processes, the load generators, to “stop” and exit. Here’s the script (stored in a file named gen_load.sql) used to perform the sort:

l_msg long;
l_status number;
dbms_alert.register( 'WAITING' );
for i in 1 .. 999999 loop
dbms_application_info.set_client_info( i );
dbms_alert.waitone( 'WAITING', l_msg, l_status, 0 );
exit when l_status = 0;
for x in ( select * from t order by 1, 2, 3, 4 )
end loop;
end loop;

and here’s the script (stored in a file named stop.sql) to stop these processes from running:

dbms_alert.signal( 'WAITING', '' );

To observe the differing amounts of RAM allocated to the session I was measuring, I initially ran the SELECT in isolation—as the only session. I captured the statistics and saved them into the SESS_STATS table, along with the count of active sessions. Then I added 25 sessions to the system (i.e., I ran the preceding benchmark script (gen_load.sql) with the for i in 1 .. 999999 loop in 25 new sessions). I waited a short period of time—one minute for the system to adjust to this new load—and then I created a new session and ran the single sort query from earlier, capturing of the metrics the first time through the loop. I did this repeatedly, for up to 500 concurrent users.

Image Tip On the Apress web site for this book, you can download the scripts used for this experiment. In the ch04 directory, the run.sql script automates the test described in this section.

It should be noted that I asked the database instance to do an impossible thing here. As mentioned previously, based on the first time we ran watch_stat.sql, each connection to Oracle—before even doing a single sort—consumed a little more than .5MB of RAM. At 500 users, we would be very close to the PGA_AGGREGATE_TARGET setting just by having them all logged in, let alone actually doing any work! This drives home the point that the PGA_AGGREGATE_TARGET is just that: a target, not a directive. We can and will exceed this value for various reasons.

Now we are ready to report on the finding; for reasons of space, we’ll stop the output at 275 users—since the data starts to get quite repetitive:

EODA@ORA12CR1> column active format 999
EODA@ORA12CR1> column pga format 999.9
EODA@ORA12CR1> column "tot PGA" format 999.9
EODA@ORA12CR1> column pga_diff format 999.99
EODA@ORA12CR1> column "temp write" format 9,999
EODA@ORA12CR1> column "tot writes temp" format 99,999,999
EODA@ORA12CR1> column writes_diff format 9,999,999
EODA@ORA12CR1> select active,
2 pga,
3 "tot PGA",
4 "tot PGA"-lag( "tot PGA" ) over (order by active) pga_diff,
5 "temp write",
6 "tot writes temp",
7 "tot writes temp"-lag( "tot writes temp" ) over (order by active) writes_diff
8 from (
9 select *
10 from (
11 select active,
12 name,
13 case when name like '%ga mem%' then round(value/1024/1024,1) else value end val
14 from sess_stats
15 where active < 275
16 )
17 pivot ( max(val) for name in (
18 'session pga memory' as "PGA",
19 'total: session pga memory' as "tot PGA",
20 'physical writes direct temporary tablespace' as "temp write",
21 'total: physical writes direct temporary tablespace' as "tot writes temp"
22 ) )
23 )
24 order by active
25 /

ACTIVE PGA tot PGA PGA_DIFF temp write tot writes temp WRITES_DIFF
------ ------ ------- -------- ---------- --------------- -----------
0 3.5 7.6 0 0
1 15.2 19.5 11.90 0 0 0
26 15.2 195.6 176.10 0 243,387 243,387
51 7.7 292.7 97.10 1,045 518,246 274,859
76 5.2 188.7 -104.00 3,066 941,324 423,078
101 5.2 232.6 43.90 6,323 1,834,035 892,711
126 5.2 291.8 59.20 6,351 3,021,485 1,187,450
151 5.1 345.0 53.20 6,326 4,783,879 1,762,394
177 5.0 403.3 58.30 6,321 8,603,295 3,819,416
201 5.2 453.2 49.90 6,327 12,848,568 4,245,273
226 4.8 507.5 54.30 6,333 15,225,399 2,376,831
251 5.1 562.2 54.70 6,315 17,579,502 2,354,103
12 rows selected.

Before we analyze the results, let’s look at the query I used for reporting. My query uses a feature, available starting with Oracle 11g Release 1, called pivot to pivot a result set. Here’s an alternate way to write lines 11 through 22 of that SQL query that would work in 10g Release 2 and before:

11 select active,
12 max( decode(name,'session pga memory',val) ) pga,
13 max( decode(name,'total: session pga memory',val) ) as "tot PGA",
14 max( decode(name,
'physical writes direct temporary tablespace',
val) ) as "temp write",
15 max( decode(name,
'total: physical writes direct temporary tablespace',
val) ) as "tot writes temp"
16 from (
17 select active,
18 name,
19 case when name like '%ga mem%' then round(value/1024/1024,1) else value end val
20 from sess_stats
21 where active < 275
22 )
23 group by active
24 ))

This part of the query retrieved the records from the table of metrics when there were less than 275 active sessions, converted the metrics for memory (UGA/PGA memory) from bytes into megabytes, and then pivoted—turned rows into columns—on the four interesting metrics. Once we got those four metrics in a single record, we used analytics (the LAG() function specifically) to add to each row the prior rows’ total observed PGA and total observed I/O to temp so we could easily see the incremental differences in these values. Back to the data—as you can see, when I had a few active sessions, my sorts were performed entirely in memory. For an active session count of 1 to somewhere less than 50, I could sort entirely in memory. However, by the time I had 50 users logged in and actively sorting, the database started reining in the amount of memory I was allowed to use at a time. It would have taken a couple of minutes before the amount of PGA being used fell back within acceptable limits (the 256MB request), but eventually it would at these low concurrent user levels. The amount of PGA memory allocated to the session we were watching dropped from 15.2MB to 7.7MB and settled on around 5.2MB (remember, parts of that PGA are not for work area (sorting) allocations, but are for other operations; just the act of logging in created a .5MB PGA allocation). The total PGA in use by the system remained within tolerable limits until somewhere around 126 users. At that point, I started to exceed on a regular basis the PGA_AGGREGATE_TARGET, and continued to do so until the end of the test. I gave the database instance in this case an impossible task; the very act of having 126 users, most executing PL/SQL, plus the sort they were all requesting, just did not fit into the 256MB of RAM I had targeted. It simply could not be done. Each session therefore used as little memory as possible, but had to allocate as much memory as it needed. By the time I finished this test, the active sessions were using a total of about 560MB of PGA memory—as little as they could.

You should, however, consider what that output would look like under a manual memory management situation. Suppose the SORT_AREA_SIZE had been set to 5MB. The math is very straightforward: each session would be able to perform the sort in RAM (or virtual memory as the machine ran out of real RAM), and thus would consume 6MB to 7MB of RAM per session (the amount used without sorting to disk in the previous single-user case). The memory use would look something like this:

EODA@ORA12CR1> column total_pga format 9,999
EODA@ORA12CR1> with data(users)
2 as
3 (select 1 users from dual
4 union all
5 select users+25 from data where users+25 <= 275)
6 select users, 7 my_pga, 7*users total_pga
7 from data
8 order by users
9 /

---------- ---------- ---------
1 7 7
26 7 182
51 7 357
76 7 532
101 7 707
126 7 882
151 7 1,057
176 7 1,232
201 7 1,407
226 7 1,582
251 7 1,757

11 rows selected.

Image Note This query uses a technique—recursive subquery factoring—that is available only in Oracle 11g Release 2 and above. It will not work in earlier releases.

Had I run this test (I have 2GB of real memory on this server and my SGA is 256MB), by the time I got to 250 users, the machine would have begun paging and swapping to the point where it would have been impossible to continue; at 500 users I would have allocated around 3,514MB of RAM! So, the DBA would probably not set the SORT_AREA_SIZE to 5MB on this system, but rather to about 0.5 MB, in an attempt to keep the maximum PGA usage at a bearable level at peak. At 500 users, I would have had about 500MB of PGA allocated, perhaps similar to what we observed with automatic memory management, but even when there were fewer users, we would still have written to temp rather than performing the sort in memory.

Manual memory management represents a very predictable—but suboptimal—use of memory as the workload increases or decreases over time. Automatic PGA memory management was designed specifically to allow a small community of users to use as much RAM as possible when it was available, to back off on this allocation over time as the load increased, and increase the amount of RAM allocated for individual operations over time as the load decreased.

Using PGA_AGGREGATE_TARGET to Control Memory Allocation

Earlier, I wrote that “in theory” we can use the PGA_AGGREGATE_TARGET to control the overall amount of PGA memory used by the instance. We saw in the last example that this is not a hard limit, however. The instance will attempt to stay within the bounds of thePGA_AGGREGATE_TARGET, but if it can’t, it won’t stop processing; rather, it will just be forced to exceed that threshold.

Another reason this limit is “theory” is because work areas, though large contributors to PGA memory, are not the only contributors to PGA memory. Many factors contribute to PGA memory allocation and only the work areas are under the control of the database instance. If you create and execute a PL/SQL block of code that fills in a large array with data in dedicated server mode where the UGA is in the PGA, Oracle can’t do anything but allow you to do it.

Consider the following quick example. We’ll create a package that can hold some persistent (global) data in the server:

EODA@ORA12CR1> create or replace package demo_pkg
2 as
3 type array is table of char(2000) index by binary_integer;
4 g_data array;
5 end;
6 /
Package created.

Now we’ll measure the amount of memory our session is currently using in the PGA/UGA (I used a dedicated server in this example, so the UGA is a subset of the PGA memory):

EODA@ORA12CR1> select, to_char(b.value, '999,999,999') bytes,
2 to_char(round(b.value/1024/1024,1), '99,999.9' ) mbytes
3 from v$statname a, v$mystat b
4 where a.statistic# = b.statistic#
5 and like '%ga memory%';

------------------------------ ------------ ---------
session uga memory 1,526,568 1.5
session uga memory max 1,526,568 1.5
session pga memory 2,208,088 2.1
session pga memory max 2,208,088 2.1

Initially we are using about 2.1MB of PGA memory in our session (as a result of compiling a PL/SQL package, running this query, etc.). Now, we’ll run our query against T again using the same 256MB PGA_AGGREGATE_TARGET (this was done in an otherwise idle instance; we are the only session requiring memory right now):

EODA@ORA12CR1> set autotrace traceonly statistics;
EODA@ORA12CR1> select * from t order by 1,2,3,4;

72616 rows selected.

105 recursive calls
0 db block gets
1103 consistent gets
993 physical reads
0 redo size
3665844 bytes sent via SQL*Net to client
53795 bytes received via SQL*Net from client
4843 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
72616 rows processed

EODA@ORA12CR1> set autotrace off

As you can see, the sort was done entirely in memory, and in fact if we peek at our session’s PGA/UGA usage, we can see how much we used:

EODA@ORA12CR1> select, to_char(b.value, '999,999,999') bytes,
2 to_char(round(b.value/1024/1024,1), '99,999.9' ) mbytes
3 from v$statname a, v$mystat b
4 where a.statistic# = b.statistic#
5 and like '%ga memory%';

------------------------------ ------------ ---------
session uga memory 1,854,008 1.8
session uga memory max 11,213,280 10.7
session pga memory 2,470,232 2.4
session pga memory max 12,104,024 11.5

We see 11.5MB of RAM being used, in the ballpark range of the 15MB we observed earlier in the prior test for sorting. Now we’ll fill up that CHAR array we have in the package (a CHAR data type is blank-padded so each of these array elements is exactly 2,000 characters in length):

EODA@ORA12CR1> begin
2 for i in 1 .. 200000
3 loop
4 demo_pkg.g_data(i) := 'x';
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.

If we then measure our session’s current PGA utilization, we find something similar to the following:

EODA@ORA12CR1> select, to_char(b.value, '999,999,999') bytes,
2 to_char(round(b.value/1024/1024,1), '99,999.9' ) mbytes
3 from v$statname a, v$mystat b
4 where a.statistic# = b.statistic#
5 and like '%ga memory%';

------------------------------ ------------ ---------
session uga memory 469,569,304 447.8
session uga memory max 469,569,304 447.8
session pga memory 470,921,560 449.1
session pga memory max 470,921,560 449.1

Now, that is memory allocated in the PGA that the instance itself can’t control. We already exceeded the PGA_AGGREGATE_TARGET set for the entire instance in this single session—and there is quite simply nothing the database can do about it. It would have to fail our request if it did anything, and it will do that only when the OS reports back that there is no more memory to give (ORA-04030). If we wanted, we could allocate more space in that array and place more data in it, and the instance would just have to do it for us.

However, the instance is aware of what we have done. It does not ignore the memory it can’t control; it simply recognizes that the memory is being used and backs off the size of memory allocated for work areas accordingly. So if we rerun the same sort query, we see that this time we sorted to disk—the instance did not give us the 12MB or so of RAM needed to do this in memory since we had already exceeded the PGA_AGGREGATE_TARGET:

EODA@ORA12CR1> set autotrace traceonly statistics;
EODA@ORA12CR1> select * from t order by 1,2,3,4;

72616 rows selected.

9 recursive calls
8 db block gets
986 consistent gets
2025 physical reads
0 redo size
3665844 bytes sent via SQL*Net to client
53795 bytes received via SQL*Net from client
4843 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
72616 rows processed

EODA@ORA12CR1> set autotrace off

So, because some PGA memory is outside of Oracle’s control, it is easy to exceed the PGA_AGGREGATE_TARGET simply by allocating lots of really large data structures in our PL/SQL code. I am not recommending you do this by any means. I’m just pointing out that thePGA_AGGREGATE_TARGET is more of a request than a hard limit.

Choosing Between Manual and Auto Memory Management

So, which method should you use, manual or automatic? My strong preference is to use the automatic PGA memory management by default.

Image Caution I’ll repeat this from time to time in this book: please do not make any changes to a production system—a live system—without first testing for any side effects. For example, please do not read this chapter, check your system and find you are using manual memory management—and then just turn on automatic memory management. Query plans may change, and performance may be impacted. One of three things could happen:

· Things run exactly the same.

· Things run better than they did before.

· Things run much worse than they did before.

Exercise caution before making changes; test the proposed change first.

One of the most perplexing things for a DBA can be setting the individual parameters, especially parameters such as SORT|HASH_AREA_SIZE and so on. I often see systems running with incredibly small values for these parameters—values so small that system performance is massively impacted in a negative way. This is probably a result of the fact that the default values are very small themselves: 64KB for sorting and 128KB for hashing. There’s a lot of confusion over how big or small these values should be. Not only that, but the values you should use for them might vary over time, as the day goes by. At 8:00 a.m., with two users, a 50MB sort area size might be reasonable for the two users logged in. However, at 12:00 p.m. with 500 users, 50MB might not be appropriate. This is where the WORKAREA_SIZE_POLICY = AUTO setting and the corresponding PGA_AGGREGATE_TARGET come in handy. Setting the PGA_AGGREGATE_TARGET, the amount of memory you would like Oracle to feel free to use to sort and hash, is conceptually easier than trying to figure out the perfect SORT|HASH_AREA_SIZE, especially since there isn’t a perfect value for these parameters; the perfect value varies by workload.

Historically, DBAs configured the amount of memory Oracle would use by setting the size of the SGA (the buffer cache; the log buffer; and the shared, large, and Java pools). The remaining memory on the machine would then be used by the dedicated or shared servers in the PGA region. The DBA had little control over how much of this memory would or would not be used. She could set the SORT_AREA_SIZE, but if there were 10 concurrent sorts, Oracle could use as much as 10 * SORT_AREA_SIZE bytes of RAM. If there were 100 concurrent sorts, Oracle would use 100 * SORT_AREA_SIZE bytes; for 1,000 concurrent sorts, 1,000 * SORT_AREA_SIZE; and so on. Couple that with the fact that other things go into the PGA, and you really didn’t have good control over the maximal use of PGA memory on the system.

What you’d like to happen is for this memory to be allocated differently as the memory demands on the system grow and shrink. The more users, the less RAM each should use. The fewer users, the more RAM each should use. Setting WORKAREA_SIZE_POLICY = AUTO is just the way to achieve this. The DBA specifies a single size now, the PGA_AGGREGATE_TARGET or the maximum amount of PGA memory that the database should strive to use. Oracle then distributes this memory over the active sessions as it sees fit. Further, with Oracle9i Release 2 and up, there is even a PGA advisory (part of Statspack and AWR, available via a V$ dynamic performance view and visible in Enterprise Manager), much like the buffer cache advisor. It will tell you over time what the optimal PGA_AGGREGATE_TARGET for your instance is to minimize physical I/O to your temporary tablespaces. You can use this information to either dynamically change the PGA size online (if you have sufficient RAM) or decide whether you need more RAM on your server to achieve optimal performance.

Are there times, however, when you won’t want to use it? Absolutely, but fortunately they seem to be the exception and not the rule. The automatic PGA memory management was designed to be multiuser “fair.” In anticipation of additional users joining the system, the automatic memory management will limit the amount of memory allocated as a percentage of the PGA_AGGREGATE_TARGET. But what happens when you don’t want to be fair, when you know that you should get all of the memory available? Well, that would be time to use the ALTER SESSION command to disable automatic memory management in your session (leaving it in place for all others) and to manually set your SORT|HASH_AREA_SIZE as needed. For example, that large batch process that takes place at 2:00 a.m. and does tremendously large hash joins, some index builds, and the like? It should be permitted to use all of the resources available to the instance. It does not want to be “fair” about memory use—it wants it all, as it knows it is the only thing happening in the database right now. That batch job can certainly issue the ALTER SESSION command and make use of all resources available.

So, in short, I prefer to use automatic PGA memory management for end-user sessions—for the applications that run day to day against my database. Manual memory management makes sense for large batch jobs that run during periods when they are the only activities in the instance.

PGA and UGA Wrap-up

So far, we have looked at two memory structures: the PGA and the UGA. You should understand now that the PGA is private to a process. It is the set of variables that an Oracle dedicated or shared server needs to have independent of a session. The PGA is a “heap” of memory in which other structures may be allocated. The UGA is also a heap of memory in which various session-specific structures may be defined. The UGA is allocated from the PGA when you use a dedicated server to connect to Oracle, and from the SGA under a shared server connection. This implies that when using a shared server, you must size your SGA’s large pool to have enough space to cater to every possible user that will ever connect to your database concurrently. So, the SGA of a database supporting shared server connections is generally much larger than the SGA for a similarly configured dedicated server mode-only database. We’ll cover the SGA in more detail next.

The System Global Area

Every Oracle instance has one big memory structure referred to as the System Global Area (SGA). This is a large, shared memory structure that every Oracle process will access at one point or another. It varies in size from dozens of megabytes on small test systems, to a few gigabytes on medium-to-large systems, up to hundreds of gigabytes for really big systems.

On UNIX/Linux, the SGA is a physical entity you can “see” from the OS command line. It is physically implemented as a shared memory segment—a standalone piece of memory to which processes may attach. It is possible to have an SGA on a system without having any Oracle processes; the memory stands alone. It should be noted, however, that if you have an SGA without any Oracle processes, this is an indication that the database crashed in some fashion. It is an unusual situation, but it can happen. This is what an SGA “looks like” on Oracle Linux:

$ ipcs -m | grep ora
0x27ba944c 887324675 oracle 640 14680064 82
0x00000000 887357444 oracle 640 1061158912 41
0x749a2e08 887947269 oracle 640 14680064 72
0x00000000 887980038 oracle 640 511705088 36
0x00000000 888537095 oracle 640 8388608 16
0x00000000 888569864 oracle 640 260046848 16
0xc6e51dc4 888602633 oracle 640 2097152 16

Image Note I have multiple instances on my test/demo machine. I needed multiple instances to test the various concepts presented in this book on different releases. The only reasonable, correct number of instances on a production machine is one. In real life, never have more than one instance on a given production server. If you need more than one instance on a physical server, you should use virtualization to split that one server into many virtual servers—each with its own instance of Oracle.

Three SGAs are represented here and the report shows the OS account that owns the SGA (oracle for all of these in this example) and the size of the SGA—1G (the second line) for the first example. On Windows, you really can’t see the SGA as a distinct entity the way you can in UNIX/Linux. Because on the Windows platform Oracle executes as a single process with a single address space, the SGA is allocated as private memory to the oracle.exe process. If you use the Windows Task Manager or some other performance tool, you can see how much memoryoracle.exe has allocated, but you can’t see the SGA versus any other piece of allocated memory.

Image Note Unless you have my parameter settings and you are running my exact same version of Oracle on my exact same OS, you will almost certainly see different numbers than I do. The SGA sizing is very version/OS/parameter-dependent.

Within Oracle itself, you can see the SGA regardless of platform, using another magic V$ view called V$SGASTAT. It might look as follows:

EODA@ORA12CR1> compute sum of bytes on pool
EODA@ORA12CR1> break on pool skip 1
EODA@ORA12CR1> select pool, name, bytes
2 from v$sgastat
3 order by pool, name;

------------ -------------------------- ----------
java pool free memory 4194304
************ ----------
sum 4194304

large pool PX msg pool 491520
free memory 3702784
************ ----------
sum 4194304

shared pool 1063.kgght 36784
11G QMN so 4144
177.kggfa 39840
zlllab Group Tree Heap De 160
************ ----------
sum 314572800

buffer_cache 184549376
fixed_sga 2290264
log_buffer 7938048
shared_io_pool 4194304
************ ----------
sum 198971992

1064 rows selected.

The SGA is broken up into various pools. Here are the major ones you’ll see:

· Java pool: The Java pool is a fixed amount of memory allocated for the JVM running in the database. In Oracle10g and above, the Java pool may be resized online while the database is up and running.

· Large pool: The large pool is used by shared server connections for session memory, by parallel execution features for message buffers, and by RMAN backup for disk I/O buffers. This pool is resizable online.

· Shared pool: The shared pool contains shared cursors, stored procedures, state objects, dictionary caches, and many dozens of other bits of data. This pool is resizable online starting from Oracle version 9i.

· Streams pool: This is a pool of memory used by data-sharing tools such as Oracle GoldenGate, Oracle Streams, and so on. This pool is available in Oracle 10g and above and is resizable online. If the Streams pool is not configured and you use the Streams functionality, Oracle will use up to 10 percent of the shared pool for streams memory.

· The “Null” pool: This one doesn’t really have a name. It is the memory dedicated to block buffers (cached database blocks), the redo log buffer, and a “fixed SGA” area.

A typical SGA might look as shown in Figure 4-1.


Figure 4-1. Typical SGA

The parameters that have the greatest effect on the overall size of the SGA are as follows:

· JAVA_POOL_SIZE: Controls the size of the Java pool.

· SHARED_POOL_SIZE: Controls the size of the shared pool (to some degree).

· LARGE_POOL_SIZE: Controls the size of the large pool.

· STREAMS_POOL_SIZE: Controls the size of the Streams pool.

· DB_*_CACHE_SIZE: Eight of these CACHE_SIZE parameters control the sizes of the various buffer caches available.

· LOG_BUFFER: Controls the size of the redo buffer (to some degree).

· SGA_TARGET: Used with automatic SGA memory management in Oracle 10g and above, can be changed online.

· SGA_MAX_SIZE: Used to control the size of the SGA.

· MEMORY_TARGET: Used with automatic memory management (both PGA and SGA automatic memory management) in Oracle 11g and above.

· MEMORY_MAX_SIZE: Used to control the maximum amount of memory Oracle should strive to use over both the PGA and SGA sizes under automatic memory management in Oracle 11g and above. This is really just a target; the PGA may exceed the optimum size if the number of users increases beyond some level or a session(s) allocates large untunable bits of memory as demonstrated earlier.

In Oracle9i, the various SGA components must be manually sized by the DBA. Starting in Oracle 10g and above, however, there is an option to consider: automatic shared memory management, whereby the database instance will allocate and reallocate the various SGA components at runtime in response to workload conditions. Moreover, starting in Oracle 11g, there’s another option: automatic memory management, whereby the database instance will not only perform automatic SGA memory management and automatic PGA memory management, it will also decide the optimum size of the SGA and PGA for you—reallocating these allotments automatically when deemed reasonable.

Using the automatic shared memory management with Oracle 10g and above is simply a matter of setting the SGA_TARGET parameter to the desired SGA size, leaving out the other SGA-related parameters altogether. The database instance will take it from there, allocating memory to the various pools as needed and even taking memory away from one pool to give to another over time.

When using automatic memory management with Oracle 11g and above, you simply set the MEMORY_TARGET. The database instance will then decide the optimal SGA size and PGA size—and those components will be set up appropriately and do their own automatic memory management within their respective boundaries. Further, the database can and will resize the SGA and PGA allocations as the workload changes over time.

Regardless of whether you are using automatic or manual memory management, you’ll find that memory is allocated to the various pools in the SGA in units called granules. A single granule is an area of memory of 4MB, 8MB, or 16MB in size. The granule is the smallest unit of allocation, so if you ask for a Java pool of 5MB and your granule size is 4MB, Oracle will actually allocate 8MB to the Java pool (8 being the smallest number greater than or equal to 5 that is a multiple of the granule size of 4). The size of a granule is determined by the size of your SGA (this sounds recursive to a degree, as the size of the SGA is dependent on the granule size). You can view the granule sizes used for each pool by querying V$SGA_DYNAMIC_COMPONENTS. In fact, we can use this view to see how the total SGA size might affect the size of the granules:

EODA@ORA12CR1> show parameter sga_target

------------------------------------ ----------- ------------------------------
sga_target big integer 256M

EODA@ORA12CR1> select component, granule_size from v$sga_dynamic_components;

---------------------------------------------------------------- ------------
shared pool 4194304
large pool 4194304
java pool 4194304
streams pool 4194304
DEFAULT buffer cache 4194304
KEEP buffer cache 4194304
RECYCLE buffer cache 4194304
DEFAULT 2K buffer cache 4194304
DEFAULT 4K buffer cache 4194304
DEFAULT 8K buffer cache 4194304
DEFAULT 16K buffer cache 4194304
DEFAULT 32K buffer cache 4194304
Shared IO Pool 4194304
Data Transfer Cache 4194304
ASM Buffer Cache 4194304
15 rows selected.

Image Note This is the SGA information for the Oracle instance started with the initialization parameter file in the previous example. We specified the SGA and PGA sizes ourselves in that parameter file. Therefore we are using automatic shared memory management and automatic PGA memory management, but not the Oracle 11g (and above) “automatic memory management” setting, which would have sized and resized our PGA/SGA settings for us.

In this example, I used automatic shared memory management and controlled the size of the SGA via the single parameter SGA_TARGET. When my SGA size is under about 1GB, the granule is 4MB. When the SGA size is increased to some threshold over 1GB (it will vary slightly from operating system to operating system and even from release to release), I see an increased granule size. First we convert to using a stored parameter file to make altering the SGA_TARGET easier:

SYS@ORA12CR1> create spfile from pfile;
File created.

SYS@ORA12CR1> startup force;
ORACLE instance started.

Total System Global Area 267227136 bytes
Fixed Size 2287336 bytes
Variable Size 180357400 bytes
Database Buffers 79691776 bytes
Redo Buffers 4890624 bytes
Database mounted.
Database opened.

Image Note If your instance is currently running, the STARTUP FORCE command will shut down the instance (abort mode) and restart it.

Then we modify the SGA_TARGET:

SYS@ORA12CR1> alter system set sga_target = 1512m scope=spfile;
System altered.

SYS@ORA12CR1> startup force
ORACLE instance started.

Total System Global Area 1586708480 bytes
Fixed Size 2288824 bytes
Variable Size 402654024 bytes
Database Buffers 1174405120 bytes
Redo Buffers 7360512 bytes
Database mounted.
Database opened.

SYS@ORA12CR1> show parameter sga_target

------------------------------------ ----------- ------------------------------
sga_target big integer 1520M

Now when we look at the SGA components:

SYS@ORA12CR1> select component, granule_size from v$sga_dynamic_components;

---------------------------------------------------------------- ------------
shared pool 16777216
large pool 16777216
java pool 16777216
streams pool 16777216
DEFAULT buffer cache 16777216
KEEP buffer cache 16777216
RECYCLE buffer cache 16777216
DEFAULT 2K buffer cache 16777216
DEFAULT 4K buffer cache 16777216
DEFAULT 8K buffer cache 16777216
DEFAULT 16K buffer cache 16777216
DEFAULT 32K buffer cache 16777216
Shared IO Pool 16777216
Data Transfer Cache 16777216
ASM Buffer Cache 16777216

15 rows selected.

As you can see, at 1.5GB of SGA, my pools will be allocated using 16MB granules, so any given pool size will be some multiple of 16MB.

With this in mind, let’s look at each of the major SGA components in turn.

Fixed SGA

The fixed SGA is a component of the SGA that varies in size from platform to platform and from release to release. It is “compiled” into the Oracle binary itself at installation time (hence the name “fixed”). The fixed SGA contains a set of variables that point to the other components of the SGA, as well as variables that contain the values of various parameters. The size of the fixed SGA is something over which we have no control, and it is generally very small. Think of this area as a “bootstrap” section of the SGA—something Oracle uses internally to find the other bits and pieces of the SGA.

Redo Buffer

The redo buffer is where data that needs to be written to the online redo logs will be cached temporarily, before it is written to disk. Since a memory-to-memory transfer is much faster than a memory-to-disk transfer, use of the redo log buffer can speed up database operation. The data will not reside in the redo buffer for very long. In fact, LGWR initiates a flush to disk of this area in one of the following scenarios:

· Every three seconds

· Whenever a COMMIT or ROLLBACK is issued

· When LGWR is asked to switch log files

· When the redo buffer gets one-third full or contains 1MB of cached redo log data

For these reasons, it will be a very rare system that will benefit from a redo buffer of more than a couple of tens of megabytes in size. A large system with lots of concurrent transactions might benefit somewhat from a large redo log buffer because while LGWR (the process responsible for flushing the redo log buffer to disk) is writing a portion of the log buffer, other sessions could be filling it up. In general, a long-running transaction that generates a lot of redo will benefit the most from a larger than normal log buffer, as it will be continuously filling up part of the redo log buffer while LGWR is busy writing out some of it (we’ll cover the phenomenon of writing uncommitted data at length in Chapter 9). The larger and longer the transaction, the more benefit it could receive from a generous log buffer.

The default size of the redo buffer, as controlled by the LOG_BUFFER parameter, varies widely by operating system, database version, and other parameter settings. Rather than try to explain what the most common default size is (there isn’t such a thing), I’ll refer you to the documentation for your release of Oracle (the Oracle Database Reference guide). My default LOG_BUFFER—given the instance we just started above with a 1.5GB SGA—is shown by the following query:

EODA@ORA12CR1> select value, isdefault
2 from v$parameter
3 where name = 'log_buffer'
4 /

-------------------- ---------
7036928 TRUE

The size is about 7MB. The minimum size of the default log buffer is OS-dependent. If you’d like to find out what that is, just set your LOG_BUFFER to 1 byte and restart your database. For example, on my Oracle Linux instance I see the following:

EODA@ORA12CR1> alter system set log_buffer=1 scope=spfile;
System altered.

EODA@ORA12CR1> connect / as sysdba;
SYS@ORA12CR1> startup force;
ORACLE instance started.

Total System Global Area 1586708480 bytes
Fixed Size 2288824 bytes
Variable Size 402654024 bytes
Database Buffers 1174405120 bytes
Redo Buffers 7360512 bytes
Database mounted.
Database opened.
SYS@ORA12CR1> show parameter log_buffer

------------------------------------ ----------- ------------------------------
log_buffer integer 1703936

SYS@ORA12CR1> select 1703936/1024/1024 from dual;


The smallest log buffer I can really have, regardless of my settings, is going to be 1.625MB on this system.

Image Note For most database applications, the default value for the LOG_BUFFER parameter is sufficient. If you see a large number of waits associated with the log buffer space event, then consider increasing the LOG_BUFFER parameter.

Block Buffer Cache

So far, we have looked at relatively small components of the SGA. Now we are going to look at one that is potentially huge in size. The block buffer cache is where Oracle stores database blocks before writing them to disk and after reading them in from disk. This is a crucial area of the SGA for us. Make it too small and our queries will take forever to run. Make it too big and we’ll starve other processes (e.g., we won’t leave enough room for a dedicated server to create its PGA, and we won’t even get started).

In earlier releases of Oracle, there was a single block buffer cache, and all blocks from any segment went into this single area. Starting with Oracle 8.0, we had three places to store cached blocks from individual segments in the SGA:

· Default pool: The location where all segment blocks are normally cached. This is the original—and, previously, the only—buffer pool.

· Keep pool: An alternate buffer pool where by convention you assign segments that are accessed fairly frequently, but still get aged out of the default buffer pool due to other segments needing space.

· Recycle pool: An alternate buffer pool where by convention you assign large segments that you access very randomly, and which would therefore cause excessive buffer flushing of many blocks from many segments. There’s no benefit to caching such segments because by the time you wanted the block again, it would have been aged out of the cache. You would separate these segments out from the segments in the default and keep pools so they would not cause those blocks to age out of the cache.

Note that in the keep and recycle pool descriptions I used the phrase “by convention.” There is nothing in place to ensure that you use either the keep pool or the recycle pool in the fashion described. In fact, the three pools manage blocks in a mostly identical fashion; they do not have radically different algorithms for aging or caching blocks. The goal here was to give the DBA the ability to segregate segments to hot, warm, and “do not care to cache” areas. The theory was that objects in the default pool would be hot enough (i.e., used enough) to warrant staying in the cache all by themselves. The cache would keep them in memory since they were very popular blocks. If you had some segments that were fairly popular but not really hot, these would be considered the warm blocks. These segments’ blocks could get flushed from the cache to make room for blocks you used infrequently (the “do not care to cache” blocks). To keep these warm segments’ blocks cached, you could do one of the following:

· Assign these segments to the keep pool, in an attempt to let the warm blocks stay in the buffer cache longer.

· Assign the “do not care to cache” segments to the recycle pool, keeping the recycle pool fairly small so as to let the blocks come into the cache and leave the cache rapidly (decrease the overhead of managing them all).

Having to do one of these two things increased the management work the DBA had to perform, as there were three caches to think about, size, and assign objects to. Remember also that there is no sharing among them, so if the keep pool has lots of unused space, it won’t give it to the overworked default or recycle pool. All in all, these pools were generally regarded as a very fine, low-level tuning device, only to be used after most other tuning alternatives had been looked at (if I could rewrite a query to do one-tenth the I/O rather then set up multiple buffer pools, that would be my choice).

Starting in Oracle9i, the DBA had up to four more optional caches, the DB_nK_CACHE_SIZE, to consider in addition to the default, keep, and recycle pools. These caches were added in support of multiple block sizes in the database. Prior to Oracle9i, a database would have a single block size (typically 2KB, 4KB, 8KB, 16KB, or 32KB). Starting with Oracle9i, however, a database can have a default block size, which is the size of the blocks stored in the default, keep, or recycle pool, as well as up to four nondefault block sizes, as explained in Chapter 3.

The blocks in these buffer caches are managed in the same way as the blocks in the original default pool—there are no special algorithm changes for them either. Let’s now move on to see how the blocks are managed in these pools.

Managing Blocks in the Buffer Cache

For simplicity, assume that there’s just a single default pool. Because the other pools are managed in the same way, we need only discuss one of them.

The blocks in the buffer cache are basically managed in a single place with two different lists pointing at them:

· The list of dirty blocks that need to be written by the database block writer (DBWn; we’ll take a look at that process a little later)

· A list of nondirty blocks

The list of nondirty blocks used to be a Least Recently Used (LRU) list in Oracle 8.0 and before. Blocks were listed in order of use. The algorithm has been modified slightly in Oracle8i and in later versions. Instead of maintaining the list of blocks in some physical order, Oracle employs a touch count algorithm, which effectively increments a counter associated with a block as you hit it in the cache. This count is not incremented every time you hit the block, but about once every three seconds if you hit it continuously. You can see this algorithm at work in one of the truly magic sets of tables: the X$ tables. The X$ tables are wholly undocumented by Oracle, but information about them leaks out from time to time.

Image Note I am using a user connected with the SYSDBA privilege in the following examples, because the X$ tables are by default visible only to that account. You shouldn’t in practice use an account with SYSDBA privileges to run queries. The need to query for information about blocks in the buffer cache is a rare exception to that rule.

The X$BH table shows information about the blocks in the block buffer cache (which offers more information than the documented V$BH view). Here, we can see the touch count get incremented as we hit blocks. We can run the following query against that view to find the five “currently hottest blocks” and join that information to the DBA_OBJECTS view to see what segments they belong to. The query orders the rows in X$BH by the TCH (touch count) column and keeps the first five. Then we join the X$BH information to DBA_OBJECTS by X$BH.OBJ toDBA_OBJECTS.DATA_OBJECT_ID:

SYS@ORA12CR1> select tch, file#, dbablk,
2 case when obj = 4294967295
3 then 'rbs/compat segment'
4 else (select max( '('||object_type||') ' ||
5 owner || '.' || object_name ) ||
6 decode( count(*), 1, '', ' maybe!' )
7 from dba_objects
8 where data_object_id = X.OBJ )
9 end what
10 from (
11 select tch, file#, dbablk, obj
12 from x$bh
13 where state <> 0
14 order by tch desc
15 ) x
16 where rownum <= 5
17 /

---------- ---------- ---------- ------------------------------
98 1 2825 (INDEX) SYS.I_JOB_NEXT
13 1 337 (INDEX) SYS.I_OBJ1
13 1 62117 (INDEX) SYS.I_OBJ1
11 1 4377 (INDEX) SYS.SYS_C00819
11 1 209 (TABLE) SYS.USER$ maybe!

Image Note The (2^32 - 1) or 4,294,967,295 referred to in the CASE statement is a magic number used to denote “special” blocks. If you’d like to understand what the underlying block in that instance is associated with, use the query select * from dba_extents where file_id = <FILE#> and block_id <= <DBABLK> and block_id+blocks-1 >= <DBABLK>.

You might be asking what is meant by the “maybe!” and the use of MAX() in the preceding scalar subquery. This is due to the fact that DATA_OBJECT_ID is not a “primary key” in the DBA_OBJECTS view, as evidenced by the following:

SYS@ORA12CR1> select data_object_id, count(*)
2 from dba_objects
3 where data_object_id is not null
4 group by data_object_id
5 having count(*) > 1;

-------------- ----------
337 2
6 3
29 3
620 7
2 18
781 3
8 3
750 3
64 2
10 3

10 rows selected.

This is due to clusters (discussed in Chapter 10), which may contain multiple tables. Therefore, when joining from X$BH to DBA_OBJECTS to print out a segment name, we would technically have to list all of the names of all of the objects in the cluster, as a database block does not belong to a single table all of the time.

We can even watch as Oracle increments the touch count on a block that we query repeatedly. We will use the magic table DUAL in this example—we know it is a one row, one column table.

Image Note Prior to Oracle 10g, querying DUAL would incur a full table scan of a real table named DUAL stored in the data dictionary. If you set autotrace on and query SELECT DUMMY FROM DUAL, you’ll observe some I/O in all releases of Oracle (consistent gets). In 9i and earlier, if you query SELECT SYSDATE FROM DUAL or variable := SYSDATE in PL/SQL, you’ll also see real I/O occur. However, in Oracle 10g, that SELECT SYSDATE is recognized as not needing to actually query the DUAL table (since you are not asking for the column or rowid from DUAL) and is done in a manner similar to calling a function. Therefore DUAL does not undergo a full table scan—only SYSDATE is returned to the application. This small change can dramatically decrease the number of consistent gets a system that uses DUAL heavily performs.

So every time we run the following query, we should be hitting the real DUAL table (since we explicitly reference the DUMMY column):

SYS@ORA12CR1> select tch, file#, dbablk, DUMMY
2 from x$bh, (select dummy from dual)
3 where obj = (select data_object_id
4 from dba_objects
5 where object_name = 'DUAL'
6 and data_object_id is not null)
7 /

---------- ---------- ---------- -
1 1 929 X
2 1 928 X

SYS@ORA12CR1> exec dbms_lock.sleep(3.2);

PL/SQL procedure successfully completed.


---------- ---------- ---------- -
2 1 1416 X
2 1 1417 X

SYS@ORA12CR1> exec dbms_lock.sleep(3.2);

PL/SQL procedure successfully completed.


---------- ---------- ---------- -
4 1 1416 X
4 1 1417 X

SYS@ORA12CR1> exec dbms_lock.sleep(3.2);

PL/SQL procedure successfully completed.


---------- ---------- ---------- -
5 1 1416 X
5 1 1417 X

I expect output to vary by Oracle release; you may well see more than two rows returned. You might observe TCH not getting incremented every time. On a multiuser system, the results will be even more unpredictable. Oracle will attempt to increment the TCH once every three seconds (there is a TIM column that shows the last update time to the TCH column), but it is not considered important that the number be 100 percent accurate, as it is close. Also, Oracle will intentionally “cool” blocks and decrement the TCH count over time. So, if you run this query on your system, be prepared to see potentially different results.

So, in Oracle8i and above, a block buffer no longer moves to the head of the list as it used to; rather, it stays where it is in the list and has its touch count incremented. Blocks will naturally tend to “move” in the list over time, however. I put the word “move” in quotes because the block doesn’t physically move; rather, multiple lists are maintained that point to the blocks and the block will “move” from list to list. For example, modified blocks are pointed to by a dirty list (to be written to disk by DBWn). Also, as they are reused over time, when the buffer cache is effectively full, and some block with a small touch count is freed, it will be “placed” into approximately the middle of the list with the new data block.

The whole algorithm used to manage these lists is fairly complex and changes subtly from release to release of Oracle as improvements are made. The actual full details are not relevant to us as developers, beyond the fact that heavily used blocks will be cached, and blocks that are not used heavily will not be cached for long.

Image Note If you’ve been following along, running the examples in your database, take this opportunity to log out of the SYSDBA account and get back in with your own account!

Multiple Block Sizes

Starting in Oracle9i, you can have multiple database block sizes in the same database. Previously, all blocks in a single database were the same size and in order to have a different block size, you had to rebuild the entire database. Now you can have a mixture of the “default” block size (the block size you used when you initially created the database; the size that is used for the SYSTEM and all TEMPORARY tablespaces) and up to four other block sizes. Each unique block size must have its own buffer cache area. The default, keep, and recycle pools will only cache blocks of the default size. In order to have a nondefault block size in your database, you need to have configured a buffer pool to hold them.

In this example, my default block size is 8KB. I will attempt to create a tablespace with a 16KB block size:

EODA@ORA12CR1> create tablespace ts_16k
2 datafile '/tmp/ts_16k.dbf'
3 size 5m
4 blocksize 16k;
create tablespace ts_16k
ERROR at line 1:
ORA-29339: tablespace block size 16384 does not match configured block sizes

EODA@ORA12CR1> show parameter 16k

------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 0

Right now, since I have not configured a 16KB cache, I can’t create such a tablespace. I could do one of a couple of things right now to rectify this situation. I could set the DB_16K_CACHE_SIZE parameter and restart the database. I could shrink one of my other SGA components in order to make room for a 16KB cache in the existing SGA. Or, I might be able to just allocate a 16KB cache if the SGA_MAX_SIZE parameter was larger than my current SGA size.

Image Note Starting in Oracle9i, you can resize various SGA components while the database is up and running. If you want to be able to “grow” the size of the SGA beyond its initial allocation, you must have set the SGA_MAX_SIZE parameter to some value larger than the allocated SGA. For example, if after startup your SGA size was 800MB and you wanted to add an additional 200MB to the buffer cache, you would have had to set the SGA_MAX_SIZE to 1GB or larger to allow for the growth.

In this example, I will set the DB_16K_CACHE_SIZE and restart, since I’m using automatic shared memory management and don’t wish to set any of the other caches manually:

EODA@ORA12CR1> alter system set sga_target=300m scope=spfile;
System altered.

EODA@ORA12CR1> alter system set db_16k_cache_size = 16m scope=spfile;
System altered.

EODA@ORA12CR1> connect / as sysdba

SYS@ORA12CR1> startup force
ORACLE instance started.

ORACLE instance started.

Total System Global Area 313159680 bytes
Fixed Size 2287864 bytes
Variable Size 180356872 bytes
Database Buffers 125829120 bytes
Redo Buffers 4685824 bytes
Database mounted.
Database opened.
SYS@ORA12CR1> show parameter 16k

------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 16M

So, now I have another buffer cache set up: one to cache any blocks that are 16KB in size. The default pool will consume the rest of the buffer cache space, as you can see by querying V$SGASTAT. These two buffer caches are mutually exclusive; if one “fills up,” it can’t use space in the other. This gives the DBA a very fine degree of control over memory use, but it comes at a price. That price is complexity and management. These multiple block sizes were not intended as a performance or tuning feature (if you need multiple caches, you have the default, keep andrecycle pools already), but rather came about in support of transportable tablespaces—the ability to take formatted data files from one database and transport or attach them to another database. They were implemented in order to take data files from a transactional system that was using an 8KB block size and transport that information to a data warehouse using a 16KB or 32KB block size.

The multiple block sizes do serve a good purpose, however, in testing theories. If you want to see how your database would operate with a different block size—how much space, for example, a certain table would consume if you used a 4KB block instead of an 8KB block—you can now test that easily without having to create an entirely new database instance.

You may also be able to use multiple block sizes as a very finely focused tuning tool for a specific set of segments, by giving them their own private buffer pools. Or, in a hybrid system, transactional users could use one set of data and reporting/warehouse users could query a separate set of data. The transactional data would benefit from the smaller block sizes due to less contention on the blocks (less data/rows per block means fewer people in general would go after the same block at the same time) as well as better buffer cache utilization (users read into the cache only the data they are interested inthe single row or small set of rows). The reporting/warehouse data, which might be based on the transactional data, would benefit from the larger block sizes due in part to less block overhead (it takes less storage overall) and larger logical I/O sizes perhaps. And since reporting/warehouse data does not have the same update contention issues, the fact that there are more rows per block is not a concern but a benefit. Moreover, the transactional users get their own buffer cache in effect; they don’t have to worry about the reporting queries overrunning their cache.

But in general, the default, keep, and recycle pools should be sufficient for fine-tuning the block buffer cache, and multiple block sizes would be used primarily for transporting data from database to database and perhaps for a hybrid reporting/transactional system.

Shared Pool

The shared pool is one of the most critical pieces of memory in the SGA, especially with regard to performance and scalability. A shared pool that is too small can kill performance to the point that the system appears to hang. A shared pool that is too large can have the same effect. A shared pool that is used incorrectly will be a disaster as well.

What exactly is the shared pool? The shared pool is where Oracle caches many bits of “program” data. When we parse a query, the parsed representation is cached there. Before we go through the job of parsing an entire query, Oracle searches the shared pool to see if the work has already been done. PL/SQL code that you run is cached in the shared pool, so the next time you run it, Oracle doesn’t have to read it in from disk again. PL/SQL code is not only cached here, it is shared here as well. If you have 1,000 sessions all executing the same code, only one copy of the code is loaded and shared among all sessions. Oracle stores the system parameters in the shared pool. The data dictionary cache (cached information about database objects) is stored here. In short, everything but the kitchen sink is stored in the shared pool.

The shared pool is characterized by lots of small (generally 4KB or less) chunks of memory. Bear in mind that 4KB is not a hard limit. There will be allocations that exceed that size, but in general the goal is to use small chunks of memory to prevent the fragmentation that would occur if memory chunks were allocated in radically different sizes, from very small to very large. The memory in the shared pool is managed on an LRU basis. It is similar to the buffer cache in that respect—if you don’t use it, you’ll lose it. A supplied package called DBMS_SHARED_POOL may be used to change this behavior—to forcibly pin objects in the shared pool. You can use this procedure to load up your frequently used procedures and packages at database startup time, and make it so they are not subject to aging out. Normally, though, if over time a piece of memory in the shared pool is not reused, it will become subject to aging out. Even PL/SQL code, which can be rather large, is managed in a paging mechanism so that when you execute code in a very large package, only the code that is needed is loaded into the shared pool in small chunks. If you don’t use it for an extended period of time, it will be aged out if the shared pool fills up and space is needed for other objects.

The easiest way to break Oracle’s shared pool is to not use bind variables. As you saw in Chapter 1, not using bind variables can bring a system to its knees for two reasons:

· The system spends an exorbitant amount of CPU time parsing queries.

· The system uses large amounts of resources managing the objects in the shared pool as a result of never reusing queries.

If every query submitted to Oracle is a unique query (because of unique values hard-coded in), the concept of the shared pool is substantially defeated. The shared pool was designed so that query plans would be used over and over again. If every query is a brand-new, never-before-seen query, then caching only adds overhead. The shared pool becomes something that inhibits performance. A common but misguided technique that many use to try to solve this issue is adding more space to the shared pool, which typically only makes things worse than before. As the shared pool inevitably fills up once again, it gets to be even more of an overhead than the smaller shared pool, for the simple reason that managing a big, full shared pool takes more work than managing a smaller, full shared pool.

The only true solution to this problem is to use shared SQL to reuse queries. Earlier, in Chapter 1, we briefly looked at the parameter CURSOR_SHARING, which can work as a short-term crutch in this area. The only real way to solve this issue, however, is to use reusable SQL in the first place. Even on the largest of large systems, I find that there are typically at most 10,000 to 20,000 unique SQL statements. Most systems execute only a few hundred unique queries.

The following real-world example demonstrates just how bad things can get if you use the shared pool poorly. I was asked to work on a system where the standard operating procedure was to shut down the database every night, to wipe out the SGA and restart it clean. The reason for doing this was that the system was having issues during the day whereby it was totally CPU-bound and, if the database were left to run for more than a day, performance really started to decline. They were using a 1GB shared pool inside of a 1.1GB SGA. This is true: 0.1GB dedicated to block buffer cache and other elements and 1GB dedicated to caching unique queries that would never be executed again. The reason for the cold start was that if they left the system running for more than a day, they would run out of free memory in the shared pool. At that point, the overhead of aging structures out (especially from a structure so large) was such that it overwhelmed the system and performance was massively degraded (not that performance was that great anyway, since they were managing a 1GB shared pool). Furthermore, the people working on this system constantly wanted to add more and more CPUs to the machine, as hard-parsing SQL is so CPU-intensive. By correcting the application and allowing it to use bind variables, not only did the physical machine requirements drop (they then had many times more CPU power than they needed), but also the allocation of memory to the various pools was reversed. Instead of a 1GB shared pool, they had less than 100MB allocated—and they never used it all over many weeks of continuous uptime.

One last comment about the shared pool and the parameter SHARED_POOL_SIZE. In Oracle9i and before, there is no direct relationship between the outcome of the query:

ops$tkyte@ORA9IR2> select sum(bytes) from v$sgastat where pool = 'shared pool';


and the SHARED_POOL_SIZE parameter

ops$tkyte@ORA9IR2> show parameter shared_pool_size

------------------------------------ ----------- ------------------------------
shared_pool_size big integer 83886080

other than the fact that the SUM(BYTES) FROM V$SGASTAT will always be larger than the SHARED_POOL_SIZE. The shared pool holds many other structures that are outside the scope of the corresponding parameter. The SHARED_POOL_SIZE is typically the largest contributor to the shared pool as reported by the SUM(BYTES), but it is not the only contributor. For example, the parameter CONTROL_FILES contributes 264 bytes per file to the “miscellaneous” section of the shared pool. It is unfortunate that the “shared pool” in V$SGASTAT and the parameterSHARED_POOL_SIZE are named as they are, since the parameter contributes to the size of the shared pool, but it is not the only contributor.

In Oracle 10g and above, however, you should see a one-to-one correspondence between the two, assuming you are using manual shared memory management (i.e., you have set the SHARED_POOL_SIZE parameter yourself):

ops$tkyte@ORA10G> select sum(bytes)/1024/1024 mbytes from v$sgastat where pool = 'shared pool';


ops$tkyte@ORA10G> show parameter shared_pool_size;

------------------------------------ ----------- ------------------------------
shared_pool_size big integer 128M

Image Note I was using manual shared memory management in this example!

This is a relatively important change as you go from Oracle9i and before to 10g. In Oracle 10g, the SHARED_POOL_SIZE parameter controls the size of the shared pool, whereas in Oracle9i and before, it was just the largest contributor to the shared pool. You should review your 9i and before actual shared pool size (based on V$SGASTAT) and use that figure to set your SHARED_POOL_SIZE parameter in Oracle 10g and above. The various other components that used to add to the size of the shared pool now expect you to allocate that memory for them.

Large Pool

The large pool is not so named because it is a “large” structure (although it may very well be large in size). It is so named because it is used for allocations of large pieces of memory that are bigger than the shared pool is designed to handle.

Prior to the introduction of the large pool in Oracle 8.0, all memory allocation took place in the shared pool. This was unfortunate if you were using features that made use of “large” memory allocations, such as shared server UGA memory allocations. This issue was further confounded by the fact that processing, which tended to need a lot of memory allocation, would use the memory in a manner different from the way the shared pool managed it. The shared pool manages memory on an LRU basis, which is perfect for caching and reusing data. Large memory allocations, however, tend to get a chunk of memory, use it, and then be done with it. There was no need to cache this memory.

What Oracle needed was something similar to the recycle and keep buffer pools implemented for the block buffer cache, and that’s exactly what the large pool and shared pool are now. The large pool is a recycle-style memory space, whereas the shared pool is more like the keep buffer pool—if people appear to be using something frequently, then you keep it cached.

Memory allocated in the large pool is managed in a heap, much in the way C manages memory via malloc() and free(). As soon as you “free” a chunk of memory, it can be used by other processes. In the shared pool, there really was no concept of freeing a chunk of memory. You would allocate memory, use it, and then stop using it. After a while, if that memory needed to be reused, Oracle would age out your chunk of memory. The problem with using only a shared pool is that one size doesn’t always fit all.

The large pool is used specifically by:

· Shared server connections, to allocate the UGA region in the SGA.

· Parallel execution of statements, to allow for the allocation of interprocess message buffers, which are used to coordinate the parallel query servers.

· Backup for RMAN disk I/O buffers in some cases.

As you can see, none of these memory allocations should be managed in an LRU buffer pool designed to manage small chunks of memory. With shared server connection memory, for example, once a session logs out, this memory is never going to be reused so it should be immediately returned to the pool. Also, shared server UGA memory allocation tends to be “large.” If you review the earlier examples with the SORT_AREA_RETAINED_SIZE or PGA_AGGREGATE_TARGET, you’ll remember that the UGA can grow very large and is definitely bigger than 4KB chunks. Putting shared server memory into the shared pool causes it to fragment into odd-sized pieces and, furthermore, you will find that large pieces of memory that will never be reused will age out memory that could be reused. This forces the database to do more work to rebuild that memory structure later.

The same is true for parallel query message buffers, since they are not LRU-manageable. They are allocated and can’t be freed until they are done being used. Once they have delivered their message, they are no longer needed and should be released immediately. With backup buffers, this applies to an even greater extent—they are large, and once Oracle is done using them, they should just “disappear.”

The large pool is not mandatory when using shared server connections, but it is highly recommended. If you don’t have a large pool and use a shared server connection, the allocations come out of the shared pool as they always did in Oracle 7.3 and before. This will definitely lead to degraded performance over some period of time and should be avoided. The large pool will default to some size if the parameter DBWR_IO_SLAVES or PARALLEL_MAX_SERVERS is set to some positive value. You should set the size of the large pool manually if you are using a feature that employs it. The default value typically will not be appropriate for your situation.

Java Pool

The Java pool was added in version 8.1.5 of Oracle to support running Java in the database. If you code a stored procedure in Java, Oracle will use this chunk of memory when processing that code. The parameter JAVA_POOL_SIZE is used to fix the amount of memory allocated to the Java pool for all session-specific Java code and data.

The Java pool is used in different ways, depending on the mode in which the Oracle server is running. In dedicated server mode, the Java pool includes the shared part of each Java class, which is actually used per session. These are basically the read-only parts (execution vectors, methods, etc.) and are about 4KB to 8KB per class. Thus, in dedicated server mode the total memory required for the Java pool is quite modest and can be determined based on the number of Java classes you’ll be using. Note that none of the per-session state is stored in the SGA in dedicated server mode, as this information is stored in the UGA and, as you will recall, the UGA is included in the PGA in dedicated server mode.

When connecting to Oracle using a shared server connection, the Java pool includes both of the following:

· The shared part of each Java class.

· Some of the UGA used for per-session state of each session, which is allocated from the Java pool within the SGA. The remainder of the UGA will be located as usual in the shared pool, or if the large pool is configured, it will be located there instead.

As the total size of the Java pool is fixed in Oracle9i and before, application developers will need to estimate the total requirement of their applications and multiply this estimate by the number of concurrent sessions they need to support. This number will dictate the overall size of the Java pool. Each Java UGA will grow or shrink as needed, but bear in mind that the pool must be sized such that all UGAs combined can fit in it at the same time. In Oracle 10g and above, this parameter may be modified, and the Java pool may grow and shrink over time without the database being restarted.

Streams Pool

The Streams pool is an SGA structure introduced in Oracle 10g. Oracle products that use the Streams pool include Oracle GoldenGate, XStream, Oracle Streams, Oracle Advanced Queuing, and Oracle Data Pump.

The size of the Streams pool is governed by setting the STREAMS_POOL_SIZE parameter. If SGA_TARGET is a non-zero value, then automatic SGA memory management will use the setting of STREAMS_POOL_SIZE as a minimum value for the Streams pool. If both theSGA_TARGET and STREAMS_POOL_SIZE are both set to zero, then up to 10 percent of the shared pool is used by the Streams pool.

Products that use the Streams pool will buffer queue messages. Instead of using permanent disk-based queues, with their attendant overhead, these features use in-memory queues. If these queues fill up, they eventually spill over to disk. If the Oracle instance with the memory queue fails for some reason, due to an instance failure (software crash), power failure, or whatever, these in-memory queues are rebuilt from the redo logs.

The Streams pool is only important in systems using features (such as GoldenGate, Streams, and so on) that need space in this memory area. In those environments, the STREAMS_POOL_SIZE should be set in order to avoid “stealing” 10 percent of the shared pool.

SGA Memory Management

The SGA memory-related parameters are classified into one of two areas:



At any time in Oracle 10g and above, you may query V$SGAINFO to see which components of the SGA are resizable:

EODA@ORA12CR1> select * from V$SGAINFO;

-------------------------------- ---------- --- ----------
Fixed SGA Size 2287336 No 0
Redo Buffers 4890624 No 0
Buffer Cache Size 67108864 Yes 0
Shared Pool Size 184549376 Yes 0
Large Pool Size 4194304 Yes 0
Java Pool Size 4194304 Yes 0
Streams Pool Size 0 Yes 0
Shared IO Pool Size 4194304 Yes 0
Data Transfer Cache Size 0 Yes 0
Granule Size 4194304 No 0
Maximum SGA Size 267227136 No 0
Startup overhead in Shared Pool 169940696 No 0
Free SGA Memory Available 0 0

13 rows selected.

For the SGA memory components that can be auto-tuned, there are three ways to manage these:

· Manual shared memory management: Setting all of the necessary pool and cache parameters.

· Starting in Oracle 10g and above, automatic shared (or SGA) memory management: Setting the SGA_TARGET parameter. By setting the SGA_TARGET parameter, you are allowing the instance to size and resize various SGA components.

· Starting in Oracle 11g and above, automatic memory management: Setting the MEMORY_TARGET parameter. By setting the MEMORY_TARGET parameter, you are allowing the instance to size and resize the SGA and PGA memory areas.

We will discuss each in turn.

Manual Shared Memory Management

If you require some degree of control over the auto-tunable areas of the SGA memory, then set MEMORY_TARGET and SGA_TARGET to zero. When MEMORY_TARGET is set to zero, this disables automatic memory management; when SGA_TARGET is set to zero, this disables automatic shared memory management.

Image Note In Oracle9i and before, only manual shared memory management was available—the parameter SGA_TARGET did not exist and the parameter SGA_MAX_SIZE specified the maximum size of the SGA.

When automatic memory management is disabled, you can manually set the size of your SGA by specifying sizes for the following memory parameters: DB_CACHE_SIZE, SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE, and STREAMS_POOL_SIZE. Each of these parameters have a default value that Oracle will use in the event you don’t explicitly set them. For example, the DB_CACHE_SIZE will be set to either 48MB or 4MB times the number of CPUs, whichever happens to be the largest for your system.

Here’s an example of an initialization file that enables manual shared memory management:


One aspect to be aware of with manual shared memory management in Oracle 11g Release 2 and higher, is that even when you explicitly turn off all automatic memory management (by setting MEMORY_TARGET and SGA_TARGET to zero), Oracle may still do some automatic reallocation of memory from the database buffer cache to the shared pool. When running out of space in the shared pool, Oracle will automatically add space to the shared pool to avoid the ORA-04031 “Unable to allocate %s bytes of shared memory” error.

You can view the automatic resizing of SGA memory by querying the V$MEMORY_RESIZE_OPS view. The OPER_MODE column will contain the value of either DEFERRED or IMMEDIATE for any automatic SGA resizing operations. When using manual SGA memory management, SGA automatic resizing is disabled for DEFERRED mode requests, but allowed for IMMEDIATE mode requests. Therefore, when you’re using manual shared memory management, you may see GROW and SHRINK operations (in the OPER_TYPE column) for IMMEDIATE auto-tuning requests for the DB_CACHE_SIZE and SHARED_POOL size memory areas.

A simple example will demonstrate what’s described in the prior paragraphs. First, I’ll create the DBMS_SHARED_POOL package so that I can pin objects in the shared pool:

SYS@ORA12CR1> @?/rdbms/admin/dbmspool
Session altered.
Package created.
Grant succeeded.
Session altered.

SYS@ORA12CR1> grant execute on dbms_shared_pool to eoda;
Grant succeeded.

Next, I’ll run some code that will quickly start filling up the shared pool (by pinning multiple PL/SQL procedures in the shared pool):

SYS@ORA12CR1> conn eoda/foo

EODA@ORA12CR1> declare
2 k varchar2(30);
3 ss varchar2(2000);
4 begin
5 for i in 1 .. 100000 loop
6 ss := 'create or replace procedure SP' || i || ' is
7 a number;
8 begin
9 a := 123456789012345678901234567890;
10 a := 123456789012345678901234567890;
11 a := 123456789012345678901234567890;
12 a := 123456789012345678901234567890;
13 a := 123456789012345678901234567890;
14 a := 123456789012345678901234567890;
15 a := 123456789012345678901234567890;
16 a := 123456789012345678901234567890;
17 a := 123456789012345678901234567890;
18 a := 123456789012345678901234567890;
19 a := 123456789012345678901234567890;
20 a := 123456789012345678901234567890;
21 a := 123456789012345678901234567890;
22 a := 123456789012345678901234567890;
23 a := 123456789012345678901234567890;
24 end;';
25 execute immediate ss;
26 k := 'SP' || i;
27 sys.dbms_shared_pool.keep(k);
28 end loop;
29 end;
30 /

Now, from another session, I’ll query the data dictionary to view the memory resize operations as Oracle shifts memory to the shared pool:

EODA@ORA12CR1> select component, parameter, oper_type, oper_mode from v$memory_resize_ops;
DEFAULT buffer cache db_cache_size SHRINK IMMEDIATE
shared pool shared_pool_size GROW IMMEDIATE
DEFAULT buffer cache db_cache_size SHRINK IMMEDIATE
DEFAULT buffer cache db_cache_size SHRINK IMMEDIATE
shared pool shared_pool_size GROW IMMEDIATE
DEFAULT buffer cache db_cache_size SHRINK IMMEDIATE
shared pool shared_pool_size GROW IMMEDIATE

So just be aware of the possibility that in 11g Release 2 and higher, Oracle might perform some degree of automatic SGA memory reallocation, even when you’ve turned the feature off. As earlier noted, Oracle does this to keep processes from running out of space in the shared pool.

Image Tip See MOS note 1269139.1 for further details on automatic SGA resizing when using manual shared memory management.

Automatic Shared Memory Management

Under automatic shared memory management, the primary parameter for sizing the total auto-tuned components is SGA_TARGET, which may be dynamically sized while the database is up and running, up to the setting of the SGA_MAX_SIZE parameter. This defaults to be equal to theSGA_TARGET, so if you plan on increasing the SGA_TARGET, you must have set the SGA_MAX_SIZE larger before starting the database instance. The database will use the SGA_TARGET value, minus the size of any of the other manually sized components such as theDB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE, and so on, and use that amount of memory to size the default buffer pool, shared pool, large pool, and Java pool. Dynamically at runtime, the instance will allocate and reallocate memory among those four memory areas as needed. Instead of returning an ORA-04031 “Unable to allocate N bytes of shared memory” error to a user when the shared pool runs out of memory, the instance could instead choose to shrink the buffer cache by some number of megabytes (a granule size) and increase the shared pool by that amount.

Image Note To use automatic shared memory management, the parameter STATISTICS_LEVEL must be set to TYPICAL (default value) or ALL. If statistics collection is not enabled, the database will not have the historical information needed to make the necessary sizing decisions.

Over time, as the memory needs of the instance are ascertained, the size of the various SGA components would become more or less fixed in size. The database also remembers the sizes of these four components across database startup and shutdown so that it doesn’t have to start all over again figuring out the right size for your instance each time. It does this via four double-underscore parameters: __db_cache_size, __java_pool_size, __large_pool_size, and __shared_pool_size. During a normal or immediate shutdown, the database will record these values to the stored parameter file and use them at startup to set the default sizes of each area.

Image Note This last feature, of storing the recommended values for the pools, only works if you are using stored parameter files (also known as spfile).

Additionally, if you know you want a certain minimum value to be used for one of the five areas, you may set that parameter in addition to setting the SGA_TARGET. The instance will use your setting as the lower bound, or the smallest size that particular area may be.

Automatic Memory Management

Starting in Oracle 11g Release 1 and above, the database also offers automatic memory management—sort of a one stop shop for all of your memory settings. With Oracle 10g and automatic SGA memory management—the DBA could get away with just two major memory settings—thePGA_AGGREGATE_TARGET and the SGA_TARGET. The database would automatically allocate and reallocate memory chunks within each as described earlier. In Oracle 11g, the DBA can now get away with setting a single memory parameter—the MEMORY_TARGET. ThisMEMORY_TARGET represents the total amount of memory the combined SGA and PGA allocations should strive to stay within (remember, the PGA memory can be somewhat uncontrollable). The database will dynamically determine what the proper SGA size is and what the proper PGA size is, based on workload history). Over time, as the workload performed in the database changes, the allocations to the SGA and PGA will change as well. For example, if you are heavy OLTP (Online Transaction Processing) during the day and heavy batch processing at night, you might discover that the daytime SGA is much larger than the PGA and the nighttime SGA is much smaller than the PGA. This would reflect the different memory needs of these two application types.

Image Note Before implementing automatic memory management, consider checking for any MOS related operating system specific notes such as 749851.1 (for Linux) and 1399274.1 (for Solaris).

Just as with automatic SGA memory management, the DBA can set up lower bounds for the size of each memory area by setting the SGA_TARGET and PGA_AGGREGATE_TARGET, or the lower bound of each of the pools in the SGA by setting their values to that lower bound. The database will remember the optimal settings for the pools and the SGA and PGA in the stored parameter file if you are using one. For example, on one of my test systems I’ve set:

· memory_target = 756m

· sga_target = 256m

· pga_aggregate_target = 256m

The stored parameter file for that database currently has:

SYS@ORA12CR1> create pfile='/tmp/pfile' from spfile;
File created.

SYS@ORA12CR1> !cat /tmp/pfile;
ORA12CR1.__oracle_base='/orahome/app/oracle'#ORACLE_BASE set from environment

As you can see, the double-underscore parameters in bold now include the __sga_target and __pga_aggregate_target settings as well as the various pools. These values are derived based on the last three memory parameters shown in bold, as well as the observed server workload. In this fashion, Oracle will remember your last optimal SGA/PGA settings and use them upon the next restart.


In this chapter, we took a look at the Oracle memory structures. We started at the process and session level, examining the PGA and UGA and their relationship. We saw how the mode in which we connect to Oracle dictates how memory is organized. A dedicated server connection implies more memory used in the server process than under a shared server connection, but that use of a shared server connection implies there will be the need for a significantly larger SGA. Then we discussed the main structures of the SGA itself. We discovered the differences between the shared pool and the large pool, and looked at why we might want a large pool to “save” our shared pool. We covered the Java pool and how it is used under various conditions, and we looked at the block buffer cache and how that can be subdivided into smaller, more focused pools.

Now we are ready to move on to the physical processes that make up the rest of an Oracle instance.