Home » Developer & Programmer » Forms » Query slow in Forms
icon13.gif  Query slow in Forms [message #188174] Thu, 17 August 2006 06:18 Go to next message
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 #189324 is a reply to message #188174] Thu, 24 August 2006 02:53 Go to previous messageGo to next message
niis98
Messages: 16
Registered: August 2006
Junior Member
Has not anyone had this problem????

[Updated on: Thu, 24 August 2006 02:53]

Report message to a moderator

Re: Query slow in Forms [message #190308 is a reply to message #188174] Wed, 30 August 2006 02:51 Go to previous messageGo to next message
elsy
Messages: 40
Registered: August 2006
Location: Kuwait
Member

I am also facing the same problem


please help


Re: Query slow in Forms [message #191744 is a reply to message #188174] Fri, 08 September 2006 00:31 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Have you solved your problems?

David
Re: Query slow in Forms [message #191750 is a reply to message #191744] Fri, 08 September 2006 00:37 Go to previous messageGo to next message
niis98
Messages: 16
Registered: August 2006
Junior Member
No, not me.

/Niklas
Re: Query slow in Forms [message #192121 is a reply to message #191750] Mon, 11 September 2006 01:37 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT 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 #192124 is a reply to message #192121] Mon, 11 September 2006 01:53 Go to previous messageGo to next message
niis98
Messages: 16
Registered: August 2006
Junior Member
No it has not, since my query only returns like 8-10 rows.
Re: Query slow in Forms [message #192394 is a reply to message #192124] Tue, 12 September 2006 02:49 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
You say:

my query only returns like 8-10 rows
Please verify whether you main block, when queried, returns 8-10 rows, or there is 8-10 rows in your LOV.

I am now unsure whether you are talking about the time it takes for an LOV to display or the time it takes for a block on a canvas to populate.

David
Re: Query slow in Forms [message #192396 is a reply to message #192394] Tue, 12 September 2006 02:52 Go to previous messageGo to next message
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 #192568 is a reply to message #192396] Tue, 12 September 2006 21:22 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
I repeat

I am now unsure whether you are talking about the time it takes for an LOV to display or the time it takes for a block on a canvas to populate.

David
Re: Query slow in Forms [message #192607 is a reply to message #192568] Wed, 13 September 2006 00:14 Go to previous messageGo to next message
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 #192640 is a reply to message #192607] Wed, 13 September 2006 02:32 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
I guess that you are using Forms 4.5 which uses a different PL/SQL version to that which exists in the database. I suggest adding a hint to your query.

David
Re: Query slow in Forms [message #192644 is a reply to message #192640] Wed, 13 September 2006 02:36 Go to previous messageGo to next message
niis98
Messages: 16
Registered: August 2006
Junior Member
We are using version "Forms [32 Bit] Version 6.0.8.21.3". Should I anyway add a hint to the query?

Niklas
Re: Query slow in Forms [message #192649 is a reply to message #192644] Wed, 13 September 2006 02:46 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Do you have an index on the table? If so, use the generic hint "/*+ INDEX table_name */". If you have no indexes then use "/*+ RULE */".

David
Re: Query slow in Forms [message #192650 is a reply to message #192649] Wed, 13 September 2006 02:48 Go to previous messageGo to next message
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 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT 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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT 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 Go to previous messageGo to next message
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 #193101 is a reply to message #192870] Thu, 14 September 2006 21:27 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
You have the problem, I am asking you to assist me in solving them.

David
Re: Query slow in Forms [message #193131 is a reply to message #193101] Fri, 15 September 2006 00:21 Go to previous messageGo to next message
niis98
Messages: 16
Registered: August 2006
Junior Member
What? Sorry, I dont know what you mean now.
Re: Query slow in Forms [message #193137 is a reply to message #193131] Fri, 15 September 2006 00:38 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
I repeat

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.
Please verify that the problem schema and the non-problem schema match.

David
Re: Query slow in Forms [message #193138 is a reply to message #193137] Fri, 15 September 2006 00:42 Go to previous messageGo to next message
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 #193146 is a reply to message #193138] Fri, 15 September 2006 01:07 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Will you just have a bloody look! How many times do I have to ask? What version of Oracle are you running?

David
Re: Query slow in Forms [message #193240 is a reply to message #193146] Fri, 15 September 2006 07:06 Go to previous messageGo to next message
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 #193686 is a reply to message #193240] Tue, 19 September 2006 00:19 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Bummer!! Sorry about the language but I was extremely confident that an index was missing and that that was the problem.

Do you have TOAD? If so use it to do an Explain Plan on the LOV query in the two environments. If not, try using the Oracle Explain Plan (I can't remember how to do that but I think it is in the 'Tuning' guide). If you have trouble determining or capturing the 'SQL' then use 'TKProf' to get it. But hey, you're using 10g so you should be able to use the DBA panels to query the recently run queries and to show the Explain Plan without any difficulty at all (as long as you have been given the access).

David
Re: Query slow in Forms [message #193689 is a reply to message #193686] Tue, 19 September 2006 00:26 Go to previous messageGo to next message
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
icon4.gif  Re: Query slow in Forms [message #193739 is a reply to message #188174] Tue, 19 September 2006 03:52 Go to previous messageGo to next message
shahidmughal
Messages: 91
Registered: January 2006
Location: Faisalabad Pakistan
Member

hi

1. it happens when ever hard disk have some problem
on the server

2. if this problem is on client side
then rebuild your tns listner

solution
A.server side
1. scan your hard disk for viruses and bad sectors or bad area
2. defragment your hard disk
3. it can be also the problem of operating system's system files
scan for damaged system files and repare your operating system
do not reinstall operating system just repair.
4. Uninstall Un necessary Programs from your computer

-------------------------------------------------------


i hope your problem will solve

Muhammad Shahid Mughal
Pakistan
Re: Query slow in Forms [message #193743 is a reply to message #193739] Tue, 19 September 2006 04:20 Go to previous messageGo to next message
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 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT 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 Go to previous messageGo to next message
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 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT 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
Previous Topic: Form 10g Problem
Next Topic: Help
Goto Forum:
  


Current Time: Sun Dec 04 22:50:25 CST 2016

Total time taken to generate the page: 0.13016 seconds