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

[Jason]

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

[Philip Nelson]

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

[Jason]

Philip,

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

Jason

[Lennart]

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.s ql
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.s ql

done

# number of exec
rm -f ${dir}/10_worst_number _of_exec_snap.s ql
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_s nap.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

[jacques]

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.s ql
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.s ql

done

# number of exec
rm -f ${dir}/10_worst_number _of_exec_snap.s ql
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_s nap.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

[Lennart]

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.s ql
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.s ql

done

# number of exec
rm -f ${dir}/10_worst_number _of_exec_snap.s ql
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_s nap.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