Re: Performance Problems with Ora 7.33

Kevin Brand wrote in message <71sqmo$fm3$1_at_news.gte.com>...
>
>Also, setting OPTIMIZER_GOAL=FIRST_ROWS in your session for testing and
>finally instance wide in the init file with OPTIMIZER_MODE=FIRST_ROWS, will
>minimize the use of HASH joins, which will minimize the potential of CBO
>choosing full table scans in error.

Seems like I'm a lone voice in the dark here. A FTS is -not- an error. There -are- situations where a FTS is the -only- way to process data effectively. DO NOT DISABLE AND DISREGARD IT.

I want to see any of you guys running a SQL statement to process 50 million rows from a 200 million row table and not using FTS. :-)

The CBO is only as good as the information you supply it ito of statistics. Sure, the CBO gets it knickers in a knot sometimes, but the majority of the time it is due to incorrect or no statistics being available.

These "solutions" being presented are work-arounds to get the CBO to do what you would like it to do. Work arounds are not solutions. And they're likely to bite you in the butt when you least expect it. Rather use the CBO as it is intended to be used, and in those cases where you have no choice (like we had because our analyse stat samples were too small as we had VLTs), you add hints to the SQL statement for the CBO.

Very few databases are static. Last year a single process doing an index range scan worked in seconds. This year the volume of data is simple too large for a single process to handle, which means parallelising the process and doing FTS instead. Disabling CBO features in the database config and you are quite likely going to spend a lot of time tracing this performance problem and running around in circles.

IMHO anyway...

regards,
Billy Received on Thu Nov 05 1998 - 23:40:13 CST