Re: Oracle CBO query cost - retrieve value?

On Oct 7, 11:47 am, Barry Bulsara <bbulsar..._at_hotmail.com> wrote:
> 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

Without actually executing the query the cost won't be available automatically to you; of course you could generate an 'explain plan' on the fly and query PLAN_TABLE for the results. Additionally setting autotrace to traceonly would provide such output (albeit after the time required to actually run the query [from my experience, and I suppose this is to return an actual plan rather than an estimated one such as 'explain plan' generates]).

Have you enabled event 10046 at level 8 or level 12 and examined the tkprof output from these traces? Have you enabled event 10053 and perused the trace files produced? Possibly you should do so before embarking upon a project to generate and read query plans for as-yet- unsubmitted queries. Generating plans and costs seems to be a tremendous amount of work to validate actually running a submitted query simply to keep response times fast.

I understand why you'd elect to do this, however you are on 10.1.0.x; is migrating to 10.2.0.x not possible? 10.1 was certainly not the most bug-free of releases, and 10.2 is far more stable. Also, you may find such performance issues reduced in 10.2.

My two cents.

David Fitzjarrell Received on Mon Oct 08 2007 - 09:54:09 CDT