how to determine the optimal column order for a composite index

What is the secret for creating a composite index with the columns in the proper sequence?

• In general, when using a multi-column index, you want to put the most restrictive column value first (the column with the highest unique values) because this will trim-down the result set.
• Because Oracle can only access one index, your job is to examine your historical SQL workload and build a single composite index that satisfies the majority of the SQL queries.
  
• The Oracle optimizer may try to make single column  indexes behave as-if they were a single composite index.  Prior to 10g, this could be done with the "and_equal" hint.
  
• Beware that indexes have overhead
• You can run scripts to monitor the invocation count for each column in a multiple column composite index

Assign effectivly tables to Oracle buffer pools to maximize performance

Any segments whose blocks tend to be accessed with less frequency should be assigned to the recycle pool so that it does not flush the other segments, either in the default cache or the keep pool.

Segments that contain frequently accessed blocks should be assigned to the keep buffer pool so that the blocks of those segments will not be inadvertently removed, thus impacting performance.

the keep buffer pool is used to retain objects in memory that are likely to be reused. Keeping these objects in memory reduces physical I/O operations. For optimal performance you should make sure that you tune your SQL to reduce logical I/O operations as well as physical operations. 

When Not to Use Indexes in Oracle

There are some circumstances where indexes can be detrimental to performance and sometimes those indexes should not exist. The Optimizer will occasionally ignore indexes and consider reading the entire table a faster option.

• A table with a small number of columns may not benefit from an index if a large percentage of its rows are always retrieved from it.
• NULL values are generally not included in indexes. Do not index columns containing many NULL values unless there is a specific use for it such as filtering the column as NOT NULL, thus only scanning the index and the rows without NULL values.
• Indexes should usually be created on a small percentage of the columns in a table. Large composite indexes may be relatively large compared with the table. The relative size between index and table is important. The larger the ratio of index to table physical size then the less helpful the index will be in terms of decreasing physical space to be read. Also many columns not in the index may contain NULL values. It may be faster to read the entire table. Why create indexes?
• Small static data tables may be so small that the Optimizer will simply read those tables as a full table scan and ignore indexes altogether. In some cases the Optimizer may use an index on a small table where it should not since a full table scan would be faster. An exception to this rule will be in mutable joins where unique index hits are often used, even on small tables. If full table scans are faster than index reads in those mutable joins you might want to remove indexes from the small tables altogether or override with hints. Examine your small static data tables. Do they really need indexing ?
   

ORA-01555 causes and suggestions to avoid it

Here are the causesof this error

• The undo segments are too small
•  The application fetches across commits (a design flaw)
• Delayed block cleanout

Here are some suggestions to avoid  ORA-01555: snapshot too old erro

• Adjust the undo_retention parameter (for 9i and higher releases of Oracle
•  Increase the size of the UNDO tablespace 
• Increase the size of the undo segments [if using manual UNDO management]

Some factors that can prevent an index from being used in Oracle

The presence of an index on a column does not guarantee it will be used.

• The optimizer decides it would be more efficient not to use the index. If your query is returning the majority of the data in a table, then a full table scan is probably going to be the most efficient way to access the table.
• You perform a function on the indexed column i.e. WHERE UPPER(name) = 'JONES'. The solution to this is to use a Function-Based Index.
• You perform mathematical operations on the indexed column i.e. WHERE salary + 1 = 10001
• You concatenate a column i.e. WHERE firstname || ' ' || lastname = 'JOHN JONES'
• You do not include the first column of a concatenated index in the WHERE clause of your statement. For the index to be used in a partial match, the first column (leading-edge) must be used. Index Skip Scanning in Oracle 9i and above allow indexes to be used even when the leading edge is not referenced.  
• The use of 'OR' statements confuses the Cost Based Optimizer (CBO). It will rarely choose to use an index on column referenced using an OR statement. It will even ignore optimizer hints in this situation. The only way of guaranteeing the use of indexes in these situations is to use an INDEX hint.

How to get paging space informations on IBM AIX

To list the characteristics of all paging spaces, enter: lsps -a

       This displays the characteristics for all paging spaces and provides a listing similar to the following listing:

       Page Space      Physical Volume   Volume Group    Size %Used Active  Auto  Type
       hd6             hdisk0            rootvg         512MB     1     yes   yes    lv

Avoid double caching to improve disk I/O performance in Oracle

If Oracle data are stored in a Filesystem a Concurrent IO mount option can improve performance ...

• Data is transfered directly from the disk to the application buffer, bypassing the file buffer cache hence avoiding double caching (filesystem cache + Oracle SGA)
• Emulates a raw-device implementation
• Give a faster access to the backend disk and reduce the CPU utilization
• Disable the inode-lock to allow several threads to read and write the same file