To describe which are the how init.ora parameters that has more affect
in the database performance.
SCOPE & APPLICATION
This article detail information on how certain init.ora parameters affect
How init.ora parameters affect performance.
The parameters listed below are what we consider to be the top init.ora
parameters which per se, affect database tuning.
This parameter is available in all versions of Oracle, and is measured in
Oracle blocks. The value of this parameter is very important for storing data
into memory as users are requesting information from the system.
DB_Block_Buffers are the number of buffers in the SGA cache that will be
available for user data to be stored in memory. The size of the buffer cache,
which is used to cache db blocks in the SGA, is specified by this parameter.
Because the data is cached, this reduces the amount of physical I/O.
In turn, the setting of this parameter has a large effect on the buffer cache
hit ratio, which you generally want to be above 90%. The hit ratio can be
dynamically determined based on the following query:
‘physical reads’, value,0))/
(sum(decode(name, ‘db block gets’, value,0))+
(sum(decode(name, ‘consistent gets’, value, 0))))))*100),2)
|| ‘%’ “Buffer Cache Hit Ratio”
The result of this query would be similar to the following:
Buffer Cache Hit Ratio:
If the hit ratio is below 90%, then it would be advisable to increase
DB_Block_Buffers until you obtain a hit ration above 90%. The implications of
the DB_Block_Buffers being set too low are that the least recently used data
will be flushed from memory. So guess what? If another query needs this data,
back out to disk we go. This will cause I/O and CPU resources to be used.
If the value is set too high, swapping will began to occur on the OS and the
system may come to a halt.
See Note 62172.1 for further information on Tuning the Database Buffer Cache.
The Shared_Pool_Size is specific to all versions of Oracle and is measured in
bytes. This is the memory that is allocated to Oracle for areas like the data
dictionary, stored procedures, and statements. Comprising a large part of the
SGA, the Shared_Pool_Size is comprised of the dictionary cache and library
cache, and just like DB_Block_Buffers above, should not be set too low or too
The Shared_Pool_Size can be monitored through the data dictionary cache and
the library cache. Both should be continuously monitored for an appropriate
When a user processes a SQL statement, Oracle references the data dictionary
several times. Reducing physical disk IO is very important, so the more
information that is stored in memory, the less that needs to be read from disk.
The data dictionary cache is very important in this respect because this is
where the data dictionary components are buffered (think of it as a print
buffer). The data dictionary component can be monitored via v$rowcache using
the following select statement:
select (1-(sum(getmisses)/sum(gets))) * 100 “Hit Ratio”
You should see output similar to the following:
You should aim to have this value above 90%. The exception is when the
database is first started, the value will be somewhere around 85%.
The library cache consists of shared SQL and PL/SQL areas. When SQL is
executed, the statement has to be parsed (or taken apart for further
clarification). The library cache reduces the overhead of this by maintaining
parsed SQL and PL/SQL in the library cache. When there is a
subsequent execution of this statement, there will not be the need to re-parse
the statement. Essentially, you are reducing the work of the database. If,
for example, the customer is on an OLTP system which consistently issues the
same SQL, performance is enhanced. Of course, using bind variables to have sharable
versions of the statements.
We have discussed having the Shared_Pool_Size set too low, but adverse effects
may comes from setting the Shared_Pool_Size too high as well. Having the
shared pool set too large increase the probability of latch contention when the
database needs to find a free peace of memory to load a new statement.
You can query v$sgastat to show the available free memory. This will tell you
memory is being wasted. As an example, let’s look at the following problem:
select name, bytes/1024/1024 “Size in MB”
where name=’free memory’;
You should see output similar to the following:
NAME Size in MB
Free memory 39.6002884
What this return would tell you is that there is 39M of free memory in the
shared pool, which would mean that the shared pool is being under utilized.
If the shared pool was 70M, over half of it would be under utilized. This
memory could be allocated elsewhere.
See Note 62143.1 for further reference on tuning the shared pool.
The SORT_AREA_SIZE is an oft-misunderstood init.ora parameter. Many believe
that this parameter is an amount allocated for the users as a whole in terms
of sorting. Not true, the SORT_AREA_SIZE is what Oracle allocates per
user process for sorting data. Unlike the two previous parameters, the
SORT_AREA_SIZE is outside of the SGA. If the sort_area_size is too small, the
process has to then be sorted on disk in the user’s temporary tablespace.
Where does this lead back? Physical disk IO.
The SQL statements that typically generate sort activity are order by and
group by clauses. Activities such as create index generate sort activity as
Because tuning in memory is always preferred over sorting to disk,
continuously monitoring the SORT_AREA_SIZE is always a good idea. Having to
sort in the temporary tablespace means accessing temporary segments. Accessing
temporary segments artificially decreases the hit ratio, so should be avoided in
OLTP applications. Having a large sort_area_size value will open the possibility
that each user uses a large amount of memory in the system generating swapping
which in turn decrease the performance as well.
BEWARE!!!-The SORT_AREA_SIZE can be set within an individual session, if they
have ALTER SESSION privilege. What does this mean? A single use can run an
ENTIRE system performance.
You can find more detail on How to determine an optimal value for this parameter
in Note 109907.1
Another oft-misunderstood init.ora parameter, DBWR_IO_SLAVES was introduced in
Oracle 8. This parameter was to replace DB_WRITERS that was available before
Oracle 8. The truth is that DB_WRITERS is still available in Oracle 8 under
the name of DB_WRITER_PROCESSES as there are still a number of issues to work
out with DBWR_IO_SLAVES.
DBWR_IO_SLAVES was introduced in Oracle 8 to allow for asynchronous I/O of
slave writer processes, if it is available on the OS Platform. As the name
implies, there is a master process and slaves processes, which are defined by
this parameter setting.
There is a lot of information to know regarding DB_WRITERS and DBWR_IO_SLAVES.
One of the most important things to realize is that DB_WRITERS and
DBWR_IO_SLAVES cannot co-exist. There are issues with DBWR_IO_SLAVES still.
Note 97291.1 details the difference between both implementations.
This parameter, available in all versions of Oracle, specifies the number of
rollback segments that are explicitly brought online during instance startup.
The rollback segment must already exist, or otherwise, Oracle will give you an
ora-1534 regarding the rollback segment. The instance will then mount, but
Rollback segments are areas of the database that record transactions should
the transactions need to be rolled back or (in Windows terms, ‘undo’). The
rollback segments consists of contiguous or adjoined extents. The extents
are used in a circular (round-robin) order, and move from one extent to the
next after the current extent is full.
Rollback segments are important to the database, as they are used to provide
read consistency, undo of transactions, and recovery. Read consistency is
very important if you look at this from a business perspective. If user1 is
running a query at the same time user2 is changing the data inside of a
particular table, but user2 has incorrect information that he has yet to
notice, you wouldn’t want user1 seeing this data yet. So user1 can only see
this data once it has been committed. There are numerous scenarios where this
is important. Rolling back is of course if you make a mistake-the data has
not been automatically committed. You can “rollback” and start all over
I’ve given a brief rundown of rbs, but what size should they be and how many
should you have? As with every answer, it is relative and depends entirely on
the normal transaction level of your database. When sizing the rbs
extents, Oracle generally has a recommendation for efficiency reasons that the
extent sizes (Initial & Next).
In determining the number of rollback segments, the goal is to avoid
contention among the rollback segments. Every transaction is stored in a
transaction table in the header of the RBS. Because every transaction has to
update this table, contention is a possibility. A rule of thumb would be not
to have more than one user using a rollback segment at any given time. Oracle
recommends four concurrent transactions per rollback segment, but this to is
You can look at v$waitstat table in order to monitor contention between the
rollback segments. The following query would be useful in examining
contention among the rollback segments:
Select a. name, b.extents, b.rssize, b.xacts, b.waits,
b. gets, optsize, status
From v$rollname a, v$rollstat b
Where a.usn = b.usn;
You should see results similar to the following:
NAME EXTENTS RSSIZE XACTS WAITS GETS OPTSIZE STATUS
SYSTEM 4 540672 1 0 51 ONLINE
RB1 2 10240000 0 0 427 10240000 ONLINE
RB2 2 10240000 1 0 425 10240000 ONLINE
RB3 2 10240000 1 0 422 10240000 ONLINE
RB4 2 10240000 0 0 421 10240000 ONLINE
If you run the above query, and the column “xacts” (which are active
transactions) are continually above 1 for the RBS’s, you will probably need to
increase the number of rollback segments to avoid the possibility of
contention. If the waits are greater than zero, and this is normal
processing, there may also be a need to increase the number of rollback segments.
There is a lot more information that could be discussed for rollback segments,
much too much for this page, but the following are useful documents:
Note 10579.1 How Many Rollback Segments To Have
Note 62005.1 Creating, Optimizing, and Understanding Rollback Segments
Another one of the init.ora parameters which affect sorting on the system,
SORT_AREA_RETAINED_SIZE is the size that the SORT_AREA_SIZE is actually
reduced to once the sort is complete. This parameter should be set less than
or equal to the parameter SORT_AREA_SIZE.
The SORT_AREA_RETAINED_SIZE complements the SORT_AREA_SIZE because memory is
held for the entire duration of the sort, and it allows multiple sort areas of
memory if there are more than one sort within the same sql statement. On the
downside, this memory is released not when the statement is finished, but when
the session is exited. If you compare this to SORT_AREA_SIZE, the memory
allocated to the SORT_AREA_SIZE gets released when the last row has been
fetched from within the sort.
The SORT_AREA_RETAINED_SIZE is, as any memory parameter, limited by physical
memory. The recommended setting for both this parameter and SORT_AREA_SIZE is
Obsolete in Oracle 8i, this parameter is used to enable or disable to
compilation of statistics, which measures the effect of increasing the number
of buffers in the buffer cache in the SGA. This allows the user to do an
alter system rather than setting the changing the DB_BLOCK_BUFFERS in the SGA.
The logic behind this parameter is that the system really has to be shut down
to change the DB_BLOCK_BUFFERS. The statistics are capture in the X$KCBRBH table.
Setting this value greater than zero specifies the additional number of
DB_BLOCK_BUFFERS, or simulates this additional setting.
That sounds a good tool to measure performance, but this parameter has been known
to cause problems. Oracle does not recommend to use this parameter in a production
environment since it cause overhead to store the statistics related to the buffers
used by the database.
Specifies the allocation of a certain amount of the shared pool for large
objects. In effects, you are reserving some portion of the shared pool. The
default setting of this parameter is 5% of your shared_pool_size setting.
This parameter is measured in bytes.
Setting this value to ensures that the majority of the area within the shared
pool is not being used for large objects, but rather in a “separate area”
Note 106285.1 – TROUBLESHOOTING GUIDE: Common Performance Tuning Issues
Note 62161.1 – Systemwide Tuning using UTLESTAT Reports in Oracle7/8
Bookmarks Admin Profile Feedback Sign Out Help