Sizing the index object involves a series of calculations. Some of the data required will come from the index column specifications, some of the data will come from the V$PARAMETER, and the others will be from intermediate calculations. Table 19.2 below is the Index Size Worksheet that can be used to aid in the collection of necessary information.

The first step is to populate the DB_BLOCK_SIZE value. The following query will access this information from the Oracle V$PARAMETER table:

Select VALUE from V$PARAMETER where NAME = ‘db_block_size’;

You need to calculate the KEY SIZE. This is similar to the table column calculation except you need to perform the calculation only on the indexed fields. You are going to need to know the average column length for each column in this index. This can easily be found by running the following query once for each column in the table being indexed:

select avg(vsize(<COLUMN NAME>)) from <TABLE NAME>;

Record the sum of the returned values in the KEY SIZE area of the Table Size Worksheet (Table 19.2). A manual way to arrive at this total is to sum up the result of the following calculation: individual column size = column size + (1 unless the column size > 250, and then use 3). NUMBER field sizes are half the column size but not greater than 21. DATE fields take 7 positions. Use an average size for VARCHAR2 fields. Multiply any NOT NULL field by a percentage of the time it is expected to be NULL. All other fields use their referenced size value.

Calculate

HEADER SIZE = 113 + (24×INITRANS)

and record this number in the HEADER SIZE area of Table 19.2.

Calculate

AVAILABLE DATA SPACE = (DB_BLOCK_SIZE–HEADER

SIZE)–((DB_BLOCK_SIZE–HEADER SIZE)×(PCTFREE/100))

and record this number in AVAILABLE DATA SPACE area of Table 19.2.

Calculate

KEYS<127 = (1×number of indexed columns with a length < 127 bytes)

and record this number in the KEYS<127 area of Table 19.2.

Calculate

KEYS>127 = (2×number of indexed columns with a length > 126 bytes)

and record this number in the KEYS>127 area of Table 19.2.

Calculate

ENTRY SIZE = 12 + KEYS<127 + KEYS>127 + KEY SIZE

and record this number in the ENTRY SIZE area of Table 19.2.

Calculate

BLOCKS REQUIRED = (Estimated Number of Rows / (round(AVAILABLE DATA SPACE / ENTRY SIZE)))×1.05

and record this number in the BLOCKS REQUIRED area of Table 19.2.

Calculate

SIZE in bytes of INDEX = BLOCKS REQUIRED×DB_BLOCK_SIZE

and record this number in the SIZE in bytes of INDEX area of Table 19.2.

---

Tip:  
Make the initial extent size large enough to hold this index.

---

Tuning Concepts

There are two basic modes and reasons for tuning: proactive and reactive. Proactive tuning involves capacity planning, including planning for the growth of objects, adequate application and data access response times, and financial benefits. Financial benefits can be achieved if the response times can be made adequate without having to purchase additional hardware. Reactive tuning simply addresses slow response times.

Who Is Responsible for Tuning Tasks?

Several roles have responsibility for various tuning functions. All of these roles may be handled by a single person in smaller shops or by teams of people in larger shops. The system administrator needs to work closely with the database administrator to make sure the system environment is set correctly. The database administrator will handle the init.ora tuning parameters, monitoring, and I/O distribution. The DBA will work closely with the data administrator (or systems analyst) to determine object location needs. The DBA will monitor for resource intensive SQL statements, but the developer will tune and implement the offending SQL statements.