Question Re: Oracle table statistics in Technology Q&A

Oracle table statistics

Former Member — Mon, 12 Nov 2012 06:59:10 GMT

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.

Re: Oracle table statistics

Former Member — Mon, 12 Nov 2012 07:52:39 GMT

Hi Shanaka,

As far as I know that the DBA_TAB_MODIFICATIONS table used to collect quality of statistics value by SAP, also. There's only one exception that at the first step, without paying an attention to the DBA_TAB_MODIFICATIONS records, the system creates new statistics by using "-f stats -t all" parameters. At the second and further steps, new stats will be created for the tables that are need to be collected.

In short, because of the DB20 transaction reflects DBA_TAB_MODIFICATIONS table to store quality of the table stats, both values should be same.

In addition to the information above, you can read the notes, below;

Note 588668 - FAQ: Database statistics

Note 408527 - Checking the statistics using DBA_TAB_MODIFICATIONS,

Best regards,

Orkun Gedik

Re: Oracle table statistics

Former Member — Mon, 12 Nov 2012 08:31:28 GMT

Statistics do not get outdated, you only need to collect new statistics, when the data changes significantly. Thus the trigger is not the last_analyzed field.

588668 - FAQ: Database statistics

Cheers Michael

Re: Oracle table statistics

fidel_vales — Mon, 12 Nov 2012 08:57:25 GMT

Shanaka Chandrasekera wrote:
Dear All Members,
Is there any way where we can check whether current statistics in table are outdated in oracle?

First you have to define "OUTDATED" and this is the main problem a lot of people has.

do you mean that the statistics were calculated long time ago? (to be define what is "long time ago)

of do you mean that the table content has changed a lot? (also to be define what "a lot" means)

The first point should NOT be used (see note 825653 - Oracle: Common misconceptions point 7)

The second one is already used by BRCONNECT as already mentioned and, by defaut, it check that the change is bigger than 50%. for details check the documentation Internal Rules for Update Statistics

Re: Oracle table statistics

Former Member — Mon, 12 Nov 2012 09:49:38 GMT

HI Orkun,

Thanks for the reply,

I think in note 588668 question number 28 is relevant to my query thanks for sharing this and note 408527 can use to create user define threshold to collect statistic on rapidly updating tables.

Will check them .

Thanks and best regards,

Shanaka.

Re: Oracle table statistics

Former Member — Mon, 12 Nov 2012 10:26:47 GMT

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.

Re: Oracle table statistics

fidel_vales — Mon, 12 Nov 2012 10:40:33 GMT

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?)

Re: Oracle table statistics

Former Member — Mon, 12 Nov 2012 11:00:03 GMT

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

Re: Oracle table statistics

fidel_vales — Mon, 12 Nov 2012 11:11:51 GMT

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

Re: Oracle table statistics

Former Member — Mon, 12 Nov 2012 11:48:48 GMT

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