Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

How to upgrade Dynamic Sampling on a Query already using an imported SQL Profile

with 3 comments


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“?


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.

q'[OPT_PARAM('optimizer_dynamic_sampling' 6)]',




Written by Carlos Sierra

May 21, 2014 at 7:24 am

3 Responses

Subscribe to comments with RSS.

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

Leave a Reply

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

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

Connecting to %s

%d bloggers like this: