Oracle High Performance Tuning for 9i and 10g

I like to make a distinction between physical block attributes and extent attributes. A block is a subset of an extent. Blocks contain data values. Extents are added to datafiles when a database object such as a table requires more physical datafile space and none can be allocated from existing unused extents. Extents can be sized and blocks can have specific allocation behaviors.

In this section we will discuss what is known as either the segments clause or the physical attributes clause. We will cover the storage clause later in this chapter. Physical attributes affect the physical structure of blocks and apply to objects such as tables, indexes, or clusters. Some factors apply to all object types and some apply to specific object types

14.7.1 What is in a Block?

A data block is the smallest unit of storage in a database. The block format is the same regardless of the type of object the data block contains. In general, an Oracle database block is divided into specific parts. These parts could be described vaguely as follows:

• Fixed Header.   The fixed header space contains general block information such as block address and segment type. The fixed block header is as shown in Figure 14.1 and size may vary by segment type.

  Figure 14.1: Block Fixed Header

• Table Directory.   The table directory space stores information about the tables and rows stored in the block as shown in Figure 14.2. Only clusters allow two or more tables to share a block.

  Figure 14.2: Block Table Directory

• Row Directory.   The row directory area contains information about all rows in the block as shown in Figure 14.3. Each row requires a few bytes of overhead. The row directory grows as rows are added to the block.

  Figure 14.3: Block Row Directory

• Free Space.   The free space area is the amount of space available either for insertion of new rows into the block or updates to existing rows as shown in Figure 14.4.

  Figure 14.4: Block Free Space

• Row Data.   The row data space contains table or index data as shown in Figure 14.5.

  Figure 14.5: Block Row Data

14.7.2 Block Space Management

Two parameters or settings are used to control how physical space is used in a block. PCTUSED determines how much space is required before rows can be inserted into a block. PCTFREE determines how much space is left free in a block when that block is first added to.

Default values for PCTUSED and PCTFREE are 40% and 10%, respectively.

• PCTUSED.   When a block has rows deleted such that its capacity falls below 40%, it is placed onto a list of blocks available for insertion of new rows.

  Tip

  Indexes do not reclaim space at the leaf block level where the index column values are stored. Thus PCTUSED does not apply to indexes and index-organized tables. This makes it essential to occasionally rebuild BTree indexes if their tables are subject to heavy deletion activity.

• PCTFREE.   10% of space is reserved in every block as empty. When a row is updated and a row's length is increased that free space is used by the extra bytes required.

By no means do the Oracle Database default values have to be used. In fact it is advisable to change these values for certain types of combinations of DML activities on table and indexes.

Let's go through the process of tuning the block structure in the Accounts schema. My Accounts schema is a heavily concurrent DML intensive database. I will also adjust the approach assuming that an application would be running against this schema, including some reporting based on content in tables. First of all take a look at Figure 14.6. Figure 14.6 shows all tables with rows and blocks occupied after statistics generation.

Figure 14.6: Accounts Schema Current Row and Block Numbers

The number of rows in a table and the number of blocks occupied are important with respect to both DML and SELECT activity. Searching fewer rows is faster. Searching less physical space is also better. On the contrary, a few rows being accessed by many sessions using DML statements, all at once, can cause performance problems. Accessing a lot of rows in a single block is good for data retrieval but not for concurrent changes.

Possible changes for PCTUSED and PCTFREE parameters are shown in Figure 14.7. Index block structures must be changed in the same way that their respective tables are changed. This is because indexes undergo the same DML and SELECT activity as their respective tables.

Figure 14.7: Changes to Block Structure Space Usage

What exactly have I done in Figure 14.7? That is the question that should be asked. I have done various things for various reasons. Let's work through a list to break it up into easily understandable pieces.

What are influential factors for altering PCTUSED and PCTFREE?

• Type of Table.   A static table either does not change, changes very little, and is usually very small but not necessarily too small. Static tables are usually lookup tables but they can be subject to small amounts of DML activity.

• Number of Rows.   Distinguish between extremely small, average, and very large tables. Extremely small tables generally fit into a single block. Average-size tables are often static but occupy less than 100 blocks. Extremely large tables can have millions of rows and potentially millions of blocks.

