RUNSTATS hitting SYSSTATS a lot

mark chorley

RUNSTATS hitting SYSSTATS a lot

Z/OS DB2 v10  

we have two TS that need to add extra parts on the end.

both TS have 380 parts already, only partitioning indexes.

TS1 has a TABLE with 19 columns

TS2 has a TABLE with 624 columns

both tables are very large, so we thought to only do the RUNSTATS for the new parts

TS1 runs quickly but TS2 is taking over 4 mins per part to run.

doing a trace i see that the I/O to SYSSTATS is massive, like 600,000 for a 5 min trace on TS2

something very odd is happening, anyone any clues?

we tried different RUNSTATS options and just going from TABLE and INDEX to just TABLE, didn't seem to make any difference.

below are the stats from some insight traces

Read I/O Summary by DB and Pageset

Database Reads Avg/Max Pages Tot Prf Seq Prf Lst Prf Dyn Prf Sync
Pageset Partition Seconds Read Pgs/Pct Pgs/Pct Pgs/Pct Pgs/Pct Pgs/Pct
-------- --------- ------- -------- -------- -------- -------- -------- -------
DSNDB06 3 0.0003 3 0 0 0 0 3
DSNRTX02 0 0.0003 0.0 0.0 0.0 0.0 100.0

DSNDB06 5169 0.0004 5169 0 0 0 0 5169
DSNTCX01 0 0.0176 0.0 0.0 0.0 0.0 100.0

DSNDB06 8 0.0003 8 0 0 0 0 8
DSNTTX01 0 0.0006 0.0 0.0 0.0 0.0 100.0

DSNDB06 10 0.0017 68 64 0 0 64 4
SYSRTSTS 0 0.0128 94.1 0.0 0.0 94.1 5.9

DSNDB06 311297 0.0007 614260 614064 0 0 614060 196
SYSSTATS 0 0.1518 100.0 0.0 0.0 100.0 0.0

DSNDB06 2 0.0003 2 0 0 0 0 2
SYSTSDBA 1 0.0003 0.0 0.0 0.0 0.0 100.0

DSNDB06 1 0.0003 1 0 0 0 0 1
SYSTSTAB 1 0.0003 0.0 0.0 0.0 0.0 100.0

DSNDB06 12 0.0077 116 110 0 0 110 6
SYSTSTPT 1 0.0224 94.8 0.0 0.0 94.8 5.2

DSNDB06 1 0.0003 1 0 0 0 0 1
SYSTSTSP 1 0.0003 0.0 0.0 0.0 0.0 100.0

 

Write I/O Summary by DB and Pageset 

Total Sync Async SES DB2
Pages Pages Pages Write Write
Database Pageset Part. Avg Sec Max Sec Wrt Wrt %SIO Wrt %AIO I/Os I/Os
-------- -------- ----- ------- ------- ----- ----- ---- ----- ---- ----- -----
DSNDB01 DSNLLX01 0 0.0053 0.0053 6 0 0 6 100 0 0
DSNDB01 DSNLLX02 0 0.0022 0.0036 5 0 0 5 100 0 0
DSNDB01 DSNLUX01 0 0.0010 0.0013 3 1 33 2 67 0 0
DSNDB01 DSNLUX02 0 0.0017 0.0017 2 0 0 2 100 0 0
DSNDB01 SYSLGRNX 0 0.0014 0.0021 6 0 0 6 100 0 0
DSNDB01 SYSUTILX 0 0.0033 0.0033 3 0 0 3 100 0 0
DSNDB06 SYSRTSTS 0 0.0010 0.0010 2 0 0 2 100 0 0
DSNDB06 SYSSTATS 0 0.0026 0.0057 14 0 0 14 100 0 0
DSNDB06 SYSTSCOL 1 0.0032 0.0032 12 0 0 12 100 0 0
DSNDB06 SYSTSTAB 1 0.0007 0.0007 1 0 0 1 100 0 0
DSNDB06 SYSTSTPT 1 0.0014 0.0018 3 1 33 2 67 0 0
DSNDB06 SYSTSTSP 1 0.0014 0.0014 1 0 0 1 100 0 0
DSNDB07 DSN32K01 0 0.0023 0.0023 4 0 0 4 100 0 0
DSNDB07 DSN32K02 0 0.0013 0.0013 1 0 0 1 100 0 0
DSNDB07 DSN4K01 0 0.0011 0.0011 1 0 0 1 100 0 0
DSNDB07 DSN4K02 0 0.0013 0.0013 1 0 0 1 100 0 0

 

