Skip to Main Content

Breadcrumb

Warning

Before you submit your comment...

Remember - this is about feedback to the original question. AskTOM is about building a strong Oracle community, not stealing time away from others needing assistance by sneaking in a brand new question under the guise of "Comment".

If your comment requires a response that might include a query, please supply very simple CREATE TABLE and INSERT statements. Nice simple scripts so we can replicate your issue simply.

Remember to take a look at Legal Notices and Terms of Use before supplying a comment...

Don't forget, your comments are public. If you want to send a comment to just the AskTOM team, please use the feedback option

Comment

Highlight any SQL, PL/SQL, or fixed-width text and click the <code> button
 (will never be published or used to spam you)

Question and Answer

Connor McDonald

Thanks for the question, Navinth.

Asked: April 19, 2017 - 11:44 am UTC

Last updated: March 31, 2020 - 6:04 am UTC

Version: Oracle 12.1.0.2 Standard Edition

Viewed 10K+ times! This question is

You Asked

It is clear that dynamic sampling queries run by the optimizer contains /* DS_SVC */ clause in them (when traced).
e.g.
SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring
optimizer_features_enable(default) no_parallel result_cache(snapshot=3600)
*/ SUM(C1)
FROM
(SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "TEST_TAB") */ 1 AS
C1 FROM IFSAPP."TEST_TAB" SAMPLE BLOCK(0.51398, 8) SEED(1)
"TEST_TAB" WHERE ("TEST_TAB"."ATTRIBUTE"='Client') AND
("TEST_TAB"."MAIN_TYPE"='LU') AND ("TEST_TAB"."PATH" LIKE
'Navigate.%.FULL_SCHEDULE_DONE%')) innerQuery


But I noted that there are a set of queries which contains clause /* OPT_DYN_SAMP */ in them.
e.g.
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("A") FULL("A") NO_PARALLEL_INDEX("A") */ 1 AS C1, CASE WHEN "A"."BUFFER"=:B1 THEN 1 ELSE 0 END AS C2 FROM "IFSAPP"."PLSQLAP_BUFFER_TMP" "A") SAMPLESUB

What is the purpose of this type of queries with /* OPT_DYN_SAMP */. Is there something to do with Dynamic Sampling?

Is there any proper way to clearly identify all types of dynamic sampling queries done by the optimizer in Oracle 12c? Couldn't find a clear document explaining this either in Oracle support or outside on this.

and we said...

Hi Navinth,

Most of the internal SQL generated by the optimizer team contains comments to make it easier for them to identify these statements in the Shared Pool and to calculate what additional overhead they add or system resources they consume.

As you have correctly identify, SQL statements with the comments DS_SVC and OPT_DYN_SAMP are internal SQL statements that are generated when Dynamic Sampling kicks in.

These are the only two comments we use to identify internals SQL statements for Dynamic Sampling.

So, why do we use two different comments?

OPT_DYN_SAMP represent dynamic sampling statements that are triggered when regular statistics are not sufficient to get good quality cardinality estimates or when statistics are missing altogether. More information on this type of dynamic sampling is available on the Optimizer Blog https://blogs.oracle.com/optimizer/entry/dynamic_sampling_and_its_impact_on_the_optimizer

DS_SVC represent statements that are triggered by dynamic statistics in Oracle Database 11gR2 or 12c. More information on dynamic statistics are available in the Database SQL Tuning Guide https://docs.oracle.com/database/121/TGSQL/tgsql_astat.htm#TGSQL450

Rating

  (9 ratings)

Is this answer out of date? If it is, please let us know via a Comment