Re: how to deal with this situation? truncate table + statistics

If you found the current statistics works well, why not keep the statistics 
data?

 On 9/13/05, The Human Fly <sjaffarhussain@xxxxxxxxx> wrote: 
> 
> Appreciated you guys valuable opinions and great response towards my 
> question.
> 
> Carel, my mgmt. carry the misconception that this database has lot of
> deletes, updates and huge inserts, we need to analzye it every day. I
> tried to explain them technically, I couldn't convience more to them.
> Since this procedure going good, I dont want to temper or bring more
> issues.
> Though, its a 24x7 database, but, the tradining hrs are only 3 hrs in
> the morning and 3 hrs in the evening. When I put these options in
> front of my boss and mgmt. they have given permission to analyze those
> tables before evening session starts and regarding the morning session
> issue, they says analyze table before truncate and leave the old
> statistics.
> I once again thank you guys for your immense response.
> 
> 
> On 9/13/05, Carel-Jan Engel <cjpengel.dbalert@xxxxxxxxx> wrote:
> > Hi Syed.
> >
> > First of all, why re-analyze the schema over and ovr again? If it 
> contains tables that are not truncated, the statistics of those tables are 
> probably OK.
> >
> > For the queries that access the truncated tables, you might also 
> consider to use stored outlines, if you know a suitable plan that works well 
> against the almost empty and the almost filled tables. Without invalidating 
> the cursors a bad plan, created against the almost empty table, will 
> probably survive, even when the table has grown. Version mgmt. will take you 
> more effort when using stored outlines, because they need to be created 
> again after the SQL has changed.
> >
> >
> > Best regards,
> >
> > Carel-Jan Engel
> >
> > ===
> > If you think education is expensive, try ignorance. (Derek Bok)
> > ===
> >
> > On Tue, 2005-09-13 at 12:30 +0300, The Human Fly wrote:
> > Hello List,
> >
> > Well, I have a little bit tricky or I can say confused situation. We
> > have database for shares tradining. Beginning of every day, a couple
> > of tables will be truncted and we analyze the schema.
> > The situation is, when we truncate and analzye those tables,
> > statitiscs are updated as rum_rows=0, and blocks =0. The entire day,
> > about 100,000 records comes in each of those truncated tables. But,
> > for the optimizer, the data dictionary statistics provides nothing,
> > when there are records coming every second, we dont re-analyze again,
> > i.e. old statistics available to the optimizer.
> > How can we deal with this situation? Is it better to collect the
> > statistics before table get truncated, so that statistics available
> > for those tables to help otpimizer? If so, lets say, if our num_rows
> > says 100,000 and truncated the table, when records start coming in a
> > table, optimizer assumed that this table has 100,000 records, may be
> > the empty table starts filling from 10 records and keep growing.?
> > What is the best way to deal this?
> >
> >
> >
> >
> >
> 
> 
> 
> --
> Best Regards,
> Syed Jaffar Hussain
> OCP 8i & 9i DBA,
> Banque Saudi Fransi,
> Saudi Arabia
> 
> ----------------------------------------------------------------------------------------
> "It is your atittude, not your aptitude that determins your altitude."
> --
> http://www.freelists.org/webpage/oracle-l
> 



-- 
Welcome, Fangxin.Lou from China
Home: http://www.anysql.net/en/
Mydul: http://www.anysql.net/en/mydul.html

Other related posts: