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

Rick Denoire 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.
>
> 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) */ *
>
> Changing the session parameter OPTIMIZER_INDEX_COST_ADJ in a wide
> range had no effect either. I also tried the Outline Editor of the
> OEM, which does not seem to let you edit anything. After thinking for
> a while, I came up with a solution based on rewriting the query so to
> "force" the CBO to do an index access on quot_user:
>
> SELECT * FROM quot a
> WHERE a.quot_id =
> ( SELECT quot_id FROM quot_user au WHERE au.quot_id =
> ( SELECT quot_id from quot WHERE flow_node = 711
> )
> )
> This query returns exact the one same record and runs 0.02 sec!
>
> The execution plan looks like this:
>
> SELECT STATEMENT, GOAL = CHOOSE Cost=2
> TABLE ACCESS BY INDEX ROWID quot Cost=2
> INDEX UNIQUE SCAN pk_quot Cost=2
> INDEX UNIQUE SCAN pk_quot_user Cost=3
> TABLE ACCESS BY INDEX ROWID quot Cost=188
> INDEX RANGE SCAN fk_quot_flow_node Cost=30
>
> Sorry if this is too trivial for you.
> What is wrong in the "slow" version of the query?
> Why didn't hints affect the execution plan?
> Isn't SQL a declarative language? (I always thought that procedural
> incluences are only necessary in order to overcome implementation
> deficiencies - Well, Oracle seems to be very deficient if this is
> true).
>
> My colleague, who is much more proficient writing SQL than me, but who
> has no clue at all about how Oracle works internally, did not like the
> "fast" version of the query. He usually writes queries one page long
> at least, this one was just a sample - fiddling with the code like
> this is not feasible for him, specially if he does not see the logic
> to head for (perhaps that is why he always complains about performance
> - eh ).
>
> By the way, in this DB there are lots of pairs of tables holding the
> same field as primary key (1:1 relationship). Is there a way to create
> an object coupling both tables - something like an index spanning both
> tables? This way, Oracle would always find corresponding rows without
> FTS. Unfortunately, this is a canned application, schema changes are
> tabu. Changes need to behave neutral to the application logic.
>
> Bye
> Rick Denoire
>

Please post output from...
DESC quot
DESC quot_user

 > only one row is returned
One row from which table?

SELECT *
FROM quot a
WHERE flow_node_id = 711
  AND EXISTS (SELECT '1'

               FROM quot_user au
              WHERE  a.quot_id = au.quot_id
              );

What is returned by above SQL?
What is EXPLAIN_PLAN for above SQL?
How fast to above SQL run? Received on Sat Jun 19 2004 - 10:31:06 CDT