which oracle statistics methood should be used

[Former Member]

Hello,

we are running ecc6 on oracle 10.2.0.4 and AIX 5.3 - DB size - 150GB.

we had a few performance problems and we would like to know what is the

recommended methood for running statistics on oracle 10G - i read a lot

of oracle notes regarding this isuue and i'm very confused - please

make some order among all information.

what is the recommended methood for running oracle statistics?

should the dbms_stats be ran from the oracle scheduler? this should be

done for large DB's according to note 408532.moreover -according to

note 974781 this job should be disabled among also a few more oracle

jobs - auto_space_advisor_job, oracle_ocm.mgmt_config_job,

oracle_ocm.mgmt_stats_config_job

does the db13 update optimizer statistics job is the only statistics

job which should be run on the system? (according to note 132861)

what about the dictionary statistics? - according to note 838725

what about system statistics? - why is the noworkload is the

recommended methood when we ran the brconnect according to note 927295

with the noworkload methood - afterwards every select sentence we tried

to run on sys tables (v$ tables - basically the metadata) did not

return anything - it was just stuck- when we started the AWR (which

can't be done in noworkload methood) and ran the system and dictionary

statistics - every select sentence we tried to run on sys tables (v$

tables) worked fine.

so when does system and dictionary statistics should be run and with

which methood (you claim in note 927295 in section 12 that workload

statistics is not recommended)?

Regards,

Moshe

[stefan_koehler]

Hello Moshe,

> what is the recommended methood for running oracle statistics?

SAP recommends to use the BRTools for collecting oracle database statistics (table/index statistics, system statistics, dictionary statistics). Why ? .. because of the BRTools have an internal algorithm for sampling sizes, time frames for collecting statistics, etc..

You can find the rules here: http://help.sap.com/saphelp_sm32/helpdata/de/f4/81e93a637bfd70e10000000a11402f/content.htm

The algorithm for the sample sizes is described in sapnote #892296 (Point 9).

> should the dbms_stats be ran from the oracle scheduler?

No you should disable the automatic statistic gathering run of oracle (check sapnote #974781 for that recommendation).

When or what statistics should be collected you already have found the correct sapnote #838725.

Regards

Stefan

[Former Member]

Hello stefan,

thank you for the replay.

basically i only have to run on a daily basis the update optimizer statistics from db13 and the system & dictionary statistics from the command line using brconnect once ina while?

what about the problem i mentioned regarding the noworkload and workload methoods?

there are afew notes telling to run the workload methood and others who say that this is not recommended by sap.

what about the awr - without it i have problem running select on sys tables - v$ table views - only after running the dbms_stats it worked fine

Regards,

Moshe

[stefan_koehler]

Hello Moshe,

> basically i only have to run on a daily basis the update optimizer statistics from db13 and ....

We run the statistic collection for tables/indexes daily ... dictionary and system statistics are collected once in a quarter. We use an external scheduler for calling brconnect .. but you can also do this with DB13.

> what about the problem i mentioned regarding the noworkload and workload methoods?

I can only find the suggestion to collect the system statistics with NOWORKLOAD (regarding sapnote #838725).

Since system statistics are more easily determined using the NOWORKLOAD method, we recommend this method for SAP.

> afterwards every select sentence we tried to run on sys tables (v$ tables - basically the metadata) did not return anything - it was just stuck

This information is just not enough ... What is the wait event for the corresponding SQL? Mostly these problems have nothing todo with the system statistics .. it is has something todo with the dictionary statistics (and for these statistics the NOWORKLOAD / WORKLOAD topic is irrelevant).

Regards

Stefan