Home » RDBMS Server » Performance Tuning » ANALYZE TABLE Frequently
ANALYZE TABLE Frequently [message #65743] Mon, 20 December 2004 01:30 Go to next message
Shikha
Messages: 36
Registered: January 2002
Member
Is there any harm analyzing a table more than once a day?

I have four tables of about 3 MIllions records which are
queried heavily. And there are some inserts into these tables
every now and then.

I usually do an analyze after every dml. Each DML affects about
15000 records at a time. It takes about 6 minutes at the most
and 45 seconds at the least to analyze.

Is that a problem to analyze frequently?

And I have an IOT with zero records. Why does it take a really
long time to analyze that? (Now, Please don't ask why I tried
to analyze a zero records table.)

 

TIA,

Shikha
Re: ANALYZE TABLE Frequently [message #65744 is a reply to message #65743] Mon, 20 December 2004 03:10 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
After an analyze I expect that all queries in the shared pool that touched the analyzed table are invalidated. (Note: I'm not sure this happens, but I expect this behaviour. Can't test it right now) This would mean that Oracle has to parse them again to recalculate the execution plan.
This would cause a decrease in performance.
If the distribution of yor data does not radically change, there would be no need for an analyze after each dml-operation !

hth
Previous Topic: Bookon tuning
Next Topic: Using Stored Outlines
Goto Forum:
  


Current Time: Mon Sep 28 00:59:18 CDT 2020