Hi,

I have a question regarding statistics in Oracle and how well they work with Baan.

Assuming a baan database size of around 130 gigs (gld410 around 25 gigs). What would be the difference between compute statistics on tables and estimate statistics 10% on tables? Could we expect equal performance? Is 10% to low?



Also,

Should we generate statistics for indexes aswell?

Thanks,

Mark

also... does oracle automatically compute statistics on tables when you do an import?

If you estimate statistics, then a sample size of 20% is considered adequate. I was encouraged to use a sample size of 30% and do with our Baan database. I analyze our tables once a week, and that interval is a judgement call.

When you use Oracle Export it has a parameter called STATISTICS and defaults ESTIMATE if not specified. You can specify the statistics options to be either ESTIMATE, COMPUTE, or NONE.

If you use sample size that exceeds 49% on estimate statistics , it will cause a compute of the statistics on a table.

Gilbert Guymer
Database Administrator
Lufkin Industries, Inc.

Hi,
usually fro oracle statistic 10% would be enough, but for the very big tables maby you will not get inprovement with 10%, and then you must do higher procent or COMPUTE STATISTICS.

I use my script with COMPUTE STATISTICS for all tables and index.
Just put the shema name!

Pedja


SET PAGES 999
SET FEEDBACK OFF
SET HEADING OFF
SET PAGESIZE 999
SET LINES 79
SET VERIFY OFF
ACCEPT ime PROMPT 'Shema : '
SPOOL cal_stat_exe.SQL
SELECT 'ANALYZE TABLE '||UPPER('&ime')||'.'||object_name||' COMPUTE STATISTICS;' FROM dba_objects
WHERE owner = UPPER('&ime') AND OBJECT_TYPE='TABLE';
SPOOL OFF;
SPOOL cal_stat_idx_exe.SQL
SELECT 'ANALYZE INDEX '||UPPER('&ime')||'.'||object_name||' COMPUTE STATISTICS;' FROM dba_objects
WHERE owner = UPPER('&ime') AND OBJECT_TYPE='INDEX';
SPOOL OFF;
@cal_stat_exe.SQL
@cal_stat_idx_exe.SQL

As you can read from above threads for most situations a small estimate is sufficient. Maybe even 1% is fine for your very large tables. But it al depends on:
- the query
- the optimizer
As the optimizer is version dependend I can't judge for all your queries but in general 10% is sufficient.

The easy way to test is is by experience. If you face an extreme performance drop on these tables you know that you have to change it.

If you specified the 010 bit in the ora_storage (or related file) for the table/ index optimisation (5th field or so that is 0214 by default) than you automatically create statistics (estimate) when creating the table. Keep in mind that the statiscs only show non zero values when uploading with bdbpost -f.

Hope this helps,
Dick