How to upgrade Dynamic Sampling on a Query already using an imported SQL Profile
Problem
I have this query that references a couple of Global Temporary Tables (GTT). These GTT have no CBO Statistics, thus Dynamic Sampling (DS) is used on them. The problem is default value of 2 at the instance level samples only 64 blocks and these GTT are large. Testing with DS on larger samples provides better quality on these dynamic statistics and a better performing Execution Plan for this particular SQL.
This SQL already uses an imported SQL Profile generated by coe_xfr_sql_profile.sql out of SQLT (MOS 215187.1) under sqlt/utl directory. This SQL Profile contains CBO Hints created out of the Outline Data contained on the other_xml column of the Plan. Still a higher level of DS is needed for these two GTT in order to produce a better performing Plan. Setting DS at the instance level or session level is not an option. Modifying the query is not an option. So the question is: “how to embed a dynamic sampling hint on a SQL that already has a SQL Profile based on an Outline“?
Solution
On the script generated by coe_xfr_sql_profile.sql add one extra Hint with the DS level that is needed. Look at sample below, where one extra Hint has added right below DB_VERSION. Since the two GTT have no CBO Statistics, they invoke DS, which would use now level 6 instead of 2.
h := SYS.SQLPROF_ATTR( q'[BEGIN_OUTLINE_DATA]', q'[IGNORE_OPTIM_EMBEDDED_HINTS]', q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.3')]', q'[DB_VERSION('11.2.0.3')]', q'[OPT_PARAM('optimizer_dynamic_sampling' 6)]', q'[ALL_ROWS]',
Great post Carlos as I am faced with a similar situation. Since the dynamic sampling can go all the way to 10, what made you decide to use a setting of 6? Thanks.
Jon Adams
May 21, 2014 at 7:44 am
I used 6 for educational purposes. Of course you can use 10. You can also use similar syntax for other Hints. Just keep in mind that OPT_PARAM does not support all Hints.
Carlos Sierra
May 21, 2014 at 8:09 am
Do you have a list of the hints that opt_param does support?
Jon Adams
May 28, 2014 at 7:13 am