Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jul 2012
    Posts
    16

    Unanswered: DB2 RUNSTATS repeatedly executing on some tables

    Hello,

    I have DB2 9.7 database (on Windows 2008) with some quite busy tables (lots of reading, constant inserts).
    By running "db2 list utilities" I've noticed very frequent automatic RUNSTATS operations, that occur every few seconds! , sometimes even on the same table twice at the time. So, basicaly, runstats are performing all the time especially when DB trafic is high. Single operation usually takes a few seconds, after that a new one starts, and so on....
    C:\IBM\SQLLIB\bnd>db2 list utilities

    ID = 1371143
    Type = RUNSTATS
    Database Name = MYDB
    Partition Number = 0
    Description = MYSCHEMA.DOCUMENTS
    Start Time = 23.02.2015 12:07:45.770415
    State = Executing
    Invocation Type = User
    Throttling:
    Priority = Unthrottled


    C:\IBM\SQLLIB\bnd>db2 list utilities

    ID = 1371143
    Type = RUNSTATS
    Database Name = MYDB
    Partition Number = 0
    Description = MYSCHEMA.DOCUMENTS
    Start Time = 23.02.2015 12:07:45.770415
    State = Executing
    Invocation Type = User
    Throttling:
    Priority = Unthrottled


    Automatic mainenance is configured like this:
    Automatic maintenance (AUTO_MAINT) = ON
    Automatic database backup (AUTO_DB_BACKUP) = OFF
    Automatic table maintenance (AUTO_TBL_MAINT) = ON
    Automatic runstats (AUTO_RUNSTATS) = ON
    Automatic statement statistics (AUTO_STMT_STATS) = ON
    Automatic statistics profiling (AUTO_STATS_PROF) = OFF
    Automatic profile updates (AUTO_PROF_UPD) = OFF
    Automatic reorganization (AUTO_REORG) = OFF


    Maintenance is set for ONLINE window from 22:00 to 6:00 in the morning, but the runstats operations I'm talking about are occuring all the time (outside the maintenance window).
    This is strange, because even the data in the table is beeing changed havily, the runstats evaluation, as I understand, should occur only at 2 hour intervals.

    Could someone, please help me to understand what it's going on? Why the table statistics updates are running so frequently?

    Thanks in advance!
    Regards,
    DM
    Last edited by domenm; 02-23-15 at 07:37.

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Can you post the full XML file contents that result from this query:

    call sysproc.automaint_get_policyfile('MAINTENANCE_WIND OW', 'mymaintwindow.xml')

    (the file mymaintwindow.xml will be in the directory identified by db2 registry variable DB2INSTPROF , subdirectory %DB2INSTANCE%\tmp ). for example, by default, that will be in path C:\PROGRAMDATA\IBM\DB2\DB2COPY1\db2\tmp\

  3. #3
    Join Date
    Jul 2012
    Posts
    16
    Hi db2mor,
    thanks for your reply.

    Here is the contents of mymaintwindow.xml file:

    <?xml version="1.0" encoding="UTF-8"?>
    <DB2MaintenanceWindows
    xmlns="http://www.ibm.com/xmlns/prod/db2/autonomic/config" >

    <!-- Online Maintenance Window -->
    <OnlineWindow Occurrence="During" startTime="22:00:00" duration="08" >
    <DaysOfWeek>All</DaysOfWeek>
    <DaysOfMonth>1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31</DaysOfMonth>
    <MonthsOfYear>All</MonthsOfYear>
    </OnlineWindow>
    </DB2MaintenanceWindows>

    As you can see there is only online window configured for 8 hours during the night. But the problem I described is present also during the day. What concerns me most is the performance impact of runstats on production system during the busy hours.


    BR,
    DM

  4. #4
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Which fixpack of V9.7 are you running here?

    Is the system-time of the windows-server the same as your local time?

    Are you sure that no application (or script) is running runstats explicitly?

    In the db2diag.log do you see entries for the automated runstats on these tables at the times they are running (and if so, do the timestamps match your local time)?

  5. #5
    Join Date
    Jul 2012
    Posts
    16
    Hi db2mor,

    1. Version of DB2 is:
    Informational tokens are "DB2 v9.7.500.4305", "special_27968", "IP23287_27968",
    and Fix Pack "5".

    2. The system time is correct and synchronized with the NTP server.

    3. About the application or script running runstats I'm not so sure. It is possible, since I'm not the only person who has access to the db. Is there any way to check this?

    4. using the default diag level 3 there is not much to see regarding the runstats, so I raised the logging to 4. There are a lot of these inside the file:

    2015-02-23-14.02.36.516000+060 I104295F564 LEVEL: Info
    PID : 2300 TID : 52644 PROC : db2syscs.exe
    INSTANCE: MYINST NODE : 000 DB : MYDB
    APPHDL : 0-13192 APPID: 192.168.245.191.62016.150223123145
    AUTHID : MYUSER
    EDUID : 52644 EDUNAME: db2agent (MYDB) 0
    FUNCTION: DB2 UDB, trace services, sqlt_logerr_string (secondary logging fu, probe:0
    MESSAGE : Runstats:
    DATA #1 : String, 69 bytes
    No. of pages used in statistics heap for table MYDB.ACCOUNTS: 1120

    I would expect something like this: "START : Automatic Runstats: runstats has started on table....", but there are no such records. Strange.
    I also attached db2diag to this post.


    BR,
    DM


    db2diag_4_runstats.txt

  6. #6
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Yes, I would expect that the db2diag (at diaglevel 3) should show the automated runstats entries.
    Additionally, for the 'list utilities...' your original post shows:
    Invocation Type = User
    and I might expect this to not be User if it was auto-runstats.

    Time to check the details of the connection that is triggering the runstats? source/authid/location/etc.
    'list applications show detail' can help.

    Also, You can find this out from snapshot/monitoring functions, but if you have a linux (or other unix) lpar available with a db2-server on it then it's quicker to catalog the windows-db2-server and database from linux/aix and on linux/aix run db2pd against the windows database.

  7. #7
    Join Date
    Jul 2012
    Posts
    16
    OK, I'll try to do some monitoring on connections/sessions and see if anything can be seen there.

    Thanks for your help.

    BR,
    DM

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You have real-time statistics enabled (AUTO_STMT_STATS), which will trigger RUNSTATS whenever DB2 thinks the table has been sufficiently modified.
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6
    If the runstats are caused by auto_maint then the invocation type is automatic. Well that's the case in 10.5 anyway

  10. #10
    Join Date
    Jul 2012
    Posts
    16
    Hi,

    thanks to all for your answers.

    I've checked the apps that excplicit call the runstats, but I couldn't find any.
    Also, I agree that in case of auto_maint the invocation would be "Automatic" (checked in another db with 9.7 ver).
    From above it seems like some kind of script or app is running runstats, but when I temporarily disable AUTO_RUNSTATS (db2 update db cfg using AUTO_RUNSTATS off) the "phantom" runstats stops immediately. So, I assume that runstats are automatic any way.

    I have also noticed a strange thing about the stats_time from the system tables for the specific user table and it seems the time is not updated when the table is refreshed "automatically". But if I invoke manual runstats from cmd, the time is updated correctly. Could this mean that phantom runstats don't complete successfuly?
    Please note the "STATS_TIME" from the query result that is 1 day old (the time the runstats was executed manualy).

    db2 list utilities

    ID = 1460139
    Type = RUNSTATS
    Database Name = MYDB
    Partition Number = 0
    Description = MYSCHEMA.DOCUMENTS
    Start Time = 24.02.2015 09:57:32.557842
    State = Executing
    Invocation Type = User
    Throttling:
    Priority = Unthrottled

    db2 select substr(tabname,1,24) as tabname, stats_time from syscat.tables where type = 'T' and tabname='DOCUMENTS' and tabschema='MYSCHEMA'

    TABNAME STATS_TIME
    ------------------------ --------------------------
    DOCUMENTS 2015-02-23-10.32.25.313000
    Last edited by domenm; 02-24-15 at 05:29.

  11. #11
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Did you try this combination below , as implied by n_i ?

    Automatic runstats (AUTO_RUNSTATS) = ON
    Automatic statement statistics (AUTO_STMT_STATS) = OFF

    With those settings the maintenance-window stats will run, but the real time stats will not run (according to docs).

    Docs suggest that the real time statistics (RTS) (i.e. when AUTO_STMT_STATS=ON) won't update the catalog in real time, but instead keeps stats in memory (with deferred background catalog update) , and are limited to 5 seconds per statement. That may explain what you see.

  12. #12
    Join Date
    Jul 2012
    Posts
    16
    I've set the runstats and stmt stats as you suggested and currently I'm not seeing any runstats running.
    Automatic runstats (AUTO_RUNSTATS) = ON
    Automatic statement statistics (AUTO_STMT_STATS) = OFF

    I'm not sure whether this could be solution for us, since the AUTO_STMT_STATS is set to ON by default. I've googled a little, but so far I couldn't find any negative experience regarding disabled AUTO_STMT_STATS. But let's see... I also hope the maintenance-window stats will continue to work.
    In your opinion, is it wise at all to have the param disabled? What's best practice you guys are using regarding auto maintenance?


    BR,
    DM

  13. #13
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    It's up to you to monitor the performance of queries against high-flux tables (subject to frequent inserts/update/deletes) -i.e to determine if access plans deterioriate because daily stats are insufficient. It may be that daily stats are enough for stable plans. Otherwise a different approach might suit, especially if the tables are high cardinality and rapidly growing, perhaps a runstats-profile specific to those tables that does sampling at some low % of rows would allow limited runstats at scheduled intervals during daytime..
    If the CPU and I/O overhead of AUTO_STMT_STATS is acceptable (you would need to measure) then you also need to decide whether anything should change. In other words, was the frequent runstats really causing a problem?

  14. #14
    Join Date
    Jul 2012
    Posts
    16
    Actually, by disabling RTS I've got 5-7% CPU usage improvement and that's not neglectable.
    I think daily stats should be fine regarding the current DB load, so I'm going to leave this config and monitor the overall DB behavior/performance.

    Thanks again to all of you for your responses.
    BR,
    DM

  15. #15
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by domenm View Post
    I've got 5-7% CPU usage improvement
    What do you mean by that? Your CPU cores are now idle more of the time? Was your system CPU-bound in the first place?
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •