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

Rick Denoire <100.17706_at_germanynet.de> wrote:
> ctcgag_at_hotmail.com wrote:
>
> >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.
>
> Don't how the size of the table relates to the selectivity of the
> index on any of its column.

It doesn't, it relates to the selectivity of the index on a different table's column. Since flow_node is small, it's primary key must have a small number of distinct values. Since quot is large, then its flow_node_id column must have a large number of values, each of which must occur in flow_node because of the foreign key constraint. Therefore, flow_node_id has a large number of values, but a small number of distinct values. That is pretty much what low selectivity means.

> Perhaps you meant cardinality, not size.

Do you want to tune your system, or lawyer it? You are the one who started using the words large and small to refer to the cardinality of your tables, I merely continued along your path.

> Essentially, you are suggesting that the CBO does not recognize the
> advantange of using the index due to the lack of histograms. But *it
> is* using the index on quot.flow_node_id.

No, I'm suggesting that Oracle thinks that it is going to find a lot of rows that meet the requirement on flow_node_id. Regardless of what access path it uses to find these rows.

> The point is that it is not
> using the index to access quot_user.quot_id (which should not need any
> histogram as it is unique).

If you have make a lot of lookups against an index and then follow that to the table, that is a lot of work, whether the index is unique or not. The number of inner look-ups needed depends on the number of things returned by the outer rowset. That's is why overestimating the (hypothetical) outer rowset causes the (hypothetical) inner rowset to change access path.

> >294 sounds like a pretty low cost for FTS of a "large" table. Are
> >stats up to date? Is multiblock read count reasonable?
>
> Needless to say, stats are up to date. DB_FILE_MULTIBLOCK_READ_COUNT
> is set to 64, thus facilitating reading 8x64K from 9 disks of the RAID
> 5 in one strike, which is "reasonable". The kernel parameter MAXPHYS
> is set to 1 M, should not be a limit here.

Does p3 from the trace file "scattered read" show that it is actually reading that amount on FTSs?

> But the CBO does not know
> anything about that, so it does not sell FTS cheaper (although I am
> not sure about this point since Oracle 9 can rate actual operation
> costs to some extent).
>
> >Also, 30 seems pretty high for an index range scan returning only one
> >row, so the CBO probably thinks it is returning many rows.
>
> Is it possible to say anything about abolute values of costs on
> different systems? Can they be compared?

Of course. There are caveats, but there are with everything.

> Is there a "typical" value
> for a FTS per Million rows, each row 100 bytes in size?

I'd say around 2000 would be typical.

> Difficult to
> tell anyway, since blocks might be used to a different level, so the
> average block capacity should be taken into consideration too.

Well, it's not like average percent utilization ordinarily varies over several orders of magnitude.

Xho

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