Re: Cost is higher but query is much faster

As a general rule lower is better. But as you have observed there is no substitute for verifying.

Though depending on what that hint is (you don't say) the total query might be taking longer but you might be getting back an initial record set immediately giving the impression of greater speed. Did you use the FIRST_ROWS hint?

I would be very interested in receiving a copy of the query if you wouldn't mind posting it as it is hard to find such dramatic examples for my classes.

Thanks,

Daniel Morgan

BG wrote:

> 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 Wed Mar 27 2002 - 10:06:03 CST