RE: 10g, swapping stored outlines & _optimizer_ignore_hints
- From: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
- To: <Brandon.Allen@xxxxxxxxxxx>
- Date: Sun, 24 Jun 2007 09:52:43 +0200
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
