When to Rebuild an Index?

When to Rebuild an Index?

It is important to periodically examine your indexes to determine if they have become skewed and might need to be rebuilt.

When an index is skewed, parts of an index are accessed more frequently than others. As a result, disk contention may occur,creating a bottleneck in performance. Here is a sample procedure on how to identify the such indexes:

1. Gather statistics on your indexes. For large indexes (over one hundred thousand records in the underlying table), use ESTIMATE instead of COMPUTE STATISTICS.


For example:

SQL> analyze index emp_empno_pk compute statistics;
Index analyzed.

2. Run the query below to find out how skewed each index is. This query checks on all indexes that are on emp table.


select index_name, blevel,decode(blevel,0,'OK BLEVEL',1,'OK BLEVEL',2,'OK BLEVEL',3,'OK BLEVEL',4,'OK BLEVEL','BLEVEL HIGH') OK
from user_indexes;

select * from dba_indexes where blevel > 4;



3. The BLEVEL (or branch level) is part of the B-tree index format and relates to the number of times Oracle has to narrow its search on the index while searching for a particular record. In some cases, a separate disk hit is requested for each BLEVEL. If the BLEVEL were to be more than 4, it is recommended to rebuild the index.

Note: If you do not analyze the index, the index_check.sql script will show "BLEVEL HIGH" for such an index.

4. Gather more index statistics using the VALIDATE STRUCTURE option of the ANALYZE command to populate the INDEX_STATS virtual table.



SQL> analyze index emp_empno_pk validate structure;
Index analyzed.

5. Run the following query to find out PCT_DELETED ratio.
SQL> select DEL_LF_ROWS*100/decode(LF_ROWS, 0, 1, LF_ROWS) PCT_DELETED,
2 (LF_ROWS-DISTINCT_KEYS)*100/ decode(LF_ROWS,0,1,LF_ROWS) DISTINCTIVENESS
3 from index_stats
4 where NAME='EMP_EMPNO_PK';



The PCT_DELETED column shows the percent of leaf entries (i.e. index entries) that have been deleted and remain unfilled. The more deleted entries exist on an index, the more unbalanced the index becomes. If the PCT_DELETED is 20% or higher, the index is candidate for rebuilding. If you can afford to rebuild indexes more frequently, then do so if the value is higher than 10%. Leaving indexes with high PCT_DELETED without rebuild might cause excessive redo allocation on some systems.

The DISTINCTIVENESS column shows how often a value for the column(s) of the index is repeated on average. For example, if a table has 10000 records and 9000 distinct SSN values, the formula would result in (10000-9000) x 100 / 10000 = 10. This shows a good distribution of values. If, however, the table has 10000 records and only 2 distinct SSN values, the formula would result in (10000-2) x 100 /10000 = 99.98. This shows that there are very few distinct values as a percentage of total records in the column. Such columns are not candidates for a rebuild but good candidates for bitmapped indexes.




In general, indexes and tables should be rebuilt when they become too fragmented.
In practice, you probably will not have need to do it with Oracle 10g. Fragmentation occurs on tables and indexes with lots of changes to structure (adding/removing columns) and lots of data changes (insert, update, delete).

From v10, Oracle have number of automated processes that take care about database performance. One of them is "Segment advisor" that runs automatically.


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

Reuse statements, cursor_sharing=force

Reuse statements

select * from emp where ename = :EMPNAME


select * from v$parameter where name like '%cursor_sharing%';

cursor_sharing=force

The kernel parameter cursor_sharing defaults to EXACT, but can be set to FORCE or SIMILAR in order to have the database convert literals to bind variables before parsing the statement.

Setting cursor_sharing =force greatly reduced the contention on the library cache and reduced CPU consumption. The end users reported a 75 percent improvement in overall performance.

Our queries use a lot of bind variables, by design. In recent benchmarks, CURSOR_SHARING=FORCE was helpful for benchmark runs reducing query execution time by several times.
However, for a specific query involving bind variables, using CURSOR_SHARING=FORCE was very much slower than if executed while CURSOR_SHARING=EXACT.


This is probably a bug. Cursor_sharing = force is not really reliable. We hash the value of the statement and then go looking for a matching hash, regardless of how many users are on the system or how many times the statement has been issued or even what the statement looks like. Force means force, regardless of what is going on, we are going to force the
sharing of cursors. This leads to problems like what you are seeing and the use of suboptimal plans. You can attempt to tune around the sub-optimal plan but you may find that there are a number of queries with that problem. The other possibility is to set cursor_sharing = exact. That will prevent this problem from occuring.

Oracle Flushing-Pinning PL/SQL packages, FLUSH SHARED POOL

Oracle Flushing-Pinning PL/SQL packages, FLUSH SHARED POOL

http://www.remote-dba.cc/oracle_tips_flushing_secrets.htm
1. Pinning all commonly used PL/SQL packages.

The initial call to a package causes the whole package to be loaded into the shared pool. For large packages this may represent an unacceptable delay for two reasons. First the size of the package causes a delay in load time. Second if the shared pool is already full, several smaller objects may need to be aged out to make room for it. In these circumstances, performance can be improved by pinning large packages in the shared pool.

Under normal circumstances, objects in the shared pool are placed on a least recently used (LRU) list. If the shared pool is full and a new object must be loaded, items on the LRU list are aged out. Subsequent calls to objects that have been aged out result in them being reloaded into the shared pool.

The processes of pinning objects in the shared pool removes them from the LRU list so they are no longer available to be aged out, regardless of usage. The process of pinning objects in the shared pool is achieved using the dbms_shared_pool package.

http://www.dba-oracle.com/plsql/t_plsql_pinning_pkgs.htm
http://www.oracle-training.cc/oracle_tips_pinning_packages.htm



SQL> conn sys/password as sysdba
Connected.
SQL> @$ORACLE_HOME/rdbms/admin/dbmspool.sql


Pinning and unpinning objects in the shared pool is achieved using the keep and unkeep procedures, both of which accept the same case-insensitive parameters.

PROCEDURE keep (
name VARCHAR2,
flag CHAR DEFAULT 'P'
)

PROCEDURE unkeep (
name VARCHAR2,
flag CHAR DEFAULT 'P'
)

P - Package, Procedure or Function. This is the default value.
T - Type.
R - Trigger.
Q - Sequence.
C – Cursor.


SQL> conn sys/password as sysdba
Connected.
SQL> SELECT address || ',' || hash_value FROM v$open_cursor WHERE rownum = 1;



SQL> EXEC DBMS_SHARED_POOL.keep(‘

,2. FLUSH SHARED POOL

Periodically issuing the--
ALTER SYSTEM FLUSH SHARED POOL;
alter system flush buffer_pool;
----------
This will "flush" out, or clear, all SQL statements that are in the Shared Pool Area. Oracle keeps track of each SQL statement that users execute.
It is stored parsed in memory so that if a SQL statement already has been executed then Oracle does not need to re-parse it. The exception is if the shared pool area is not large enough, then the least recently used SQL statements (except for pinned packages) will be removed from memory.

By flushing the shared pool, all SQL statements are removed from memory.

----------