Solved

rebuilding Indexes

Posted on 2007-03-17
9
1,950 Views
Last Modified: 2013-12-18
We are running the below script every week.
We are not rebuilding Indexes every week.
Is it a good Idea to rebuild Indexes every week or periodically.(All the indexes in the database)
We have a OLTP database.
we are  not facing any performance problem yet

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('SYSADM',DBMS_STATS.AUTO_SAMPLE_SIZE);
0
Comment
Question by:ballioballi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 70

Expert Comment

by:Qlemo
ID: 18741753
reorganizing or rebuilding indexes does only make sense when data is removed very often., or you have a strictly ascending/descending key. Even in this cases, I would recommend to reorganize no more than once a month.
0
 
LVL 7

Expert Comment

by:Fayyaz
ID: 18742691
It is best to check first which indexes are required to be rebuild and then rebuild only those indexes. You can use
Analyze index .... validate structure command to  check an index candidate for rebuild. the result of this command will populate index_stats table. In this table, you can query to check the candidate indexes
The criteria of rebuild may be
if ( height > 5 ) OR ( (del_lf_rows/lf_rows) > 0.2 ) then
rebuild .......

0
 
LVL 48

Expert Comment

by:schwertner
ID: 18747319
Rebuilding indexes periodically is very good thing.
After some DML they got unusual big and also possibly
some blocks get logically corrupted (mainly because of
Oracle bugs).
So there are 2 main things:
1. When run the statistics use the option cascade to run the statistics also
over the indexes.
2. 2-3 times yearly rebuild them in case of normal use.
If you expertience difficulties do this often.

There is nothing bad to rebuild the indexes.
Some of them cannot be rebuilded - Index Organized tables and LOB indexes.
So be prepared to get error messages in these case - it is normal.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 18748350
I agree with Qlemo and Fayyaz, that is: you don't regularly need to rebuild indexes in most Oracle OLTP databases.  An exception would be indexes on any tables that are used as "queue" tables.  For example, if you have tables that are used as "staging" or "work" tables that get lots of inserts and deletes every day, indexes on those tables should be rebuilt regularly (maybe nightly or on the weekend).

I have worked with Oracle databases for over 15 years and I don't recall ever seeing an index with logical corruption.  Maybe this happened a time or two and I have forgotten, but this certainly isn't common or frequent in my experience.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 18748440
I haven't seen corrupted blocks on Windows.
But I have met every 5 months corrupted index
blocks on Linux. I mean Novel linux (SuSE Linux).
So I have prepared a script that rebuilds the
indexes, but do not run it regularly.

Please if you have time pay attention to:
http://www.dba-oracle.com/art_dbazine_idx_rebuild.htm

Inside Oracle b-tree indexes

There are many myths and legends surrounding the use of Oracle indexes, especially the ongoing passionate debate about rebuilding of indexes for improving performance.  Some experts claim that periodic rebuilding of Oracle b-tree indexes greatly improves space usage and access speed, while other experts maintain that Oracle indexes should “rarely” be rebuilt.  Interestingly, Oracle reports that the new Oracle10g Automatic Maintenance Tasks (AMT) will automatically detect indexes that are in need of re-building.  Here are the pros and cons of this highly emotional issue:

 

Arguments for Index Rebuilding – Many Oracle shops schedule periodic index rebuilding, and report measurable speed improvements after they rebuild their Oracle b-tree indexes.  In an OracleWorld 2003 presentation titled Oracle Database 10 g: The Self-Managing Database by Sushil Kumar of Oracle Corporation, Kumar states that the Automatic Maintenance Tasks (AMT) Oracle10g feature will automatically detect and rebuild sub-optimal indexes.  “AWR provides the Oracle Database 10g a very good 'knowledge' of how it is being used. By analyzing the information stored in AWR, the database can identify the need of performing routine maintenance tasks, such as optimizer statistics refresh, rebuilding indexes, etc. The Automated Maintenance Tasks infrastructure enables the Oracle Database to automatically perform those operations.”
 

Arguments against Index Rebuilding – Some Oracle in-house experts maintain that Oracle indexes are super-efficient at space re-use and access speed and that a b-tree index rarely needs rebuilding.  They claim that a reduction in Logical I/O (LIO) should be measurable, and if there were any benefit to index rebuilding, someone would have come up with “provable” rules.

So who is right?  I suspect that they both are correct.  There is a huge body of evidence that index rebuilding makes the end-users report faster response time, and I have to wonder if this is only a Placebo Effect, with no scientific basis.  Some experts suspect a Placebo Effect may be at work here, and the end-users, knowing that they have new index trees, report a performance gain when none exists.

