469,286 Members | 2,521 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,286 developers. It's quick & easy.

How to find out the most expensive SQLs in J2EE and DB2 environment.

Hello,

I am testing a complex J2EE application on WebSphere and UDB V8.2.3.
Because of the compliexity of the application, we have to run it a few
hours to finish all test scenarios.

One of our test objectives is to identify the top 100 most expensive
SQLs (based on CPU time, the total executive time, and the number of
executions). So I am trying different DB2 monitoring tools, but I can't
get the SQLs that I want. Here is what I tried.

1. Use the event monitor to dump out all SQL statements. But the event
monitor report doesn't provide sorting option to list the expensive
SQLs.

2. Use the snapshot to dump out the SQL statements. Again the snapshot
doesn't provide sorting option. More importantly, snapshot can only
dump out the SQLs in the database package cache (PCKCACHESZ). So I
tried to make the package size as big as possible. However, since the
application needs to run a few hours with different SQLs, there is no
guarantee that all SQLs are in the cache.

3. Tried the Activity monitor (Resolving the performance degradation of
an application). I found that it only shows very limited number of
SQLs. Even if I enlarge the PCKCACHESZ, it doesn't show more SQLs.

Any comment are highly appreciated!

Jason Zhang

Nov 23 '06 #1
5 3371
Jason wrote:
Hello,

I am testing a complex J2EE application on WebSphere and UDB V8.2.3.
Because of the compliexity of the application, we have to run it a few
hours to finish all test scenarios.

One of our test objectives is to identify the top 100 most expensive
SQLs (based on CPU time, the total executive time, and the number of
executions). So I am trying different DB2 monitoring tools, but I can't
get the SQLs that I want. Here is what I tried.

1. Use the event monitor to dump out all SQL statements. But the event
monitor report doesn't provide sorting option to list the expensive
SQLs.

2. Use the snapshot to dump out the SQL statements. Again the snapshot
doesn't provide sorting option. More importantly, snapshot can only
dump out the SQLs in the database package cache (PCKCACHESZ). So I
tried to make the package size as big as possible. However, since the
application needs to run a few hours with different SQLs, there is no
guarantee that all SQLs are in the cache.

3. Tried the Activity monitor (Resolving the performance degradation of
an application). I found that it only shows very limited number of
SQLs. Even if I enlarge the PCKCACHESZ, it doesn't show more SQLs.

Any comment are highly appreciated!

Jason Zhang
Jason,

You probably want a tool to process the event monitor output to provide this
information. We currently use a tool called SQL-Guy, which does an
excellent job of this. The only problem is that the company which supplied
it (DGI) was bought by BMC a couple of years ago and have just announced
that support for this will end with V8.

We are going to look for an alternative, for it is so useful for exactly
what you describe. I know of at least two others tools on the market
which do such a thing, and you may want to look at these -

