That crazy CBO.....
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 27 Oct 2004 16:40:34 -0400
Ok, I'm a bit at a loss to explain this.....
I have two tables, ADDS_USERS, which has AUSR_ID as the primary key, and
AUTHORIZED_IP_ADDRESSES.
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 =3D B.AUSR_ID AND :B1 BETWEEN B.AIA_IP_ADDRESS_START AND
B.AIA_IP_ADDRESS_END;
and when run w/ autotrace set to traceonly, produces the following
output:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D13 Card=3D110 =
Bytes=3D46
20)
1 0 NESTED LOOPS (Cost=3D13 Card=3D110 Bytes=3D4620)
2 1 INDEX (RANGE SCAN) OF 'AIA_INDX_PR01' (NON-UNIQUE) (Cost
=3D4 Card=3D110 Bytes=3D3080)
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'ADDS_USERS' (Cost=3D2 Ca
rd=3D1 Bytes=3D14)
4 3 INDEX (UNIQUE SCAN) OF 'AUSR_PK' (UNIQUE)
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
Now, that plan looks quite reasonable, but, 1321 buffer gets is too
many, not to mention the physical I/O. This is a very frequently called
SQL statement.
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.
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 =3D b.ausr_id) from AUTHORIZED_IP_ADDRESSES B
WHERE :B1 BETWEEN B.AIA_IP_ADDRESS_START AND B.AIA_IP_ADDRESS_END;
which produces an output like:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D2 Card=3D110 =
Bytes=3D308
0)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'ADDS_USERS' (Cost=3D2 Card
=3D1 Bytes=3D14)
2 1 INDEX (UNIQUE SCAN) OF 'AUSR_PK' (UNIQUE) (Cost=3D1 =
Card=3D1
)
3 0 INDEX (RANGE SCAN) OF 'AIA_INDX_PR01' (NON-UNIQUE) (Cost=3D4
Card=3D110 Bytes=3D3080)
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
Now, that's a bit odd looking plan, but, look at those consistent gets!
Down to 67 from 1321!
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 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.
Well, anyhow, I thought this was pretty odd.
Thoughts, anyone?
-Mark
--
Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
"On two occasions, I have been asked [by members of 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)
--
//www.freelists.org/webpage/oracle-l
Other related posts:
