tag:blogger.com,1999:blog-5124641802818980374.post5129647720367259869..comments2023-09-06T11:58:30.752+02:00Comments on Oracle related stuff: AllThingsOracle.com - Dynamic Sampling (III) Part IUnknownnoreply@blogger.comBlogger2125tag:blogger.com,1999:blog-5124641802818980374.post-22154342286455016042012-06-27T12:05:30.607+02:002012-06-27T12:05:30.607+02:00Raheel,
a few comments:
1. The UPDATE statement ...Raheel,<br /><br />a few comments:<br /><br />1. The UPDATE statement as posted by you doesn't seem to have a WHERE clause, so all rows will be updated.<br /><br />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.<br /><br />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<br /><br />3. Please notice that updating all rows via an UPDATE is potentially an expensive operation<br /><br />4. Dynamic Sampling doesn't solve the problem of highly volatile data in all cases. See <a href="http://oracle-randolf.blogspot.de/2011/10/volatile-data-dynamic-sampling-and.html" rel="nofollow">this post</a> for more details.<br /><br />RandolfRandolfhttps://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-5124641802818980374.post-46452332704594352032012-06-26T22:15:14.323+02:002012-06-26T22:15:14.323+02:00Everytime i take something from your posts :) Than...Everytime i take something from your posts :) Thanks for the post.<br /><br />One 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.<br /><br />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.<br /><br />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 ...<br /><br />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 ...<br /><br />Regards<br />Raheel SyedAnonymousnoreply@blogger.com