Re: Cost is higher but query is much faster

Alan wrote:
>
> Also, remember that the best CBO query will never perform any faster than an
> optimally constructed RBO query.
>
> "Ed prochak" <ed.prochak_at_alltel.com> wrote in message
> news:e51b160.0203271037.2bfeed19_at_posting.google.com...
> > While there is great technology in the ORACLE Optimizer, it still
> > basically is making a guess. It views the query with the hint as
> > costing more because it expects to do more I/O (read the index, then
> > read the table). It is not clear to the Optimizer that the index will
> > help as much as it does IN THIS CASE.
> >
> > A different date value might result in the hint version taking longer.
> > The Optimizer tries to do the best for all cases.
> >
> > just my 2cents
> > Ed
> >
> >
> > crockydile_at_hotmail.com (BG) 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

Would you care to justify that remark?

I'm dying to see how the RBO is going to handle a function based index..

Cheers
Connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."