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
SQL> SELECT count(DISTINCT line)
2  FROM t1 WHERE owner = 'SYS' ;

COUNT(DISTINCTLINE)
-------------------
6440

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' ;

COUNT(DISTINCTLINE)
-------------------
6440

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 10.2.0.1.0

Refences Used :
http://www.psoug.org/reference/outlines.html
Using Plan Stability to Preserve Execution Plans
Using Optimizer Hints
Oracle’s corporate pricing lists