Re: how to deal with this situation? truncate table + statistics
- From: The Human Fly <sjaffarhussain@xxxxxxxxx>
- To: Carel-Jan Engel <cjpengel.dbalert@xxxxxxxxx>, Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 13 Sep 2005 15:17:02 +0300
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."
--
//www.freelists.org/webpage/oracle-l
Other related posts:
