predictive governor questions

Larry Kirkpatrick

predictive governor questions
I have just implemented the predictive governor and now have two questions.

Just a bit of background first. Prior to implementing this, every dynamic
query in the shop was governed in some way by the reactive governor. In
some cases, this restriction gave a great deal of latitude. A predictive
governor limit has now been added for all dynamic queries with a limit that
is fifty times the current reactive governor limit (which continues to be
in effect). In this way, I hope to prevent the running of queries where a
join predicate was left out. I also hope to give enough latitude to the
DB2 optimizer so no one is slimed by this limit.

My first question is simple: Is there any way of knowing all queries that
are receiving a -495 SQLCODE? (using IFI or some other means?)

My second question involves my one known query that is now receiving a
-495. The following query has an estimated CPU time that is greater than
the threshold:

SELECT
OB_DBNAME,
OB_PSNAME,
RD_DIST_PG_CNT,
TOT_READ_CNT,
PGS_REREAD_PG_CNT,
CRNT_BP_NUM,
MIN_REREAD_SECS
FROM PDBR111.TDBR_RDHIST A
WHERE OB_TS =
(SELECT VALUE(MAX(B.OB_TS),
CURRENT TIMESTAMP - 10 DAYS)
FROM PDBR111.TDBR_RDHIST B)
ORDER BY RD_DIST_PG_CNT DESC

This query has a estimated CPU time of 11053480 milliseconds (three hours
of CPU). The actual run time of this query is less than .15 CPU seconds.
The column OB_TS is indexed on table PDBR111.TDBR_RDHIST and the access
path is showing index access (with one matching column). Each value of
OB_TS has approximately 1600 rows associated with it (a rather even
distribution). Also, the index for column OB_TS on table
PDBR111.TDBR_RDHIST is descending, so an access path of "I1" is being used
for the subquery. Since this is using a non-correlated subselect, I would
think that the estimated CPU time for the total query would be the sum of
the estimated time for the subquery plus the estimated time for the outer
query using a parameter marker in place of the subselect. With this in
mind, I explained the subselect:



SELECT VALUE(MAX(B.OB_TS),
CURRENT TIMESTAMP - 10 DAYS)
FROM PDBR111.TDBR_RDHIST B ;


It has an estimated CPU time of 1262 milliseconds.





I then explained the outer query:





SELECT
OB_DBNAME,
OB_PSNAME,
RD_DIST_PG_CNT,
TOT_READ_CNT,
PGS_REREAD_PG_CNT,
CRNT_BP_NUM,
MIN_REREAD_SECS
FROM PDBR111.TDBR_RDHIST A
WHERE OB_TS = ?
ORDER BY RD_DIST_PG_CNT DESC ;


and it has an estimated CPU time of 45 milliseconds.





Why does not DB2 use 1307 milliseconds for an estimate?





Larry Kirkpatrick


Database consultant


Mutual of Omaha



Terry Purcell

Re: predictive governor questions
(in response to Larry Kirkpatrick)
Larry,

My first suggestion would be to check the following APAR which relates to
non-correlated subquery cost estimation - PQ50462: PREDICTED SERVICE UNITS
MUCH HIGHER THAN ACTUAL SU'S.

If this is the correct estimate, then only IBM can answer why this does not
match the sum of the two individual query blocks. To quote consultant, and
optimizer cost formula guru Frank Ingrassia, "The optimizer's job is to pick
the best access path, not provide real world cost estimates". And my
understanding is that the same timeron values that we know and love are used
as input to the values reported in the DSN_STATEMNT_TABLE.

I know the last few comments are not much relief for you.

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:[login to unmask email]On Behalf Of
[login to unmask email]
Sent: Thursday, December 12, 2002 3:33 PM
To: [login to unmask email]
Subject: predictive governor questions


I have just implemented the predictive governor and now have two questions.

