473,578 Members | 3,025 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

Jan 16 '06 #1
4 2201
"Raj" <sp****@yahoo.c om> wrote in message
news:11******** *************@g 43g2000cwa.goog legroups.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.
Jan 16 '06 #2

Mark A wrote:
"Raj" <sp****@yahoo.c om> wrote in message
news:11******** *************@g 43g2000cwa.goog legroups.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_OPT IMIZATION. 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

Jan 17 '06 #3
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

Jan 17 '06 #4
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


Jan 17 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
4503
by: Mahesh Hardikar | last post by:
Hi , Oracle 8.1.7.0.0 on HP-UX 11.0 I am a newbie as far as PL-SQL is concerned . I have Sybase/MSSQL T-SQL background. We have a report which uses a select statement . This select statement joins 15 tables . SOme of the tables are outer joined. It runs much slow when parameters (From & To Date) are for a month.
14
9280
by: Bob | last post by:
Hi there, Need a little help with a certain query that's causing a lot of acid in my stomach... Have a table that stores sales measures for a given client. The sales measures are stored per year and there could be multiple sales measures every year per client. There is another field called last update date. If there are multiple sales...
9
3400
by: majsen | last post by:
Hi, I have problem running this query. It will time out for me... My database are small just about 200 members. I have a site for swaping appartments (rental). my query should look for match in a triangle. Like this member A -> B->C A give his appartment to B. B gives his appartment to C and finally C gives his appartment to A Soo my...
6
1899
by: Steven D.Arnold | last post by:
I have a query which does not use column indexes that it should use. I have discovered some interesting behaviors of Postgres which may indicate a bug in the database's query planning. Take a look at the query below. There is a btree index on both m.account_id and a.account_id. Query (1) does not use the index on the messages table,...
1
1999
by: Peter Alberer | last post by:
Hi there, i have a problem with a query that uses the result of a plsql function In the where clause: SELECT assignments.assignment_id, assignments.package_id AS package_id, assignments.title AS title, COUNT(*) AS Count
3
2022
by: mleal | last post by:
Does anyone have some more detailed information about how Oracle and MS implement / allow Tuning on Oracle 10g and SQL Server 2005 and the differences between them? Which of them, In a deep comparison about it, allow better tuning and why. Regards, Marcio Evangelista
3
1716
by: dba_222 | last post by:
Dear experts, I hate to ask such a seemingly dumb question. But I have spent some time trying to solve this already to no avail. When I was using my query analyzer last, a few months ago, I was tuning some strange code. I changed some settings to see what was going on.
1
6222
by: Vinod Sadanandan | last post by:
A Roadmap To Query Tuning ============================ For each SQL statement, there are different approaches that could be used to retrieve the required data. Optimization is the process of choosing the most efficient way to retrieve this data based upon the evaluation of a number of different criteria. The CBO bases optimization choices...
3
7899
by: dunleav1 | last post by:
In 9.1 and 9.5 (Linux 64 bit) when a buffer pool is set to self- tuning, how are blocks configured in respect to blocked vs non-blocked when self-tuning is set to on? (ie) I have one bufferpool that is 16k that the IBMDEFAULTBP is shared between all tablespaces. The data tablespace has prefetch automatic set to on.
0
7778
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8125
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7874
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8148
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6522
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5342
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
1
2292
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1389
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1113
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.