Skip to Content
avatar image
Former Member

which oracle statistics methood should be used

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Nov 08, 2009 at 03:02 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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