Because of their extreme flexibility, Oracle b-tree indexes are quite complex, and to really gather scientific evidence we must examine all of the index metrics.  Getting a meaningful measure of the benefit of an index rebuild in a production environment is very difficult because the system is under heavy load and usage patterns change constantly.  Plus, many IT managers require periodic index re-building because it is a low-risk job and it curries favor from the end-user community.  

0
 

Author Comment

by:ballioballi
ID: 18761102
I agree with all of you .. experts
I have a question especially for Favvaz,

I have like 35,000 tables and lot many indexes.
How can I check which index has to be rebuild by Analyze index .... validate structure command . This looks  impossible for me to put the individual Indexes......

Thanks,
0
 
LVL 48

Expert Comment

by:schwertner
ID: 18762339
These entries are in index_stats:

SQL> descr index_stats;
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 HEIGHT                                             NUMBER
 BLOCKS                                             NUMBER
 NAME                                               VARCHAR2(30)
 PARTITION_NAME                                     VARCHAR2(30)
 LF_ROWS                                            NUMBER
 LF_BLKS                                            NUMBER
 LF_ROWS_LEN                                        NUMBER
 LF_BLK_LEN                                         NUMBER
 BR_ROWS                                            NUMBER
 BR_BLKS                                            NUMBER
 BR_ROWS_LEN                                        NUMBER
 BR_BLK_LEN                                         NUMBER
 DEL_LF_ROWS                                        NUMBER
 DEL_LF_ROWS_LEN                                    NUMBER
 DISTINCT_KEYS                                      NUMBER
 MOST_REPEATED_KEY                                  NUMBER
 BTREE_SPACE                                        NUMBER
 USED_SPACE                                         NUMBER
 PCT_USED                                           NUMBER
 ROWS_PER_KEY                                       NUMBER
 BLKS_GETS_PER_ACCESS                               NUMBER
 PRE_ROWS                                           NUMBER
 PRE_ROWS_LEN                                       NUMBER
 OPT_CMPR_COUNT                                     NUMBER
 OPT_CMPR_PCTSAVE                                   NUMBER
0
 
LVL 7

Accepted Solution

by:
Fayyaz earned 125 total points
ID: 18762357
Here is the PL/SQL code

declare
 c_name        INTEGER;
 ignore        INTEGER;
 height        index_stats.height%TYPE := 0;
 lf_rows       index_stats.lf_rows%TYPE := 0;
 del_lf_rows   index_stats.del_lf_rows%TYPE := 0;
 distinct_keys index_stats.distinct_keys%TYPE := 0;
 cursor c_indx is
  select owner, table_name, index_name
  from dba_indexes
  where owner like upper('&schema')
    and owner not in ('SYS','SYSTEM');
begin
 dbms_output.enable (1000000);
 dbms_output.put_line ('Owner          Index Name                              % Deleted Entries Blevel Distinctiveness');
 dbms_output.put_line ('--------------- -------------------------------------- ----------------- ------ ---------------');
 c_name := DBMS_SQL.OPEN_CURSOR;
 for r_indx in c_indx loop
  DBMS_SQL.PARSE(c_name,'analyze index ' || r_indx.owner || '.' ||
                 r_indx.index_name || ' validate structure',DBMS_SQL.NATIVE);
  ignore := DBMS_SQL.EXECUTE(c_name);
  select HEIGHT, decode (LF_ROWS,0,1,LF_ROWS), DEL_LF_ROWS,
         decode (DISTINCT_KEYS,0,1,DISTINCT_KEYS)
         into height, lf_rows, del_lf_rows, distinct_keys
  from index_stats;
  if ( height > 5 ) OR ( (del_lf_rows/lf_rows) > 0.2 ) then
    dbms_output.put_line (rpad(r_indx.owner,16,' ') || rpad(r_indx.index_name,40,' ') ||
                          lpad(round((del_lf_rows/lf_rows)*100,3),17,' ') ||
                          lpad(height-1,7,' ') || lpad(round((lf_rows-distinct_keys)*100/lf_rows,3),16,' '));
  end if;
 end loop;
 DBMS_SQL.CLOSE_CURSOR(c_name);
end;
/

 

0
 

Author Comment

by:ballioballi
ID: 18910701
I run this and it is Great.
Can I just rebuild this Indexes. I am getting like 250  out of a big list.

Once again it is just Great.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
oracle forms question 22 61
scheduler notification 9 80
SQL Syntax Question 9 57
replicate in oracle 13 46
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question