• Insert Activity.   Raise PCTUSED for high insert activity to allow for reuse of blocks more easily.

• Update Activity.   Raise PCTFREE for high update activity involving possible row length changes.

• Delete Activity.   Raise PCTUSED to allow for future reuse of blocks.

• Select Activity.   Lower PCTFREE and raise PCTUSED to squash more rows into each block and thus increase data retrieval performance.

• Potential Concurrency.   This is very important because the more rows in a single block having DML activity on them at once the more likely hot blocking and locking will occur. Locking can cause the biggest potential performance problems due to lock and latch waits, especially in highly active OLTP databases like my Accounts schema.

  Tip

  The biggest danger from setting of low PCTFREE values is chained and migrated rows. Check the CHAIN_CNT statistic column in the USER_TABLES view.

Now I will group the tables and examine why I changed values for each table grouping.

• Static Lookup.   Category, Period, Subtype, and Type. These tables have PCTFREE = 0 and PCTUSED = 99. There is no point reserving free space for tables not subject to any DML activity. Lookup tables are only ever read and may as well be squashed up as much as possible to ensure a single block read every time.

• Static (Subject to DML).   The Period and Posting tables are subject to very occasional DML activity thus settings other than 0 and 99 would probably be pointless. Additionally the Posting table is subject to intense SELECT activity, which is better served by ensuring fewer blocks are occupied. The PeriodSum, Stock, and COA tables have high PCTFREE and low PCTUSED values for two reasons. The first reason is because all three tables are subject to a lot of updates. The COA table is higher than the other two because those updates involve higher row length growth. PCTFREE and PCTUSED can only be applied to new blocks. Thus existing data cannot be affected. Therefore, it makes sense to change these values on empty tables and then fill the tables with rows. Thus columns will be null-valued to start with. Three columns on the COA table are updated constantly. The Stock table has more rows updated but not as often. The same occurs with the PeriodSum table since it is an aggregation of the COA table. The second reason for high PCTFREE and low PCTUSED is to attempt to alleviate potential DML activity concurrency issues. Oracle Database locks rows and unlike other relational databases does not escalate row locking to block locks, even if there is high concurrent DML activity. Concurrent capacity in a block can be increased using the INITRANS and MAXTRANS parameters. High concurrent block activity can however lead to hot blocks and contention. Therefore, it makes sense to attempt to distribute rows over more blocks. These tables are small and multiple block read counts will probably cater for a single read even though there are potentially more blocks. The chances are if all the block and row size values were calculated precisely PCTFREE would probably have to be a lot higher. It is best to utilize a combination of multiple parameters including PCTUSED, PCTFREE, and INITRANS.

  Note

  Oracle Database 10 Grid   MAXTRANS is deprecated.

• Semi-static (Large).   The Customer and Supplier tables are updated frequently but are much larger than other static tables. The Customer and Supplier tables are subject to a lot of lookup activity thus the relatively high PCTUSED values. PCTFREE is low at 20% but allows for some updating row length growth. The StockSource table does not grow nearly as much as Customer and Supplier tables in terms of updates because only a single column is changed, rather than three columns. Also queries executing against the StockSource table tend to read large numbers of rows.

• Transactional.   All standard transaction tables involve three master detail relationships for orders, transactions, and cash-book entries. These tables are generally only subject to insertion activity due to the nature of their purpose. It is very unlikely that an accounting system would have frequent changes to invoices. Accounting practices are strictly controlled. Changes to transactions involve new transactions executing the opposite effect. In other words, a sale transaction is documented using an invoice. A return of sale does not change the invoice but creates a new transaction called a credit note. The invoice and credit note are separate transactions. Therefore, all these six tables have minimal PCTFREE and fairly high PCTUSED values. PCTUSED is not set to 99% because typically transactional files in accounting systems are purged or transferred to archives at the end of distinct periods, such as every quarter.

• Transactional Large.   The GeneralLedger and StockMovement tables are very similar to logs or audit trails. A log file is filled over a period of time and then purged. Purging processing may involve aggregation into other tables, archiving or complete destruction of data. These types of tables are usually subject to heavy insertion and full table scan data select activity. Therefore, PCTFREE is 0 and PCTUSED is high but not too high. The reason why PCTUSED is 80% and not 99% is because these tables are large and space must be conserved. On the contrary, INSERT commands are better executed into clean blocks if possible. Appending of data is faster than searching for usable blocks. When a table like this is purged it is probably best to TRUNCATE the entire table rather than delete, or perhaps even drop and recreate the table. If that is the case then PCTUSED can be set to 99%.

