Creating & Maintaining Tables for Good Performance

When creating tables and indexes, note the following:

1. Specify automatic segment-space management for tablespaces

This allows Oracle to automatically manage segment space for best
performance.

2.Set storage options carefully

Applications should carefully set storage options for the intended use of the
table or index. This includes setting the value for PCTFREE. Note that using
automatic segment-space management eliminates the need to specify PCTUSED.

Table Compression

Heap-organized tables can be stored in a compressed format that is transparent for any kind of application. Table compression was designed primarily for read-only environments and can cause processing overhead for DML operations in some cases. However, it increases performance for many read operations, especially when your system is I/O bound.

Compressed data in a database block is self-contained which means that all the
information needed to re-create the uncompressed data in a block is available
within that block. A block will also be kept compressed in the buffer cache. Table.compression not only reduces the disk storage but also the memory usage,specifically the buffer cache requirements. Performance improvements are accomplished by reducing the amount of necessary I/O operations for accessing a table and by increasing the probability of buffer cache hits.

Reclaiming Unused Space

Over time, it is common for segment space to become fragmented or for a segment to acquire a lot of free space as the result of update and delete operations. The resulting sparsely populated objects can suffer performance degradation during queries and DML operations.Oracle Database provides a Segment Advisor that provides advice on whether an object has space available for reclamation based on the level of space fragmentation
within an object.

Indexing Data

The most efficient way to create indexes is to create them after data has been loaded.By doing this, space management becomes much simpler, and no index maintenance takes place for each row inserted. SQL*Loader automatically does this,but if you are using other methods to do initial data load, you might need to do this manually. Additionally, index creation can be done in parallel using the PARALLEL clause of the CREATE INDEX statement. However, SQL*Loader is not able to do this, so you must manually create indexes in parallel after loading data.

Written by admin on December 6th, 2010 with comments disabled.
Read more articles on Oracle Tuning.

Related articles

Comments disabled

Comments on this article have been disabled.