I/O Summary by Database and Pageset

Avg Max SES DB2 Total Avg
Part. Read Pages Read Read Wrt Wrt Wrt Pages Write
Database Pageset Num I/Os Read Time Time I/Os I/Os I/Os Wrt Time
-------- -------- ----- ----- ----- ------ ------ ---- ---- ----- ----- ------
-------- -------- ----- ----- ----- ------ ------ ---- ---- ----- ----- ------
DSNDB06 DSNTCX01 ===== 5144 5144 0.000 0.017 0 0 0 0 0.000

DSNDB06 DSNTTX01 0 8 8 0.000 0.000 0 0 0 0 0.000
-------- -------- ----- ----- ----- ------ ------ ---- ---- ----- ----- ------
DSNDB06 DSNTTX01 ===== 8 8 0.000 0.000 0 0 0 0 0.000

DSNDB06 SYSRTSTS 0 10 68 0.001 0.012 0 0 0 0 0.000
-------- -------- ----- ----- ----- ------ ------ ---- ---- ----- ----- ------
DSNDB06 SYSRTSTS ===== 10 68 0.001 0.012 0 0 0 0 0.000

DSNDB06 SYSSTATS 0 312K 615K 0.000 0.151 0 4 4 14 0.000
-------- -------- ----- ----- ----- ------ ------ ---- ---- ----- ----- ------
DSNDB06 SYSSTATS ===== 312K 615K 0.000 0.151 0 4 4 14 0.000

DSNDB06 SYSTSCOL 1 0 0 0.000 0.000 0 1 1 12 0.003
-------- -------- ----- ----- ----- ------ ------ ---- ---- ----- ----- ------
DSNDB06 SYSTSCOL ===== 0 0 0.000 0.000 0 1 1 12 0.003

DSNDB06 SYSTSDBA 1 2 2 0.000 0.000 0 0 0 0 0.000
-------- -------- ----- ----- ----- ------ ------ ---- ---- ----- ----- ------
DSNDB06 SYSTSDBA ===== 2 2 0.000 0.000 0 0 0 0 0.000

DSNDB06 SYSTSTAB 1 1 1 0.000 0.000 0 0 0 0 0.000
-------- -------- ----- ----- ----- ------ ------ ---- ---- ----- ----- ------
DSNDB06 SYSTSTAB ===== 1 1 0.000 0.000 0 0 0 0 0.000

DSNDB06 SYSTSTPT 1 11 115 0.007 0.022 0 1 1 2 0.000

 

 

Dynamic Prefetch by DB and Pageset 

Part. Dyn Prf Dyn Prf Total Total
Database Pageset Num Pages I/Os Pages I/Os
-------- -------- ----- ------- ------- ------- -------
DSNDB01 DSNLUX01 0 0 0 2 2
DSNDB01 DSNLUX02 0 0 0 2 2
DSNDB01 SYSUTILX 0 0 4 0 4
DSNDB06 DSNDCX01 0 34 1261 38 1265
DSNDB06 DSNDDH01 0 0 0 2 2
DSNDB06 DSNDPX01 0 0 0 8 8
DSNDB06 DSNDSX01 0 0 0 2 2
DSNDB06 DSNDTX01 0 0 0 2 2
DSNDB06 DSNRTX01 0 0 0 3 3
DSNDB06 DSNRTX02 0 0 0 3 3
DSNDB06 DSNTCX01 0 0 0 5102 5102
DSNDB06 DSNTTX01 0 0 0 8 8
DSNDB06 SYSRTSTS 0 64 6 68 10
DSNDB06 SYSSTATS 0 612079 310059 612279 310259
DSNDB06 SYSTSCOL 1 0 42138 0 42138
DSNDB06 SYSTSDBA 1 0 0 2 2
DSNDB06 SYSTSTAB 1 0 0 1 1
DSNDB06 SYSTSTPT 1 110 6 115 11
DSNDB06 SYSTSTSP 1 0 0 1 1

 

 

 

Edited By:
mark chorley[Organization Members] @ Dec 22, 2016 - 09:46 AM (America/Eastern)

Chris Tee

