AAAAARRRRRRGGGHHHHH! Bitten by AUTOTRACE again! It LIED to me! Do the old faithful 10046 trace, what happens? Argh..... Rows Row Source Operation ------- --------------------------------------------------- 1 HASH JOIN 1 INDEX RANGE SCAN OBJ#(19119) (object id 19119) 170359 VIEW 170359 HASH JOIN 170359 INDEX FAST FULL SCAN OBJ#(19153) (object id 19153) 170359 INDEX FAST FULL SCAN OBJ#(19158) (object id 19158) Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 1 NESTED LOOPS 1 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'AIA_INDX_PR01' (NON-UNIQUE) 170359 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF 'ADDS_USERS' 170359 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'AUSR_PK' (UNIQUE) Ok, ok, I should know better by now, AUTOTRACE CANNOT BE TRUSTED!! It should be removed from the database! It's too tempting, due to ease of use! =20 I've GOT to stop using it! So, the rewrite into the scalar subquery forced the optimizer to do what I thought it was doing all along... Argh.... > -----Original Message----- > From: oracle-l-bounce@xxxxxxxxxxxxx > [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Bobak, Mark > Sent: Wednesday, October 27, 2004 4:41 PM > To: oracle-l@xxxxxxxxxxxxx > Subject: That crazy CBO..... >=20 >=20 > Ok, I'm a bit at a loss to explain this..... >=20 > I have two tables, ADDS_USERS, which has AUSR_ID as the=20 > primary key, and > AUTHORIZED_IP_ADDRESSES. >=20 > Now, my original query looks like this: > SELECT A.AUSR_LOGIN_SCREEN_NAME FROM ADDS_USERS A, > AUTHORIZED_IP_ADDRESSES B > WHERE A.AUSR_ID =3D3D B.AUSR_ID AND :B1 BETWEEN=20 > B.AIA_IP_ADDRESS_START AND > B.AIA_IP_ADDRESS_END; >=20 > and when run w/ autotrace set to traceonly, produces the following > output: >=20 > Execution Plan > ---------------------------------------------------------- > 0 SELECT STATEMENT Optimizer=3D3DCHOOSE (Cost=3D3D13 = Card=3D3D110 =3D > Bytes=3D3D46 > 20) >=20 > 1 0 NESTED LOOPS (Cost=3D3D13 Card=3D3D110 Bytes=3D3D4620) > 2 1 INDEX (RANGE SCAN) OF 'AIA_INDX_PR01' (NON-UNIQUE) (Cost > =3D3D4 Card=3D3D110 Bytes=3D3D3080) >=20 > 3 1 TABLE ACCESS (BY INDEX ROWID) OF 'ADDS_USERS'=20 > (Cost=3D3D2 Ca > rd=3D3D1 Bytes=3D3D14) >=20 > 4 3 INDEX (UNIQUE SCAN) OF 'AUSR_PK' (UNIQUE) >=20 >=20 >=20 >=20 > Statistics > ---------------------------------------------------------- > 0 recursive calls > 0 db block gets > 1321 consistent gets > 864 physical reads > 0 redo size > 513 bytes sent via SQL*Net to client > 652 bytes received via SQL*Net from client > 2 SQL*Net roundtrips to/from client > 0 sorts (memory) > 0 sorts (disk) > 1 rows processed >=20 > Now, that plan looks quite reasonable, but, 1321 buffer gets is too > many, not to mention the physical I/O. This is a very=20 > frequently called > SQL statement. >=20 > So, I tried: > re-creating ADDS_USERS ordered by AUSR_ID, to improve AUSR_PK index > clustering factor. No use. > creating AUTHORIZED_IP_ADDRESSES as an IOT. Also useless. >=20 > So, I went back to SQL hacking, and finally came up with the somewhat > odd looking, but effective: > SELECT (select A.AUSR_LOGIN_SCREEN_NAME FROM ADDS_USERS A where > a.ausr_id =3D3D b.ausr_id) from AUTHORIZED_IP_ADDRESSES B > WHERE :B1 BETWEEN B.AIA_IP_ADDRESS_START AND B.AIA_IP_ADDRESS_END; >=20 > which produces an output like: > Execution Plan > ---------------------------------------------------------- > 0 SELECT STATEMENT Optimizer=3D3DCHOOSE (Cost=3D3D2 = Card=3D3D110 =3D > Bytes=3D3D308 > 0) >=20 > 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'ADDS_USERS'=20 > (Cost=3D3D2 Card > =3D3D1 Bytes=3D3D14) >=20 > 2 1 INDEX (UNIQUE SCAN) OF 'AUSR_PK' (UNIQUE) (Cost=3D3D1 = =3D > Card=3D3D1 > ) >=20 > 3 0 INDEX (RANGE SCAN) OF 'AIA_INDX_PR01'=20 > (NON-UNIQUE) (Cost=3D3D4 > Card=3D3D110 Bytes=3D3D3080) >=20 >=20 >=20 >=20 >=20 > Statistics > ---------------------------------------------------------- > 0 recursive calls > 0 db block gets > 67 consistent gets > 0 physical reads > 0 redo size > 562 bytes sent via SQL*Net to client > 652 bytes received via SQL*Net from client > 2 SQL*Net roundtrips to/from client > 0 sorts (memory) > 0 sorts (disk) > 1 rows processed >=20 > Now, that's a bit odd looking plan, but, look at those=20 > consistent gets! > Down to 67 from 1321! >=20 > So, I guess what I'm wondering is why this re-write is so effective? > I'm happy that I found a solution, but, I'm curious what's going on > here. Intuitively, I'm thinking that all I'm doing with the=20 > re-write is > forcing the optimizer to do what it seems to claim to be already doing > with the original version . With the scalar subquery, it seems the > nested loop join is implicit in the select statement. >=20 > Well, anyhow, I thought this was pretty odd. >=20 > Thoughts, anyone? >=20 > -Mark > -- > Mark J. Bobak > Oracle DBA > ProQuest Company > Ann Arbor, MI > "On two occasions, I have been asked [by members of=20 > Parliament], "Pray, > Mr. Babbage, if you put into the machine wrong figures, will the right > answers come out?' I am not able to rightly apprehend the kind of > confusion of ideas that could provoke such a question." > -- Charles Babbage (1791-1871) >=20 > -- > //www.freelists.org/webpage/oracle-l >=20 -- //www.freelists.org/webpage/oracle-l