Re: Oracle CBO query cost - retrieve value?

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 08 Oct 2007 09:39:39 -0700
Message-ID: <1191861567.211123@bubbleator.drizzle.com>

Barry Bulsara wrote:
> On Oct 7, 10:37 pm, DA Morgan <damor..._at_psoug.org> wrote:

>> csn..._at_gmail.com wrote:
>>> Urm, Morgan, you didn't really think that was a question on syntax,
>>> did you?
>>> To the OP, don't look at cost. It is not for consumption by the end-
>>> user.
>>> You have a bind-variable peeking problem.
>>> Construct say 3 different cursors each with a different stabilised
>>> plan based on selectivity of the inputs, and call the appropriate
>>> cursor based on the input variable. Or reject it altogether.
>>> Comes down to: know your data.
>> Given the syntax error ... you are assuming that what you are seeing
>> is the real statement. I'm not willing to make that assumption.
>>
>> The OP has now stated: "I anonymised & simplified the query data
>> incorrectly" so, given that it has been simplified, the real issue
>> may bear no relationship to what has been posted.
>>
>> To the OP. Anonymizing, ok, rename the tables and columns if you wish.
>> But simplifying makes the exercise meaningless.
>> --
>> Daniel A. Morgan
>> University of Washington
>> damor..._at_x.washington.edu (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org

>
> Daniel, here is what I wrote
>

>> 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.

>
> Here is what you wrote
>

>> To the OP. Anonymizing, ok, rename the tables and columns if you wish.
>> But simplifying makes the exercise meaningless.

>
> An attempt to answer my question has not been made and it does not
> have anything to do with the query syntax or simplification of it. My
> question, as I wrote clearly, is "how can I get the cost of a query so
> I can make a decision whether to show the user a 'refine your search'
> webpage or proceed by executing the query". Who cares what the query
> is, I want to get its cost in advance of performing the query so I can
> decide whether to perform it or whether to reject it and get the user
> to refine their search criteria.
>
> Barry

And if the cost is 300 does that make it a good query or a bad query? Are you going to refuse to run queries over a specific threshhold? Seems to me wholly misdirected.

If you want to control queries then use the built-in tools designed to do just that ... Consumer Groups and Profiles. http://www.psoug.org/reference/dbms_res_mgr.html http://www.psoug.org/reference/profiles.html

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Mon Oct 08 2007 - 11:39:39 CDT