RUNSTATS hitting SYSSTATS a lot
(in response to mark chorley)
Mark


Have you checked SYSTABLESPACESTATS and SYSINDEXSPACESTATS for SYSSTATS and its indexes to see if it would benefit from a reorg?


regards


Chris


________________________________
From: mark chorley <[login to unmask email]>
Sent: 22 December 2016 14:29
To: [login to unmask email]
Subject: [DB2-L] - RUNSTATS hitting SYSSTATS a lot


Z/OS DB2 v10

we have two TS that need to add extra parts on the end.

both TS have 380 parts already, only partitioning indexes.

TS1 has a TABLE with 19 columns

TS2 has a TABLE with 624 columns

both tables are very large, so we though to only do the RUNSTATS for the new parts

TS1 runs quickly but TS2 is taking over 4 mins per part to run.

doing a trace i see that the I/O to SYSSTATS is massive, like 600,000 for a 5 min trace on TS2

something very odd is happening, anyone any clues?

we tried different RUNSTATS options and just going from TABLE and INDEX to just TABLE, didn't seem to make any difference.

below are the stats from some insight traces

Read I/O Summary by DB and Pageset

Database Reads Avg/Max Pages Tot Prf Seq Prf Lst Prf Dyn Prf Sync
Pageset Partition Seconds Read Pgs/Pct Pgs/Pct Pgs/Pct Pgs/Pct Pgs/Pct
-------- --------- ------- -------- -------- -------- -------- -------- -------
DSNDB06 3 0.0003 3 0 0 0 0 3
DSNRTX02 0 0.0003 0.0 0.0 0.0 0.0 100.0

DSNDB06 5169 0.0004 5169 0 0 0 0 5169
DSNTCX01 0 0.0176 0.0 0.0 0.0 0.0 100.0

DSNDB06 8 0.0003 8 0 0 0 0 8
DSNTTX01 0 0.0006 0.0 0.0 0.0 0.0 100.0

DSNDB06 10 0.0017 68 64 0 0 64 4
SYSRTSTS 0 0.0128 94.1 0.0 0.0 94.1 5.9

DSNDB06 311297 0.0007 614260 614064 0 0 614060 196
SYSSTATS 0 0.1518 100.0 0.0 0.0 100.0 0.0

DSNDB06 2 0.0003 2 0 0 0 0 2
SYSTSDBA 1 0.0003 0.0 0.0 0.0 0.0 100.0

DSNDB06 1 0.0003 1 0 0 0 0 1
SYSTSTAB 1 0.0003 0.0 0.0 0.0 0.0 100.0

DSNDB06 12 0.0077 116 110 0 0 110 6
SYSTSTPT 1 0.0224 94.8 0.0 0.0 94.8 5.2

DSNDB06 1 0.0003 1 0 0 0 0 1
SYSTSTSP 1 0.0003 0.0 0.0 0.0 0.0 100.0



Write I/O Summary by DB and Pageset

Total Sync Async SES DB2
Pages Pages Pages Write Write
Database Pageset Part. Avg Sec Max Sec Wrt Wrt %SIO Wrt %AIO I/Os I/Os
-------- -------- ----- ------- ------- ----- ----- ---- ----- ---- ----- -----
DSNDB01 DSNLLX01 0 0.0053 0.0053 6 0 0 6 100 0 0
DSNDB01 DSNLLX02 0 0.0022 0.0036 5 0 0 5 100 0 0
DSNDB01 DSNLUX01 0 0.0010 0.0013 3 1 33 2 67 0 0
DSNDB01 DSNLUX02 0 0.0017 0.0017 2 0 0 2 100 0 0
DSNDB01 SYSLGRNX 0 0.0014 0.0021 6 0 0 6 100 0 0
DSNDB01 SYSUTILX 0 0.0033 0.0033 3 0 0 3 100 0 0
DSNDB06 SYSRTSTS 0 0.0010 0.0010 2 0 0 2 100 0 0
DSNDB06 SYSSTATS 0 0.0026 0.0057 14 0 0 14 100 0 0
DSNDB06 SYSTSCOL 1 0.0032 0.0032 12 0 0 12 100 0 0
DSNDB06 SYSTSTAB 1 0.0007 0.0007 1 0 0 1 100 0 0
DSNDB06 SYSTSTPT 1 0.0014 0.0018 3 1 33 2 67 0 0
DSNDB06 SYSTSTSP 1 0.0014 0.0014 1 0 0 1 100 0 0
DSNDB07 DSN32K01 0 0.0023 0.0023 4 0 0 4 100 0 0
DSNDB07 DSN32K02 0 0.0013 0.0013 1 0 0 1 100 0 0
DSNDB07 DSN4K01 0 0.0011 0.0011 1 0 0 1 100 0 0
DSNDB07 DSN4K02 0 0.0013 0.0013 1 0 0 1 100 0 0



