High-Performance Oracle Database Applications:Physical Performance Design For Oracle Databases

Table of Contents

PCTUSED tells Oracle when it is acceptable to insert rows onto a database block. For example, if PCTUSED is set to 80, Oracle will not allow rows to be added to any database blocks unless they are less than 80 percent full. Oracle will try to keep a database block at least PCTUSED full. As rows are deleted from a table, those database blocks that fall below PCTUSED will become eligible to receive new rows. Since the default is 40 percent, any database block that is less than 40 percent full may have rows added to that block. PCTUSED is a tradeoff between efficient table utilization and performance. If PCTUSED is set to a large value, Oracle will keep the database blocks more full, making more efficient use of storage. However, it does so at the expense of performance, especially for INSERT and UPDATE operations. For very high performance, PCTUSED could be set to a lower value, say 60. This will keep Oracle from constantly moving database blocks onto the FREELIST as rows are deleted, thereby improving performance. Again, PCTUSED plays an essential role for very volatile tables with many DELETE operations.

NOTE: Note that PCTFREE and PCTUSED work together. The sum of these values can never equal more than 100, and it is usually wise to sum the values at a limit of 90 because of the overhead that is found on each database block.

INITTRANS specifies the initial number of transactions that are allocated within each block. MAXTRANS is a value that specifies the maximum number of concurrent transactions that can update a block.

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. As 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 had 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, it 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 FREELISTS. 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 will 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.

Example 1:

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

Here we can infer the table has very few updates that cause the row length to increase since PCTFREE is only 10 percent. We can also infer that this table will have a great deal of delete activity, since 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, since FREELISTS is set to 3, indicating that up to 3 concurrent processes will be inserting into the table.

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, since 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 FREELISTS.

Referential Integrity And Performance

Before most relational database-supported referential integrity, it was the responsibility of the programmer to guarantee the maintenance of data relationships and business rules. While this was fine for the applications, the risk came into play when ad hoc updated SQL commands were issued using Oracle’s SQL*Plus. With these ad hoc update tools, the programmatic SQL could be easily bypassed, skipping the business rules and creating logical corruption.

Relational database systems such as Oracle allow for the control of business rules with “constraints.” These RI rules are used to enforce one-to-many and many-to-many relationships within the relational tables. For example, RI would ensure that a row in the CUSTOMER table could not be deleted if orders for that customer exist in the ORDER table. (See Figure 2.2.)

Figure 2.2 An overview of referential integrity.

Table of Contents