BW : Run stats for a very long time.

[Former Member]

Hi,

The UpdateStats job takes up to 48h to complete the run. It appears that the problem happened for a while (at least 3 months). The command used is : brconnect -u / -jid STATS20080413100000 -c -f stats -t ALL -f monit I remark that the sample size of method Estimate is setting at 100, and almost of tables are partitionned. My questions are : 1) Where is the sample size set in SAP for partitionned tables ? 2) How can I modify it ? Here is an extraction of "Tables with longest duration of collecting statistics for owner R3"

Table--

---

Duration-Rows/oldRows/newMeth./Samp. -

---

[m:s] /BIC/FZPM_CEVT--288:12(106219000)-106353900EH/P100 /BIC/FZPM_C04-----285:47-(57471500)57513900---EH/P100 /BIC/FZPMCINEVT-219:34-(107708800)-107947000 EH/P100

Thanks for your input.

[Former Member]

Hi,

agree with the others - once we faced a similar problem . Someone changed the percentage to 100 ( = compute)

on our 250 Gbyte partitioned table and it took more than 2 hours, we changed to 5% and now we speed through

it in about 17 minutes.

For the CBO very often a smaller value is sufficient for generating statistics for good execution plans.

Make sure you have the MONITORING option set for your tables so statistics are only calculated when necessary to give a representative view on your data.

Bye

Yk

[Former Member]

Additionally you can consider to run the stats generation in parallel, you can either add -p <degree> or set stats_parallel_degree in the initSID.sap. The degree can be something like your number of cpu cores to start, degrees of 4 or 8 work quite well on smaller systems.

We always do parallel stats generation for the larger db's.

Regards

Michael

[fidel_vales]

> 1) Where is the sample size set in SAP for partitionned tables ?

The sample size for any table is specified in DB21. You should only specify there special cases.

Change it there.

> 2) How can I modify it ?

DB21

Estimate 100% is the same as compute.

This is not done by SAP, someone changed it manually. Try to find out who did it and ask why.

The command you use is not correct:

> brconnect -u / -jid STATS20080413100000 -c -f stats -t ALL -f monit

-f monit is used to activate monitoring.

• Monitoring is activated only once

• Oracle 10g has monitoring activated, it is not necessary to do it

SAP Note 628590 tells you:

Caution:
-
This new option is no longer required in Oracle 10g of higher. The monitoring attribute is automatically active there.

you should use

> brconnect -u / -c -f stats -t ALL

[Former Member]

Thank you for your inputs.

I did check the DB21 transactions, there is no special indications for partitionned tables.

If someone changed the sample size for statistics , I should see those changes in DB21. Or there is other places to change it ?

[Former Member]

Hi,

Please try by below command

brconnect -u / -c -f stats -t ALL -f monit -p 4

Here -p 4= 4 parallel processes

Hope this helps.

[former_member204746]

which Oracle version are you using?

With Oracle 10g, recalculating stats in much faster well, at least, it was much faster for me.

what is the size of your system ? Are you using BW? which version?

P1000 is too high, use a smaller value. it can be chagned in initSID.sap file.

[Former Member]

Oracle db 10.2.0.2.0

BW release 3.5 with SEM-BPS 4.0

Database size is about 3TB.