By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,236 Members | 1,292 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,236 IT Pros & Developers. It's quick & easy.

snapshot_dyn_sql

P: n/a
In this function there is an element TOTAL_EXEC_TIME defined as bigint.
I'm trying to figure out a bigint of what time scale. Microseconds,
milliseconds, seconds?

I'm trying to get the average execution time for each statement (
total_exec_time / num_executions ) but I'm not sure what the unit of
measure the result is in. Anybody know?

DB2 V8 fixpack 9.

Thanks in advance.

Sep 7 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
From the docs. AGoogle search on: db2 and TOTAL_EXEC_TIME
total_exec_time - Elapsed Statement Execution Time monitor element
Element identifier
total_exec_time
Element type
time

Table 455. Snapshot Monitoring Information Snapshot Level Logical Data
Grouping Monitor Switch
Dynamic SQL dynsql Statement

For snapshot monitoring, this counter can be reset.
Description
The total time in seconds and microseconds that was spent executing a
particular statement in the SQL cache.
Usage
Use this element with num_executions determine the average elapsed time
for the statement and identify the SQL statements that would most benefit
from a tuning of their SQL. The num_compilation must be considered when
evaluating the contents of this element.
HTH, Pierre.
--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Private Pyle" <lp*****@gmail.coma écrit dans le message de news:
11**********************@m79g2000cwm.googlegroups. com...
In this function there is an element TOTAL_EXEC_TIME defined as bigint.
I'm trying to figure out a bigint of what time scale. Microseconds,
milliseconds, seconds?

I'm trying to get the average execution time for each statement (
total_exec_time / num_executions ) but I'm not sure what the unit of
measure the result is in. Anybody know?

DB2 V8 fixpack 9.

Thanks in advance.
Sep 8 '06 #2

P: n/a
Yes, I saw that doc on this element too, but my question was specific
to this table function where the time elements are documented to be
stored as big int. Look at the last four columns in the table
function. Three of them are time measures documented as BIGINT.

Column name Data type
SNAPSHOT_TIMESTAMP TIMESTAMP
ROWS_READ BIGINT
ROWS_WRITTEN BIGINT
NUM_EXECUTIONS BIGINT
NUM_COMPILATIONS BIGINT
PREP_TIME_WORST BIGINT
PREP_TIME_BEST BIGINT
INT_ROWS_DELETED BIGINT
INT_ROWS_INSERTED BIGINT
INT_ROWS_UPDATED BIGINT
STMT_SORTS BIGINT
TOTAL_EXEC_TIME BIGINT
TOTAL_SYS_CPU_TIME BIGINT
TOTAL_USR_CPU_TIME BIGINT
STMT_TEXT CLOB(16M)1

If the time is being stored as big int, and I want to find out what the
average execution time for a statement is, how would I do that? Bigint
isn't a unit of time measure the way seconds, millisecond,
mircoseconds, minutes, or hours are. Is this some kind of offset of
seconds from a certain time value? Is it a running sum of micro
seconds? BIGINT isn't clear.

When I take a snapshot against the database for dynamic sql, the value
is returned as described in the doc you posted below. I'm interested
in using this table function to perform the calculations repeatedly in
an SQL select statement.

Thanks,

Pierre Saint-Jacques wrote:
From the docs. AGoogle search on: db2 and TOTAL_EXEC_TIME
total_exec_time - Elapsed Statement Execution Time monitor element
Element identifier
total_exec_time
Element type
time

Table 455. Snapshot Monitoring Information Snapshot Level Logical Data
Grouping Monitor Switch
Dynamic SQL dynsql Statement

For snapshot monitoring, this counter can be reset.
Description
The total time in seconds and microseconds that was spent executing a
particular statement in the SQL cache.
Usage
Use this element with num_executions determine the average elapsed time
for the statement and identify the SQL statements that would most benefit
from a tuning of their SQL. The num_compilation must be considered when
evaluating the contents of this element.
HTH, Pierre.
--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Private Pyle" <lp*****@gmail.coma écrit dans le message de news:
11**********************@m79g2000cwm.googlegroups. com...
In this function there is an element TOTAL_EXEC_TIME defined as bigint.
I'm trying to figure out a bigint of what time scale. Microseconds,
milliseconds, seconds?

I'm trying to get the average execution time for each statement (
total_exec_time / num_executions ) but I'm not sure what the unit of
measure the result is in. Anybody know?

DB2 V8 fixpack 9.

Thanks in advance.
Sep 8 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.