| estimate statistics [message #53345] |
Fri, 13 September 2002 10:49  |
Keith Sherman
Messages: 4
Registered: December 2001
|
Junior Member |
|
|
|
I've seen many different 'recommendations' on what an appropriate percent sample size for analyze table estimate statistics but there must be a way a single definitive number. I've seen people recommend just about every number over 9%. In fact I have two popular books by the same publisher that recommends 33% in one and then in another 3%. I have always been under the impression that under 20% leads to the CBO missing indexes, but there must be a real number out there to go by, does anyone know of any? Is there some sort of formula one can use?
|
|
|
|
| Re: estimate statistics [message #53346 is a reply to message #53345] |
Fri, 13 September 2002 13:06  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
If you know that your data is randomly spread in value throughout your table/index then you can use a lower percentage. If your data is loaded in some order you could have a problem if the % is too low. If the histograms derived from reading just a sample of your data are very different from the histograms which would be derived from reading all the blocks (compute statistics) then you have a problem. I stick to low % untill I hit something which warrants more - then I would probably go straight to 'compute'; Rather than continuing to use "analyze", you should consider dbms_stats. It's supposed to be faster and better (especially with newer features like partitions etc). Check the documentation for the differences.
I simply submit a daily dbms_stast job something like this:
declare
l_job number;
begin
dbms_job.submit( job => l_job,
what =>
'begin /* get stats */
dbms_stats.GATHER_SCHEMA_STATS (''SCOTT'');
end;',
next_date => sysdate,
interval => 'trunc(sysdate+1)+22/24' );
commit;
dbms_output.put_line( ' your job is # '|| l_job);
end;
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
