Results 1 to 3 of 3

Thread: Optimizer

  1. #1
    Join Date
    Jul 2003

    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.

  2. #2
    Join Date
    Apr 2003
    Greenville, SC (USA)
    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 ...


  3. #3
    Join Date
    Apr 2004
    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