Optimzer first_rows or all_rows?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Optimzer first_rows or all_rows?

  1. #1
    Join Date
    Jul 2000
    Location
    Pune, India
    Posts
    80

    Optimzer first_rows or all_rows?

    Hello,

    On Oracle 10g we set optimizer mode=first_rows and run following query, it takes
    209 seconds, plan shows that query used index on column where as
    if we set optimizer mode=all_rows then same query execute in milli seconds and
    uses index on column . This is what is expected.

    Many of our OLTP application queries may affect with performance when optimizer
    mode=first_rows. What shall I do to avoid this. If I remove ORDER BY clause then
    it works properly.

    All details are given below.

    SELECT ParentOE
    FROM TblMovement
    WHERE ParentOE IS NOT NULL AND Fallid = :1
    AND MovementsDate <= :2
    ORDER BY MovementsDate Desc

    First Rows:
    -------------

    call count cpu elapsed disk query current rows
    ------- ------ ------ ------- ----- ----- ----- -----
    Parse 1 0.00 0.00 0 0 0 0
    Execute 1 0.00 0.00 0 0 0 0
    Fetch 1 8.48 209.62 48494 1002335 0 2
    ------- ---- ----- ------- ------ -------- ---- -----
    total 3 8.48 209.63 48494 1002335 0 2

    Misses in library cache during parse: 1
    Misses in library cache during execute: 1
    Optimizer mode: FIRST_ROWS
    Parsing user id: 52

    Rows Row Source Operation
    ------- ---------------------------------------------------
    2 TABLE ACCESS BY INDEX ROWID TBLMOVEMENT (cr=1002335 pr=48494 pw=0 time=106281
    us)
    1720211 INDEX RANGE SCAN DESCENDING XIE2TBLMOVEMENT (cr=6419 pr=6415 pw=0
    time=37844652 us)(object id 48934)


    SELECT STATEMENT Optimizer Mode=FIRST_ROWS 1 13165
    TABLE ACCESS BY INDEX ROWID SYSADM.TBLMOVEMENT 1 14 13165
    INDEX RANGE SCAN DESCENDING SYSADM.XIE2TBLMOVEMENT 22 K 87

    -----------------------------------------------------------
    ALL_ROWS:
    ----------

    SELECT STATEMENT Optimizer Mode=ALL_ROWS 1 6
    SORT ORDER BY 1 14 6
    TABLE ACCESS BY INDEX ROWID SYSADM.TBLMOVEMENT 1 14 5
    INDEX RANGE SCAN SYSADM.XIE1TBLMOVEMENT 2 3


    Since we have migrated from 8i to 10g, currently issuing following statement
    solves purpose and query returns result in milli seconds and uses required
    index.

    alter session set OPTIMIZER_FEATURES_ENABLE='8.1.7'

    But is this recommanaded? will this have any effects? we may have to check. By
    seeting 8.1.7 we will miss enahancement in optimizer for 10g. We also checked
    v$parameter when we set this value optimizeer_mode is set to CHOOSE which was
    our 8i optimizer.

    Could you please help us.

    Thanks & Regards,

    Shailesh

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    If you have an ORDER BY on the query result then unless Oracle can use an index to return the rows in the required order it has to identify and sort the entire result set before it returns the first row. Hence for a query with an Order By, the optimizer setting is not likely to make a difference.
    Is this what you were asking about?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Just for one query you don't need to change optimizer_mode.

    Use plan stability for this query alone so that the execution plan will not be changed.

    Tamil

  4. #4
    Join Date
    Jul 2000
    Location
    Pune, India
    Posts
    80
    Thanks Tamil for your suggestion.

    Those who have migrated from 8i to 9i, many of them have faced performance issues and I have read that using plan stability was not enough, even you need to issue following.

    alter session set OPTIMIZER_FEATURES_ENABLE='8.1.7'

    But we have migrated from 8i to 10g, so let see.

    Thanks & Regards,

    Shailesh

  5. #5
    Join Date
    Jun 2005
    Location
    India
    Posts
    14
    There are lot of enhancements in CBO by oracle.If u keep optimizer_feature_enabled to old version you will loss all enhancements made by oracle.On the other hand initially some query may suffer performance if you make optimizer_fuarure to release10.You need to understand CBO in detail and collect the statistics before doing so.After collecting statistics you should change optimizer_mode to all_rows as RBO is out of date..After that is you are satisfied with performance of all queries you can think about plan stability..

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