Oracle stats, update row data only?

[TwoProc]

I've got stats being calculated on some tables that are HUUUGE, and I feel that the only thing that really matters is that the rowcount statistic gets updated ( and I could very well be wrong ). Is there is a way to update *just the row count statistic*, leaving all other stats alone? I'd like to be able to trace and test some runtimes on some queries with this as a tool to see if I could eliminate some huge and long statistics gathering that runs every night. Or, does anyone else have a suggestion to tackle this same issue in another manner?

Thanks for your assistance.

[Hein van den Heuvel · Accepted solution]

Hi John

I think it is perfectly reasonable and desirable ti jsut set the stats yourself if you pretty much know what they will be like. No point in counting if you know the outcome.

Oracle provices several procedures for this in the DBMS_STATS package

Check out (google), SET_TABLE_STATS and perhaps also SET_COLUMN_STATS

hth,
Hein van den Heuvel
HvdH Performance Consulting.

[TwoProc]

Thanks Hein,

Looks like an example would be:
exec dbms_stats.set_table_stats(OWNNAME=>'MYUSER',TABNAME=>'MYTABLE',NUMROWS=>'10000000');

[Patti Johnson]

How are you generating stats now?
With dbms_stats.gather_schema_stats use
options=> 'GATHER STALE'
and it will only generate statistics for tables with the MONITORING flag set to Y.
Then you can set all the tables that do not require stats to be generated daily to N and the rest to Y.

You could then update row counts manually if necessary.

Patti

[TwoProc]

Good suggestion Patti!

What defines when a table is stale? Or is it simply that if the monitoring flag is set, then then the table will always be reviewed, regardless of how old the stats are?

Thanks for the suggestion.

[Yogeeraj_1]

hi John,

Maybe this can shed some more lights.

http://download-east.oracle.com/docs/cd/A81042_01/DOC/server.816/a76992/stats.htm#25754

Basically, when you designate a table for monitoring, Oracle will watch for DML activity on it. It will capture the approximate number of inserts, updates or deletes for the table.

Later, DBMS_STATS can be used to gather stats on "stale" tables. Tables are classified "stale" by the act of monitoring them.

It takes a combination of MONITORING and DBMS_STATS with a "gather stale" to update the statistics for stale tables -- monitoring does not analyze the tables themselves, only counts (approximately) the DML hits on it.

The criteria for monitoring implying stales is that the total number of inserts, deletes and updates for the table exceeds 10% of the number of rows in the table as recorded by the last analyze.

Hope this helps!

kind regards
yogeeraj

[Volker Borowski]

Hi,

DBMS_STATS can use multiple parallel threads utilizing parallel Query to calculate the stats.

It's not just the rowcount that matters but the amount of blocks and esp. the number of blevels of the indexes.

I.e. if you increase the numer of rows, but do not adjust the number of blocks accordingly, the optimizer might very well make wrong decisions because you'll change the "rows per block" ratio wehn doing so.

Worst will be if, i.e. your HUGE table is an item table and you need to join a header table to this item table. Having a high number of rows, but a low number of blocks on the item table can drive the optimizer to select the item table as the first table for the join (which in general is a bad descision, when you want to key-join header and item tables).

Go for real stats and go with 8 or 16 threads to calculate them.

Volker

[TwoProc]

Yogeeraj,
Thanks for the tips, we are checking this option out. My concern is that it seems that if a table is monitored, then it must change by 10% before it needs regathering. Well, with tables that are over 100 gig in size, a change of 10% is 10 Gig, and I won't achieve that change on a daily basis. Sadly some of these largest tables need gathers done daily to avoid problems, and I wouldn't get that on these. Which, in itself makes no sense. Statistically, the largest tables we have have, are not statistically different than before, only 40k rows larger or so.
However, your recommendation does suggest to me a hybrid approach which utilizes monitoring for more regularly sized tables, and just specific gathers on large tables.

Volker,
thanks for your input, but I am already doing these in parallel. But, your input is appreciated.

[Yogeeraj_1]

hi,

Our database is not as huge as yours and we managed to implement a simple routine that runs every night:

DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'dbms_stats.gather_schema_stats( ownname => user, options => ''GATHER STALE'' );'
,next_date => trunc(sysdate+1)+((00+(20/60))/24)
,interval => 'trunc(sysdate+1)+((00+(20/60))/24)'
,no_parse => FALSE
);
END;
/


Good luck.

kind regards
yogeeraj

[TwoProc]

thanks Yogeeraj for the nice example.

In your example, does it run 20 minutes after midnight tomorrow? And the "interval" parameter is 20 minutes, does it rerun in 20 minutes?

[Yogeeraj_1]

hi,

Answers to your questions:

>does it run 20 minutes after midnight tomorrow?
Yes.
SQL>alter session set nls_date_format='dd/mm/yyyy hh24:mi';

Session altered.

SQL>select trunc(sysdate+1)+((00+(20/60))/24) from dual;

TRUNC(SYSDATE+1)
________________
04/10/2007 00:20

SQL>


> the "interval" parameter is 20 minutes, does it rerun in 20 minutes?

interval => 'trunc(sysdate+1)+((00+(20/60))/24)'

This always evaluates to the next day at 00:20.

You may wish to make appropriate modifications.


hope this helps!
kind regards
yogeeraj

[TwoProc]

Thanks once again Yogeeraj.