Tutorials

Database Storage Hierarchy

TABLESPACE

SEGMENTS

EXTENTS

BLOCKS

ALLOCATING AN EXTENT

When database operations cause the data to grow and exceed the space allocated, oracle extends the segment. Dynamic extension, extending the segment when executing and INSERT or UPDATE statement, reduces performance, because the server executes several recursive SQL statements to find free space and add the extent to the data dictionary.

To display segments with <10%>

SVRMGR> select owner, table_name, blocks, empty_blocks

2> from dba_tables

3> where empty_blocks/(blocks+empty_blocks)>1;

OWNER TABLE_NAME BLOCKS EMPTY_BLOC

------------------------------ ------------------------------ ---------- ----------

HR EMP 1450 50

HR REGION 460 40

To avoid dynamic allocation:

SQL> ALTER TABLE hr.emp ALLOCATE EXTENT;

To avoid dynamic extension:

- size the segment appropriately by:

o determining the max size of ur object.

o Chosing storage parameters that allocate extents large enough to accommodate all of ur data when u create the obj.

o When determining the segment size, the DBA should allow for the growth of data. E.g., allocate enough space for the current data and for any data that will b inserted into the segment over the next year.

LARGE EXTENTS

Adv:

- large extents avoid dynamic extents, becuz segments with larger extents r less likely to need to be extended.

- It can have small performacnce benefil becus oracle can read one large extent from disk with fewer multi block reads than would be required to read many small extents. To avoid partial multi block reads, set the extent size to a multiple of

5 * DB_FILE_MULTIBLOCK_READ_COUNT

multiply by cuz oracle tries to allocate blocks on 5 blocks boundries. By matching extent sizes of I/O and space allocation sizes, the performance cost of having many extents in a segment will b minimized.

- The performance of searches using an index is not affected by the index having one extent or multiple extents.

- Extent maps list all the extents for a certain segment. For best performance, u should b able to tead the extent map with a single I/O. performance degrades if multiple I/O’s r necessary for a FTS to get the extent map. Also a large number of extents can degrade data dict performance, becuz each extent uses space in the dict cache.

DisAdv:

- Because the DBA sizes the segment to allow for growth, some of the space allocated to the segment will not be initially used.

- free space may not b available.

Large blocks are good for very large rows and sequential reads. It is not beneficial for DSS systems to have a small block size or for OLTP systems to have a large block size.

SMALL BLOCK SIZE Pros & Cons

ADV:

- small blocks reduce blocm, contention, since there r fewer rows per block.

- R good for small rows.

- Good for random access, becuz its unlikely that a block will b reused after its read into memory, a smaller block size makes more efficient use of buffer cache. This is espically imp when memory resources r scarce, cecause the size of the dbbc is ltd.

Dis Adv:

- Has relatively large overhead.

- Small number of rows per block, depending on the size of the row, this may cause additional I/O’s.

LARGE BLOCK SIZE Pros & Cons

Adv:

- less overhead and thus more room to store useful data.

- Good for sequential reads.

- Good for very large rows.

- Improve performance of index reads. The larger blocks can hold more index entries in each block which reduces the num of levels in large indexes. Fewer index levels means fewer I/O’s when traversing the index branches.

Dis Adv:

- Its not good for index blocks used in an OLTP type of environment, because they increase block contention on the index leaf blocks.

- Use more space in the Buffer Cache.

Technical Note

- Instead of using small block sizes for reading rows from large tables, the DBA could use a recycle buffer pool.

- Instead of using large block sizes to reduce index I/O, the DBA could use a keep buffer pool.

Two space management para’s, PCTFREE & PCTUSED, enable u to control the use of free space within all the data blocks of a segment. U specify these paras when creating or altering a table or cluster (which has its own data segment). U can also specify the storeage para PCTFREE when creating or altering an index (which has its own index segment).

PCTFREE

PCTFREE and PCTUSED

Pctfree: - sets the minimum % of a data block to be reserved as free spaced for possible updates to rows that already exist in that block.

After u issue a DELETE or UPDATE statement, oracle processes the statemtn and cheks to see if the space being used in the block is now less thatn PCTUSED. If it is, the block goes to the beginning of the free list. When the transaction commits, free space in the block becomes available for other transactions.

After a data block is filled to the PCTUSED limit again, oracle considers the block unavailable for the insertion of new rows until the % of that block falls below the PCTUSED parameter.

Indicates what percentage of space in each block of the table to reserve for updates to existing rows. Its purpose is to reduce row chaining.

A higher PCTFREE affords more room for updates within a database block. Set a higher value if table contains:

- columns that are initially NULL and later updated with a value.

- columns that are likely to increase in size as a result of an update.

A higher PCTFREE will result in lower block density – each block can accommodate fewer rows.

When free space in a block reaches the PCTFREE threshold, that block is removed from the free list. The block will first be removed from the process free list, and then after the transaction is committed, it will be removed from the transaction free list. By default its set to 10 for tables that don’t specify this clause.

If you are creating a table that will have a great deal of update activity, consider reducing the setting of PCTFREE. Therefore, the smaller the overall size of the changes, the higher you can set PCTFREE. For tables with no update activity, setting PCTFREE to 95 or even higher is acceptable. For moderate around 80-90.

When setting PCTFREE for an index, u cant alter the PCTFREE setting for an index. Oracle uses the PCTFREE setting of an index only during the creation or recreation of an index.

PCTFREE = (Avg row size – initial row size) * 100

-----------------------------------

Avg row size

