Optimizer_mode
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Optimizer_mode

  1. #1
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    What's the suggested/recommended optimizer_mode for DW/DSS databases??

  2. #2
    Join Date
    Feb 2001
    Posts
    389
    ALL_ROWS

  3. #3
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    Isn't this used for materialized views??

  4. #4
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    FIRST_ROWS or CHOOSE would be your best choice.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  5. #5
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    Sam
    -
    I believe Choose can only be used if tables are analyzed regularly. What does First_rows signify?? Thanx.

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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.

  7. #7
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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
    Sam



    Life is a journey, not a destination!


  8. #8
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    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.

  9. #9
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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
    Sam



    Life is a journey, not a destination!


  10. #10
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759

    Talking

    Thanx all.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width