| ANALYZE TABLE Frequently [message #65743] |
Mon, 20 December 2004 01:30  |
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  |
Frank
Messages: 7901
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
