I filed a case with Oracle Support and ended up with a carpet salesman. :-( I asked him specifically where this number is coming from and he threw metalink note 68992.1 at me. When I told him that 1/NDV does not match any of our indexes, he wants me to reset all my statistics back to default, run a few 10046 traces, download and install sqlt (the next version of traceanalzyer, apparently) and upload all the results. Sound familiar to anyone? On Thu, Mar 6, 2008 at 10:45 AM, Charles Schultz <sacrophyte@xxxxxxxxx> 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