• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

issue related with the time taken by a particular query

 
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi All,

I have an issue related with the time taken by a particular query.

In one DB(our location) it takes in milliSeconds and in other DB(client location) is takes more than 20 seconds. The Data base is Oracle-9i and there is no any setup/environment differences.

Please suggest me the possible ways to debug the same.

Thanks in advance!
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Are the indexes the same? Disk configuration?
 
deo swaroop
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yes all the indexes are the same.

We ve put indexes where ever it was required to reduce the time....but it did not helped
 
Ranch Hand
Posts: 118
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
are you running the same client software at both locations?
You are not running the same query with a different client
at the different locations. ie sql+ locally and some application
remotely.
 
Ranch Hand
Posts: 133
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Have you tried to run Explain Plan on both environments? Do the execution plans match.

Which optimizer do you use? Rule Based or Cost Based? If you use the Cost Based optimizer or the default one for Oracle 9i, which was called "Choose" and used the Cost Based Algorithm, if statistics are available: what about your table statistics for both environments? Especialy for the slow one: Are there tables without statistics or with stale statistics?

A last possibility for the different execution time: does this query return lots of data (I'm speaking of really, really much data)? If yes: maybe the network between the DB server and the client is different in both situations (in the most extreme case: in one case the query is executed in an SQL window directly on the DB server, in the second case there is a WAN between them).

Those were the reasons I could think of..

Maybe it helps.
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic