DB2 - L

 View Only
  • 1.  using runstats with "use profile"

    Posted Nov 22, 2021 09:03 AM
    We are trying runstats with "use profile". We find it runs for a long time requiring many colgroups. 
    What we dont find a way of knowing is how this might be worth running and improve performance.
    Might anyone have experience using runstats with "use profile"?
    And if so might there be a way of determining how this would impact performance?
    thanks
    Bill

    ------------------------------
    williamgiannelliMe
    ------------------------------


  • 2.  RE: using runstats with "use profile"

    Posted Nov 22, 2021 09:18 AM
    I would recommend a careful use of Profiles... you can indeed end up with 1000's of pointless COLGROUPs taking tons of CPU for no reason.
    To find out if they are needed or not is non-trivial but there are a load of vendor tools out there that do it all for money!

    Doing it for free takes a bit of jiggling but you can get there yourself:
    Back up everything to do with profiles
    Explain all of the static and dynamic sql that uses any of the tables that you have in profiles
    Delete all related table/column data from SYSCOLDIST and SYSCOLDISTSTATS, SYSKEYTARGETSTATS, SYSKEYTGTDISTSTATS etc
    Explain all sql again
    Compare explains. Resurrect any data for those access paths that improved with the old data

    This is a lot of work and of course very tricky to do in production... best is in a sandbox with faked stats...


    Roy Boxwell

    SOFTWARE ENGINEERING GmbH and SEGUS Inc.
    -Product Development-

    Vagedesstrasse 19
    40479 Dusseldorf/Germany
    Tel. +49 (0)211 96149-675
    Fax +49 (0)211 96149-32
    Email: R.Boxwell@seg.de
    Web http://www.seg.de
    Link zur Datenschutzerklärung

    Software Engineering GmbH
    Amtsgericht Düsseldorf, HRB 37894
    Geschäftsführung: Gerhard Schubert, Ulf Heinrich




  • 3.  RE: using runstats with "use profile"

    Posted Nov 22, 2021 10:03 AM

    We're not currently using profiles, but did take a look at them.

    Db2 v12 made some changes such that, if you use the defaults for some of the new v12 zparms, will automatically create/update RUNSTATS profiles with any recommendations that get written to the SYSSTATFEEDBACK table.  Which means your profiles might contain a mix of legitimate helpful recommendations along with a bunch of "one-off" recommendations from infrequent ad-hoc queries, or even some queries which may have been poorly written or flat out written in error.

     

    Bill Gallagher | Senior Systems Engineer, DBA

     






  • 4.  RE: using runstats with "use profile"

    Posted Nov 26, 2021 09:36 AM
    I know from experience that it does help but mostly in specific situations. We had a situation where a dynamic query started using the "wrong" index after an IPL. I tried everything I could think of to get it to use the "right" index. This was a severe issue for us as this was a highly executed statement. Our system was suffering greatly just due to this single statement. After several hours I remembered the Use Profile runstats. I ran a runstats on the table with Use Profile and invalidated the dynamic sql cache. Just like magic the statement switched to the "right" index and all was well. We had both IBM and BMC support involved for several hours. 
    So that was a life saver for us but I also caution using it routinely. I started to do that but actually ran the public storage group out of space due to the sorting required for a large statement. So we keep this in our box of tricks to use when necessary. I still use it from time to time, am not afraid to use it. 
    Hope this helps.

    ------------------------------
    RussellPetersCentral Technology Services
    ------------------------------