DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: analyze index "indexname" validate structure is deadly slow

  1. #1
    Join Date
    Mar 2001
    Posts
    149

    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

  2. #2
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    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.
    http://www.perf-engg.com
    A performance engineering forum

  3. #3
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    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,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  4. #4
    Join Date
    Mar 2001
    Posts
    149
    Quote Originally Posted by malay_biswal View Post
    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

  5. #5
    Join Date
    Mar 2001
    Posts
    149
    Quote Originally Posted by vnktummala View Post
    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...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width