We would like to allow users to submit whatever query they want and reject those that are 'too expensive'
There are two possible ways to do this:
1: use optimizer statistics in conjunction with upper limits for statement cost, number of rows etc. Problem with this is that statement costs are not comparable between different statements
2: quit the query after a certain amount of time/cpu/... has been consumed. This has the disadvantage that runaway queries are started anyway, thus consuming resources until they are aborted.
How can the idea be rescued?
You are correct in you analysis with respect to #1. The cost assigned to a query may not be used to make any assumptions as to how many resources/how long they will take. They are only comparable internally.
Resource profiles are a valid method. It is true that until we actually hit the limit -- we do not know that it will hit the limit and hence cannot proactively "not run the query" in the first place. Resource profiles work until the limit is hit and then abort the query/session. Any work would be undone.
Another thing to consider is resource allocation -- new in 8i. It will not prevent the queries from being run but you can create classes of users such that "more important" people get more resources. If a lower priority user does submit that query from #@#$#@ -- it will not affect the high priority users as much. See:
</code>
http://docs.oracle.com/cd/A81042_01/DOC/server.816/a76962/ch2.htm#140579 "database resource management".
Some query tools, such as Oracle discoverer, have "predicative" query resource governors that might be of interest as well. They will not run a query (optionally) that it feels is too costly (you define that). See:
http://www.oracle.com/datawarehouse/products/bitools/discoverer/index.html?tech_over.html <code>
for info on that.