Solved

Rebuilding Index and  ANALYZE

Posted on 2004-08-18
13
2,170 Views
Last Modified: 2009-12-16
Hi experts
can u tell me rebuilding index and analyzing will increase our database performance
or analyzing will  slow down the database .
actually after rebuilding indexes when ever i am analyzing it I am  facing lots of trouble  because its  making verrrrrrrrrrry slow  to database.
0
Comment
Question by:rehman123
13 Comments
 
LVL 12

Accepted Solution

by:
catchmeifuwant earned 50 total points
ID: 11838301
0
 
LVL 6

Assisted Solution

by:Jankovsky
Jankovsky earned 45 total points
ID: 11839686
It depends ...
analyzis should in most cases improve execution, but there are cases where it makes trables. There are several ways to solve it:
1. add hints into touched statements to force them work ok even on analyzed tables, because it is not primaryli matter of analyzis (recommended)
2. delete analysis on given table, if statements work better without analysis (like rule based) - it's a bit dirty
3. optimize set of indexes on given tables.

To solve it You should evaluate execution plans of run statements.
0
 
LVL 13

Assisted Solution

by:riazpk
riazpk earned 45 total points
ID: 11839883
So my first question to Rehman is "Why do you rebuild the indexes" ???

about 90% og time, i found people rebuilding indexes uselessly and thus causing resources and time wastage.
0
 
LVL 1

Assisted Solution

by:Ajay_Gupta
Ajay_Gupta earned 45 total points
ID: 11840169
We rebuild indexes to maintain the integerity of indexes and as they become unbalanced because of updations/deletions/insertions on the table.
0
 

Assisted Solution

by:sonjasperson
sonjasperson earned 45 total points
ID: 11840882
Read the asktom article mentioned in the first post.   It is really very good.

The database probably gets really sloooooowwwww after you rebuild the indexes because it takes Oracle a while to get them back to the shape that works for the data.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 45 total points
ID: 11843874
An unbalanced index is not the same as an invalid index (or one that has integrity problems).  Just because an index is unbalanced, you may not need to, or benefit much from, an index rebuild.   If you are convinced that rebuilding indexes is a good thing to do, this process can be automated to occur at off-peak times (assuming that there are some off-peak times in your database).
0
 
LVL 18

Assisted Solution

by:JR2003
JR2003 earned 45 total points
ID: 11847299
If you add records and delete them on a regular basis the indexes will grow and grow and become very slow.
Rebuilding an index is much quicker than dropping and recreating the index as rebuilding takes all its information from the old index.
0
 
LVL 7

Assisted Solution

by:BobMc
BobMc earned 45 total points
ID: 11853700
Analyzing objects is a very resource intensive operation. In earlier versions of Oracle it would require exclusive locks, which obviously hits performance. I believe these restrictions have been relaxes slightly, but you will still notice the hit.

If you must rebuild and reanalyze indexes regularly, schedule it to run overnight when no one will notice.

I have to agree with several earlier comments - manual maintenance on correctly implemented indexes is not normally required.
0
 
LVL 3

Assisted Solution

by:aalapsharma
aalapsharma earned 45 total points
ID: 11869027
Hey rehman123,

When the size of database is very large then the  ANALYSE statement itself takes a lot of time, but it does help in later queries.
But you should use DBMS_STATS package to compute statistics. This package is far better than ANALYZE statement.
It has lots of other facilities available to manipulate your database analysis.

http://builder.com.com/5100-6388_14-5057857.html

Regarding rebuilding Index, I must say, you should not rebuild indexes very frequently. This also takes a lot of time, and does not help more to later queries. The best way is to rebuild the indexes once a week or month.

Regards,

Aalap Sharma :)
0
 
LVL 1

Assisted Solution

by:luttappi123
luttappi123 earned 45 total points
ID: 11892324
Hi,

     There is no point in rebuilting the index at regular intervals unless and otherwise there is defenit need for it. If there is frequent deletions on column which is incremented sequentially, coelasing is a better option. But Y u want to rebult and analyze the index?

Rgds
Luttappi
0
 
LVL 9

Assisted Solution

by:konektor
konektor earned 45 total points
ID: 11912787
some hints :
- rebuild indexex on tables, to which are often inserted or deleted rows.
- do not use analye tables, or indexes on such tables. it goes rapidly old and if it is old, SQL optimizer can chhose slow ways to execute statements
- be carefull, when joining analyzed and unanalyzed table. SQL optimizer chooses analyzed table firstly, so if you are joining some transaction table (nonanalyzed) with some reference table (analyzed), use hint /*+ RULE */ in select statement to make optimizer browse transaction table firstly and join with reference table afterthat
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now