RE: 10g, swapping stored outlines & _optimizer_ignore_hints

From: Christian Antognini <Christian.Antognini_at_trivadis.com>
Date: Sun, 24 Jun 2007 09:52:43 +0200
Message-ID: <F2C9CCA71510B442AF71446CAE8AEBAFBBD632@MSXVS04.trivadis.com>

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

Received on Sun Jun 24 2007 - 02:52:43 CDT