Oracle table statistics

[#1]

Dear All Members,

Is there any way where we can check whether current statistics in table are outdated in oracle? In OTN i found using view "DBA_TAB_MODIFICATIONS" we can gather timestamps of statistics

But can we use this to compare the quality of statistics in DB tables?

Regards,

Shanaka.

[Former Member]

Thanks both Michal and Fiel,

Michal, yest i meant those statistics which are calculated long time ago.....

To my knowledge what oracle do is it keeps on analyzing statistics and once it realize current statistics were collected long time ago it automatically update current statistics ... If this is the normal method then there can be situation if tables are updating rapidly statistics can become old very soon ..

Regards,

Shanaka.

[fidel_vales]

Shanaka Chandrasekera wrote:
 
To my knowledge what oracle do is it keeps on analyzing statistics and once it realize current statistics were collected long time ago it automatically update current statistics ... If this is the normal method then there can be situation if tables are updating rapidly statistics can become old very soon ..

Hi,

Oracle has a job (10g) or "autotask" (11g) that keeps looking the tables for "stalle" statistics. It is basically the same as BRCONNECT does. That is the reason why SAP deactivate that job (and that the job calculate histograms some times where it is not needed and ....)

This means that you should not have such job working.

Again, the date when statistics were calculated is not "important" and should not be a focus of any analysis (if the table do not changes why calculate statistics?)

[Former Member]

Thanks Fidel, does this job (collecting statistics ) have any frequent of running or it automatically trigger once statistics become old?.... In my case i have tables which are updating rapidly 

[fidel_vales]

Shanaka Chandrasekera wrote:
Thanks Fidel, does this job (collecting statistics ) have any frequent of running or it automatically trigger once statistics become old?.... In my case i have tables which are updating rapidly 

Do you mean the oracle job?

if so, it MUST NOT RUN in SAP systems.

You should run instead BRCONNECT (from DB13) once per day.

And again, it does NOT look at the date of the statistics but how much the table has changed (for that it needs the DBA_TAB_MODIFICATIONS, that is also used by BRCONNECT)

check note 974781 Oracle internal maintenance jobs

[Former Member]

Thanks Fidal,

We are running those jobs in DB13 daily and not from oracle. I'll initially check these notes...

Thanks for those information provided

Thanks all for valuable replies