Query Problem
On IDS 7.31 with a Tuxedo front end, a query ran fast for one locationid value (using an index) but was slow or hung for other values, where the plan showed a sequential scan. Indexes existed and the application SQL couldn't be changed. The poster answered his own question about an hour later: running UPDATE STATISTICS on all tables involved in the query fixed the plan choice. A later reply suggested checking for column distributions (sysdistrib) and the exact 7.31 sub-release; the rest of the thread is off-topic bickering.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi We are using ids 7.31 and tuxedo application. I have found a strange problem, whenever we click a button in the application it executes a certain query based on the value we pass(say locationid). If we pass value(locationid) 1 it returns the output immediately but if we pass some other value(locationid) it is taking time and getting hanged and returning err msg. We have all the indexes in place for necessary columns. one more observation.... For value(locationid) 1 its using index path, for other values(locationid) its taking Sequential path. We have found it using explain plan. We cant change the code as its inbuilt.. Is it a DB problem or Application problem.. Any idea Pls help
Hi Got the solution for the problem We have to run Update statistics on all the tables involved in the query.
S SURESH said: > > Hi > > Got the solution for the problem > > We have to run Update statistics on all the tables > involved in the query. No shit, Sherlock. -- Bye now, Obnoxio "I don't read newspapers anymore except the local rag which I do weekly to cheer myself trying to see if anyone I hate has been stabbed." -- Horribilis XVI -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.
Get off his case ... why can't you just hold your tongue once in a while ... not all of us have obtained perfection like yourself. "Obnoxio The Clown" <obnoxio@serendipita.com> Sent by: ids-bounces@iiug.org 12/07/2006 06:46 AM Please respond to ids@iiug.org To ids@iiug.org cc Subject Re: Query Problem [7931] S SURESH said: > > Hi > > Got the solution for the problem > > We have to run Update statistics on all the tables > involved in the query. No shit, Sherlock. -- Bye now, Obnoxio "I don't read newspapers anymore except the local rag which I do weekly to cheer myself trying to see if anyone I hate has been stabbed." -- Horribilis XVI -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Peter_Logan@spartanstores.com said: > > Get off his case ... why can't you just hold your tongue once in a while > .... not all of us have obtained perfection like yourself. Have you tried UPDATE STATISTICS? -- Bye now, Obnoxio "I don't read newspapers anymore except the local rag which I do weekly to cheer myself trying to see if anyone I hate has been stabbed." -- Horribilis XVI -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.
Hi,
1) Have you distributions for your table ? High or Medium ?
select distinct tabname,colname,mode,constructed
from sysdistrib d,syscolumns c,systables t
where d.tabid=t.tabid and c.tabid=d.tabid and c.colno=d.colno
and tabname='<your-table>';
2) What is the release of 7.31 ?
onstat -V
Regards.
________________________________
________________________________
"S SURESH"
<suresh.sambana@r
il.com> To
Sent by: ids@iiug.org
ids-bounces@iiug. cc
org
Subject
Query Problem [7929]
07/12/2006 10:29
Please respond to
ids@iiug.org
Hi
We are using ids 7.31 and tuxedo application.
I have found a strange problem, whenever we click a button in the
application
it executes a certain query based on the value we pass(say locationid).
If we pass value(locationid) 1 it returns the output immediately but if we
pass
some other value(locationid) it is taking time and getting hanged and
returning err msg.
We have all the indexes in place for necessary columns.
one more observation....
For value(locationid) 1 its using index path,
for other values(locationid) its taking Sequential path.
We have found it using explain plan.
We cant change the code as its inbuilt..
Is it a DB problem or Application problem..
Any idea Pls help
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Yes, I'm very familiar with the process. All I was saying is cut the guy some slack. He obviously missed that one ... or doesn't know about it ... so he asked a question ... you don't have to cut him down for that ... "Obnoxio The Clown" <obnoxio@serendipita.com> Sent by: ids-bounces@iiug.org 12/07/2006 08:41 AM Please respond to ids@iiug.org To ids@iiug.org cc Subject Re: Query Problem [7933] Peter_Logan@spartanstores.com said: > > Get off his case ... why can't you just hold your tongue once in a while > .... not all of us have obtained perfection like yourself. Have you tried UPDATE STATISTICS? -- Bye now, Obnoxio "I don't read newspapers anymore except the local rag which I do weekly to cheer myself trying to see if anyone I hate has been stabbed." -- Horribilis XVI -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Peter_Logan@spartanstores.com said: > > Yes, I'm very familiar with the process. All I was saying is cut the guy > some slack. He obviously missed that one ... or doesn't know about it ... > so he asked a question ... you don't have to cut him down for that ... I think you need to try decaf for a week or two. -- Bye now, Obnoxio "I don't read newspapers anymore except the local rag which I do weekly to cheer myself trying to see if anyone I hate has been stabbed." -- Horribilis XVI -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.