SQL Sleuth from Martin Hubel Consulting (http://www.mhubel.com)
SpeedGain for DB2 from ITGain (http://www.itgain.de)

HTH

Phil
Nov 23 '06 #2
Philip,

Thank you so much for the advice. I will try the tools that you
mentioned...

Jason

Nov 23 '06 #3

Jason wrote:
Hello,
[...]
>
2. Use the snapshot to dump out the SQL statements. Again the snapshot
doesn't provide sorting option.
You could dump it to a file and sort the file. Here is how I normally
do it:

db=...
dir=`pwd`/snap-${db}-`date +"%Y%m%d_%H%M%S"`
mkdir -p ${dir}

db2 get snapshot for dynamic sql on $db ${dir}/snap.sql

# exec time
rm -f ${dir}/10_worst_total_exectime_snap.sql
for g in `grep "Total execution time (sec.ms)" ${dir}/snap.sql | cut
-f2 -d= | s
ort -n | tail -10`; do
grep -B20 -A4 "Total execution time (sec.ms) [ ]* = $g"
${dir}/snap.sql
>${dir}/10_worst_total_exectime_snap.sql
done

# number of exec
rm -f ${dir}/10_worst_number_of_exec_snap.sql
for g in `grep "Number of executions" ${dir}/snap.sql | cut -f2 -d= |
sort -n |
tail -10`; do
,grep -A24 "Number of executions [ ]* = $g" ${dir}/snap.sql >>
${dir}/10_
worst_number_of_exec_snap.sql
done

# sort
rm -f ${dir}/10_worst_sort_snap.sql
for g in `grep "Statement sorts" ${dir}/snap.sql | cut -f2 -d= | sort
-n | tail
-10`; do
grep -A14 -B9 "Statement sorts [ ]* = $g" ${dir}/snap.sql >>
${dir}/10_w
orst_sort_snap.sql
done
More importantly, snapshot can only
dump out the SQLs in the database package cache (PCKCACHESZ). So I
tried to make the package size as big as possible. However, since the
application needs to run a few hours with different SQLs, there is no
guarantee that all SQLs are in the cache.
True

Main advantage (beside the price :) with a method like the above, is
that you only need an ssh connection to the server to get it up and
running.

/Lennart

Nov 24 '06 #4
Try db2top, this is exactly what it does.
http://www.alphaworks.ibm.com/tech/db2top

Lennart a écrit :
Jason wrote:
Hello,
[...]

2. Use the snapshot to dump out the SQL statements. Again the snapshot
doesn't provide sorting option.

You could dump it to a file and sort the file. Here is how I normally
do it:

db=...
dir=`pwd`/snap-${db}-`date +"%Y%m%d_%H%M%S"`
mkdir -p ${dir}

db2 get snapshot for dynamic sql on $db ${dir}/snap.sql

# exec time
rm -f ${dir}/10_worst_total_exectime_snap.sql
for g in `grep "Total execution time (sec.ms)" ${dir}/snap.sql | cut
-f2 -d= | s
ort -n | tail -10`; do
grep -B20 -A4 "Total execution time (sec.ms) [ ]* = $g"
${dir}/snap.sql
${dir}/10_worst_total_exectime_snap.sql
done

# number of exec
rm -f ${dir}/10_worst_number_of_exec_snap.sql
for g in `grep "Number of executions" ${dir}/snap.sql | cut -f2 -d= |
sort -n |
tail -10`; do
,grep -A24 "Number of executions [ ]* = $g" ${dir}/snap.sql >>
${dir}/10_
worst_number_of_exec_snap.sql
done

# sort
rm -f ${dir}/10_worst_sort_snap.sql
for g in `grep "Statement sorts" ${dir}/snap.sql | cut -f2 -d= | sort
-n | tail
-10`; do
grep -A14 -B9 "Statement sorts [ ]* = $g" ${dir}/snap.sql >>
${dir}/10_w
orst_sort_snap.sql
done
More importantly, snapshot can only
dump out the SQLs in the database package cache (PCKCACHESZ). So I
tried to make the package size as big as possible. However, since the
application needs to run a few hours with different SQLs, there is no
guarantee that all SQLs are in the cache.

True

Main advantage (beside the price :) with a method like the above, is
that you only need an ssh connection to the server to get it up and
running.

/Lennart
Nov 24 '06 #5

jacques wrote:
Try db2top, this is exactly what it does.
http://www.alphaworks.ibm.com/tech/db2top
I havent seen this one before. I'll give it a try right away.
Thanx
/Lennart

Lennart a écrit :
Jason wrote:
Hello,
[...]
>
2. Use the snapshot to dump out the SQL statements. Again the snapshot
doesn't provide sorting option.
You could dump it to a file and sort the file. Here is how I normally
do it:

db=...
dir=`pwd`/snap-${db}-`date +"%Y%m%d_%H%M%S"`
mkdir -p ${dir}

db2 get snapshot for dynamic sql on $db ${dir}/snap.sql

# exec time
rm -f ${dir}/10_worst_total_exectime_snap.sql
for g in `grep "Total execution time (sec.ms)" ${dir}/snap.sql | cut
-f2 -d= | s
ort -n | tail -10`; do
grep -B20 -A4 "Total execution time (sec.ms) [ ]* = $g"
${dir}/snap.sql
>${dir}/10_worst_total_exectime_snap.sql
done

# number of exec
rm -f ${dir}/10_worst_number_of_exec_snap.sql
for g in `grep "Number of executions" ${dir}/snap.sql | cut -f2 -d= |
sort -n |
tail -10`; do
,grep -A24 "Number of executions [ ]* = $g" ${dir}/snap.sql >>
${dir}/10_
worst_number_of_exec_snap.sql
done

# sort
rm -f ${dir}/10_worst_sort_snap.sql
for g in `grep "Statement sorts" ${dir}/snap.sql | cut -f2 -d= | sort
-n | tail
-10`; do
grep -A14 -B9 "Statement sorts [ ]* = $g" ${dir}/snap.sql >>
${dir}/10_w
orst_sort_snap.sql
done
More importantly, snapshot can only
dump out the SQLs in the database package cache (PCKCACHESZ). So I
tried to make the package size as big as possible. However, since the
application needs to run a few hours with different SQLs, there is no
guarantee that all SQLs are in the cache.
>
True

Main advantage (beside the price :) with a method like the above, is
that you only need an ssh connection to the server to get it up and
running.



/Lennart
Nov 24 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Dom Incolingo | last post: by
15 posts views Thread by Herman | last post: by
reply views Thread by Jim Collins | last post: by
13 posts views Thread by Jonathan Li | last post: by
7 posts views Thread by Luca | last post: by
dmjpro
3 posts views Thread by dmjpro | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.