14 Replies Latest reply: Sep 15, 2010 9:34 AM by NM RSS

    optimizer dynamic sampling issues

    573141
      hi gurus,

      emp_cur had 100K rows, i deleted many.
      experimenting the dynamic sampling feature...

      SQL> select * from v$version;
      
      BANNER
      ----------------------------------------------------------------------------
      
      Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
      PL/SQL Release 11.1.0.6.0 - Production
      CORE    11.1.0.6.0      Production
      TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
      NLSRTL Version 11.1.0.6.0 - Production
      SQL> select name,value from v$parameter where name like 'optimizer_dynamic%';
      
      NAME
      -------------------------------------------------------------------------------
      
      VALUE
      -------------------------------------------------------------------------------
      
      optimizer_dynamic_sampling
      2
      
      SQL> select count(*) from emp_par;
      
        COUNT(*)
      ----------
            4999
      
      SQL> set autotrace traceonly explain
      SQL> select * from emp_par;
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 3159588169
      
      -----------------------------------------------------------------------------
      | Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
      -----------------------------------------------------------------------------
      |   0 | SELECT STATEMENT  |         |   100K|   878K|    63   (2)| 00:00:01 |
      |   1 |  TABLE ACCESS FULL| EMP_PAR |   100K|   878K|    63   (2)| 00:00:01 |
      -----------------------------------------------------------------------------
      since dynamic sampling in enabled, i expected the rows value close to 4999.

      i tried to force dynamic sampling with a hint
      SQL> select /*+ dynamic_sampling(t 2) */ * from emp_par t;
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 3159588169
      
      -----------------------------------------------------------------------------
      | Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
      -----------------------------------------------------------------------------
      |   0 | SELECT STATEMENT  |         |   100K|   878K|    63   (2)| 00:00:01 |
      |   1 |  TABLE ACCESS FULL| EMP_PAR |   100K|   878K|    63   (2)| 00:00:01 |
      -----------------------------------------------------------------------------
      though, dynamic sampling is enabled, optimizer plan is still showing the row count as 100K.

      am i missing something?

      thanks,
      charles

      Edited by: user570138 on Feb 23, 2010 9:43 PM
        • 1. Re: optimizer dynamic sampling issues
          561093
          Dynamic Sampling is not kicking in as you have gathered statistics on table "T". Delete the statistics and run your query again.

          You may find this link helpful.

          http://momendba.blogspot.com/2008/11/dynamic-sampling-myths-dispelled.html


          SQL> select num_rows from user_tables where table_name='T';
          
            NUM_ROWS
          ----------
                   3
          
          SQL> set autotrace traceonly exp
          SQL> select /*+ dynamic_sampling(t 2)*/ * from t;
          
          Execution Plan
          ----------------------------------------------------------
          Plan hash value: 1601196873
          
          --------------------------------------------------------------------------
          | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
          --------------------------------------------------------------------------
          |   0 | SELECT STATEMENT  |      |     3 |    18 |     3   (0)| 00:00:01 |
          |   1 |  TABLE ACCESS FULL| T    |     3 |    18 |     3   (0)| 00:00:01 |
          --------------------------------------------------------------------------
          
          SQL> exec dbms_stats.delete_table_stats(user, 't');
          
          PL/SQL procedure successfully completed.
          
          SQL> select /*+ dynamic_sampling(t 2)*/ * from t;
          
          Execution Plan
          ----------------------------------------------------------
          Plan hash value: 1601196873
          
          --------------------------------------------------------------------------
          | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
          --------------------------------------------------------------------------
          |   0 | SELECT STATEMENT  |      |     3 |    75 |     3   (0)| 00:00:01 |
          |   1 |  TABLE ACCESS FULL| T    |     3 |    75 |     3   (0)| 00:00:01 |
          --------------------------------------------------------------------------
          
          Note
          -----
             - dynamic sampling used for this statement
          
          SQL>
          Asif Momen
          http://momendba.blogspot.com

          Edited by: Asif Momen on Feb 24, 2010 9:28 AM
          • 2. Re: optimizer dynamic sampling issues
            Rafi (Oracle DBA)
            Hi Asif,
            I have one question for you?Shall I post it as separate one?Are you active?





            Thanks,
            Rafi.
            • 3. Re: optimizer dynamic sampling issues
              561093
              Rafi,

              If your question is related to this thread, you may post it here or start a new thread.

              If you would like, you may reach me at: asif.momen@gmail.com



              Asif Momen
              http://momendba.blogspot.com
              • 4. Re: optimizer dynamic sampling issues
                Rafi (Oracle DBA)
                Hi Asif,
                I had send a mail to your gmail id as it is not related with this question.Kindly go through it.



                Thanks,
                Rafi.
                • 5. Re: optimizer dynamic sampling issues
                  Jonathan Lewis
                  user570138 wrote:
                  hi gurus,
                  SQL> select /*+ dynamic_sampling(t 2) */ * from emp_par t;
                  
                  Execution Plan
                  ----------------------------------------------------------
                  Plan hash value: 3159588169
                  
                  -----------------------------------------------------------------------------
                  | Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
                  -----------------------------------------------------------------------------
                  |   0 | SELECT STATEMENT  |         |   100K|   878K|    63   (2)| 00:00:01 |
                  |   1 |  TABLE ACCESS FULL| EMP_PAR |   100K|   878K|    63   (2)| 00:00:01 |
                  -----------------------------------------------------------------------------
                  though, dynamic sampling is enabled, optimizer plan is still showing the row count as 100K.
                  Check the 10053 trace - the hint would have made the optimizer take a dynamic sample, but it has then rejected the sample as irrelevant.
                  If you want to change your test, try this:

                  <ul>
                  Create your table with 100,000 rows
                  Create the stats
                  Insert another 10%
                  Do the massive delete
                  </ul>
                  Then see if you get a difference between hinted and unhinted query plans.
                  (NB See also: http://jonathanlewis.wordpress.com/2010/02/23/dynamic-sampling/ )


                  Regards
                  Jonathan Lewis
                  http://jonathanlewis.wordpress.com
                  http://www.jlcomp.demon.co.uk

                  To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
                  {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
                  fixed format
                  .
                  
                  There is a +"Preview"+ tab at the top of the text entry panel. Use this to check what your message will look like before you post the message. If it looks a complete mess you're unlikely to get a response. (Click on the +"Plain text"+ tab if you want to edit the text to tidy it up.)
                  
                  +"I believe in evidence. I believe in observation, measurement, and reasoning, confirmed by independent observers. I'll believe anything, no matter how wild and ridiculous, if there is evidence for it. The wilder and more ridiculous something is, however, the firmer and more solid the evidence will have to be."+
                  Isaac Asimov                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                  • 6. Re: optimizer dynamic sampling issues
                    561093
                    Hi Jonathan,
                    SQL> drop table t purge;
                    
                    Table dropped.
                    
                    SQL> create table t (id number, name varchar2(100));
                    
                    Table created.
                    
                    SQL> insert into t select level, 'name ' || level from dual connect by level <= 100000;
                    
                    100000 rows created.
                    
                    SQL> commit;
                    
                    Commit complete.
                    
                    SQL> exec dbms_stats.gather_table_stats(user, 't');
                    
                    PL/SQL procedure successfully completed.
                    
                    SQL> insert into t select level, 'name ' || level from dual connect by level <= 10000;
                    
                    10000 rows created.
                    
                    SQL> commit;
                    
                    Commit complete.
                    
                    SQL> delete from t where mod(id, 3) in (0,1);
                    
                    73334 rows deleted.
                    
                    SQL> commit;
                    
                    Commit complete.
                    
                    SQL> set autotrace traceonly exp
                    SQL> select /*+ dynamic_sampling(t 2) */ * from t;
                    
                    Execution Plan
                    ----------------------------------------------------------
                    Plan hash value: 1601196873
                    
                    --------------------------------------------------------------------------
                    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
                    --------------------------------------------------------------------------
                    |   0 | SELECT STATEMENT  |      |   100K|  1464K|   105   (3)| 00:00:02 |
                    |   1 |  TABLE ACCESS FULL| T    |   100K|  1464K|   105   (3)| 00:00:02 |
                    --------------------------------------------------------------------------
                    
                    SQL> select  * from t;
                    
                    Execution Plan
                    ----------------------------------------------------------
                    Plan hash value: 1601196873
                    
                    --------------------------------------------------------------------------
                    | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
                    --------------------------------------------------------------------------
                    |   0 | SELECT STATEMENT  |      |   100K|  1464K|   105   (3)| 00:00:02 |
                    |   1 |  TABLE ACCESS FULL| T    |   100K|  1464K|   105   (3)| 00:00:02 |
                    --------------------------------------------------------------------------
                    
                    SQL>
                    Yes, from the 10053 trace it is evident that Dynamic Sampling was rejected by the optimizer.

                    *** 2010-02-24 14:53:32.140
                    ** Performing dynamic sampling initial checks. **
                    ** Dynamic sampling initial checks returning TRUE (level = 2).
                    *** 2010-02-24 14:53:32.140
                    ** Generated dynamic sampling query:
                        query text : 
                    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 /*+ NO_PARALLEL("T") FULL("T") NO_PARALLEL_INDEX("T") */ 1 AS C1, 1 AS C2 FROM "T" SAMPLE BLOCK (17.027027 , 1) SEED (1) "T") SAMPLESUB
                    *** 2010-02-24 14:53:32.140
                    ** Executed dynamic sampling query:
                        level : 2
                        sample pct. : 17.027027
                        actual sample size : 5750
                        filtered sample card. : 5750
                        orig. card. : 100000
                        block cnt. table stat. : 370
                        block cnt. for sampling: 370
                        max. sample block cnt. : 64
                        sample block cnt. : 63
                        min. sel. est. : -1.00000000
                    ** Not using dynamic sampling for single table sel. or cardinality.
                    DS Failed for : Current SQL statement for this session:
                    Asif Momen
                    http://momendba.blogspot.com
                    • 7. Re: optimizer dynamic sampling issues
                      573141
                      hi Asif,

                      thanks for the clarification, but some more doubts.
                      Asif Momen wrote:
                      Dynamic Sampling is not kicking in as you have gathered statistics on table "T". Delete the statistics and run your query again.
                      1. i thought dynamic sampling would be useful in cases where there is obsolete statistics.
                      you mean, dyanmic sampling would work only in situations where there is no statistics(updated or obsolete)?

                      2. in a volatile environment where there are lots of inserts and deletes what would be best stragey as far as optimizer statistics gathering is concerned?


                      thanks,
                      charles

                      Edited by: user570138 on Feb 26, 2010 1:30 AM
                      • 8. Re: optimizer dynamic sampling issues
                        Hemant K Chitale
                        It's still not clear why Oracle rejected the results from dynamic sampling.

                        Hemant K Chitale
                        • 9. Re: optimizer dynamic sampling issues
                          490123
                          If table got statistics, then try to include a predicate (where ...) and see whether dynamic sampling is used.

                          Regards,
                          Vineeth
                          • 10. Re: optimizer dynamic sampling issues
                            573141
                            i checked with a where condition, no.. it is not taking dynamic sampling
                            • 11. Re: optimizer dynamic sampling issues
                              490123
                              Well its worked for me, your table got how many blocks? is it below 32? did you tried the test case above (table t)

                              &
                              whats the output for this

                              show parameter optimizer_features_enable

                              Regards,
                              Vineeth
                              • 12. Re: optimizer dynamic sampling issues
                                NM
                                Hi Asif,

                                Thanks for providing the Good information.

                                I have few Questions

                                In our Environment we have high data changes from the Beginning of the day to EOD.

                                Eg:
                                In the Morning Following tables
                                Tibex_Order=700 records
                                Tibex_Quote=500
                                Tibex_Fixsessionstat=0

                                By Afternoon
                                Tibex_Order=2 Million
                                Tibex_Quote=1 million
                                Tibex_Fixsessionstat=3 million


                                By Eod of Day
                                Tibex_Order=6 Million
                                Tibex_Quote=4 million
                                Tibex_Fixsessionstat=9 million

                                In the EOD process we gather the stats and truncate this tables the above tables that is business process.

                                So my Question is are we doing the right thing by collecting stats and truncating the table.

                                If i use the Dynamic Sampling does it help the Queries to perform better.

                                After reading your article it look like Dynamic Sampling will be helpfull.

                                Some of the Queries takes ages to retrieve.So Kindly suggest me
                                SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jul 15 15:41:41 2010
                                
                                Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
                                
                                
                                Connected to:
                                Oracle Database 10g Release 10.2.0.4.0 - Production
                                
                                sys@MIFEX3> alter system flush shared_pool;
                                
                                System altered.
                                
                                sys@MIFEX3> show parameter opt
                                
                                NAME                                 TYPE                             VALUE
                                ------------------------------------ -------------------------------- ------------------------------
                                filesystemio_options                 string                           asynch
                                object_cache_optimal_size            integer                          102400
                                optimizer_dynamic_sampling           integer                          2
                                optimizer_features_enable            string                           10.2.0.4
                                optimizer_index_caching              integer                          0
                                optimizer_index_cost_adj             integer                          100
                                optimizer_mode                       string                           ALL_ROWS
                                optimizer_secure_view_merging        boolean                          TRUE
                                plsql_optimize_level                 integer                          2
                                Regards
                                Narasimha

                                Edited by: NM on 15-Jul-2010 08:01
                                • 13. Re: optimizer dynamic sampling issues
                                  NM
                                  Hi Asif,

                                  Thanks for providing the Good information.

                                  I have few Questions

                                  In our Environment we have high data changes from the Beginning of the day to EOD.

                                  Eg:
                                  In the Morning Following tables
                                  Tibex_Order=700 records
                                  Tibex_Quote=500
                                  Tibex_Fixsessionstat=0

                                  By Afternoon
                                  Tibex_Order=2 Million
                                  Tibex_Quote=1 million
                                  Tibex_Fixsessionstat=3 million

                                  By Eod of Day
                                  Tibex_Order=6 Million
                                  Tibex_Quote=4 million
                                  Tibex_Fixsessionstat=9 million

                                  In the EOD process we gather the stats and truncate this tables the above tables that is business process.

                                  So my Question is are we doing the right thing by collecting stats and truncating the table.

                                  If i use the Dynamic Sampling does it help the Queries to perform better.

                                  After reading your article it look like Dynamic Sampling will be helpfull.

                                  Some of the Queries takes ages to retrieve.So Kindly suggest me
                                  SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jul 15 15:41:41 2010
                                   
                                  Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
                                   
                                   
                                  Connected to:
                                  Oracle Database 10g Release 10.2.0.4.0 - Production
                                   
                                  sys@MIFEX3> alter system flush shared_pool;
                                   
                                  System altered.
                                   
                                  sys@MIFEX3> show parameter opt
                                   
                                  NAME                                 TYPE                             VALUE
                                  ------------------------------------ -------------------------------- ------------------------------
                                  filesystemio_options                 string                           asynch
                                  object_cache_optimal_size            integer                          102400
                                  optimizer_dynamic_sampling           integer                          2
                                  optimizer_features_enable            string                           10.2.0.4
                                  optimizer_index_caching              integer                          0
                                  optimizer_index_cost_adj             integer                          100
                                  optimizer_mode                       string                           ALL_ROWS
                                  optimizer_secure_view_merging        boolean                          TRUE
                                  plsql_optimize_level                 integer                          2
                                  Regards
                                  Narasimha
                                  • 14. Re: optimizer dynamic sampling issues
                                    NM
                                    Hi Asif,

                                    Dynamic Sampling even wont work if you dont have stats when the Query Uses AS OF SCN.

                                    Below it the test Case 10.2.0.4.
                                    tst_pre_eod@MIFEX3> SELECT ParticipantID, BoardID, InstrumentID, MMListAction FROM
                                             TIBEX_MEMMHybridAdmView as of scn 6148947776 WHERE MEGroupID = 'ME1'
                                             ORDER BY Timestamp ASC  2    3
                                      4  /
                                    
                                    Execution Plan
                                    ----------------------------------------------------------
                                    Plan hash value: 39089053
                                    
                                    -------------------------------------------------------------------------------------------------------------
                                    | Id  | Operation                     | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
                                    -------------------------------------------------------------------------------------------------------------
                                    |   0 | SELECT STATEMENT              |                             |     1 |   159 |    88   (3)| 00:00:02 |
                                    |   1 |  SORT ORDER BY                |                             |     1 |   159 |    88   (3)| 00:00:02 |
                                    |   2 |   NESTED LOOPS                |                             |     1 |   159 |    87   (2)| 00:00:02 |
                                    |*  3 |    HASH JOIN                  |                             |    60 |  3780 |    86   (2)| 00:00:02 |
                                    |*  4 |     TABLE ACCESS FULL         | TIBEX_INSTRUMENT            |    47 |  1598 |    59   (0)| 00:00:01 |
                                    |   5 |     SORT UNIQUE               |                             |  2361 | 68469 |    26   (0)| 00:00:01 |
                                    |*  6 |      INDEX FAST FULL SCAN     | XPKTIBEX_ADMINACK           |  2361 | 68469 |    26   (0)| 00:00:01 |
                                    |*  7 |    TABLE ACCESS BY INDEX ROWID| TIBEX_HYBRIDMMINSTRADMIN    |     1 |    96 |     1   (0)| 00:00:01 |
                                    |*  8 |     INDEX UNIQUE SCAN         | XPKTIBEX_HYBRIDMMINSTRADMIN |     1 |       |     0   (0)| 00:00:01 |
                                    -------------------------------------------------------------------------------------------------------------
                                    
                                    Predicate Information (identified by operation id):
                                    ---------------------------------------------------
                                    
                                       3 - access("B"."MEGROUPID"="C"."SERVERID")
                                       4 - filter("B"."MEGROUPID"='ME1')
                                       6 - filter("C"."SERVERID"='ME1')
                                       7 - filter("A"."INSTRUMENTID"="B"."INSTRUMENTID")
                                       8 - access("A"."ADMINID"="C"."ADMINID")
                                    
                                    
                                    Query without AS OF SCN
                                    
                                    
                                    tst_pre_eod@MIFEX3> SELECT ParticipantID, BoardID, InstrumentID, MMListAction FROM
                                             TIBEX_MEMMHybridAdmView  WHERE MEGroupID = 'ME1'
                                             ORDER BY Timestamp ASC;  2    3
                                    
                                    Execution Plan
                                    ----------------------------------------------------------
                                    Plan hash value: 1706923895
                                    
                                    -----------------------------------------------------------------------------------------------------------
                                    | Id  | Operation                      | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
                                    -----------------------------------------------------------------------------------------------------------
                                    |   0 | SELECT STATEMENT               |                          |     1 |   159 |     5  (20)| 00:00:01 |
                                    |   1 |  SORT ORDER BY                 |                          |     1 |   159 |     5  (20)| 00:00:01 |
                                    |   2 |   NESTED LOOPS SEMI            |                          |     1 |   159 |     4   (0)| 00:00:01 |
                                    |   3 |    NESTED LOOPS                |                          |     1 |   130 |     3   (0)| 00:00:01 |
                                    |   4 |     TABLE ACCESS FULL          | TIBEX_HYBRIDMMINSTRADMIN |     1 |    96 |     2   (0)| 00:00:01 |
                                    |*  5 |     TABLE ACCESS BY INDEX ROWID| TIBEX_INSTRUMENT         |     1 |    34 |     1   (0)| 00:00:01 |
                                    |*  6 |      INDEX UNIQUE SCAN         | XPKTIBEX_INSTRUMENT      |     1 |       |     0   (0)| 00:00:01 |
                                    |*  7 |    INDEX RANGE SCAN            | XPKTIBEX_ADMINACK        |   134K|  3799K|     1   (0)| 00:00:01 |
                                    -----------------------------------------------------------------------------------------------------------
                                    
                                    Predicate Information (identified by operation id):
                                    ---------------------------------------------------
                                    
                                       5 - filter("B"."MEGROUPID"='ME1')
                                       6 - access("A"."INSTRUMENTID"="B"."INSTRUMENTID")
                                       7 - access("A"."ADMINID"="C"."ADMINID" AND "C"."SERVERID"='ME1')
                                           filter("B"."MEGROUPID"="C"."SERVERID")
                                    
                                    Note
                                    -----
                                       - dynamic sampling used for this statement