For a statement that is compiled once, how can prep_time_worst <>
prep_time_best?

select num_compilations, prep_time_worst, prep_time_best from
sysibmadm.snapdyn_sql where num_compilations = 1 and prep_time_worst <>
prep_time_best order by abs(prep_time_worst - prep_time_best) desc fetch
first 5 rows only"

NUM_COMPILATIONS PREP_TIME_WORST PREP_TIME_BEST
-------------------- -------------------- --------------------
1 7583 10
1 5724 56
1 4174 3
1 4149 5
1 3858 9

The reasons I can think of:

a) prep_time = compile_time + x where x may differ between executions.
b) hwm/lwm for prep_time_... is kept, but num_compilations is reset for
some reason.

Any thoughts anyone?


/Lennart

db2level
DB21085I Instance "db2inst1" uses "64" bits and DB2 code release
"SQL09055"
with level identifier "06060107".

On Mon, 05 Dec 2011 22:37:01 +0100, Lennart Jonsson wrote:

The snapshot table functions and views have never worked corrrectly in my experience, which is why I
still do SQL snapshots the old fashion way.

On 2011-12-06 07:07, Mark A wrote:
[...]
Hi Mark, I'm a bit puzzled since I always assumed that prep_time is the
same as compile_time (at least that there is no part of prep_time that
is influenced of the execution of the package). What is your opinion on
that matter?

As for snapshot info, I get the same info using "get snapshot for ...":

Number of executions = 399860
Number of compilations = 1
Worst preparation time (ms) = 7583
Best preparation time (ms) = 10

The statement itself is not very complicated so I really don't see why
it would take 7 seconds to prepare it.


Anyhow, I don't really have a problem with this, I'm just curious and is
trying to improve my understanding on the subject.


/Lennart

On Dec 5, 2:37*pm, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:
Compilation is not the same thing as a prepare. Every time an
application executes a (new) statement, the statement has to be
prepared.

When the prepare occurs, 2 things can happen:

1) There is a package cache hit –*the execution plan is in the
package cache, and DB2 can leverage it without further work.
2) There is a package cache miss –*the statement is NOT in the
package cache, so DB2 sends it to the optimizer and compiles a new
execution plan.

The long prepare times are (obviously) your package cache misses. The
short ones are your package cache hits.

Note that when NUM_COMPILATIONS > 1, it indicates that sections have
been evicted from your package cache (via an LRU scheme) and/or the
cache has been flushed.

On 2011-12-06 17:41, Ian wrote:
[...]
Thanks Ian, that makes sense.


Cheers
/Lennart

On Dec 6, 1:37*am, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:
Hi Lennart,

Some statements which affect the tables participated in the query can
"invalidate" corresponding entry in the package cache.
For exampe, runstats or create/drop index.
For some strange reason prep times, num_executions are retained, but
num_compilations is reset to 0 after these statements in the output of
the sysibmadm.snapdyn_sql function.

Sincerely,
Mark B.

On 2011-12-08 09:01, MarkB wrote:
[...]
Hi Mark, I did some simple tests. Perhaps this is a coincidence but it
seems like a reasonable explanation (sorry about the length of this post).

First I tried invalidating a package when no one was using it -> both
num_executions, num_compilations where reset

Then I did the same, when another connection accessed the package ->
Only num_compilations where reset, num_executions kept it's value.

Test
----

]$ db2 -tvf pkgcache.sql
create table t ( x int not null, y int not null )
DB20000I The SQL command completed successfully.

insert into t (x,y) with x(n) as ( values 0 union all select n+1 from x
where n < 1000 ) select n, mod(n,4) from x
DB20000I The SQL command completed successfully.

select * from t where x = 500

X Y
----------- -----------
500 0

1 record(s) selected.


select * from t where x = 500

X Y
----------- -----------
500 0

1 record(s) selected.


select * from t where x = 500

X Y
----------- -----------
500 0

1 record(s) selected.


select num_executions, num_compilations, substr(stmt_text,1,40) as
stmt_text from sysibmadm.snapdyn_sql where stmt_text like '%500%' and
stmt_text not like '%sysibmadm.snapdyn_sql%'

NUM_EXECUTIONS NUM_COMPILATIONS STMT_TEXT

-------------------- --------------------
----------------------------------------
3 1 select * from t where x = 500


1 record(s) selected.