I/O Summary by Database and Pageset

Avg Max SES DB2 Total Avg
Part. Read Pages Read Read Wrt Wrt Wrt Pages Write
Database Pageset Num I/Os Read Time Time I/Os I/Os I/Os Wrt Time
-------- -------- ----- ----- ----- ------ ------ ---- ---- ----- ----- ------
-------- -------- ----- ----- ----- ------ ------ ---- ---- ----- ----- ------
DSNDB06 DSNTCX01 ===== 5144 5144 0.000 0.017 0 0 0 0 0.000

DSNDB06 DSNTTX01 0 8 8 0.000 0.000 0 0 0 0 0.000
-------- -------- ----- ----- ----- ------ ------ ---- ---- ----- ----- ------
DSNDB06 DSNTTX01 ===== 8 8 0.000 0.000 0 0 0 0 0.000

DSNDB06 SYSRTSTS 0 10 68 0.001 0.012 0 0 0 0 0.000
-------- -------- ----- ----- ----- ------ ------ ---- ---- ----- ----- ------
DSNDB06 SYSRTSTS ===== 10 68 0.001 0.012 0 0 0 0 0.000

DSNDB06 SYSSTATS 0 312K 615K 0.000 0.151 0 4 4 14 0.000
-------- -------- ----- ----- ----- ------ ------ ---- ---- ----- ----- ------
DSNDB06 SYSSTATS ===== 312K 615K 0.000 0.151 0 4 4 14 0.000

DSNDB06 SYSTSCOL 1 0 0 0.000 0.000 0 1 1 12 0.003
-------- -------- ----- ----- ----- ------ ------ ---- ---- ----- ----- ------
DSNDB06 SYSTSCOL ===== 0 0 0.000 0.000 0 1 1 12 0.003

DSNDB06 SYSTSDBA 1 2 2 0.000 0.000 0 0 0 0 0.000
-------- -------- ----- ----- ----- ------ ------ ---- ---- ----- ----- ------
DSNDB06 SYSTSDBA ===== 2 2 0.000 0.000 0 0 0 0 0.000

DSNDB06 SYSTSTAB 1 1 1 0.000 0.000 0 0 0 0 0.000
-------- -------- ----- ----- ----- ------ ------ ---- ---- ----- ----- ------
DSNDB06 SYSTSTAB ===== 1 1 0.000 0.000 0 0 0 0 0.000

DSNDB06 SYSTSTPT 1 11 115 0.007 0.022 0 1 1 2 0.000





Dynamic Prefetch by DB and Pageset

Part. Dyn Prf Dyn Prf Total Total
Database Pageset Num Pages I/Os Pages I/Os
-------- -------- ----- ------- ------- ------- -------
DSNDB01 DSNLUX01 0 0 0 2 2
DSNDB01 DSNLUX02 0 0 0 2 2
DSNDB01 SYSUTILX 0 0 4 0 4
DSNDB06 DSNDCX01 0 34 1261 38 1265
DSNDB06 DSNDDH01 0 0 0 2 2
DSNDB06 DSNDPX01 0 0 0 8 8
DSNDB06 DSNDSX01 0 0 0 2 2
DSNDB06 DSNDTX01 0 0 0 2 2
DSNDB06 DSNRTX01 0 0 0 3 3
DSNDB06 DSNRTX02 0 0 0 3 3
DSNDB06 DSNTCX01 0 0 0 5102 5102
DSNDB06 DSNTTX01 0 0 0 8 8
DSNDB06 SYSRTSTS 0 64 6 68 10
DSNDB06 SYSSTATS 0 612079 310059 612279 310259
DSNDB06 SYSTSCOL 1 0 42138 0 42138
DSNDB06 SYSTSDBA 1 0 0 2 2
DSNDB06 SYSTSTAB 1 0 0 1 1
DSNDB06 SYSTSTPT 1 110 6 115 11
DSNDB06 SYSTSTSP 1 0 0 1 1







