NL cost question
- From: John Clarke <jclarke@xxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 1 Feb 2005 14:39:07 -0500
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
