Dynamic sampling works differently than documented

Dynamic sampling in Oracle versions 12c to 19 is documented as functioning the same if it is enabled via the init parameter or by hinting. This does not seem to be true. I have tested this in customer databases, personal databases, on Linux and windows, and it always results in the same conclusion. When dynamic sampling at level 2 is only enabled via the init parameter, dynamic sampling occurs for objects without statistics, as advertised. When there is a /*+ dynamic_sampling(2) */ hint in the query, you get sampling for functions, multi-column joins and maybe more. Here is an example to demonstrate my point: - Connect to the database and query the sampling queries:

select sql_id, executions, sql_text from v$sql 
  where parsing_schema_name = user and sql_text like 'SAMPLESUB';

- Confirm the init parameter is set to 2 show parameter optimizer_dynamic_sampling; - Put something into the plan table explain plan for select * from user_tables; - Look at the sampling queries again - Query the plan table with no hints select * from table(dbms_xplan.display); - Look at the sampling queries again - Query the plan table with the hint select /*+ dynamic_sampling(2) */ * from table(dbms_xplan.display); - Look at the sampling queries again, you will see more sampling queries after using the hint Explain plans and optimizer traces provide much more concrete data to confirm this.