-----End Original Message-----

Venkat Srinivasan

RE: RUNSTATS hitting SYSSTATS a lot
(in response to mark chorley)

How many rows are there on coldist, colstats etc (tables in sysstats) for the affected pageset. That may explain the access need.

Venkat 

In Reply to mark chorley:

Z/OS DB2 v10  

we have two TS that need to add extra parts on the end.

both TS have 380 parts already, only partitioning indexes.

TS1 has a TABLE with 19 columns

TS2 has a TABLE with 624 columns

both tables are very large, so we thought to only do the RUNSTATS for the new parts

TS1 runs quickly but TS2 is taking over 4 mins per part to run.

doing a trace i see that the I/O to SYSSTATS is massive, like 600,000 for a 5 min trace on TS2

something very odd is happening, anyone any clues?

we tried different RUNSTATS options and just going from TABLE and INDEX to just TABLE, didn't seem to make any difference.

below are the stats from some insight traces

Read I/O Summary by DB and Pageset

Database Reads Avg/Max Pages Tot Prf Seq Prf Lst Prf Dyn Prf Sync
Pageset Partition Seconds Read Pgs/Pct Pgs/Pct Pgs/Pct Pgs/Pct Pgs/Pct
-------- --------- ------- -------- -------- -------- -------- -------- -------
DSNDB06 3 0.0003 3 0 0 0 0 3
DSNRTX02 0 0.0003 0.0 0.0 0.0 0.0 100.0

DSNDB06 5169 0.0004 5169 0 0 0 0 5169
DSNTCX01 0 0.0176 0.0 0.0 0.0 0.0 100.0

DSNDB06 8 0.0003 8 0 0 0 0 8
DSNTTX01 0 0.0006 0.0 0.0 0.0 0.0 100.0

DSNDB06 10 0.0017 68 64 0 0 64 4
SYSRTSTS 0 0.0128 94.1 0.0 0.0 94.1 5.9

DSNDB06 311297 0.0007 614260 614064 0 0 614060 196
SYSSTATS 0 0.1518 100.0 0.0 0.0 100.0 0.0

DSNDB06 2 0.0003 2 0 0 0 0 2
SYSTSDBA 1 0.0003 0.0 0.0 0.0 0.0 100.0

DSNDB06 1 0.0003 1 0 0 0 0 1
SYSTSTAB 1 0.0003 0.0 0.0 0.0 0.0 100.0

DSNDB06 12 0.0077 116 110 0 0 110 6
SYSTSTPT 1 0.0224 94.8 0.0 0.0 94.8 5.2

DSNDB06 1 0.0003 1 0 0 0 0 1
SYSTSTSP 1 0.0003 0.0 0.0 0.0 0.0 100.0

 

Write I/O Summary by DB and Pageset 

Total Sync Async SES DB2
Pages Pages Pages Write Write
Database Pageset Part. Avg Sec Max Sec Wrt Wrt %SIO Wrt %AIO I/Os I/Os
-------- -------- ----- ------- ------- ----- ----- ---- ----- ---- ----- -----
DSNDB01 DSNLLX01 0 0.0053 0.0053 6 0 0 6 100 0 0
DSNDB01 DSNLLX02 0 0.0022 0.0036 5 0 0 5 100 0 0
DSNDB01 DSNLUX01 0 0.0010 0.0013 3 1 33 2 67 0 0
DSNDB01 DSNLUX02 0 0.0017 0.0017 2 0 0 2 100 0 0
DSNDB01 SYSLGRNX 0 0.0014 0.0021 6 0 0 6 100 0 0
DSNDB01 SYSUTILX 0 0.0033 0.0033 3 0 0 3 100 0 0
DSNDB06 SYSRTSTS 0 0.0010 0.0010 2 0 0 2 100 0 0
DSNDB06 SYSSTATS 0 0.0026 0.0057 14 0 0 14 100 0 0
DSNDB06 SYSTSCOL 1 0.0032 0.0032 12 0 0 12 100 0 0
DSNDB06 SYSTSTAB 1 0.0007 0.0007 1 0 0 1 100 0 0
DSNDB06 SYSTSTPT 1 0.0014 0.0018 3 1 33 2 67 0 0
DSNDB06 SYSTSTSP 1 0.0014 0.0014 1 0 0 1 100 0 0
DSNDB07 DSN32K01 0 0.0023 0.0023 4 0 0 4 100 0 0
DSNDB07 DSN32K02 0 0.0013 0.0013 1 0 0 1 100 0 0
DSNDB07 DSN4K01 0 0.0011 0.0011 1 0 0 1 100 0 0
DSNDB07 DSN4K02 0 0.0013 0.0013 1 0 0 1 100 0 0

 

I/O Summary by Database and Pageset

Avg Max SES DB2 Total Avg
Part. Read Pages Read Read Wrt Wrt Wrt Pages Write
Database Pageset Num I/Os Read Time Time I/Os I/Os I/Os Wrt Time
-------- -------- ----- ----- ----- ------ ------ ---- ---- ----- ----- ------
-------- -------- ----- ----- ----- ------ ------ ---- ---- ----- ----- ------
DSNDB06 DSNTCX01 ===== 5144 5144 0.000 0.017 0 0 0 0 0.000

DSNDB06 DSNTTX01 0 8 8 0.000 0.000 0 0 0 0 0.000
-------- -------- ----- ----- ----- ------ ------ ---- ---- ----- ----- ------
DSNDB06 DSNTTX01 ===== 8 8 0.000 0.000 0 0 0 0 0.000

DSNDB06 SYSRTSTS 0 10 68 0.001 0.012 0 0 0 0 0.000
-------- -------- ----- ----- ----- ------ ------ ---- ---- ----- ----- ------
DSNDB06 SYSRTSTS ===== 10 68 0.001 0.012 0 0 0 0 0.000

DSNDB06 SYSSTATS 0 312K 615K 0.000 0.151 0 4 4 14 0.000
-------- -------- ----- ----- ----- ------ ------ ---- ---- ----- ----- ------
DSNDB06 SYSSTATS ===== 312K 615K 0.000 0.151 0 4 4 14 0.000

DSNDB06 SYSTSCOL 1 0 0 0.000 0.000 0 1 1 12 0.003
-------- -------- ----- ----- ----- ------ ------ ---- ---- ----- ----- ------
DSNDB06 SYSTSCOL ===== 0 0 0.000 0.000 0 1 1 12 0.003

DSNDB06 SYSTSDBA 1 2 2 0.000 0.000 0 0 0 0 0.000
-------- -------- ----- ----- ----- ------ ------ ---- ---- ----- ----- ------
DSNDB06 SYSTSDBA ===== 2 2 0.000 0.000 0 0 0 0 0.000

DSNDB06 SYSTSTAB 1 1 1 0.000 0.000 0 0 0 0 0.000
-------- -------- ----- ----- ----- ------ ------ ---- ---- ----- ----- ------
DSNDB06 SYSTSTAB ===== 1 1 0.000 0.000 0 0 0 0 0.000

DSNDB06 SYSTSTPT 1 11 115 0.007 0.022 0 1 1 2 0.000

 

 

Dynamic Prefetch by DB and Pageset 

Part. Dyn Prf Dyn Prf Total Total
Database Pageset Num Pages I/Os Pages I/Os
-------- -------- ----- ------- ------- ------- -------
DSNDB01 DSNLUX01 0 0 0 2 2
DSNDB01 DSNLUX02 0 0 0 2 2
DSNDB01 SYSUTILX 0 0 4 0 4
DSNDB06 DSNDCX01 0 34 1261 38 1265
DSNDB06 DSNDDH01 0 0 0 2 2
DSNDB06 DSNDPX01 0 0 0 8 8
DSNDB06 DSNDSX01 0 0 0 2 2
DSNDB06 DSNDTX01 0 0 0 2 2
DSNDB06 DSNRTX01 0 0 0 3 3
DSNDB06 DSNRTX02 0 0 0 3 3
DSNDB06 DSNTCX01 0 0 0 5102 5102
DSNDB06 DSNTTX01 0 0 0 8 8
DSNDB06 SYSRTSTS 0 64 6 68 10
DSNDB06 SYSSTATS 0 612079 310059 612279 310259
DSNDB06 SYSTSCOL 1 0 42138 0 42138
DSNDB06 SYSTSDBA 1 0 0 2 2
DSNDB06 SYSTSTAB 1 0 0 1 1
DSNDB06 SYSTSTPT 1 110 6 115 11
DSNDB06 SYSTSTSP 1 0 0 1 1

 

 

 

