OraFAQ Forum: Forms » speed of oracle program

Home » Developer & Programmer » Forms » speed of oracle program

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

speed of oracle program [message #451480]

Thu, 15 April 2010 01:25

haider_1pk
Messages: 135
Registered: March 2009
Location: PAKISTAN

Senior Member

Hello All,

I have made the inventory sale program in oracle. Its working from 7 years. Now its speed is very slow.It take much time to execute query.Please some one guide me how to solve my problem.

Thanks

Haider

Report message to a moderator

Re: speed of oracle program [message #451483 is a reply to message #451480]

Thu, 15 April 2010 01:33

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Make sure that you have an index based on the columns that you use in your query.

David

Report message to a moderator

Re: speed of oracle program [message #451485 is a reply to message #451483]

Thu, 15 April 2010 01:46

sonia.ali
Messages: 40
Registered: April 2009
Location: Pakistan

Member

Yes Sir,
it make index based on the columns.

Thanks

Haider

Report message to a moderator

Re: speed of oracle program [message #451487 is a reply to message #451485]

Thu, 15 April 2010 02:04

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Is this query slow in Forms only, or is it slow when you execute it from SQL*Plus? Did you try to trace it, run through TKPROF in order to see what consumes that much time?

Report message to a moderator

Re: speed of oracle program [message #451490 is a reply to message #451487]

Thu, 15 April 2010 02:13

haider_1pk
Messages: 135
Registered: March 2009
Location: PAKISTAN

Senior Member

Dear Sir,

Its run in sql and show data more then 100000 recodes.
So I think thats why its show in forms.

Thanks

Report message to a moderator

Re: speed of oracle program [message #451515 is a reply to message #451490]

Thu, 15 April 2010 03:21

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

What will your users do with 100.000 records in a form? Are you sure it is the correct way to do the job? Can't you restrict number of records (by applying additional condition into the WHERE clause)?

Report message to a moderator

Re: speed of oracle program [message #451634 is a reply to message #451515]

Thu, 15 April 2010 20:08

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Are you retrieving ALL the records or is the first block just taking a long time to appear?

David

Report message to a moderator

Re: speed of oracle program [message #451649 is a reply to message #451634]

Fri, 16 April 2010 00:10

shaz
Messages: 182
Registered: June 2009

Senior Member

I think OP want to say that he has run the query in sql*Plus and the table has over 100000 records. All the records are not used in form.

[Updated on: Fri, 16 April 2010 00:17]

Report message to a moderator

Re: speed of oracle program [message #451701 is a reply to message #451480]

Fri, 16 April 2010 04:30

haider_1pk
Messages: 135
Registered: March 2009
Location: PAKISTAN

Senior Member

Yes Sir,

Actually My program base on codes. I put the all name of medicine with code. So that s why i need to see all code.
As I told u , I have made the inventory program for medicine . So It have a lot of recodes. Well I dont have any idea of TKPROF.
Please Sir Guide me.

Thanks

Haider

Report message to a moderator

Re: speed of oracle program [message #451704 is a reply to message #451701]

Fri, 16 April 2010 04:52

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I'm sorry, but I don't know anything about "inventory programs for medicine" so it is kind of difficult for me to understand what "all code" or "recode" means. Perhaps, if you could present (simplified) data model, form screenshot, SELECT statements you use, number of records they return, ... - in other words, provide some more information - maybe it would be easier to help.

As of TKPROF, is it described in documentation.

Report message to a moderator

Re: speed of oracle program [message #451711 is a reply to message #451480]

Fri, 16 April 2010 05:25

haider_1pk
Messages: 135
Registered: March 2009
Location: PAKISTAN

Senior Member

Dear Sir,

I have attached the pic of my program. there i am using the code of sale man, customer code and medicine code.But the problem is that it become very slow to execute query .I have 10 client of my inventory program. At the same time 10 people are using the software.It was good. Bur from two month its going slow. Actually I made the software for a medicine firm.
Please Help me.

Thanks

Haider

Attachment: df.GIF
(Size: 43.62KB, Downloaded 687 times)

Report message to a moderator

Re: speed of oracle program [message #451712 is a reply to message #451480]

Fri, 16 April 2010 05:33

cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester

Senior Member

You need to use SQL_Trace to trace the session, then use tkprof to get readable output. Then post that output here.

Report message to a moderator

Re: speed of oracle program [message #451714 is a reply to message #451711]

Fri, 16 April 2010 05:38

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

haider_1pk

It was good. Bur from two month its going slow.

What changed within the last two months?

"Codes" you use seem to be "sales manager" and "customer" ones. Right?

Please confirm that columns that make master-detail relationship are indexed. Did you collect statistics? If not, perhaps you should.

Report message to a moderator

Re: speed of oracle program [message #451823 is a reply to message #451480]

Sat, 17 April 2010 01:54

haider_1pk
Messages: 135
Registered: March 2009
Location: PAKISTAN

Senior Member

Dear sir,

I think data is our from two month.

Report message to a moderator

Re: speed of oracle program [message #451844 is a reply to message #451823]

Sat, 17 April 2010 07:16

shaz
Messages: 182
Registered: June 2009

Senior Member

Quote:

I think data is our from two month.

what does it mean? Well you have to collect statistics about the slow speed. we can help you only if you help yourself.

Report message to a moderator

Re: speed of oracle program [message #452149 is a reply to message #451480]

Tue, 20 April 2010 05:37

haider_1pk
Messages: 135
Registered: March 2009
Location: PAKISTAN

Senior Member

Dear Sir,

kindly guide me SQL_Trace tkprof.

Thanks

Haider

Report message to a moderator

Re: speed of oracle program [message #452150 is a reply to message #451480]

Tue, 20 April 2010 05:40

cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester

Senior Member

You've already been provided links to the documentation and our wiki site on those subjects. If you have a specific question/problem post it but otherwise we are just going to point you back to the documentation.

Report message to a moderator

Re: speed of oracle program [message #452417 is a reply to message #451711]

Thu, 22 April 2010 00:07

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

If you are retrieving by 'invoice', the number of entries in the table should be irrelevant when it comes to retrieval time. The code descriptions will need to be retrieved and again the number of them should be irrelevant to retrieval time as there entries should be indexed in their tables.

David

Report message to a moderator

Previous Topic:	Upload File Use Java Bean in Form 10g
Next Topic:	hierarchical Tree

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 05:31:08 CDT 2024