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.
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.
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.