cancel
Showing results for 
Search instead for 
Did you mean: 

"Last analyze" of Table DBA_TAB_COLUMNS has not been updated

Former Member
0 Kudos

Recently PRD system's performance became worse.

And I look for some trace, I found statics has not been updated since 12/08/2006.

ex; "Last analyze" of Table DBA_TAB_COLUMNS

I can't understand why this situation happend, because we have excuted command "brconnect -u / -c -f stats -t all" weekly.

And we did migration last Aug.

That is exactly last statics was updated.

At that time, we changed Oracle version( 8 → 9), Hardware(Server, EMC).

If someone experiences that problem or similar one, please tell me the reason and how to act.

Please help on this.

Regards

Toshi

Accepted Solutions (0)

Answers (1)

Answers (1)

lbreddemann
Active Contributor
0 Kudos

Hi Toshi,

hmm... usually this should not happen if there is some "life" in the database - that is: the stats are recalculated whenever the amount of data changed about 50% up or down.

So in theory it may be possible that this threshold was never reached for all tables (although this is <b>very</b> unlikely).

If I were you I'd check the log-files of the last brconnect -f stats runs and check the settings in the init<sid>.sap file (perhabs the threshold had been changed...).

If this does not reveal the problem source - perhabs a support message might help.

KR Lars

Former Member
0 Kudos

Hi KR Lars.

Thanks you for your advise.

Actually, I think the amount of data has never changed since Migration had excuted.

And I found many tables that haven't been changed stats were Master-Table.

That is why, stats keeps old information -- perhaps.

Is it common that update all stats of table and index (include Master) after Migration?

Best Regards

Toshi

lbreddemann
Active Contributor
0 Kudos

Hi Toshi,

Ok, that does explain the age of the statistics.

Usually it's recommended to update the statistics after actions where the storage structure of the database changes (heterogenous copy, reorganisation etc.).

After a DB version upgrade - well I would recommend it but it may be to much doing...

After a Patch/Patchset: only if there is a specific reason to do so.

Anyhow: if your system runs on a performance level you like - the stats are OK, the optimizer does what you want.

KR Lars

Former Member
0 Kudos

Hi KR Lars.

Generally a performance level keeps "not bad", but some PGM's one became much worse.

This is why our system's performance level is not preferable.

Maybe I will propose that we update the stats.

But it is not easy for us to explain the reason why we didn't update the stats since we had excuted Migration.

Thank you for your kindly advice.

Toshi