Assessing PCTFREE Settings

A vague attempt can be made at assessing PCTFREE settings using a query such as this.

COL Table FORMAT a16; COL Rows FORMAT 9999999; COL Blocks FORMAT 9999999; COL RPB FORMAT 9999999 HEADING "Rows/Block"; COL SU FORMAT 9999999 HEADING "Space Free %"; SELECT table_name "Table", num_rows "Rows", blocks "Blocks" ,num_rows/blocks AS RPB, ROUND(avg_space/8192*100) AS SU ,chain_cnt "Chaining" FROM user_tables ORDER BY table_name;

Here are the results. Note that no changes have been made to PCTUSED and PCTFREE values in the Accounts schema at the time of execution of this query. All tables over 1,000 rows have over 10% of free space. This is a particularly obvious problem for the GeneralLedger and StockMovement tables because they never have any update activity.

Table Rows Blocks Rows/Block Space Free % Chaining -------------- ------- ------ ---------- ------------ -------- CASHBOOK 450957 4067 111 13 0 CASHBOOKLINE 2529733 6557 386 11 0 CATEGORY 13 1 13 96 0 COA 55 1 55 77 0 CUSTOMER 2694 60 45 17 0 GENERALLEDGER 2158809 9654 224 10 0 ORDERS 435076 1924 226 13 0 ORDERSLINE 2866927 9757 294 12 0 PERIOD 72 1 72 68 0 PERIODSUM 221 1 221 35 0 POSTING 8 1 8 94 0 STOCK 118 4 30 27 0 STOCK- 3032315 12937 234 10 0    MOVEMENT STOCKSOURCE 12083 30 403 15 0 SUBTYPE 4 1 4 98 0 SUPPLIER 3874 85 46 16 0 TRANSACTIONS 450957 3091 146 13 0 TRANSACTIONS- 2896275 9847 294 12 0 LINE TYPE 6 1 6 98 0

14.7.3 Block Concurrency

Block-level concurrency determines the number of locks which can be placed on a block. Concurrency capacity is the ability for rows in the same block to be accessed by multiple sessions at the same time, for both DML and SELECT activity. At the block level the parameters INITRANS and MAXTRANS help to control block concurrency. The INITRANS parameter can be increased for higher levels of concurrency.

• INITRANS.   This parameter determines the initial concurrency allowance for a block. Increasing INITRANS increases block concurrency (active transactions per block) but decreases available row data block space; more space is reserved in each block for higher transactional concurrency rates.

  Tip

  INITRANS must always be at least double the value for an index as for its parent table. The default for a table is 1 and for an index is 2. Set INITRANS to more than double for indexes when indexes are nonunique. The more duplicated values in an index the higher INITRANS should be, because searches on those values will be more likely to be in the same block.

• MAXTRANS. This parameter determines the maximum number of active transactions per block. The more active transactions the more space required in block header areas and the less space allowed for row data.

  Note

  Oracle Database 10 Grid   MAXTRANS is deprecated.

Let's look once again at the Accounts schema to determine appropriate changes to INITRANS parameters. I will not alter MAXTRANS parameter values. Possible changes to INITRANS are shown in Figure 14.8.

Figure 14.8: Changes to Block Structure Concurrency

In Figure 14.8 INITRANS values have been adjusted to account for potential DML activity concurrency; multiple transactions are changing rows in the same block at once.

Most drastically changed are the COA and PeriodSum tables based on their high amount of DML activity. The Stock table INITRANS value is lower since it occupies more physical blocks than the other two tables (see Figure 14.6). The large static tables have INITRANS set to 2 because they are subject to concurrent UPDATE command activity. Setting INITRANS to 2 for the GeneralLedger and StockMovement tables is probably an unwise choice. These tables are subject to intense INSERT command activity. A reduction in concurrency problems could probably be resolved by changing their sequence number generated primary keys into reverse key indexes. Reverse key indexes would prevent concurrent sequential insertions of similar key values into the same block for the primary key index.