NL cost question

From: John Clarke <jclarke_at_centroidsys.com>
Date: Tue, 1 Feb 2005 14:39:07 -0500
Message-ID: <20050201143907.7d9e7b6e@centroidsys.com>

I've got a query that CBO is generating a "bad" plan for and was wonderi= ng whether anyone could shed some light ...

SELECT T.REVERSAL=5FDATE,L.STATISTIC=5FAMOUNT, ... FROM PS=5FJRNL=5FLN L, PS=5FJRNL=5FHDR=5FTMP T WHERE L.JOURNAL=5FID =3D T.JOURNAL=5FID

AND L.JOURNAL=5FDATE =3D T.JOURNAL=5FDATE
AND L.BUSINESS=5FUNIT =3D T.BUSINESS=5FUNIT
AND L.UNPOST=5FSEQ =3D T.UNPOST=5FSEQ
AND T.PROCESS=5FINSTANCE =3D 0002167103;

The optimizer correctly determines the outer table in the join and decid= es to full scan on T (PS=5FJRNL=5FHDR=5FTMP) - so far so good. L has se= veral indexes on it:

PSDJRNL=5FLN (PROCESS=5FINSTANCE,BUSINESS=5FUNIT)
PSEJRNL=5FLN (BUSINESS=5FUNIT,CURRENCY=5FCD,ACCOUNT)
PSFJRNL=5FLN (JOURNAL=5FDATE,BUSINESS=5FUNIT,UNPOST=5FSEQ,JOURNAL=5FID)

PS=5FJRNL=5FLN (BUSINESS=5FUNIT,a bunch of the same columns, plus some m= ore)

Since the code we're seeing doesn't specifically join T and L on PROCESS=
=5FINSTANCE, I'm assuming that doing so is for one reason or another ill=
ogical in the context of the program (perhaps I'm putting too much trust=
 in the developers, but anyway ...).  This being the case, the NL costin=

g section of the 10053 trace looks like this ...

Join order[1]: PS=5FJRNL=5FHDR=5FTMP [ T] PS=5FJRNL=5FLN [ L] Now joining: PS=5FJRNL=5FLN [ L] ******* NL Join
  Outer table: cost: 2 cdn: 1 rcz: 74 resp: 2   Inner table: PS=5FJRNL=5FLN
    Access path: tsc Resc: 35672
    Join: Resc: 35674 Resp: 35674
  Access path: index (no sta/stp keys)

      Index: PSDJRNL=5FLN
  TABLE: PS=5FJRNL=5FLN
      RSC=5FCPU: 0 RSC=5FIO: 35461
  IX=5FSEL: 1.0000e+00 TB=5FSEL: 0.0000e+00     Join: resc: 35463 resp: 35463
  Access path: index (index-only)

      Index: PSEJRNL=5FLN
  TABLE: PS=5FJRNL=5FLN
      RSC=5FCPU: 0 RSC=5FIO: 2
  IX=5FSEL: 2.2727e-02 TB=5FSEL: 2.2727e-02     Join: resc: 4 resp: 4
  Access path: index (join index)

      Index: PSFJRNL=5FLN
  TABLE: PS=5FJRNL=5FLN
      RSC=5FCPU: 0 RSC=5FIO: 3
  IX=5FSEL: 0.0000e+00 TB=5FSEL: 1.0817e-10     Join: resc: 5 resp: 5
  Access path: index (index-only)

      Index: PS=5FJRNL=5FLN
  TABLE: PS=5FJRNL=5FLN
      RSC=5FCPU: 0 RSC=5FIO: 3
  IX=5FSEL: 1.0817e-10 TB=5FSEL: 1.0817e-10     Join: resc: 5 resp: 5
Join cardinality: 0 =3D outer (0) * inner (12637436) * sel (0.0000e+00)=   [flag=3D0]
  Best NL cost: 4 resp: 4

It then goes on to cost SM and Hash joins, but they're all higher.

Based on the above, the NL join cost that CBO determines in the best is = 4, which equals the cost of access against T (which I'm comfortable with= ) plus 2, which is the index access cost for index PSEJRNL=5FLN. =20

>From what I can tell from the contents of the predicate/join, CBO can e=

ffectively do nested loop index access using any of PSEJRNL=5FLN, PSJHRN=
L=5FLN, or PS=5FJRNL=5FLN.  I'm guessing that the RSC=5FIO, which it equ=
ates as the cost for the index scan, is based on the index height.  Doub=
le-checking against the data dictionary I can confirm that BLEVEL is 3 f=
or both PSFJRNL=5FLN and PS=5FJRNL=5FLN, but 2 for PSEJRNL=5FLN. In real=
ity though, as the outer table's rows are retrieved and fed to the inner=
 table, the values taht would have been provided to PSFJRNL=5FLN are muc=
h more selective than those feeding PSEJRNL=5FLN, yet it looks to me as =
if CBO thinks PSEJRNL=5FLN is cheaper simply due to the height of the in=

dex.

Am I interpreting this correctly=3F  And if so, what can be done about i=
t short of hints and/or seeding bogus blevel statistics=3F  Is this situ=
ation the join uniformity assumption fallacy that I've read about, or am=

I misinterpreting the statistics=3F

Thanks

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D John Clarke
Sr. Oracle DBA
Centroid Systems, Inc.

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Feb 01 2005 - 14:41:49 CST