SQL Performance (MOSC)

MOSC Banner

Dynamic Statistics in 12.1.0.2 used on small table with good stats

edited Aug 22, 2016 2:50PM in SQL Performance (MOSC) 19 commentsAnswered ✓

Oracle RDBMS 12.1.0.2

While reviewing a session trace I see evidence of dynamic statistics work going on.

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( "GDB_ITEMS")  */

              1 AS C1

          FROM "SDEREG"."GDB_ITEMS" "GDB_ITEMS"

         WHERE ("GDB_ITEMS"."TYPE" = '{F3783E6F-65CA-4514-8315-CE3985DAD3B1}')) innerQuery;


sql tuning advisor and auto space advisor are disabled.

There are good statistics on this table, which is quite small. 

These init params are set:


optimizer_dynamic_sampling                 2

optimizer_features_enable                    12.1.0.2

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center