Re: Odd join selectivity RESOLVED (partially)

From: Charles Schultz <sacrophyte_at_gmail.com>
Date: Fri, 7 Mar 2008 13:59:27 -0600
Message-ID: <7b8774110803071159v18723ec6q9cbdfbcd8dddc0e5@mail.gmail.com>


After looking at all the information collected, Oracle Support has responded by saying that the optimizer is actually taking a wild shot in the dark with this selectivity number; the root of the issue is that we have VPD enabled on the underlying tables, and since SYS_CONTEXT is non-deterministic, the run time optimizer is not correctly determining the selectivity; more importantly, the run time optimizer is *unable* to calculate the proper number. I still have no idea how the number was actually generated; for all intents and purposes, it could come from a random number generator, I guess. =) The suggested work-around is to have SQL Tuner come up with a profile. I know one can assign statistics to functions using the Data Cartridges, but that seems like overkill, and the last time I plunged into that murky bog, I came out with no noticeable benefits.

Thanks for all those who responded.

On Thu, Mar 6, 2008 at 10:45 AM, Charles Schultz <sacrophyte_at_gmail.com> wrote:

> Good day, List,
>
> I have been scratching my head, reading books (eg, page 288-291 of
> Jonathan's CBO book) and I still feel like the answer is just beyond my
> grasp.
>
> Query:
>
> select /*+ gather_plan_statistics */
> count(*)
> from
> FAMIS_CP_BUDGET_CATEGORY cat, FAMIS_CP c, FAMIS_CP_BUDGET_DETAIL b_d
> where
> "C"."CP_NUMBER"="B_D"."CP_NUMBER"
> AND "C"."ORG_ID"="B_D"."ORG_ID"
> AND "B_D"."BUDGET_CATEGORY"="CAT"."BUDGET_CATEGORY"
> AND "B_D"."ORG_ID"="CAT"."ORG_ID"
> and "C"."CP_TYPE"="CAT"."CP_TYPE"
> AND "C"."ORG_ID"="CAT"."ORG_ID"
> /
>
> note: the quotes came out of dbms_xplan.
>
> The plan:
>
>
> -------------------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Starts |
> E-Rows | A-Rows | A-Time | Buffers |
>
> -------------------------------------------------------------------------------------------------------------
> | 1 | SORT AGGREGATE | | 1 |
> 1 | 1 |00:00:00.71 | 1726 |
> |* 2 | HASH JOIN | | 1 |
> 54629 | 273K|00:00:00.84 | 1726 |
> |* 3 | HASH JOIN | | 1 |
> 852 | 852 |00:00:00.01 | 32 |
> | 4 | INDEX FULL SCAN | FAMIS_CP_BUDGET_CAT_CAT_UN | 1 |
> 5 | 5 |00:00:00.01 | 1 |
> | 5 | TABLE ACCESS FULL| FAMIS_CP | 1 |
> 852 | 852 |00:00:00.01 | 31 |
> | 6 | TABLE ACCESS FULL | FAMIS_CP_BUDGET_DETAIL | 1 |
> 273K| 273K|00:00:00.01 | 1694 |
>
> -------------------------------------------------------------------------------------------------------------
>
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 2 - access("C"."CP_NUMBER"="B_D"."CP_NUMBER" AND
> "C"."ORG_ID"="B_D"."ORG_ID" AND
> "B_D"."BUDGET_CATEGORY"="CAT"."BUDGET_CATEGORY" AND
> "B_D"."ORG_ID"="CAT"."ORG_ID")
> 3 - access("C"."CP_TYPE"="CAT"."CP_TYPE" AND
> "C"."ORG_ID"="CAT"."ORG_ID")
>
> The 10053:
>
> Using concatenated index cardinality for table FAMIS_CP
> Revised join sel:2.3474e-04 = 1.0170e-04 * (1/852.00) * (1/5.0848e-04)
> Join Card: 54629.24 = outer (852.00) * inner (273146.00) * sel (
> 2.3474e-04)
> Join Card - Rounded: 54629 Computed: 54629.24
>
> The Question:
> Where is 1.0170e-04 coming from? 1/852 is the density for CP_NUMBER, and
> 1/5.0848e-04 is the density for ORG_ID (both on FAMIS_CP), but I cannot for
> the life of me figure out 1.0170e-04. I have to assume it is combined join
> bit for the other table columns (B_D.BUDGET_CATEGORY and B_D.ORG_ID, density
> = 1.8305E-06 for each) but am not certain at all. I do not believe this is
> an issue of transitive closure (but could be wrong). Am I missing something
> obvious?
>
> Environment:
> Oracle 10.2.0.2 on Solaris 8. Computed stats on all tables with default
> method_opt.
>
>
> Any and all help would be much appreciated!
>
>
> --
> Charles Schultz

-- 
Charles Schultz

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Mar 07 2008 - 13:59:27 CST

Original text of this message