Another great undocumented Oracle hint

Our master was in Istanbul for a two day seminar and he advised us this hint, please check out this clever trick :)

set linesize 121
set timing on

drop table t1 purge ;
create table t1 nologging as select a.* from all_source a union
select b.* from all_source b union select c.* from all_source c ;
create index nui_t1 on t1(owner) nologging ;
exec dbms_stats.gather_table_stats(USER, TABNAME=>'T1', CASCADE=>TRUE);

-- this is not a caching trick, so you may want to flush the data buffers between two
2  FROM t1 WHERE owner = 'SYS' ;


Elapsed: 00:00:04.11

-- only suggested with local XE test database
-- alter system flush buffer_cache ;

SQL> SELECT /*+ go_faster */ count(DISTINCT line)
2  FROM t1 WHERE owner = 'SYS' ;


Elapsed: 00:00:00.31

Oh come on, we all know that there is no FAST=TRUE type of parameters in real life so here is what is going on behind;

Code listing 68 : Outline Example

I had great time during this two day seminar of Mr.Jonathan Lewis, we all know about his knowledge but he is also a great teacher. Here are some highlights from my notes;

– the cpu cost improvements in the algorithm of Oracle’s on in-memory sort operations,
– alter session set “_optimizer_ignore_hints”=TRUE;
– select /*+ opt_param(‘parallel_execution_enabled’, ‘false’) */ ..
– materialize hint creates a global temporary table, efficient to use with WITH clause
– plan table is a global temporary table after 10g, which is very cool indeed
– dump all event 10132 traces of your queries before a migration and use them as a library to check for the problems that may occur after migration
– “there is no complicated execution plans, there are just long ones” so divide them into pieces
– be careful with “row source” and “execution plan” in tkprof analysis, execution plans with row source headings are the ones which really happened to be in the trace file
– push_subq hint to force to push the subquery first during execution
– no_unnest hint with correlated subquery problems
– swap_join_input(tab) hint to swap the order of the tables going through the join
– if you use high freelists(also ASSM) for insert performance know that your clustering factor will get damage(like reverse index case, range scans may have problems with using indexes)
– alter table fk_table disable table lock; command will produce an error for a locking need dml on child, there will not be any lock problem even if the fkey column is not indexed
– do not use and teach update table set row… feature since this way you may be doing unnecessary updates
– after 9iR2 select for update lock mode increased to 3 from 2
– do not believe in myths and rebuild indexes, check coalesce option for old deleted values if really needed
– if you really know what the statistics will be dont collect them, just go and set them with dbms_stats.set_.. functions. statistics gathering is something very resource consuming, so you may also go to your pre-production system and collect statistics to see what may be the statistics to set them
– dynamic_sampling hint is good for bis systems, parse time is discard able compared to execution time

and these ones are from last week;

– rac option adds %50 cost to license with enterprise edition where as after 10gR1 up to 4 cpu with ASM you don’t pay any with standard edition. still additional redundant hardware resource is needed.
– standby database doubles the licence since it must be also fully licensed,
– development environment is free for rac if you are some how able to develop on just one server and one developer :)

Thank you Julian for these RAC configuration hints :)

Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release

Refences Used :
Using Plan Stability to Preserve Execution Plans
Using Optimizer Hints
Oracle’s corporate pricing lists


  1. coskan says:

    i am shocked till the line “Oh come on, we all know that there is no FAST=TRUE type of parameters in real life so here is what is going on behind;”

    Tonguc thank you very much for sharing your highlights from the seminar of Mr Lewis now i am much more sad about what i missed :)

  2. Coskan this was something special really, only the highlights I could mention here, I am sorry for you really.

    But two things I can say; I talked with Oracle Turkey education manager I hope he will be back soon with this one, and you can contact me for a copy of my notes if you want :)

    He is a very special expert, as he knows the internals so well and lots of great experience he has a great talent in teaching these, I may say without doubt this seminar was the best thing happened to me in my entire Oracle career.

  3. dac says:


    Swung by looking for “_optimizer_ignore_hints”…

    Had to comment on RAC Costs…
    If you use Oracle Enterprise Linux, then the Clusterware Licensing cost is waived – that’s right, FREE. You only pay for Support!

    Nice Blog Post…

Leave a Comment

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s