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.
Oracle Scratchpad 
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 BST Apr 11,2017 |
I know it’s a long time since this question appeared – and I hope I emailed an answer at the time.
The exposition of the question is a little long to be published as an “Ask Jonathan” question, and the (probable answer is a little short). I think this is just a bug with the report from explain plan (which is all that autotrace is doing) – if the sampling takes place it will be at level 4, not 2 (possibly Oracle is reporting the sampling level it used for the GTT plan_table$).
Given that this is Oracle 12.2 the issue is confused by the fact that sampling from the first execution of the query may be kept in the result cache and re-used for the second version of the query – and explain plan might not have all the code to report correctly on what is going on.
The important point is that while explain plan (autotrace) may lie the query does the standard optimizer calculations with the extended stats when it actually runs.
Comment by Jonathan Lewis — July 7, 2018 @ 1:34 pm BST Jul 7,2018 |
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 BST Apr 11,2017 |
[…] 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 BST Apr 12,2017 |