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

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. Perhaps you meant cardinality, not size. 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. 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).

>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. 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? Is there a "typical" value for a FTS per Million rows, each row 100 bytes in size? Difficult to tell anyway, since blocks might be used to a different level, so the average block capacity should be taken into consideration too.

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

I tried that also. As I said, I tried lots of hints. It changed nothing.

Bye
Rick Denoire Received on Sun Jun 20 2004 - 06:15:34 CDT