Home » RDBMS Server » Performance Tuning » How to Track Delay in Displaying data on application GUI (Oracle 10.2.0.4)
How to Track Delay in Displaying data on application GUI [message #536470] Wed, 21 December 2011 01:33 Go to next message
orapratap
Messages: 134
Registered: November 2011
Location: Canada
Senior Member
Hello

What could be the reasons that some queries execute fast when executed on sqlplus on server
whereas the same queries run slower with same input values fed from application screen?

One issue I guess would be bind variable peeking while using application
whereas executing from sqlplus is causing hard parsing and thus getting rid of "peeking"

Is this correct?

What could be the other reasons?

If displaying the data on application screen is taking time after data has been fetched, where I can see this delay?

I understand the elapsed time under 'Fetch' in tkprof will show time taken to fetch from database and not the time taken to be displayed in the application GUI

finally how to set arraysize in jdbc to improve performance by reducing roundtrips? Is there better approach over this?

Regards
OraPratap
P.S. this being generic query haven't included any execution plan
Re: How to Track Delay in Displaying data on application GUI [message #536478 is a reply to message #536470] Wed, 21 December 2011 01:55 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Depends on a lot, for example, I've seen applications that the devs swore blind (and wrote out to the app internal logs) that it was passing a date...but it wasn't, it was a string.

On that basis, the first thing I'd do is check the app is actually passing to oracle (or more accurately, what Oracle is reading that as) what is being claimed
Re: How to Track Delay in Displaying data on application GUI [message #536497 is a reply to message #536478] Wed, 21 December 2011 03:31 Go to previous messageGo to next message
orapratap
Messages: 134
Registered: November 2011
Location: Canada
Senior Member
Hello Roachcoach

Thanks for the reply

I had 3 questions
1) reasons for the delay while accessing from application
2) how to measure this delay
3) option with JDBC for batch fetching the data

For Q.1 your answer has a valid point..thanks for this

Could you please suggest on Q2 and Q3 as well

Regards
OraPratap

Re: How to Track Delay in Displaying data on application GUI [message #536506 is a reply to message #536497] Wed, 21 December 2011 03:59 Go to previous message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
You'd need to trace the application session
(10046 level 12 to start with) to find its slow points.

Start with the traces, work from there out, you may find the Oracle end is identical per query and the tool is doing something funky or anything in between.


That being said, the trouble with these things are, users complain "the database is slow". It might not be, maybe its fine, maybe the network is having problems, maybe they're just having an impatient day/under pressure for fast results (I've seen that before).

First work out what both approaches are doing, if different work out why (often parameters but not always) and go from there.

Don't assume the user is telling you the truth - not from malice, it's just they are reporting symptoms not causes.



Note I'm assuming overall instance health is ok and it is a rather specific issue.



Basically, start simple and try and keep it simple, you don't want to messing with DB wide settings because of a bad/rogue query. you want to fix as unobtrusively as possible

[Updated on: Wed, 21 December 2011 04:01]

Report message to a moderator

Previous Topic: Query on Explain Plan Steps
Next Topic: Query cost
Goto Forum:
  


Current Time: Thu Mar 28 06:58:51 CDT 2024