Oracle CBO query cost - retrieve value?

Hi, I have a query

SELECT a,b,c,d
 FROM table e,f

  WHERE e.id=f.id
    AND e.g=something
    AND h.i=somethingelse

"something" and "somethingelse" are entered by the user in a webform.

Sometimes this query returns very quickly, and at other times it can take 3-4 minutes before the phone starts going with users complaining that the system has hung. It hasn't and when the query takes under a second, the cost according to explain plan is about 50 and then the query takes 3-4 minutes the cost is a lot higher.

The data distribution is skewed and the CBO sometimes chooses to use an index and at other times does a full table scan.

We are in the process of profiling user queries at the moment. Someone suggested however that in the first instance we should get Oracle to calculate the query cost, and if it is over a certain value reject the query and ask the user to refine their search.

I know I can see the cost in the explain plan table. My question is how can I get this value and decide in our pl/sql & java middleware whether to proceed or show the user the refine your search webpage.

Any ideas - we are running 10.1 on Windows 2000.

Thank you.
Barry Received on Sun Oct 07 2007 - 11:47:47 CDT