Optimizer_mode

What's the suggested/recommended optimizer_mode for DW/DSS databases??

ALL_ROWS

Isn't this used for materialized views??

FIRST_ROWS or CHOOSE would be your best choice.

Sam

Sam
-
I believe Choose can only be used if tables are analyzed regularly. What does First_rows signify?? Thanx.

FIRST_ROWS is used when the application uses Forms for UI. The advantage is that if a query is going to return 1000 rows to the Forms, then first it will return the 1st row to the UI, and process the remaining rows from the buffer, and sends the output to the UI. The idea is end user will not feel that UI takes a long time to get his result.

For DSS, always use CHOOSE. But I would recommend to use HINT in the SQL statement, because developer knows exaclty how many rows are going to be processed in a SQL statement.

It optimizes for the best time response to execute the FIRST rows. Statistics do not have to be available for any table involved in the SQL statment: their statistics are estimated by the optimizer. Other access path hints can always be used with this to over ride this.

This will always choose an index over a full table scan.
Would use nested loop joins over sort/merge joints where possible.
Uses an index to satisfy an ORDER BY clause where possible.
The optimizer would ignore this hit for DELETE and UPDATE and any select statement block that contains a grouping operation or FOR UPDATE clause, since all rows must be accessed by the statement before first row can be returned.

Sam

Thanx for the explanation Tamil. What would you recommend for a long running query for Crystal Reports for a DW/DSS type database?? Thanx again.

Since I don't know whether the Crystal Rep, uses any hints or allows you to add any hints, I would suggest that you first go for the CHOOSE and take some bench marks on the performance and then switch to FIRST_ROWS and take some bench marks and then you might be able to come to a conclusion what to take. As Tamil suggested, it would be good to have CHOOSE and oracle's fresh installs makes this as its default choice, for its optimization parameter.

Good luck
Sam

Thanx all.

For a DSS/DW application, it is better to use CHOOSE. Also, I found HASH JOIN is far better than Nested Loop. If the system has a lot of memory, enable HASH JOIN and allocate atleast 100 MB for HASH AREA SIZE. Also tune the SQL to use HASH join.

For a Crystal Report, CHOOSE is the best option followed analyzing the schema.