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

Rick,

If you combine all the information from the posts to date:

> AND flow_node_id = 711;

The execution plan in your last post shows that Oracle expects to get 10,306 rows from this predicate, and it's the only predicate it has to drive into a table.

If you hinted Oracle to use a nested loop, ordered, with the index into table quot_user, it is likely that Oracle would cost this at 3 units per row (possibly 2 depending on size of index) for a total cost of about 31,000 (or 21,000).

Your attempt to hint the index fails because you have quoted the table name in the hint - not the table alias as it appears in the query.

Given your other post - you have a multiblock read count of 64 - which means Oracle will consider a tablescan to be very cheap - costing roughly 'blocks in table / 25.9'

Regarding the comment about 9i being able to cater for actual mechanical performance of tablescans - this is only relevant where cpu_costing has been enabled, and your plan from dbms_xplan shows that you are not using cpu_costing.

If

    flow_node_id = 711;
really does return only one row from table quot, then the generic solution is to create a histogram on that column on the table so that Oracle has a chance of spotting the skewed data.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st


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

>

> 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

>