Re: Odd join selectivity
- From: "Charles Schultz" <sacrophyte@xxxxxxxxx>
- To: "Greg Rahn" <greg@xxxxxxxxxxxxxxxxxx>
- Date: Thu, 6 Mar 2008 18:45:27 -0600
It is possible I over-simplified my issue. The one query I posted originally
is a small part of a much larger query, but the cardinality starts to
diverge with this one query. They key word in your statement is "probably";
when run by itself, the query performs quite quickly (roughly one second),
even though the cardinality is off "a little". However, experience has
taught me that queries usually run fastest when the cardinality is spot on.
Another strange artifact of the CBO, I have noticed, is that a cardinality
that differs merely by 1 can be the difference between a table scan or a
index access, a nested loop join or a hash. There are a ton of variables
involved, as I am sure everyone here knows. *grin*
As to answering the question, I have to take a step back and apologize to my
Support Engineer for being so brash. He was acting in good faith upon
suggestions handed down to him from COE (some panel of experts that act
between the analysts and the developers), and they want to gather some extra
information that is not normally gather by the 10053. I am still not clear
what exactly that could be, but now I am very curious; at least now I do not
feel so foolish for not being able to figure this out on my own.
And you are right, this is not normally something that a support analyst
could (or even should?) field. I am grateful that he has taken up the task
with COE. And I think your whole point, Greg, is "Why does it matter?"
Please correct me if I am wrong. It matters to me because I am curious, and
I want to know. That is the nuts and bolts of it. Perhaps the query would
run just as fast if the cardinality were equal, or off by one (or two or
three...). At this point of the game, I just want to know how the difference
came about in an effort to understand a little better how the CBO works.
Which helps in the long run to figure out if a query can run faster or not.
Your comment about "reverse engineering" is quite interesting as well. I do
not mean to belabor this thread, but it always seems like a game with
Oracle; they tell you a lot of stuff and leave a whole of stuff unsaid (for
one reason or another). It is often a challenging, fun game, sometimes
tedious, and sometimes downright frustrating when you hit a bug or
"application design" issue. I look forward to more oracle wikis and blogs
that help me reverse engineer Oracle. *grin*
On Thu, Mar 6, 2008 at 4:55 PM, Greg Rahn <greg@xxxxxxxxxxxxxxxxxx> wrote:
> What is the problem with this plan? The estimate of 54k is probably close
> enough to 273k not to cause a plan change.
>
> The question of "Where is 1.0170e-04 coming from?" is probably not
> something that a support person would be able to answer. It probably takes
> a developer to look at the code, and it not something they are just going to
> do because you ask. If there is a problem with the plan, I'm sure they
> would help, but they are not really there to answer reverse engineering
> questions.
--
Charles Schultz
Other related posts:
