query tuning

[Raj]

Hi all,

I have couple of questions some one plzz help

1.I have a query which run for 2 hours on my production machine, it
returns 1.5 millon rows, i looked at the explain plan it is picking up
the indexes on big table. The query joins 5 tables ( 1 has 30 million
records 4 other tables have less than 100000) i see the cost at each
step is less than 1000 so i guess it shouldn't take that long to
return the result set??? it does a sort and tablescan just before the
return i guess this is because of order by, i tried to remove the order
by and re executed the query with fetch first 100 rows only and i got
the result in less than 30 seconds. All the columns in the order by are
index columns. How can i optimize order by???

2.There is another query which does a
select max( distinct c1) ,c2 from t1, t2 group by c1. I get an error to
increase the application heap size, we are on a partitioned database,
so i guess the parameter to increase would be app_ctl_heap_sz. In what
steps should we increase it? and why did we get such an error?

Any help greatly appreciated

Thanks

[Mark A]

"Raj" <sp****@yahoo.com> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.com...

Hi all,

I have couple of questions some one plzz help

1.I have a query which run for 2 hours on my production machine, it
returns 1.5 millon rows, i looked at the explain plan it is picking up
the indexes on big table. The query joins 5 tables ( 1 has 30 million
records 4 other tables have less than 100000) i see the cost at each
step is less than 1000 so i guess it shouldn't take that long to
return the result set??? it does a sort and tablescan just before the
return i guess this is because of order by, i tried to remove the order
by and re executed the query with fetch first 100 rows only and i got
the result in less than 30 seconds. All the columns in the order by are
index columns. How can i optimize order by???

2.There is another query which does a
select max( distinct c1) ,c2 from t1, t2 group by c1. I get an error to
increase the application heap size, we are on a partitioned database,
so i guess the parameter to increase would be app_ctl_heap_sz. In what
steps should we increase it? and why did we get such an error?

Any help greatly appreciated

Thanks

1. Make sure you have sufficient space in your temporary tablespace
avialable. The amount of system temporary tablespace should be at least
twice as large as your largest answer set.

Since it looks like you have a decision support system, the page size of
your regular and system temporary tablespaces should probably be 16K or 32K
and the prefetch size should be a multiple of the page size times the number
of containers. If you use mutiple containers per tablespace (equal to, or a
even multiple, of your CPU's per node) it will benefit your query. This
applies to both regular and system temporary tablespaces, for both SMS and
DMS. If you have NAS or SAN make sure you run "db2set DB2_PARALLEL_IO=*".

If you are using DB2 V7 or migrated from V7 to V8 with SMS tablespaces, then
run the db2empfa command to enable multipage file allocation.

2. Double it and see if that works. If not, double it again.

[ras9929]

Mark A wrote:

"Raj" <sp****@yahoo.com> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.com...

Hi all,

I have couple of questions some one plzz help

1.I have a query which run for 2 hours on my production machine, it
returns 1.5 millon rows, i looked at the explain plan it is picking up
the indexes on big table. The query joins 5 tables ( 1 has 30 million
records 4 other tables have less than 100000) i see the cost at each
step is less than 1000 so i guess it shouldn't take that long to
return the result set??? it does a sort and tablescan just before the
return i guess this is because of order by, i tried to remove the order
by and re executed the query with fetch first 100 rows only and i got
the result in less than 30 seconds. All the columns in the order by are
index columns. How can i optimize order by???

2.There is another query which does a
select max( distinct c1) ,c2 from t1, t2 group by c1. I get an error to
increase the application heap size, we are on a partitioned database,
so i guess the parameter to increase would be app_ctl_heap_sz. In what
steps should we increase it? and why did we get such an error?

Any help greatly appreciated

Thanks

1. Make sure you have sufficient space in your temporary tablespace
avialable. The amount of system temporary tablespace should be at least
twice as large as your largest answer set.

Since it looks like you have a decision support system, the page size of
your regular and system temporary tablespaces should probably be 16K or 32K
and the prefetch size should be a multiple of the page size times the number
of containers. If you use mutiple containers per tablespace (equal to, or a
even multiple, of your CPU's per node) it will benefit your query. This
applies to both regular and system temporary tablespaces, for both SMS and
DMS. If you have NAS or SAN make sure you run "db2set DB2_PARALLEL_IO=*".

If you are using DB2 V7 or migrated from V7 to V8 with SMS tablespaces, then
run the db2empfa command to enable multipage file allocation.

2. Double it and see if that works. If not, double it again.

1. Are the order by columns from a single table. If so, an index in
that order, possibly clustering would help trememdously. Are
statistics current? Also, check to see if the statement is overflowing
your sort heap and doing disk writes.

2. I hated the double it answer. That's the answer you get doing no
research. Doubling it never gives you a why. First, I'd remove the
distinct. It doesn't add anything functionally (the max of the unique
values would be the max of all values) and may be causing an additional
sort (or really causing db2 to use a inaccurate plan). I'd make sure
my runstasts are up to date. I'm assuming the query is a little more
complex and contains some join predicates for c1, c2. One of the db2
registry parameters I found useful in the partitioned environment is
DB2_REDUCED_OPTIMIZATION. I set it equal to somewhere around 7 or 8 to
turn off greedy join (nice feature, kills memory) on queries that
involve more than 7 or 8 tables. From first look, your query wouldn't
have that, but the first system I was with utilized UNION ALL views
that included up to 6 objects below.

As a side note, there is a new tool on alphaworks that everyone should
check out. For those of us that don't have their favorite dba tool
handy always, IBM has created an integrated command line peformance
monitor. Bout as good as sliced bread...

http://alphaworks.ibm.com/tech/db2top

[Raj]

Thanks a lot for the reply ..

1. We have a lot of SMS temp space with mutli page file allocation is
turned on and DB2_PARALLEL_IO=* registry variable is set. the query
looks like this ..

select c1,c2,c3,c4,c5
from t1,t2,t3,t4
where c1=c2, c2=c3, c3= c1 || c2..................
order by t1.c,t2.c,t3.c

without the order by ,fetch first 100 rows takes 30 seconds , 1000 rows
takes 1 min .......

there were 300 sort overflows with the order by.
2. I tried it without the distinct i get the same error , also the
explain shows the cost as -190,000,000 ( a negative value??)

runstats on all the tables are up todate..

the query looks more like this

select distinct max ("c1") over() , c2, c2||c3||c4

from t1, t2,t3

where t1.c=t2.c, t1.c ='y', t2.c ='2005' ..................

will try the db2top tool on my dev box..Thanks

[Eugene F]

You also could try using DB2 Design Advisor db2advis to see what
indexes, MDC, or/and partitioning keys it recommends for this query
given your specific database evnvironment, sometimes it's helpful...

-Eugene

Raj wrote:

Thanks a lot for the reply ..

1. We have a lot of SMS temp space with mutli page file allocation is
turned on and DB2_PARALLEL_IO=* registry variable is set. the query
looks like this ..

select c1,c2,c3,c4,c5
from t1,t2,t3,t4
where c1=c2, c2=c3, c3= c1 || c2..................
order by t1.c,t2.c,t3.c

without the order by ,fetch first 100 rows takes 30 seconds , 1000 rows
takes 1 min .......

there were 300 sort overflows with the order by.
2. I tried it without the distinct i get the same error , also the
explain shows the cost as -190,000,000 ( a negative value??)

runstats on all the tables are up todate..

the query looks more like this

select distinct max ("c1") over() , c2, c2||c3||c4

from t1, t2,t3

where t1.c=t2.c, t1.c ='y', t2.c ='2005' ..................

will try the db2top tool on my dev box..Thanks