Just a short note that the third installment's first part of the series on Dynamic Sampling has been published on AllThingsOracle.com.
You
can read there about how Dynamic Sampling deals with real-life data patterns, what about rare values and how indexes might help in such cases.
Subscribe to:
Post Comments (Atom)
Everytime i take something from your posts :) Thanks for the post.
ReplyDeleteOne of the option out of 2 to gather stats on global temporary tables is use of dynamic sampling. The other one is gather stats and lock the stats, which we normally do not follow in our environment.
Recently i came across one update statement as mentioned below, please suggest which one of the below 2 queries are acurate in terms of dynamic sampling for global temp table.
1) update /*+ dynamic_sampling (t1 10)*/ global_temp_t1 t1 set col1,col2 = select ... from global_temp_t1 tt1 inner join global_temp_t2 t2 ...
2) update global_temp_t1 t1 set col1,col2 = select /*+ dynamic_sampling (tt1 10)*/ /*+ dynamic_sampling (t2 10)*/ ... from global_temp_t1 tt1 inner join global_temp_t2 t2 ...
Regards
Raheel Syed
Raheel,
ReplyDeletea few comments:
1. The UPDATE statement as posted by you doesn't seem to have a WHERE clause, so all rows will be updated.
In that case it is probably not that relevant to the optimizer how many rows are to be updated, hence the first variant probably doesn't help a lot.
2. The second variant performs dynamic sampling on the scalar subquery that is supposed to return the new values for COL1 and COL2. That certainly makes more sense since you seem to join two tables and hence more options to the optimizer might be available
3. Please notice that updating all rows via an UPDATE is potentially an expensive operation
4. Dynamic Sampling doesn't solve the problem of highly volatile data in all cases. See this post for more details.
Randolf