Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

March 1, 2007

Another great undocumented Oracle hint

Filed under: Oracle How To,Oracle Seminars — H.Tonguç Yılmaz @ 9:00 pm

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

About these ads

5 Comments »

  1. :)
    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 :)

    Comment by coskan — March 2, 2007 @ 6:44 am | Reply

  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.

    Comment by H.Tonguç Yılmaz — March 2, 2007 @ 7:38 am | Reply

  3. [...] mentioned this option in my two day seminar notes of Mr.Jonathan Lewis before for another need – “alter table fk_table disable table lock; command will produce an [...]

    Pingback by Preventing DDL operations on a table « H.Tonguç YILMAZ Oracle Blog — September 25, 2007 @ 4:30 pm | Reply

  4. Guys,

    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…

    Comment by dac — September 15, 2010 @ 4:12 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 80 other followers

%d bloggers like this: