RE: 10g, swapping stored outlines & _optimizer_ignore_hints

Hi Brandon

> I can't prevent Baan from sending these hints, it's
> hardcoded into their database driver

I won't comment about your problem with outlines. As you know and
noticed they are simply not always reliable. (It's not a coincidence
that Oracle is deprecating them...)

If I where you I would add a SQL profile to change optimizer mode. Here
an example (notice the switch between first_rows_10 and all_rows):

SQL> select /*+ first_rows(10) */ * from sh.customers order by cust_id;

----------------------------------------------------
| Id  | Operation                   | Name         |
----------------------------------------------------
|   0 | SELECT STATEMENT            |              |
|   1 |  TABLE ACCESS BY INDEX ROWID| CUSTOMERS    |
|   2 |   INDEX FULL SCAN           | CUSTOMERS_PK |
----------------------------------------------------

SQL> begin
  2    dbms_sqltune.import_sql_profile(
  3      name     => 'test',
  4      category => 'DEFAULT',
  5      sql_text => 'select /*+ first_rows(10) */ * from sh.customers
order by cust_id',
  6      profile  =>
sqlprof_attr('ALL_ROWS','IGNORE_OPTIM_EMBEDDED_HINTS')
  7    );
  8  end;
  9  /

SQL> select /*+ first_rows(10) */ * from sh.customers order by cust_id;

----------------------------------------
| Id  | Operation          | Name      |
----------------------------------------
|   0 | SELECT STATEMENT   |           |
|   1 |  SORT ORDER BY     |           |
|   2 |   TABLE ACCESS FULL| CUSTOMERS |
----------------------------------------




HTH
Chris
--
http://www.freelists.org/webpage/oracle-l

Other related posts: