IDS Forum

Re: Long lasting dostats

Posted By: Habichtsberg, Reinhard
Date: Tuesday, 13 December 2016, at 12:10 p.m.

In Response To: Re: Long lasting dostats (BENJAMIN THOMPSON)

Hi Art and Ben,

thank you for your answers..

I now tried to only update statistics medium.

PSORT_NPROCS=50; export PSORT_NPROCS
dbaccess - dostats_xxxxxxxxxx.sql

dostats_xxxxxxxxxxx.sql:
DATABASE yyyyy;
SET ISOLATION DIRTY READ;
SET EXPLAIN ON;
SET PDQPRIORITY 80;
UPDATE STATISTICS MEDIUM FOR TABLE xxxxxxxxxx (pe_re_lfd, pe_vo, pe_pos, pe_ebene, pe_pruefung, pe_pruefende, pe_pruefart, pe_wfstatusnach, pe_ergebnis, pe_ergebnisart, pe_berinr, pe_textnr, pe_folgeber
inr, pe_jobid, pe_jobart, pe_pruefzeit, pe_pruefzeit_ges, pe_wfnr, pe_nachbeding, pe_folge_ga, pe_lfdnr_verf);
SET EXPLAIN OFF;

This run finished after a minute or so with error
-647 Error evaluating math library function function-name.

The data type of the argument is correct, but the value passed to the
function is not appropriate for the math function. Check that you are
passing a legitimate value to the function.

Hmm, have we reached any limits?

TIA
Reinhard.

-----Ursprüngliche Nachricht-----
Von: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Im Auftrag von BENJAMIN THOMPSON
Gesendet: Montag, 12. Dezember 2016 16:54
An: ids@iiug.org
Betreff: Re: Long lasting dostats [38296]

Hi Richard,

Interesting to see that turning on all the PDQ bells and whistles isn't much faster in terms of time elapsed, if at all. My tests on server grade hardware show setting PDQPRIORITY and PSORT_NPROCS only reduces the time by 10-15% at the expense of a lot more I/O.

This is a complicated subject and I did a lot of testing for IIUG presentations in 2015 and 2016 on this subject :-)

Using "statlevel fragment" might help in your situation if any of the partitions do not get updated. There is a requirement for an sbspace to use this feature (see docs).

To speed up the LOW mode part you can switch on USTLOWTS_SAMPLE which is really the only tuneable parameter. There are no reasons not to use this I know if. I think that without fragmented indices you cannot benefit from parallelism in LOW mode.

Your explain plan is only for the HIGH or MEDIUM mode work. For MEDIUM and HIGH mode I usually recommend not using PDQ and updating stats on multiple tables in parallel to maximise throughput because it is difficult to improve the run times on individual tables (see above). If you have one huge table, much bigger than all the rest, this strategy will not work. I do set DBUPSPACE='0:50:0' to increase sort memory from 15 Mb to 50 Mb, but this parameter does not make a lot of difference as long as you don't disable using indices for sorting.

If you do set PDQPRIORITY you will enable light scans which avoids churning your buffer cache: this is one possible advantage of PDQ.

Ask yourself if it's a problem that UPDATE STATISTICS takes a long time. If you can just leave it running, leaving PDQPRIORITY off will minimise the number of threads and the impact on the rest of your system. Yes there is an issue that a running UPDATE STATISTICS process can cause waits on data dictionary mutexes while it's running but this depends on your application and schema (specifically use of foreign keys).

Finally changing your distributions to MEDIUM mode from HIGH mode will speed things up and probably won't affect query plans unless you use LIKE queries on chars/varchars.

Ben.

*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.

Messages In This Thread

• Long lasting dostats
  Habichtsberg, Reinhard -- Friday, 9 December 2016, at 7:41 a.m.
  • Re: Long lasting dostats
    Art Kagel -- Friday, 9 December 2016, at 8:34 a.m.
    • AW: Long lasting dostats
      Habichtsberg, Reinhard -- Friday, 9 December 2016, at 10:22 a.m.
      • Re: Long lasting dostats
        Art Kagel -- Friday, 9 December 2016, at 11:00 a.m.
      • Re: Long lasting dostats
        Art Kagel -- Friday, 9 December 2016, at 11:01 a.m.
        • AW: Long lasting dostats
          Habichtsberg, Reinhard -- Friday, 9 December 2016, at 12:28 p.m.
        • Re: Long lasting dostats
          Habichtsberg, Reinhard -- Monday, 12 December 2016, at 3:46 a.m.
          • Re: Long lasting dostats
            Art Kagel -- Monday, 12 December 2016, at 6:33 a.m.
          • Re: Long lasting dostats
            BENJAMIN THOMPSON -- Monday, 12 December 2016, at 10:54 a.m.
            • Re: Long lasting dostats
              Habichtsberg, Reinhard -- Tuesday, 13 December 2016, at 12:10 p.m.
              • Re: Long lasting dostats
                Art Kagel -- Tuesday, 13 December 2016, at 1:40 p.m.
              • Re: Long lasting dostats
                BENJAMIN THOMPSON -- Tuesday, 13 December 2016, at 5:13 p.m.
                • Re: Long lasting dostats
                  Fernando Nunes -- Tuesday, 13 December 2016, at 8:40 p.m.
                • AW: Long lasting dostats
                  Habichtsberg, Reinhard -- Wednesday, 14 December 2016, at 6:09 a.m.
                • Re: Long lasting dostats
                  Habichtsberg, Reinhard -- Thursday, 15 December 2016, at 7:06 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.