
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

1.Documented here ... http://downloadwest.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.

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 overrun. Even if statistics job is killed performance is very poor for users afterwards.

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...

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.

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

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

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"

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...

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

Forum Rules

Click Here to Expand Forum to Full Width
