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_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Bobak, Mark
> Sent: Wednesday, October 27, 2004 4:41 PM
> To: oracle-l_at_freelists.org
> 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
> --
> http://www.freelists.org/webpage/oracle-l
>=20
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Oct 27 2004 - 15:52:37 CDT