mark chorley

RE: RUNSTATS hitting SYSSTATS a lot
(in response to Chris Tee)

Chris, 

good point, i thought that our housekeeping would have done this, although as we were testing this was the SYSPROG DB2 i didn't check. Did the REORG, RUNSTATS and reran and it went through fine. 

So something to check up on and make sure we do regular REORG/STATS on all non and prod systems for this TS is we don't already.

Just a bit surprised that the one with the columns took so much longer, obviously doing a lot of work.

Thanks and Happy Xmas everyone.

Chris Tee

RUNSTATS hitting SYSSTATS a lot
(in response to mark chorley)
Mark


Glad to hear that it's sorted out. If you have specified TABLE(ALL) on your runstats, I would expect the table with more columns to take much longer as it has 30x more columns than the other table.


regards


Chris


________________________________
From: mark chorley <[login to unmask email]>
Sent: 23 December 2016 12:38
To: [login to unmask email]
Subject: [DB2-L] - RE: RUNSTATS hitting SYSSTATS a lot


Chris,

good point, i thought that our housekeeping would have done this, although as we were testing this was the SYSPROG DB2 i didn't check. Did the REORG, RUNSTATS and reran and it went through fine.

So something to check up on and make sure we do regular REORG/STATS on all non and prod systems for this TS is we don't already.

Just a bit surprised that the one with the columns took so much longer, obviously doing a lot of work.

Thanks and Happy Xmas everyone.

-----End Original Message-----

Terry Purcell

RE: RUNSTATS hitting SYSSTATS a lot
(in response to mark chorley)

Just because it has more columns doesn't necessarily mean it should hit SYSSTATS more often. Single column cardinalities are stored in SYSCOLUMNS, which is in SYSTSCOL.

I would look at whether you are collecting unnecessary single or multi-column FREQVAL, or additional COLGROUPs or HISTOGRAMS. Some RUNSTATS products collect FREQVAL on every column by default. IBM RUNSTATS does not by default.

