Analyze on table or Index or both
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Analyze on table or Index or both

  1. #1
    Join Date
    Dec 2002
    Posts
    28

    Analyze on table or Index or both

    Can someone clarify the following for me on Oracle versions 7.3.4, 8.0.6.

    1.
    If I run commands 'analyze table xyz compute statistics;'
    Will this compute statistics for all Indexes on this table as well or do I need to run 'analyze table xyz compute statistics for all indexes;' as well.

    2.
    If using cost based optimisation if statistics are not produced on Indexes when analyzing tables could this have an affect on whether a full tablescan is performed rather than using an index.

    3.
    Should I Compute or Estimate statistics for Very Very Large tables.

    Please advise

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    1.Documented here ... http://download-west.oracle.com/docs...6a.htm#2086322

    2. Yes, because Oracle will have to estimate key statistics such as clustering factor. It will affect the decision

    3. Use the highest estimate practical.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Dec 2002
    Posts
    28
    Answer to Ques 3.

    DB Version 8.0.6.

    Our senior DBA advises not to use Estimate statistics on very large tables as only the first 1000 rows are estimated, thus optimiser can make wrong decision if table data from rows 1000 onwards is completely different.

    The problem we have is we use Compute statistics which tends to not complete on the very large tables and thus job has to be killed in the mornings as job tends to over-run. Even if statistics job is killed performance is very poor for users afterwards.

  4. #4
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    Our senior DBA advises not to use Estimate statistics on very large tables as only the first 1000 rows are estimated, thus optimiser can make wrong decision if table data from rows 1000 onwards is completely different.
    Your senior DBA?... "Estimate Statistics" estimates the statistics for the percentage you specify (10pct, 50pct etc), not for the first 1000 rows... hence

    Use the highest estimate practical.
    Assistance is Futile...

  5. #5
    Join Date
    Dec 2002
    Posts
    28
    If I have a many large tables where I currently compute statistics. I notice that the compute statistics job hangs on the large table. No alerts are raised on the DB and I have to kill the job each day. Would the solution be to estimate statistics? i.e Can a table get too large whereby computing statistics becomes unfeasible and estimate statistics need to be used

    I cant use DBMS Stats as Oracle DB version is 806.

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    it wasnt hanging probaby just taking a long time, how big were the tables.

    estimate with the default 10% is good enough for most people, unless you have some funny data patterns

  7. #7
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    I think the appropriate question here is why are you still running 8.0.6? I would recommend that your Senior DBA check out this Metalink note of 118156.1

    Also, if your Senior DBA is telling you things like this...

    Our senior DBA advises not to use Estimate statistics on very large tables as only the first 1000 rows are estimated, thus optimiser can make wrong decision if table data from rows 1000 onwards is completely different.
    It would behoove you to do some reading and research. You may just find yourself as the new Senior DBA for your company.
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by waitecj
    Your senior DBA?... "Estimate Statistics" estimates the statistics for the percentage you specify (10pct, 50pct etc), not for the first 1000 rows... hence
    If sample is not specified that oracle chooses to estimate stats on just 1000+ rows..


    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  9. #9
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    So the answer would be to use SAMPLE, not to say "ESTIMATE doesn't work as i'd like it so i'll use COMPUTE"...
    Assistance is Futile...

  10. #10
    Join Date
    Dec 2002
    Posts
    28
    What would cause poor performance each time the statistics are computed with the latest data. Poor performance is is not reported on a particular table but whole DB.

    The trend seams to be each night the stats job is run. If this job manages to complete the next day users complain of very poor performance.

    I have to stick to Oracle 8.0.6 at this organisation. No choice about it.

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