Hi, all.
I'm very new to Oracle and was wondering if there's a way either through some tool or programmatically to estimate how a long a query would to take to execute?
THanks!
+3
A:
The best way to estimate the running time it is to create some test data that resembles the real data you expect to get, run it on that data, and time how long it takes.
Mark Byers
2010-03-10 21:02:44
I've used the query plan in Oracle in two projects so far for this purpose - it worked reasonably well for simple queries; we'd get the estimated cost and cardinality, and if they exceeded certain thresholds, we'd throw up a "this query may take too long" warning to the user - then the user could cancel and add more criteria to their search.
Jeffrey Kemp
2010-03-11 02:14:45
+1
A:
Yes there is a way theoretically. At least in Sybase (and likely in oriacle as well), there's such a thing as table statistics (used by optimizer to determine the most optimal query plan). You can compute rougly the amount of data the query will return and how long it would take from that.
However, it's not trivial (you basically need to duplicate the logic of optimizer and query compiler) and the statistics may not be available to outside code.
DVK
2010-03-10 21:13:23