How can I predict execution costs?
Our application is heavily worklist based. The user can enter query criteria and then execute the query in countless possible combinations.
Now I need a way to predict the costs. I thought about sending the query to a package which then uses EXPLAIN PLAN to get and return the costs. If the costs are above a broad limit, then a warning is displayed on the client.
The problem with this solution is that the worklist queries are pretty extensive. EXPLAIN PLAN takes some time to do its job. And it seems that the following query does not reuse the plan created by EXPLAIN PLAN.
I am a Java developer, so bear with me. How can I get an estimate of the execution costs of a query without really executing it? Does anyone have an idea? Is there a way to do this?
Thanks,
Sascha