Data Warehouse Freelists and Oracle Parallel Server Contention

Oracle Data Warehouse Tips by Burleson Consulting

Freelists And Oracle Parallel Server Contention

Freelists are especially important for Oracle data warehouses that experience a high volume of localized update activity. A freelist is the parameter used when more than one concurrent process is expected to access a table. Oracle keeps one freelist for each table in memory, and uses the freelist in order to determine what database block to use when an SQL INSERT occurs. When a row is added, the freelist is locked. If more than one concurrent process is attempting to insert into your table, one of the processes may need to wait until the freelist has been released by the previous task. To see if adding a freelist to a table will improve performance, you will need to evaluate how often Oracle has to wait for a freelist. Fortunately, Oracle keeps a V$ table called V$WAITSTAT for this purpose. The following query example tells you how many times Oracle has waited for a freelist to become available. As you can see from the following query, Oracle does not tell you which freelists are experiencing the contention problems:

SELECT CLASS, COUNT

FROM V$WAITSTAT
WHERE CLASS = ?free list?;

    CLASS                           COUNT
---------------                  ------------
free list                          83

Here, we see that Oracle had to wait 83 times for a table freelist to become available. This could represent a wait of 83 times on the same table or perhaps a single wait for 83 separate tables. We have no idea. While 83 may seem to be a large number, remember that Oracle may perform hundreds of I/Os each second, so 83 may be quite insignificant to the overall system. In any case, if you suspect that you know which table?s freelist is having the contention, the table can be exported, dropped, and redefined to have more freelist. While an extra freelist consumes more of Oracle?s memory, additional freelists can help the throughput on tables that have lots of inserts. Generally, you should define extra freelists only on those tables that have many concurrent update operations. Now, let?s take a look at some table definitions and see if we can infer the type of activity that will be taking place against the tables. Listings 7.1 and 7.2 each present a table definition.

Listing 7.1 Table definition--Example 1.

CREATE TABLE ORDER (
order_nbr number,
order_date date)
STORAGE ( PCTFREE 10 PCTUSED 40 FREELISTS 3);

Here, we can infer that the table has very few updates that cause the row length to increase because PCTFREE is only 10 percent. We can also infer that this table will have a great deal of delete activity, because PCTUSED is at 40 percent, thereby preventing immediate reuse of database blocks as rows are deleted. This table must also have a lot of insert activity, because FREELISTS is set to three, indicating that up to three concurrent processes will be inserting into the table.

Listing 7.2 Table definition--Example 2.

CREATE TABLE ITEM (
   item_nbr                    number,
   item_name                   varchar(20),
   item_description            varchar(50),
   current_item_status         varchar(200) )
STORAGE ( PCTFREE 10 PCTUSED 90 FREELISTS 1);

Here, we can infer that update operations are frequent and will probably increase the size of the varchar columns, because PCTFREE is set to reserve 10 percent of each block for row expansion. We can also infer that this table has few deletes, because PCTUSED is set to 90, making efficient use of the database blocks. Assuming that there will not be very many deletes, these blocks would become constantly re-added to the freelist.

The V$LOCK_ACTIVITY View

The V$:LOCK_ACTIVITY view is a very good way to determine if you have reached the maximum lock convert rate for your DLM. Because the maximum lock convert rate is unique to each vendor's DLM, you need to compare the results from V$:LOCK_ACTIVITY with the maximum values in your OS vendor's documentation for their DLM. Regardless, if the maximum lock convert rate has been reached, you will need to repartition the application to balance alike transactions into common instances.

The V$SYSSTAT View

The V$SYSSTAT view can be used to determine whether lock converts are being performed too often. Excessive lock convert rates usually mean there is contention for a common resource within the database. This resource may be a commonly updated table. For example, inventory management systems often utilize one-of-a-kind (OOAK) rows. An OOAK row may be used to keep the order number of the last order, and all application tasks must increment this row when a new order is placed. This type of architecture forces each parallel instance to single-thread all requests for this resource. But how do we identify these types of database resources?

Just as the buffer hit ratio measures contention for data blocks, the lock hit ratio can be used to identify excessive lock conversion by the DLM. The lock hit ratio should generally be above 90 percent, and if it falls below 90 percent, you should look for sources of data contention. Here is the SQL to determine the lock hit ratio for Oracle parallel server:

SELECT
   (a.value - b.value)/(a.value)
FROM
   v$sysstat a, v$sysstat b
WHERE
   a.name = ?consistent gets?
AND
   b.name = ?global lock converts (async)?;

If you suspect that there may be data contention, there are several remedies. For example:

* If you identify a specific table as a source of contention, try increasing the freelists for the table.

* If you identify an index as the source of contention, try localizing all access to the index on a single instance.

But, how can we identify the source of contention? Oracle parallel server provides a view called V$PING to show lock conversions. We start by querying the V$PING view to see if there are any data files experiencing a high degree on lock conversions, as follows:

SELECT
   substr(name,1,10),
   file#,
   class#,
   max(xnc)
FROM
   v$ping
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3;

You will receive an output that looks similar to Table 7.1.

Name	File #	Class #	Max (XNC)
Customer	13	1	556
Customer	13	4	32
Item	6	1	11
Item	3	4	32
Order	16	1	33456

Table 7.1 Querying the V$PING view.

Here, we see that File 16 may have a problem with excessive lock conversions. To further investigate, return to V$PING, and get the sums for File 16, as follows:

SELECT *
FROM
v$ping
WHERE
file#=16
ORDER BY block#;

Now, we can see additional detail about the contents of File 16, as shown in Table 7.2.

File #	Block #	Stat	XNC	Class #	Name	Kind
16	11	XCUR	5	1	ORDER	Table
16	12	XCUR	33456	1	ORDER	Table
16	13	XCUR	12	1	ORDER	Table

Table 7.2 Viewing additional file details.

From this output, we can clearly see that Block 12 is the source of our contention.

The following query against the ORDER table will reveal the contents of the rows in the data block, as shown in Table 7.3. Remember, data blocks are numbered in hex, so we convert Block 12 to a hex(c).

SELECT
   rowid,
   order_number,
   customer_number
FROM
   ORDER
WHERE
chattorowid(rowid) LIKE ?0000000C%;

ROWID ORDER_NUMBER      CUSTOMER_NUMBER
0000000C.0000.0008      1212 73
0000000C.0000.0008      1213 73
0000000C.0000.0008      1214 73

Table 7.3 Viewing row contents.

In Table 7.3, we see that the lock conversion relates to orders placed by customer number 73. Other than a random coincidence, we can assume that there may be freelist contention in the ORDER table as new orders are added to the database. Adding new freelists will allow more concurrency during SQL INSERT operations, and the value for freelists should be reset to the maximum number of end users who are expected to be inserting an ORDER row at any given time. Unfortunately, Oracle does not allow the dynamic modification of freelists, because they are physically stored in each data block. So, the only alternative is to drop and re-create the table with more freelists in each block header. Following is the SQL used to drop and re-create the ORDER table:

CREATE TABLE ORDER_DUMMY
STORAGE (freelists 10)
AS
SELECT * FROM ORDER;

DROP TABLE ORDER;

RENAME ORDER_DUMMY TO ORDER;