 Query slow in Forms [message #188174] |
Thu, 17 August 2006 06:18  |
niis98
Messages: 16
Registered: August 2006
|
Junior Member |
|
|
I have a query that will take 10 seconds to run from a Lov in forms but if I try the same query in SQL+ it will run in 0,2 seconds. I have never experienced this before and dont know what to do about it. Does anyone have an answer?
regards,
Niklas
[Updated on: Thu, 17 August 2006 06:28] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: Query slow in Forms [message #192121 is a reply to message #191750] |
Mon, 11 September 2006 01:37  |
 |
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
Niklas,
The issue is that the LOV requires ALL the entries to be retrieved, sent to the form, and loaded into the LOV. When you run it at the SQL prompt, only the first 'array' is returned. Therefore, the response time is faster.
How many rows are returned by the query? Consider using the 'long' version of an LOV where a 'stub' screen is displayed into which the user types the first one or more characters of the string to be retrieved and then only those entries are returned. For example, if searching an ordered name list, there may be 200,000 entries, but by placing the letter 'a' in the 'stub' screen, only 10,000 entries have to be returned and loaded into the LOV.
Has this helped?
David
|
|
|
|
|
|
|
|
| Re: Query slow in Forms [message #192396 is a reply to message #192394] |
Tue, 12 September 2006 02:52 |
niis98
Messages: 16
Registered: August 2006
|
Junior Member |
|
|
The query when run in a SQL prompt or when run from the form in a Lov only returns 8-10 records totally. So it could not be a problem where only the first 'array' is returned.
Niklas
|
|
|
|
|
|
| Re: Query slow in Forms [message #192607 is a reply to message #192568] |
Wed, 13 September 2006 00:14 |
niis98
Messages: 16
Registered: August 2006
|
Junior Member |
|
|
Lov to display, opening the Lov takes about 10 seconds, running the same query from a SQL prompt takes 0,2 seconds. The query returns 8-10 records.
Niklas
|
|
|
|
|
|
|
|
|
|
| Re: Query slow in Forms [message #192650 is a reply to message #192649] |
Wed, 13 September 2006 02:48 |
niis98
Messages: 16
Registered: August 2006
|
Junior Member |
|
|
I am a bit worried about using an index hint since we have like 10 different customers using the same application and only one of them has the problem. Using the index hint might affect the other customers Lov to be slow???
Niklas
|
|
|
|
| Re: Query slow in Forms [message #192652 is a reply to message #192650] |
Wed, 13 September 2006 02:53 |
|
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
| This is new information! |
we have like 10 different customers using the same application and only one of them has the problem
|
Are the users getting the executible from the same place or do they each have their own copy?
Are they all accessing the same database, that is, do they all go in as the same user? If the users are accessing the data via their own user-id maybe this one user has different access permissions?
Does this user have slow performance on a machine different to the one on which they normally work?
David
Upd-Mod: Typos. Sorry.
[Updated on: Wed, 13 September 2006 02:54] Report message to a moderator |
|
|
|
| Re: Query slow in Forms [message #192656 is a reply to message #192652] |
Wed, 13 September 2006 03:06 |
niis98
Messages: 16
Registered: August 2006
|
Junior Member |
|
|
Sorry.
Every customer has their own environment and db. All customers have like 100 different user that uses the same runtime. They are all logging in as different users but with same permission. All users for this specific customer has the same problem with this one Lov beeing slow.
Niklas
|
|
|
|
| Re: Query slow in Forms [message #192839 is a reply to message #192656] |
Wed, 13 September 2006 18:04 |
|
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
Then I believe that this 'one' database is missing an index. You need to do a schema dump on a 'normal' machine and on this 'slow' machine, and then compare them.
But you could start just with the tables associated with this one LOV.
David
[Updated on: Wed, 13 September 2006 18:05] Report message to a moderator |
|
|
|
| Re: Query slow in Forms [message #192870 is a reply to message #192839] |
Thu, 14 September 2006 00:43 |
niis98
Messages: 16
Registered: August 2006
|
Junior Member |
|
|
But if I run the query in a SQL prompt on this machine and run open the Lov on the same machine and same db then they must be using the same indexes. Nothing is different apart from me running the query in SQL prompt or user opening the Lov.
Niklas
|
|
|
|
|
|
|
|
|
|
| Re: Query slow in Forms [message #193138 is a reply to message #193137] |
Fri, 15 September 2006 00:42 |
niis98
Messages: 16
Registered: August 2006
|
Junior Member |
|
|
The query when run from SQL prompt and opened from LOV on same schema, same user, same db, same everything is slow when opening Lov. If there is a missing index then it is missing for Lov also. So I still cant see how the SQL prompt would use an index that the Lov would not use.
Niklas
|
|
|
|
|
|
| Re: Query slow in Forms [message #193240 is a reply to message #193146] |
Fri, 15 September 2006 07:06 |
niis98
Messages: 16
Registered: August 2006
|
Junior Member |
|
|
Ok I see what you mean now, sorry. I have had a look and one index was missing. I created the index but unfortunately that did not help.
That customer is running Oracle 10g on Linux.
Niklas
|
|
|
|
|
|
| Re: Query slow in Forms [message #193689 is a reply to message #193686] |
Tue, 19 September 2006 00:26 |
niis98
Messages: 16
Registered: August 2006
|
Junior Member |
|
|
No problem. Before I posted on this forum I tried the explain plan in SQL Navigator which we are using and all is looking good.
Btw the the query in question is selected from a view, do you think this could have an affect?
Niklas
|
|
|
|
|
|
| Re: Query slow in Forms [message #193743 is a reply to message #193739] |
Tue, 19 September 2006 04:20 |
niis98
Messages: 16
Registered: August 2006
|
Junior Member |
|
|
but how can a hard disk or tns listener problem only show itself when running the Lov and not when running same query on same db and same machine in SQL prompt?
Niklas
|
|
|
|
| Re: Query slow in Forms [message #193942 is a reply to message #193743] |
Wed, 20 September 2006 01:50 |
|
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
Because when you run it against the database you are running aginst the database, but when running forms the form has to communicate with the database and that is by tns listener.
Check the tnsnames.ora and listener.ora - there may be some differences between the machines.
You say the 'explain plans' looked good - but were they identical?
David
[Updated on: Wed, 20 September 2006 01:50] Report message to a moderator |
|
|
|
| Re: Query slow in Forms [message #194794 is a reply to message #193942] |
Mon, 25 September 2006 06:13 |
niis98
Messages: 16
Registered: August 2006
|
Junior Member |
|
|
It looks like there is no problem with the listener or tnsnames. We have noticed another strange thing, when turning on a trace against the session that is running the Lov then the Lov will come quickly as it should but when we turn off the trace then it will be "slow" again?????
Niklas
|
|
|
|
| Re: Query slow in Forms [message #195131 is a reply to message #194794] |
Wed, 27 September 2006 02:39 |
|
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
This is getting silly. What were the differences between the explain plans that were done on the 'fast' versus the 'slow' machine?
All I can suggest is converting the form from 'fmb' to 'xml' and then converting it back again and doing a Ctrl-Shft-K (compile all) and then the Ctrl-T (generate). Something in the version of that form on that machine has a problem. I would have expected that there was a difference in the database but you have said that you have checked the schemas and they are identical. Also the users' roles on the 'slow' machine are identical to the 'fast' machine, so if it isn't the database (have you checked the database sizes?) or the user permissions, then it has to be the form.
David
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
