Were all your table analyzed? CBO relies on stats to estimate cost. If the
stats are wrong ...
"BG" <crockydile_at_hotmail.com> wrote in message
news:c1e19cc6.0203261417.ec8d5de_at_posting.google.com...
> Hello.
>
> I am under the impression that a lower cost is better than a higher
> cost.
>
> I have never tuned before but had to do to a query taking several
> minutes to complete (about 150,000 rows). When I did not give a hint
> the query was doing a full table scan and its cost was 2892. After I
> gave it a hint its cost went up to 44141 but it is now lightning fast.
>
> I notice that with the hint that disk IO is significantly less and
> this probably accounts for the speed, but why did the cost go up so
> much??
>
> Thanks
> Brian
>
>
>
>
>
>
> SELECT
> --SELECT /*+ INDEX(messages dt_recvd, MESSAGES_IND4) */
> msgseqnum,
> msgtype,
> cust_id,
> msgid,
> LOWER (sender),
> LOWER (receiver),
> NVL (SUBSTR (subject, 1, 25), '<>'),
> NVL (status, '<>'),
> TO_CHAR (dt_recvd, 'Mon DD, YYYY HH24:MI A.M.')
> FROM messages
> WHERE (dt_recvd between TO_DATE ('Mar 26, 2000', 'Mon dd, yyyy')
> AND TO_DATE ('Mar 26, 2002', 'Mon dd, yyyy') + 1)
> AND msgtype = UPPER ('new')
> AND cust_id <> '_none_'
>
>
>
> *(Results without giving an index hint)*
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2892 Card=33934 Byte
> s=4818628)
>
> 1 0 TABLE ACCESS (FULL) OF 'MESSAGES' (Cost=2892 Card=33934 By
> tes=4818628)
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 4 db block gets
> 186388 consistent gets
> 183632 physical reads
> 0 redo size
> 2077 bytes sent via SQL*Net to client
> 317 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 14 rows processed
>
>
> (Results with an index hint)
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=44141 Card=33934 Byt
> es=4818628)
>
> 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'MESSAGES' (Cost=44141 Ca
> rd=33934 Bytes=4818628)
>
> 2 1 INDEX (RANGE SCAN) OF 'MESSAGES_IND4' (NON-UNIQUE) (Cost
> =361 Card=33934)
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 12049 consistent gets
> 11710 physical reads
> 0 redo size
> 2100 bytes sent via SQL*Net to client
> 317 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 14 rows processed
Received on Thu Mar 28 2002 - 09:33:58 CST
