473,320 Members | 1,794 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

snapshot_dyn_sql

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
2 3059
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: dba2adm | last post by:
The SNAPSHOT_STATEMENT table function gives the CPU used rows read etc information. How do I know the total number of time the particular statement was executed (as in snapshot_dyn_sql's...
2
by: lelle | last post by:
I played around with SNAPSHOT_DYN_SQL and other snapshot functions, and is a bit confused. From what I found in the manual it says that TOTAL_EXEC_TIME represents seconds and milliseconds, but the...
1
by: Guru | last post by:
Hi All Can anyone guide me to analysis the Performance of DB2. What all are the parameters need to monitor and how to do the monitor the things? How to capature the executed SQL queries and...
14
by: Ina Schmitz | last post by:
Hello, I would like to get the logical and physical reads for every sql statement executed. Thatfore, I used the command "db2 get snapshot for dynamic sql on <mydatabase>". There, I could see...
7
by: rajdb2 | last post by:
Hi, the below list shows the top running queries in oracle on a database on a single day. Can we do something similar in DB2 v8 where we get a list of top 10 queries in execution time , or CPU...
0
by: Ina Schmitz | last post by:
Hi all, I would like to get the total execution time and the cpu time of a query execution. For that purpose, I think SNAP_GET_DYN_SQL is the correct table function. But SNAPSHOT_DYN_SQL...
0
by: Ina Schmitz | last post by:
Hi all, I would like to get the total execution time and the cpu time of a query execution. For that purpose, I think SNAP_GET_DYN_SQL is the correct table function. But SNAPSHOT_DYN_SQL...
3
by: Patrick Finnegan | last post by:
Running 8.2.8 on aix. Any reason why the dynamic cache snapshot function would return no records? db2 "SELECT * FROM TABLE (SQLCACHE_SNAPSHOT()) as SQLCACHE_SNAPSHOT" 0 record(s) selected....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.