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

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

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

I will try that (just to gather more experience).

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

Ha! I thought it was the other way around! The bad thing about hints is, if they are wrong, then they become a comment and you never get an error message...

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

That's why I set OPTIMIZER_INDEX_COST_ADJ=10 low enough to compensate.

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

That was the point when I myself noticed the same thing. (At some point in the past I started the system stats gathering, but never said STOP - my fault. I just assumed it was OK. Time constraints prevent one from doing the job thoroughly...).

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

I think that I got the point: Oracle expects to get about 10 000 rows, but I know the query returns only one, so a histogram should help. That is exactly the situation where I can take advantage of my DBA role to help my colleague - without him having to understand anything.

I was not really aware about numbers in the execution plan: They are ASSUMPTIONS (contrary to the numbers that "set autotrace on statistics" delivers).

Thanks for your valuable contribution!

Bye
Rick Denoire Received on Sun Jun 20 2004 - 15:16:13 CDT