Just a bit of background first. Prior to implementing this, every dynamic
query in the shop was governed in some way by the reactive governor. In
some cases, this restriction gave a great deal of latitude. A predictive
governor limit has now been added for all dynamic queries with a limit that
is fifty times the current reactive governor limit (which continues to be
in effect). In this way, I hope to prevent the running of queries where a
join predicate was left out. I also hope to give enough latitude to the
DB2 optimizer so no one is slimed by this limit.

My first question is simple: Is there any way of knowing all queries that
are receiving a -495 SQLCODE? (using IFI or some other means?)

My second question involves my one known query that is now receiving a
-495. The following query has an estimated CPU time that is greater than
the threshold:

SELECT
OB_DBNAME,
OB_PSNAME,
RD_DIST_PG_CNT,
TOT_READ_CNT,
PGS_REREAD_PG_CNT,
CRNT_BP_NUM,
MIN_REREAD_SECS
FROM PDBR111.TDBR_RDHIST A
WHERE OB_TS =
(SELECT VALUE(MAX(B.OB_TS),
CURRENT TIMESTAMP - 10 DAYS)
FROM PDBR111.TDBR_RDHIST B)
ORDER BY RD_DIST_PG_CNT DESC

This query has a estimated CPU time of 11053480 milliseconds (three hours
of CPU). The actual run time of this query is less than .15 CPU seconds.
The column OB_TS is indexed on table PDBR111.TDBR_RDHIST and the access
path is showing index access (with one matching column). Each value of
OB_TS has approximately 1600 rows associated with it (a rather even
distribution). Also, the index for column OB_TS on table
PDBR111.TDBR_RDHIST is descending, so an access path of "I1" is being used
for the subquery. Since this is using a non-correlated subselect, I would
think that the estimated CPU time for the total query would be the sum of
the estimated time for the subquery plus the estimated time for the outer
query using a parameter marker in place of the subselect. With this in
mind, I explained the subselect:



SELECT VALUE(MAX(B.OB_TS),
CURRENT TIMESTAMP - 10 DAYS)
FROM PDBR111.TDBR_RDHIST B ;


It has an estimated CPU time of 1262 milliseconds.





I then explained the outer query:





SELECT
OB_DBNAME,
OB_PSNAME,
RD_DIST_PG_CNT,
TOT_READ_CNT,
PGS_REREAD_PG_CNT,
CRNT_BP_NUM,
MIN_REREAD_SECS
FROM PDBR111.TDBR_RDHIST A
WHERE OB_TS = ?
ORDER BY RD_DIST_PG_CNT DESC ;


and it has an estimated CPU time of 45 milliseconds.





Why does not DB2 use 1307 milliseconds for an estimate?





Larry Kirkpatrick


Database consultant


Mutual of Omaha








Larry Kirkpatrick

Re: predictive governor questions
(in response to Terry Purcell)
Terry,

Thanks for giving me a possible APAR to check. We checked out
PQ50462 and found that we have it on.

My real problem with this is that I am attempting to use the
predictive governor for estimates that fall into category "A" (which this
one did). I have found IBM to be very receptive to a followup when the
estimated CPU is quite a bit different than the real CPU for a category "A"
optimization. Asking them to followup on the CPU estimate seems quite
different than asking them to followup on a timeron estimate. Now that the
CPU estimate is exposed for public consumption, I believe that it is very
useful (if accurate) for predictive governing. In fact, I believe that
there should be a valid explanation for bad CPU estimates in category "A"
when they occur. Since the timeron value is a relative cost estimate, I
understand IBM's reluctance to address the weirdness of this value.

My reason for posing this question to the list is to give me a sanity
check of my thinking that the total CPU should be close to the sum of the
parts (for this query). I believe that this particular estimate is a bug
and have posed the question to IBM as well as the list. Since my
understanding of the resolution of this query could be incorrect, I would
like to have others to second guess me on this.

Larry Kirkpatrick
Mutual of Omaha


