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
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.
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.
Try hard to get what you like OR you will be forced to like what you get.
Hi I didn't mean to make the query fast..I just want to make sure my index is good
Originally Posted by malay_biswal
Thanks for the advice...
Originally Posted by vnktummala
Click Here to Expand Forum to Full Width