Free Oracle Tips   HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Oracle Table Settings for OPS Oracle Tips by Burleson

It is vital to the performance of an OPS database that the DBA properly understand and define the multiple freelist groups that are required by OPS. With Oracle Parallel Server, internal segment header structures become even more complex. Oracle provides an additional OPS table parameter called freelist_groups. With multiple freelist groups, extra segment header blocks are defined, one for each freelist group that is defined for the table. For example, assume the following table definition:

L 13-3

Here we see an OPS table defined with 20 freelist groups, meaning that the OPS architecture has 20 instances accessing a single database. Within each freelist group, we see five freelists, indicating that up to five concurrent tasks may be inserting, updating, or deleting rows from this table.

Returning to our example, when the customer table is allocated, Oracle will reserve 20 blocks at the front of the table for space management purposes. Oracle allocates block 1 for the segment header, block 2 for freelist group 1, block 3 for freelist group 2, and so on.

You must define one freelist group for each instance that will be connecting to the database, and enough freelists within each group to support the high-water mark for updates and inserts. However, multiple freelist groups also have a problem relating to the reuse of space within the freelists for the tables and indexes. Because each freelist group is an isolated entity, Oracle is not able to share freelists between the Oracle instances.

Remember, each one of the OPS instances attaches to a separate freelist group, and free blocks are allocated independently of one another within these freelist groups.

Most DBAs begin to run into a problem in the Oracle Parallel Server environment when standard purge routines are executed. In most large OPS environments, it's not uncommon to periodically run purges of transaction information. One of the most common mistakes seen in OPS environments is where the purge routines are run from only one of the instances. When the purge routines are run from only a single instance in an OPS configuration, only that freelist group will receive the empty blocks. The other freelist groups for the table will not get these blocks, and consequently will not be able to access these free blocks to use for later inserts into the database.

This is the same kind of sparse table phenomenon that we discussed in Chapter 10. Again, the remedy for the sparse table phenomenon is to make sure that you parallelize all of your deletes to the same degree for which you have parallelized any insert or update statements. In other words, if you have three instances doing inserts into the customer table, the customer purge routines should be parallelized so that one-third of the purge runs on each one of the three instances. This will keep the freelists in balance, and will avoid having a largely empty table extending unnecessarily into new free space.

Now, let's take a closer look at the Oracle segment header, as shown in Figure 13-4.

Figure 13-112: Segment header details for an OPS table with two freelist groups

In an Oracle Parallel Server environment with multiple freelist groups, there are three types of freelists in each segment header:

•  The master freelist (aka the common pool)  The master freelist exists for all Oracle segments and resides in the segment header in the first block of the table. With OPS, you have a super master freelist on block 1 of the segment in addition to the master freelists that are dedicated to each freelist group.

• The process freelist (aka multiple freelists)  The process freelist is enabled by using the freelist parameter in the STORAGE clause when creating the table or index. A process freelist will acquire blocks in two ways. A process freelist can get blocks when a row delete occurs, or when a freelist merge occurs. A freelist merge is a process whereby Oracle moves blocks from the master freelist to the process freelist, usually in chunks of five blocks.
  If the freelist_groups parameter is not used, the process freelist will exist in the segment block header for the table, right next to the master freelist. As you know, multiple process freelists can be defined whenever concurrent updates are expected against the table. Process freelists greatly help reduce contention for freelists in a non-OPS environment, since each update process may read its own freelist. The downside is that each freelist exists independently of the other freelists, and blocks that appear on one freelist are not shared with the other freelists.

• The transaction freelist (not shown)  The transaction freelist is used by an in-flight program, which performs delete or insert operations. Unlike the master and process freelists, transaction freelists are allocated in the segment header on an as-needed basis. If there is no room in the segment header for the transaction freelist, the task will wait and incur a “wait on segment header.” After the task has ended or committed, the entries are transferred from the transaction freelist to the process freelist in a FIFO manner, with the most recently freed blocks being added to the head of the freelist chain.

While having multiple freelists minimizes contention in regular databases, with OPS a freelist group is used to minimize contention for space within a shared table that is being updated by multiple instances.

As of Oracle OPS release 7.3 and beyond, it is possible to allocate table extents that are “dedicated” to a specific instance. Oracle8 gives you more control over free space if you specifically allocate extents to instances, and it has the side benefit of reducing contention for resources. Consider the following example where we allocate a data file for the exclusive use of instance 10:

L 13-4

Here we see that the customer table has increased by 100MB, and this additional space is exclusively dedicated to instance number ten. Automatic table extension will never dedicate rows to an instance, and the space will go onto the master freelist.

Next, let's get deeper and look at how the OPS environment is tuned.

This is an excerpt from "Oracle High Performance tuning with STATSPACK" by Oracle Press.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.