|---------+---------------------------->
| | |
| | |
| | "Terry Purcell" |
| | <[login to unmask email]|
| | ASSOC.COM> |
| | Sent by: "DB2 |
| | Data Base |
| | Discussion List" |
| | <[login to unmask email]|
| | LASSOC.COM> |
| | |
| | |
| | 12/15/2002 11:02 |
| | PM |
| | Please respond to|
| | "DB2 Data Base |
| | Discussion List" |
| | |
|---------+---------------------------->
>--------------------------------------------------------------------------------------------------------------|
| |
| |
| |
| To: [login to unmask email] |
| cc: |
| Subject: Re: predictive governor questions |
| |
>--------------------------------------------------------------------------------------------------------------|




Larry,

My first suggestion would be to check the following APAR which relates to
non-correlated subquery cost estimation - PQ50462: PREDICTED SERVICE UNITS
MUCH HIGHER THAN ACTUAL SU'S.

If this is the correct estimate, then only IBM can answer why this does not
match the sum of the two individual query blocks. To quote consultant, and
optimizer cost formula guru Frank Ingrassia, "The optimizer's job is to
pick
the best access path, not provide real world cost estimates". And my
understanding is that the same timeron values that we know and love are
used
as input to the values reported in the DSN_STATEMNT_TABLE.

I know the last few comments are not much relief for you.

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:[login to unmask email]On Behalf Of
[login to unmask email]
Sent: Thursday, December 12, 2002 3:33 PM
To: [login to unmask email]
Subject: predictive governor questions


I have just implemented the predictive governor and now have two questions.

Just a bit of background first. Prior to implementing this, every dynamic
query in the shop was governed in some way by the reactive governor. In
some cases, this restriction gave a great deal of latitude. A predictive
governor limit has now been added for all dynamic queries with a limit that
is fifty times the current reactive governor limit (which continues to be
in effect). In this way, I hope to prevent the running of queries where a
join predicate was left out. I also hope to give enough latitude to the
DB2 optimizer so no one is slimed by this limit.

My first question is simple: Is there any way of knowing all queries that
are receiving a -495 SQLCODE? (using IFI or some other means?)

My second question involves my one known query that is now receiving a
-495. The following query has an estimated CPU time that is greater than
the threshold:

SELECT
OB_DBNAME,
OB_PSNAME,
RD_DIST_PG_CNT,
TOT_READ_CNT,
PGS_REREAD_PG_CNT,
CRNT_BP_NUM,
MIN_REREAD_SECS
FROM PDBR111.TDBR_RDHIST A
WHERE OB_TS =
(SELECT VALUE(MAX(B.OB_TS),
CURRENT TIMESTAMP - 10 DAYS)
FROM PDBR111.TDBR_RDHIST B)
ORDER BY RD_DIST_PG_CNT DESC

This query has a estimated CPU time of 11053480 milliseconds (three hours
of CPU). The actual run time of this query is less than .15 CPU seconds.
The column OB_TS is indexed on table PDBR111.TDBR_RDHIST and the access
path is showing index access (with one matching column). Each value of
OB_TS has approximately 1600 rows associated with it (a rather even
distribution). Also, the index for column OB_TS on table
PDBR111.TDBR_RDHIST is descending, so an access path of "I1" is being used
for the subquery. Since this is using a non-correlated subselect, I would
think that the estimated CPU time for the total query would be the sum of
the estimated time for the subquery plus the estimated time for the outer
query using a parameter marker in place of the subselect. With this in
mind, I explained the subselect:



SELECT VALUE(MAX(B.OB_TS),
CURRENT TIMESTAMP - 10 DAYS)
FROM PDBR111.TDBR_RDHIST B ;


It has an estimated CPU time of 1262 milliseconds.





I then explained the outer query:





SELECT
OB_DBNAME,
OB_PSNAME,
RD_DIST_PG_CNT,
TOT_READ_CNT,
PGS_REREAD_PG_CNT,
CRNT_BP_NUM,
MIN_REREAD_SECS
FROM PDBR111.TDBR_RDHIST A
WHERE OB_TS = ?
ORDER BY RD_DIST_PG_CNT DESC ;


and it has an estimated CPU time of 45 milliseconds.





Why does not DB2 use 1307 milliseconds for an estimate?





Larry Kirkpatrick


Database consultant


Mutual of Omaha



the





the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can