Oracle Scratchpad

April 10, 2017

Ask Jonathan

Filed under: Uncategorized — Jonathan Lewis @ 8:01 pm GMT Apr 10,2017

Oracle Scene, the magazine of the UK Oracle User Group is piloting a new regular feature called Ask Jonathan,  a chance to get an answer to any question you may have about how the Oracle database engine works.

I’m aiming to answer two or three questions per issue over the course of the year, using a format similar to the one Tom Kyte used in Oracle Magazine:  so if you have a question about the mechanisms, strategies, or mathematics of how Oracle does its thing then attach it as a comment to this posting.

Ideally the questions will have to be quite short (no 20MB trace files, massive schema definitions, or convoluted and exotic setup requirements or it will be too long), and I’ll aim to write something like half a page of in response.  I may summarise the question, or pick out the most interesting feature if it’s a bit too long to publish and answer in its entirety.

 

3 Comments »

  1. Jonathan,

    Thanks for this new section. Here is my question about the optimizer dynamic sampling.

    drop table t purge;
    
    create table t as
    select a.* ,
    	decode( mod(rownum,2),0,'Y','N') flag1,
    	decode( mod(rownum,2),0,'N','Y') flag2
    from all_objects a
    ;
    
    create index t_idx on t(flag1,flag2);
    
    begin
    	dbms_stats.gather_table_stats(
    		user,'T',
    		method_opt=>'for all indexed columns size 254 for columns (flag1,flag2) size 254',
    		cascade =>true);
    end;
    /
    set autotrace on explain
    
    select count(*) from t where flag1='Y';		
    select count(*) from t where flag2='Y';
    
    select count(*) from t where flag1='Y' and flag2='Y';
    
    select /*+ dynamic_sampling(t,4) */ count(*) from t where flag1='Y' and flag2='Y';
    
    set autotrace off
    
    
    demo@ORA12C> select /*+ dynamic_sampling(t,4) */ count(*) from t where flag1='Y' and flag2='Y';
    
      COUNT(*)
    ----------
             0
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 293504097
    
    ---------------------------------------------------------------------------
    | Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |       |     1 |     4 |     1   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE   |       |     1 |     4 |            |          |
    |*  2 |   INDEX RANGE SCAN| T_IDX |     3 |    12 |     1   (0)| 00:00:01 |
    ---------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("FLAG1"='Y' AND "FLAG2"='Y')
    
    Note
    -----
       - dynamic statistics used: dynamic sampling (level=2)
    
    

    I am on 12.2 and the documentation link about Dynamic sampling level is here.
    http://docs.oracle.com/database/122/TGSQL/options-for-optimizer-statistics-gathering.htm#TGSQL451

    Dynamic sampling level=2 states this
    “Use dynamic statistics if at least one table in the statement has no statistics. This is the default value”

    since we have the sufficient statistics (basic column level statistics, histograms on the indexed column, extended statistics, histograms on the extended stats)
    why does the optimizer still need the dynamic_sampling hint to report the right cardinality. why not make use of the histograms on extended stats to report the right cardinality.

    Comment by Rajeshwaran — April 11, 2017 @ 4:40 am GMT Apr 11,2017 | Reply

  2. Very cool idea Jonathan! Looking forward to read them. I always enjoy the detailed analysis and your reasons. Sometimes I read your recent blog before starting to work on a problem – just as inspiration to approach the problem with a clear and logical mind

    Comment by Christo Kutrovsky — April 11, 2017 @ 4:00 pm GMT Apr 11,2017 | Reply

  3. […] few days ago, fellow OakTable member Jonathan Lewis put a post on his blog: https://jonathanlewis.wordpress.com/2017/04/10/ask-jonathan/ where he is launching a mechanism where you can pose questions to him, and he will select topics of […]

    Pingback by SUM is better than DISTINCT | Learning is not a spectator sport — April 12, 2017 @ 5:23 am GMT Apr 12,2017 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

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

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Powered by WordPress.com.