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 4 2204
"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.
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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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.
|
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 measures then need to select
the one that's been entered last based on this field. Also, if there
|
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 query looks for matching parameters like rooms, location, size
|
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, instead opting for a full table scan, thus killing
performance. The messages table can contain...
|
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
| |
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
|
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.
|
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 on pre-gathered table and index statistics while the RBO makes it's decisions based on a set of ...
|
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.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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 synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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 most users, this new feature is actually very convenient. If you want to control the update process,...
|
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 launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |