When you create a database, Oracle creates a rollback segment called SYSTEM in the system tablespace. The system rollback segment is only used for transactions that occur inside the system tablespace. The system rollback segment's main purpose is to handle the rollback generated for DDL statements. DDL statements cause updates to be applied to the data dictionary tables in the system tablespace.

It is possible for the system rollback segment to be used for non-data dictionary tables, but only if those tables are created inside the system tablespace. Application objects should not be stored in the system tablespace. Create separate tablespaces for application tables and indexes.

Why Is Oracle's New Recommendation to have All Rollback Segments have a Minimum of Twenty Extents?

Rollback segments are very dynamic in nature. They allocate space when required and deallocate it when it is no longer needed. If the extents are large in size and few in number, the less granular these space allocations and deallocations become.

A 200 megabyte rollback segment which consists of 2 100 megabyte segments would allocate another 100 megabyte each time it requires additional space. This automatically increases the rollback segment size by 50% and may acquire much more space than is actually needed. If the rollback consisted of twenty 10 megabyte extents, additional extents would be acquired in 10 megabyte chunks.

When a rollback segment consists of twenty or more extents, any single change in the number of extents will not move the total size of the rollback segment by more than 5%, resulting in a much smoother allocation and deallocation of space. It has been found that rollback segments with a minimum extent greater than 20 have a diminishing return on performance.

Using V$WAITSTAT to size Rollback Segments

The V$WAITSTAT performance table can be queried to prevent rollback segment contention between user processes. The following query will display the number of waits since startup:

SELECT class, count FROM v$waitstat WHERE class = '%undo%';

Any non-zero value in the count column indicates rollback segment header contention.

Finding Problem Queries

Execute the following query to identify the SQL responsible for the most disk reads:

SELECT disk_reads, executions, disk_reads/executions, sql_text FROM v$sqlarea WHERE disk_reads > 5000 ORDER BY disk_reads;

Execute the following query to identify the SQL responsible for the most buffer hits:

SELECT buffer_gets, executions, buffer_gets/executions, sql_text FROM v$sqlarea WHERE buffer_gets > 100000 ORDER BY buffer_gets;

When I was first starting as a DBA, an Oracle consultant was kind enough to send me an entire directory's worth of tuning scripts. These two queries were contained in the directory along with a couple of dozen others that provided buffer hit ratios and other performance related statistical information.

I never knew that that these two queries were the most important ones in the bunch. That was until I attended an IOUG presentation given by Richard Niemiec from TUSC. Rich focused on the benefits that these two queries provide, the light bulb finally came on and I now use them religiously.

It's common knowledge that poorly performing SQL is responsible for the majority of database performance problems. The first query returns SQL statements responsible for generating disk reads greater than 5,000 while the second query returns SQL statements responsible for generating buffer reads greater than 100,000.

These are good numbers to start with and you can adjust them according to the size of the system you are tuning. I modified the original queries to divide the number of disk and buffer reads by the number of statement executions. If a statement is generating 1,000,000 disk reads but is executed 500,000 times, it probably doesn't need tuning.

Heavy disk reads per statement execution usually means a lack of proper indexing. Heavy buffer reads usually means the exact opposite, indexes are being used when they shouldn't be.

Pinning Objects in the Shared Pool

Loading the same objects into the shared pool again and again and shared pool fragmentation are common problems in many Oracle databases. The keys to tuning the shared pool are:

1. Identifying the objects being reloaded into the shared pool time and time again:

 

 
 
 

SELECT owner, name||' - '||type name, loads , sharable_mem FROM v$db_object_cache WHERE loads > 1 AND type IN ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE') ORDER BY loads DESC;

2. Identifying large objects that may not be able to be loaded into a fragmented shared pool:

 

 
 
 

SELECT owner, name||' - '||type name, sharable_mem FROM v$db_object_cache WHERE sharable_mem > 10000 AND type IN ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')ORDER BY sharable_mem DESC;

Exit to : Oracle Hints and Tips