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

Rick Denoire <100.17706_at_germanynet.de> wrote:
> 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.

If flow_node is a small table, then the index on quot.flow_node_id must have low average selectivity. So while the query may return only one row, the CBO probably doesn't know that. Maybe histograms would help.

>
> 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

294 sounds like a pretty low cost for FTS of a "large" table. Are stats up to date? Is multiblock read count reasonable?

Also, 30 seems pretty high for an index range scan returning only one row, so the CBO probably thinks it is returning many rows.

> 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) */ *

When I give up on finding underlying problems and resort to hints, the first thing I usually try is a first_rows hint. It requires no deep thinking, and it very often works. Beyond that, I'd try a use_nl hint.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB