Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle stats, update row data only?

SOLVED
Go to solution
TwoProc
Honored Contributor

Oracle stats, update row data only?

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.
We are the people our parents warned us about --Jimmy Buffett
11 REPLIES
Hein van den Heuvel
Honored Contributor
Solution

Re: Oracle stats, update row data only?

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
Honored Contributor

Re: Oracle stats, update row data only?

Thanks Hein,

Looks like an example would be:
exec dbms_stats.set_table_stats(OWNNAME=>'MYUSER',TABNAME=>'MYTABLE',NUMROWS=>'10000000');
We are the people our parents warned us about --Jimmy Buffett
Patti Johnson
Respected Contributor

Re: Oracle stats, update row data only?

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
Honored Contributor

Re: Oracle stats, update row data only?

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.
We are the people our parents warned us about --Jimmy Buffett
Yogeeraj_1
Honored Contributor

Re: Oracle stats, update row data only?

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Volker Borowski
Honored Contributor

Re: Oracle stats, update row data only?

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
Honored Contributor

Re: Oracle stats, update row data only?

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.
We are the people our parents warned us about --Jimmy Buffett
Yogeeraj_1
Honored Contributor

Re: Oracle stats, update row data only?

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
TwoProc
Honored Contributor

Re: Oracle stats, update row data only?

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?
We are the people our parents warned us about --Jimmy Buffett
Yogeeraj_1
Honored Contributor

Re: Oracle stats, update row data only?

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
TwoProc
Honored Contributor

Re: Oracle stats, update row data only?

Thanks once again Yogeeraj.
We are the people our parents warned us about --Jimmy Buffett