create index x on t (x,y)
DB20000I The SQL command completed successfully.

runstats on table lelle.t with distribution and detailed indexes all
DB20000I The RUNSTATS command completed successfully.

select num_executions, num_compilations, substr(stmt_text,1,40) as
stmt_text from sysibmadm.snapdyn_sql where stmt_text like '%500%' and
stmt_text not like '%sysibmadm.snapdyn_sql%'

NUM_EXECUTIONS NUM_COMPILATIONS STMT_TEXT

-------------------- --------------------
----------------------------------------

0 record(s) selected.


select * from t where x = 500

X Y
----------- -----------
500 0

1 record(s) selected.


select num_executions, num_compilations, substr(stmt_text,1,40) as
stmt_text from sysibmadm.snapdyn_sql where stmt_text like '%500%' and
stmt_text not like '%sysibmadm.snapdyn_sql%'

NUM_EXECUTIONS NUM_COMPILATIONS STMT_TEXT

-------------------- --------------------
----------------------------------------
1 1 select * from t where x = 500


1 record(s) selected.



In this case both num_executions and num_compilations are reset when the
package is invalidated.


However, if I open a second connection after the table is created and
use the statement as in:

c1: db2 "create table t ( x int not null, y int not null )"
c2: db2 +c "select * from t where x = 500"
c1: <as before>

insert into t (x,y) with x(n) as ( values 0 union all select n+1 from x
where n < 1000 ) select n, mod(n,4) from x
DB20000I The SQL command completed successfully.

select * from t where x = 500

X Y
----------- -----------
500 0

1 record(s) selected.


select * from t where x = 500
X Y
----------- -----------
500 0

1 record(s) selected.


select * from t where x = 500

X Y
----------- -----------
500 0

1 record(s) selected.


select num_executions, num_compilations, substr(stmt_text,1,40) as
stmt_text from sysibmadm.snapdyn_sql where stmt_text like '%500%' and
stmt_text not like '%sysibmadm.snapdyn_sql%'

NUM_EXECUTIONS NUM_COMPILATIONS STMT_TEXT

-------------------- --------------------
----------------------------------------
4 1 select * from t where x = 500


1 record(s) selected.


create index x on t (x,y)
DB20000I The SQL command completed successfully.

runstats on table lelle.t with distribution and detailed indexes all
DB20000I The RUNSTATS command completed successfully.

select num_executions, num_compilations, substr(stmt_text,1,40) as
stmt_text from sysibmadm.snapdyn_sql where stmt_text like '%500%' and
stmt_text not like '%sysibmadm.snapdyn_sql%'

NUM_EXECUTIONS NUM_COMPILATIONS STMT_TEXT

-------------------- --------------------
----------------------------------------
4 0 select * from t where x = 500


1 record(s) selected.


Now num_executions is kept but num_compilations is reset

c2: commit

c1: db2 "select num_executions, num_compilations, substr(stmt_text,1,40)
as stmt_text from sysibmadm.snapdyn_sql where stmt_text like '%500%' and
stmt_text not like '%sysibmadm.snapdyn_sql%'"

NUM_EXECUTIONS NUM_COMPILATIONS STMT_TEXT

-------------------- --------------------
----------------------------------------
5 1 select * from t where x = 500



No matter what I do now to invalidate the package num_executions is kept:

db2 drop table t
db2 "select num_executions, num_compilations, substr(stmt_text,1,40) as
stmt_text from sysibmadm.snapdyn_sql where stmt_text like '%500%' and
stmt_text not like '%sysibmadm.snapdyn_sql%'"

NUM_EXECUTIONS NUM_COMPILATIONS STMT_TEXT

-------------------- --------------------
----------------------------------------
5 0 select * from t where x = 500




It appears as if db2 can't get rid of the package because someone is
using it, the package becomes a "living dead" in the pkgcache :-)
Thoughts anyone?


/Lennart

On Dec 9, 1:15*am, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:
Hi Lennart,

Yes I've seen the same strange effects too.
So, we should live with this strange design...
Hopefully with 9.7 version it seems that the monitoring function
MON_GET_PKG_CACHE_STMT doesn't have such effects.
Moreover with this function we finally have an ability to distinguish
statistics for statements like 'select * from mytable' which can run
against table with the same name but in different schemas.
Note that snapdyn_sql doesn't do this and collects statistics for such
effectively different queries together.

Sincerely,
Mark B.