analyze index "indexname" validate structure is deadly slow

Printable View

06-01-2010, 01:05 AM
newbie

analyze index "indexname" validate structure is deadly slow

Hi everyone, I have this problem on 10.2.0.2 and I'm not sure if you have seen before, we have a relative large table (roughly around 50 mil rows) and this table has one unique key index. Since the inserts into this table is slow so I decided to do a analyze index "indexname" validate structure..after I issued this command..it was running for 4 hrs before I had to kill it. I'm not sure what caused this. I ran the same cmd in my test database and it ran within a minute..my test db is 10.2.0.4 ...is there something wrong w/ 10.2.0.2?? I googled on this but couldn't find any..can you please help me w/ some tips regarding this issue.. Thanks
06-01-2010, 01:46 AM
malay_biswal

I do not understand why did you validate structure to make insert fast.

You did not tell us how many number of records in test db and exactly how long it took.
I assume in test db there are around 100,000 records in same table and took around 1min. so that will be 500 min for a 50 million records.
06-01-2010, 02:16 AM
vnktummala

as you are using 10g, and if you feel that you have a old stats, please use the nice dbms_stats package to gather stats.

"analyze" in 10g is for backword compatibility and it needs lock on the object. In your case probably it is waiting to acquire the lock. Trace the session and see what actually it is doing.

Thanks,
06-04-2010, 12:46 AM
newbie

Quote:

Originally Posted by malay_biswal

I do not understand why did you validate structure to make insert fast.

You did not tell us how many number of records in test db and exactly how long it took.
I assume in test db there are around 100,000 records in same table and took around 1min. so that will be 500 min for a 50 million records.

Hi I didn't mean to make the query fast..I just want to make sure my index is good
06-04-2010, 12:51 AM
newbie

Quote:

Originally Posted by vnktummala

as you are using 10g, and if you feel that you have a old stats, please use the nice dbms_stats package to gather stats.

"analyze" in 10g is for backword compatibility and it needs lock on the object. In your case probably it is waiting to acquire the lock. Trace the session and see what actually it is doing.

Thanks,

Thanks for the advice...