I have a blog post out on www.worldofdb2.com that talks about how to identify unnecessary statistics (focusing mainly on multi-column frequencies and old/stale stats - only multi-column frequencies may be interesting to this discussion:

http://www.worldofdb2.com/profiles/blogs/ensure-you-have-a-simple-base-of-statistics-before-exploiting-the

Regards

Terry Purcell



In Reply to mark chorley:

Chris, 

good point, i thought that our housekeeping would have done this, although as we were testing this was the SYSPROG DB2 i didn't check. Did the REORG, RUNSTATS and reran and it went through fine. 

So something to check up on and make sure we do regular REORG/STATS on all non and prod systems for this TS is we don't already.

Just a bit surprised that the one with the columns took so much longer, obviously doing a lot of work.

Thanks and Happy Xmas everyone.

mark chorley

RE: RUNSTATS hitting SYSSTATS a lot
(in response to Terry Purcell)

Thanks Terry, i'll have a read when I get chance.

 

just a note.
When we run this with

LISTDEF LIST1                                                      
              INCLUDE TABLESPACE DB.TS PARTLEVEL(500:594)
RUNSTATS TABLESPACE LIST LIST1                                     
      SHRLEVEL REFERENCE TABLE(ALL) INDEX(ALL) UPDATE ALL       

It does the indexes every time for every part and as expected, takes a long time per part.

 


So we run them just doing the TABLE RANGE and then the INDEX RANGE in a separate step.

LISTDEF LIST1 INCLUDE TABLESPACE DB.TS PARTLEVEL(500:594)                                  
RUNSTATS TABLESPACE LIST LIST1 SHRLEVEL REFERENCE TABLE(ALL) UPDATE ALL

next step:-

LISTDEF LIST01 INCLUDE INDEXSPACES TABLESPACE DB.TS PARTLEVEL(500:594)
RUNSTATS INDEX LIST LIST01 SHRLEVEL REFERENCE UPDATE ALL

This runs as i would expect, in that it just stats the new TS parts in step 1

In Step2 it does just the specified IX parts and all the NPI's just once.


Would it be possible for the RUNSTATS to be clever enough to know that when a range is asked for,
just do the RANGE and then the indexes ONCE, not every time it does a TS PART?

I.E. like it does when we just specify the indexes.

Thanks, it's not often we get the time to look at things like this one of the few advantages of working over Xmas.

Terry Purcell

RE: RUNSTATS hitting SYSSTATS a lot
(in response to mark chorley)

Thanks for providing more detail Mark. My blog post wont apply to your situation based upon the syntax you posted.

I will take your suggestion and discuss further with our RUNSTATS developers. Quick background - given that we (in DB2 optimizer development) have delivered the "Optimizer integration with RUNSTATS" in DB2 11 & 12, I am trying to understand customer usage patterns for RUNSTATS and how we can improve the overall RUNSTATS experience. I expect many customers to say "just replace RUNSTATS with RTS!!!" - which is a separate discussion.

Thanks again for the feedback. I wont have a short term answer for you - but it looks like you already have overcome the original issue.

Thanks

Terry Purcell

In Reply to mark chorley:

Thanks Terry, i'll have a read when I get chance.

 

just a note.
When we run this with

LISTDEF LIST1                                                      
              INCLUDE TABLESPACE DB.TS PARTLEVEL(500:594)
RUNSTATS TABLESPACE LIST LIST1                                     
      SHRLEVEL REFERENCE TABLE(ALL) INDEX(ALL) UPDATE ALL       

It does the indexes every time for every part and as expected, takes a long time per part.

 


So we run them just doing the TABLE RANGE and then the INDEX RANGE in a separate step.

LISTDEF LIST1 INCLUDE TABLESPACE DB.TS PARTLEVEL(500:594)                                  
RUNSTATS TABLESPACE LIST LIST1 SHRLEVEL REFERENCE TABLE(ALL) UPDATE ALL

next step:-

LISTDEF LIST01 INCLUDE INDEXSPACES TABLESPACE DB.TS PARTLEVEL(500:594)
RUNSTATS INDEX LIST LIST01 SHRLEVEL REFERENCE UPDATE ALL

This runs as i would expect, in that it just stats the new TS parts in step 1

In Step2 it does just the specified IX parts and all the NPI's just once.


Would it be possible for the RUNSTATS to be clever enough to know that when a range is asked for,
just do the RANGE and then the indexes ONCE, not every time it does a TS PART?

I.E. like it does when we just specify the indexes.

Thanks, it's not often we get the time to look at things like this one of the few advantages of working over Xmas.

mark chorley

RE: RUNSTATS hitting SYSSTATS a lot
(in response to Terry Purcell)

Thanks Terry,

we had had a long term objective to get all TS into the RTS, it has taken quite lot of background processing to populate the RTS fully. 

One thing that i did notice a while back was that if we did a RUNSTATS SHRLEVEL CHANGE it did not fully populate the RTS for some tables.

Once we did the SHRLEVEL REFERENCE it fully populated the RTS.

So now as soon as we change or build new tables, we try and do a SHRLEVEL REFERENCE to make sure all RTS fields are populated.

We use the RTS table for all sorts of stuff and they are a great addition.

Now we are attempting to convert all TS to UTS where and when we can.

Going well so far, no reported issues, convincing the application areas and getting them to allow us the time to make the change is the challenge :-)

Roy Boxwell

RUNSTATS hitting SYSSTATS a lot
(in response to mark chorley)
Why not use the SQL in the code place to initialise all RTS in one go? Much easier and quicker than a runstats that could kill your access paths...

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 D?sseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de

Software Engineering GmbH
Amtsgericht D?sseldorf, HRB 37894
Gesch?ftsf?hrung: Gerhard Schubert, Bettina Schubert

On 30 Dec 2016, at 12:51, mark chorley <[login to unmask email]<mailto:[login to unmask email]>> wrote:


Thanks Terry,

we had had a long term objective to get all TS into the RTS, it has taken quite lot of background processing to populate the RTS fully.

One thing that i did notice a while back was that if we did a RUNSTATS SHRLEVEL CHANGE it did not fully populate the RTS for some tables.

Once we did the SHRLEVEL REFERENCE it fully populated the RTS.

So now as soon as we change or build new tables, we try and do a SHRLEVEL REFERENCE to make sure all RTS fields are populated.

We use the RTS table for all sorts of stuff and they are a great addition.

Now we are attempting to convert all TS to UTS where and when we can.

Going well so far, no reported issues, convincing the application areas and getting them to allow us the time to make the change is the challenge :-)

-----End Original Message-----