Pctused: - the default value of PCTUSED is 40, and the PCTUSED parameter is not valid when creating index or rollback segments.

PCTUSED = 100 – PCTFREE – (Avg row size * 100

-------------------------------------

Available data space

It will allow inserts into the table when there is enough space in the block for row updates and for one more row.

Its relevant only in tables that undergo deletes, but in many tables u may be able to pace rows into blocks more tightly by setting PCTUSED to be higher than the default i.e. 40%.

For tables with many inserts, changing PCTUSED may improve block storage performance.

DML and PCTFREE & PCTUSED

Two types of statements can increase the free space of one or more data blocks: DELETE and UPDATE that update existing values to values that use less space.

Released space in a block may not be contiguous. E.g. a row in the middle of the block is deleted. Oracle coalesces the free space of data block when:

- an INSERT or UPDATE statement attempts to use a block that contains enough free space to contain a new row piece.

- The free space is fragmented so that the row piece cant b inserted in a contiguous section of the block.

- Oracle does this compression only in such situations, because otherwise performance of a database system would because of the continuous compression of the free space in data blocks.

- If u change PCTFREE & PCTUSED for existing tables, there is no immediate impact on blocks. However, future DML activity uses the new rules for tables.

Migration & Chaining

- In the first case, called chaining, the row is too large to fit into an empty data block. In this case, oracle stores the data for the row in a chain of one or more data blocks. Chaining can occur when the row is inserted or updated. Row chaining usually occurs with large rows, such as rows that contain a LOB. In these cases is unavoidable.

- In the second case, migration, an UPDATE statement increases the amount of data in a row so that the row no longer fits in its data block. Oracle tries to dind another block with enough free space to hold the entire row. If such a block is available, oracle moves the entiere row to the new block. Oracle keeps the original row piece of a migrated row to point to the new block containing the actual row; the ROWID of a migrated row doesn’t change.

- INSERT & UPDATE statements that cause m & c perform poorly, cecause they perform additional processing.

- Migration is caused by PCTFREE being set low. There is not enough room in the block for updates. To avoid migration, all tables that r updated should have their PCTFREE set so that there is enough space within the block for updates.

- Detecting Chaining

SVRMGR> @c:\oracle\ora81\rdbms\admin\utlchain.sql

Statement processed.

Analyze table scott.emp list chained rows;

Statement processed.

Select owner_name, table_name, head_rowid

From chained_rows

Where table_name = 'EMP';

OWNER_NAME TABLE_NAME HEAD_ROWID

------------------------------ ------------------------------ ------------------

0 rows selected.

You can identify migrated and chained rows in a table or cluster by using the ANALYZE command with the LIST CHAINED ROWS option. This command collects info about each migrated or chained row and places this info into a specified out table. To create the table that holds the chained rows, execute the script UTLCHIAIN.SQL

SVRMGR> @c:\oracle\ora81\rdbms\admin\utlchain.sql

Or you can even do

SVRMGR> select * from v$sysstat where name ='table fetch continued row';

STATISTIC# NAME CLASS VALUE

---------- ---------------------------------------------------------------- ---------- ----------

158 table fetch continued row 64 242

1 row selected.

Eliminating Migrated Rows

- Analyze table…list chained rows;

- Copy the rows to another table.

- Delete the rows from the original table.

- Insert the rows from that another table in step 2 into the original table.

Step 4 eliminates migrated rows, because migration only occurs during an UPDATE.

High Water Mark

- Recorded in segment header block.

- Set to the beginning of the segment on creation.

- Incremented in 5-block increments as rows r inserted.

- Reset by the TRUNCATE command.

- Never reset by DELETE statements.

- Space above the HWM can b reclaimed at a table level by using :

ALTER TABLE DEALLOCATE UNUSED;

-

TABLE STATISTICS

SVRMGR> Analyze table scott.emp compute statistics;

Statement processed.

Select num_rows, blocks, empty_blocks as empty, avg_space, chain_cnt, avg_roW_len

From dba_tables

Where owner='SCOTT'

AND TABLE_NAME='EMP';

NUM_ROWS BLOCKS EMPTY AVG_SPACE CHAIN_CNT AVG_ROW_LE

---------- ---------- ---------- ---------- ---------- ----------

14 1 6 7507 0 40

1 row selected.

NUM_ROWS number of rows in the table.

BLOCKS number of blocks below the table HWM.

EMPTY_BLOOCKS number or blocks above the table HWM.

AVG_SPACE average free space in blocks below HWM.

AVG_ROW_LEN average row length, including row overhead.

CHAIN_CNT number of chained or migrated rows in the table.

INDEX REORGANIZATION

- indexes on volatile tables r a problem.

- If a block contains only one entry, it must b maintained.

- U may need to rebuild indexes.

- Empty index blocks go to the free list.

MONITORING INDEXES

SQL> ANALYZE INDEX index_name VALIDATE STRUCTURE;

You may decide to rebuild if deleted entries represent 20% or more of current entries. Use the ALTER INDEX REBUILD statement to reorganize or compact an existing index or to change its storage characteristics. The REBUILD statement uses the existing index as the basis for the new index. All index storage commands r supported, such as STORAGE (for extent allocation), TABLESPACE (to move the index to a new tablespace), and INITRANS (to change the initial number of entries).

ALTER INDEX REBUILD is usually faster than dropping and re-creating an index because it utilizes the fast full scan feature.

If this variation on the ANALYZE command shows that the index is corrupt, u need to rebuild it.