Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: When the DBA sequels - simple (phfilosophical?) tuning question

Re: When the DBA sequels - simple (phfilosophical?) tuning question

From: QINYI <qinyi_at_itpub.net>
Date: 20 Jun 2004 22:16:30 -0700
Message-ID: <653582c7.0406202116.3c9709e6@posting.google.com>


Rick Denoire <100.17706_at_germanynet.de> wrote in message news:<cni8d09rjf9v6bl31ajflnrvfqjjl223dv_at_4ax.com>...
> Hello
>
> I (the DBA) was asked by a colleague (developer) why this so simple
> query would run 29 sec when all involved columns are indexed and only
> one row is returned (V. 9.2.0.4EE/Solaris7):
>
> SELECT * FROM quot a, quot_user au
> WHERE a.quot_id = au.quot_id
> AND flow_node_id = 711;
>
> Both tables quot and quot_user are "large" (8 Mio. rows). The fields
> quod_id used for the join of both tables are primary keys.
> flow_node_id of quot is a foreign key to the primary key of the table
> flow_node, which is small, but needs not to be referenced.
>
> Taking a look at the execution plan, it turns out that the CBO decides
> to make a FTS on the table quot_user:
>
> SELECT STATEMENT, GOAL = CHOOSE Cost=494
> HASH JOIN Cost=494
> TABLE ACCESS BY INDEX ROWID quot Cost=188
> INDEX RANGE SCAN fk_quot_flow_node Cost=30
> TABLE ACCESS FULL quot_user Cost=294
>
> When comparing the quot_id values to do the join, the CBO determines
> the quod_id on the left side right away, but then compares it with
> EVERY value of quot_user.quot_id (full scan) - bad. Don't understand
> why. If the value quot.quot_id is known (due to the condition
> flow_node_id=711), and since quot_user.quot_id is indexed (primary
> key), then it should be fetched directly.
>
> So I thought that putting an appropriate hint would solve the problem.
> To my surprise, all hints I tried had no effect at all, something
> like: (and many other combinations of diff. hints)
> SELECT /*+ INDEX (quot_user pk_quot_user) */ *

try SELECT /*+ INDEX (au pk_quot_user) */ *

lucky!

                                ---qinyi
Received on Mon Jun 21 2004 - 00:16:30 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US