Results 1 to 3 of 3

Thread: Optimizer

  1. #1
    Join Date
    Jul 2003
    Posts
    70

    Red face Unanswered: Optimizer

    Hi All,
    Thanks your previous responses.
    One more doubt, how can we know which optimizer (CBO or RBO) is being used for any given query. Is there a trace to find it out?

    Thanks in advance.
    qAnand

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    You can always run your queries in SQLPlus with trace turned on and see the method Oracle uses to access the data ...

    -- This will turn on tracing, but will not return you query results, just the execution path ...

    sql> set autotrace trace
    sql> select yourquery ....;

    The execution plan will show you the optimizer method used ... CHOOSE, FIRST_ROW, ALL_ROWS, RULE ...

    HTH
    Gregg

  3. #3
    Join Date
    Apr 2004
    Posts
    246
    Depending on the version of Oracle, you can check V$SQL for the OPTIMIZER_MODE column.

    Also, when checking your SQL with TRACE, make sure that you run it the same way the app does. A stmt in native sql (sqlplus, pro*c) does not necessarily have the same opt mode as the identical stmt run in pl/sql.

Posting Permissions

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