topic Re: Oracle stats, update row data only? in Operating System - HP-UX

Oracle stats, update row data only?

TwoProc — Tue, 11 Sep 2007 13:40:02 GMT

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.

Re: Oracle stats, update row data only?

Hein van den Heuvel — Tue, 11 Sep 2007 16:26:07 GMT

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.

Re: Oracle stats, update row data only?

TwoProc — Thu, 20 Sep 2007 17:17:25 GMT

Thanks Hein,

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

Re: Oracle stats, update row data only?

Patti Johnson — Mon, 24 Sep 2007 15:05:17 GMT

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

Re: Oracle stats, update row data only?

TwoProc — Tue, 25 Sep 2007 09:34:48 GMT

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.

Re: Oracle stats, update row data only?

Yogeeraj_1 — Tue, 25 Sep 2007 11:07:06 GMT

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

Re: Oracle stats, update row data only?

Volker Borowski — Wed, 26 Sep 2007 10:29:46 GMT

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

Re: Oracle stats, update row data only?

TwoProc — Wed, 26 Sep 2007 10:42:30 GMT

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.

Re: Oracle stats, update row data only?

Yogeeraj_1 — Fri, 28 Sep 2007 05:35:23 GMT

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

Re: Oracle stats, update row data only?

TwoProc — Mon, 01 Oct 2007 07:51:53 GMT

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?

Re: Oracle stats, update row data only?

Yogeeraj_1 — Wed, 03 Oct 2007 03:46:33 GMT

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

Re: Oracle stats, update row data only?

TwoProc — Mon, 15 Oct 2007 10:50:22 GMT

Thanks once again Yogeeraj.