1 Reply Latest reply: Dec 6, 2012 6:28 AM by Dom Brooks RSS

    Query tuning -- Bind and plan

    874051
      HI Forum ,
      I am trying to understand the below part .

      -- created a dummy table
      create table test_spm( a number,b varchar2(100));
      create index test_spm_a_ind on test_spm(a);
      inserted data as
      select count(1) from test_spm--1111110
      select a,count(1) from test_spm group by a order by 2
      0 10
      1 100
      2 1000
      3 10000
      4 100000
      5 1000000
      gathered stats EXEC DBMS_STATS.GATHER_TABLE_STATS (ownname=>'TAB_OWNER',tabname=>'TEST_SPM' ,
      estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FOR ALL COLUMNS SIZE AUTO', DEGREE=>8,CASCADE=>TRUE);

      histograms
      select * from dba_tab_histograms where table_name='TEST_SPM'
      IGOAPP_ADMIN TEST_SPM A 2 1
      IGOAPP_ADMIN TEST_SPM A 5 2
      IGOAPP_ADMIN TEST_SPM A 54 3
      IGOAPP_ADMIN TEST_SPM A 570 4
      IGOAPP_ADMIN TEST_SPM A 5504 5
      IGOAPP_ADMIN TEST_SPM B 0 2.56091583960353E35
      IGOAPP_ADMIN TEST_SPM B 1 6.35842717277519E35

      select num_distinct,num_buckets,sample_size,histogram from dba_tab_col_statistics where table_name='TEST_SPM' and column_name='A'
      6 5 5504 FREQUENCY

      execution:
      SQL> variable a number;
      SQL> exec :a:=0;

      PL/SQL procedure successfully completed.

      SQL> select count(1) from test_spm where a=:a;

      COUNT(1)
      ----------
      10


      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 514245925

      -------------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
      -------------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | | 1 | 3 | 470 (7)| 00:00:06 |
      | 1 | SORT AGGREGATE | | 1 | 3 | | |
      |* 2 | TABLE ACCESS FULL| TEST_SPM | 185K| 542K| 470 (7)| 00:00:06 |
      -------------------------------------------------------------------------------

      Predicate Information (identified by operation id):
      ---------------------------------------------------

      2 - filter("A"=TO_NUMBER(:A))

      SQL> exec :a:=1;

      PL/SQL procedure successfully completed.

      SQL> select count(1) from test_spm where a=:a;

      COUNT(1)
      ----------
      100


      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 514245925

      -------------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
      -------------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | | 1 | 3 | 470 (7)| 00:00:06 |
      | 1 | SORT AGGREGATE | | 1 | 3 | | |
      |* 2 | TABLE ACCESS FULL| TEST_SPM | 185K| 542K| 470 (7)| 00:00:06 |
      -------------------------------------------------------------------------------

      Predicate Information (identified by operation id):
      ---------------------------------------------------

      2 - filter("A"=TO_NUMBER(:A))

      SQL> exec :a:=3;

      PL/SQL procedure successfully completed.

      SQL> select count(1) from test_spm where a=:a;

      COUNT(1)
      ----------
      10000


      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 514245925

      -------------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
      -------------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | | 1 | 3 | 470 (7)| 00:00:06 |
      | 1 | SORT AGGREGATE | | 1 | 3 | | |
      |* 2 | TABLE ACCESS FULL| TEST_SPM | 185K| 542K| 470 (7)| 00:00:06 |
      -------------------------------------------------------------------------------

      Predicate Information (identified by operation id):
      ---------------------------------------------------

      2 - filter("A"=TO_NUMBER(:A))

      SQL> exec :a:=5;

      PL/SQL procedure successfully completed.

      SQL> select count(1) from test_spm where a=:a;

      COUNT(1)
      ----------
      1000000


      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 514245925

      -------------------------------------------------------------------------------
      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
      -------------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | | 1 | 3 | 470 (7)| 00:00:06 |
      | 1 | SORT AGGREGATE | | 1 | 3 | | |
      |* 2 | TABLE ACCESS FULL| TEST_SPM | 185K| 542K| 470 (7)| 00:00:06 |
      -------------------------------------------------------------------------------

      Predicate Information (identified by operation id):
      ---------------------------------------------------

      2 - filter("A"=TO_NUMBER(:A))

      SQL>



      select sql_id,address,child_address,is_bind_sensitive,is_bind_aware,is_shareable,exact_matching_signature,
      force_matching_signature,bind_data from v$sql where sql_text like 'select count(1) from test_spm where a=:a'

      4amggdrpyddkf 00000003E0613138 00000003EAD51F70 Y N N 3.49662019466122E18 3.49662019466122E18 BEDA0B20020050C08013000101C002160180
      4amggdrpyddkf 00000003E0613138 00000003E03007F0 Y Y N 3.49662019466122E18 3.49662019466122E18 BEDA0B20020050C080F4000101C0021602C103
      4amggdrpyddkf 00000003E0613138 00000003E0C5EBA8 Y Y N 3.49662019466122E18 3.49662019466122E18 BEDA0B20020050C0820B000101C002160180
      4amggdrpyddkf 00000003E0613138 000000041B38B3F0 Y Y Y 3.49662019466122E18 3.49662019466122E18 BEDA0B20020050C084AC000101C0021602C105

      SQL> show parameter cursor_sharing

      NAME TYPE VALUE
      ------------------------------------ ----------- ----------------------------
      cursor_sharing string EXACT
      SQL>

      SQL> select * from v$version;

      BANNER
      --------------------------------------------------------------------------------
      Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
      PL/SQL Release 11.2.0.2.0 - Production
      CORE 11.2.0.2.0 Production
      TNS for Solaris: Version 11.2.0.2.0 - Production
      NLSRTL Version 11.2.0.2.0 - Production


      -- I am trying to understabd the behaviour of optimizer on bind values .


      Thanks ,
      Mahesh
        • 1. Re: Query tuning -- Bind and plan
          Dom Brooks
          How are you getting the actual execution plans?

          Use DBMS_XPLAN.DISPLAY_CURSOR i.e. straight after executing:
          SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);
          select sql_id,address,child_address,is_bind_sensitive,is_bind_aware,is_shareable,exact_matching_signature,
          force_matching_signature,bind_data from v$sql where sql_text like 'select count(1) from test_spm where a=:a'
          Can you include the plan_hash_value?


          This is probably the single best resource on the adaptive cursor sharing (is_bind_aware / is_bind_sensitive) that you're seeing:
          https://blogs.oracle.com/optimizer/entry/why_are_there_more_cursors_in_11g_for_my_query_containing_bind_variables_1