如何让index range scan变成full table scan

Posted: December 12, 2011 | Author: | Filed under: Oracle | Tags: | Leave a comment »

我们来做一个仅通过修改clustering factor就让原本走index range scan变成了走full table scan的例子。

这种改法的理论基础是来源于index range scan（IRS）的cost计算公式：

IRS Cost = I/O Cost + CPU Cost

其中：

I/O Cost = Index Access I/O Cost + Table Access I/O Cost

Index Access I/O Cost = LVLS + CEIL(#LB * ix_sel)

Table Access I/O Cost = CEIL(CLUF * ix_sel_with_filters)

CPU Cost = ROUND(#CPUCycles/cpuspeed/1000/sreadtim)

 

所以走index range scan （IRS）的成本可以近似看作是和clustering factor成正比。

 

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.5.0

Connected as ipra

 

SQL> create table t1 as select * from dba_objects;

 

Table created

 

SQL> select count(*) from t1;

 

  COUNT(*)

———-

     57526

 

SQL> create index idx_t1 on t1(object_id);

 

Index created

 

SQL> exec dbms_stats.gather_table_stats(ownname=>’IPRA’,tabname=>’T1′,method_opt=>’for all columns size auto’,CASCADE=>true,estimate_percent=>100);

 

PL/SQL procedure successfully completed

 

从如下结果里可以看到现在索引idx_t1的clutering factor是1887：

SQL> @’E:\sosi.txt’;

SQL> set echo off

 

 

 

 

Please enter Name of Table Owner (Null = IPRA):

Please enter Table Name to show Statistics for: t1

 

***********

Table Level

***********

 

 

Table                   Number                 Empty Average    Chain Average Global User           Sample Last

Name                   of Rows   Blocks       Blocks   Space    Count Row Len Stats  Stats            Size Analyzed

————— ————– ——– ———— ——- ——– ——- —— —— ——–

T1                      57,526      802            0       0        0      93 YES    NO             57,526 2011-09-23

 

Column                    Column                       Distinct                          Number     Number Global User   Last

Name                      Details                        Values Density Histogram       Buckets      Nulls Stats  Stats  Analyzed

————————- ———————— ———— ——- ————— ——- —-

OWNER                     VARCHAR2(30)                       23       0 NONE                  1          0 YES    NO     2011-09-23

OBJECT_NAME               VARCHAR2(128)                  34,983       0 NONE                  1          0 YES    NO     2011-09-23

SUBOBJECT_NAME            VARCHAR2(30)                      346       0 NONE                  1     56,822 YES    NO     2011-09-23

OBJECT_ID                 NUMBER(22)                     57,520       0 NONE                  1          6 YES    NO     2011-09-23

DATA_OBJECT_ID            NUMBER(22)                      6,587       0 NONE                  1     50,900 YES    NO     2011-09-23

OBJECT_TYPE               VARCHAR2(19)                       42       0 NONE                  1          0 YES    NO     2011-09-23

CREATED                   DATE                            7,579       0 NONE                  1          0 YES    NO     2011-09-23

LAST_DDL_TIME             DATE                            6,876       0 NONE                  1          6 YES    NO     2011-09-23

TIMESTAMP                 VARCHAR2(19)                    7,561       0 NONE                  1          6 YES    NO     2011-09-23

STATUS                    VARCHAR2(7)                         2       1 NONE                  1          0 YES    NO     2011-09-23

TEMPORARY                 VARCHAR2(1)                         2       1 NONE                  1          0 YES    NO     2011-09-23

GENERATED                 VARCHAR2(1)                         2       1 NONE                  1          0 YES    NO     2011-09-23

SECONDARY                 VARCHAR2(1)                         1       1 NONE                  1          0 YES    NO     2011-09-23

 

                                        B                                           Average     Average

Index                                Tree    Leaf       Distinct         Number Leaf Blocks Data Blocks      Cluster Global Last

Name                      Unique    Level    Blks           Keys        of Rows     Per Key     Per Key       Factor Stats  Analyzed

————————- ——— —– ——- ————– ————– ———– ——–

IDX_T1                    NONUNIQUE     1     127         57,520         57,520           1           1        1,887 YES    2011-09-23

 

Index                     Column                     Col Column

Name                      Name                       Pos Details

————————- ————————- —- ————————

IDX_T1                    OBJECT_ID                    1 NUMBER(22)

 

***************

Partition Level

***************

 

***************

SubPartition Level

***************

 

SQL> select /*+ cluster_factor_example_1 */object_id,object_name from t1 where object_id between 103 and 108;

 

 OBJECT_ID OBJECT_NAME

———- ——————————————————————————–

       103 I_OBJAUTH1

       104 I_OBJAUTH2

       105 I_SYSAUTH1

       106 I_DEFROLE1

       107 I_LINK1

       108 I_COM1

 

6 rows selected

 

SQL> select sql_text, SQL_ID, VERSION_COUNT,BIND_DATA from v$sqlarea where sql_text like ‘select /*+ cluster_factor_example_1 */%’;

 

SQL_TEXT                                                                         SQL_ID        VERSION_COUNT BIND_DATA

——————————————————————————– ————- ————- ——————————————————————————–

select /*+ cluster_factor_example_1 */object_id,object_name from t1 where object 511vza7u2ayzk             1

 

从结果里可以看到现在上述sql走的就是IRS，其成本为3：

SQL> select * from table(dbms_xplan.display_cursor(‘511vza7u2ayzk’,0,’advanced’));

 

PLAN_TABLE_OUTPUT

——————————————————————————–

SQL_ID  511vza7u2ayzk, child number 0

————————————-

select /*+ cluster_factor_example_1 */object_id,object_name from t1 where

object_id between 103 and 108

Plan hash value: 3636266709

——————————————————————————–

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time

——————————————————————————–

|   0 | SELECT STATEMENT            |        |       |       |     3 (100)|

|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     3 |    87 |     3   (0)| 00:0

|*  2 |   INDEX RANGE SCAN          | IDX_T1 |     3 |       |     2   (0)| 00:0

——————————————————————————–

Query Block Name / Object Alias (identified by operation id):

————————————————————-

   1 – SEL$1 / T1@SEL$1

   2 – SEL$1 / T1@SEL$1

 

PLAN_TABLE_OUTPUT

——————————————————————————–

Outline Data

————-

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE(‘10.2.0.5′)

      ALL_ROWS

      OUTLINE_LEAF(@”SEL$1″)

      INDEX_RS_ASC(@”SEL$1″ “T1″@”SEL$1” (“T1″.”OBJECT_ID”))

      END_OUTLINE_DATA

  */

Predicate Information (identified by operation id):

—————————————————

   2 – access(“OBJECT_ID”>=103 AND “OBJECT_ID”<=108)

Column Projection Information (identified by operation id):

———————————————————–

 

PLAN_TABLE_OUTPUT

——————————————————————————–

   1 – “OBJECT_NAME”[VARCHAR2,128], “OBJECT_ID”[NUMBER,22]

   2 – “T1”.ROWID[ROWID,10], “OBJECT_ID”[NUMBER,22]

 

45 rows selected

 

从结果里可以看到现在上述sql如果走FTS，则其成本为183：

SQL> select /*+ full(t1) */object_id,object_name from t1 where object_id between 103 and 108;

 

 OBJECT_ID OBJECT_NAME

———- ——————————————————————————–

       103 I_OBJAUTH1

       104 I_OBJAUTH2

       105 I_SYSAUTH1

       106 I_DEFROLE1

       107 I_LINK1

       108 I_COM1

 

6 rows selected

 

SQL> select sql_text, SQL_ID, VERSION_COUNT,BIND_DATA from v$sqlarea where sql_text like ‘select /*+ full(t1) */%’;

 

SQL_TEXT                                                                         SQL_ID        VERSION_COUNT BIND_DATA

——————————————————————————– ————- ————- ——————————————————————————–

select /*+ full(t1) */object_id,object_name from t1 where object_id between 103  gfmw7yrbvksnw             1

 

SQL> select * from table(dbms_xplan.display_cursor(‘gfmw7yrbvksnw’,0,’advanced’));

 

PLAN_TABLE_OUTPUT

——————————————————————————–

SQL_ID  gfmw7yrbvksnw, child number 0

————————————-

select /*+ full(t1) */object_id,object_name from t1 where object_id

between 103 and 108

Plan hash value: 838529891

————————————————————————–

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

————————————————————————–

|   0 | SELECT STATEMENT  |      |       |       |   183 (100)|          |

|*  1 |  TABLE ACCESS FULL| T1   |     3 |    87 |   183   (4)| 00:00:03 |

————————————————————————–

Query Block Name / Object Alias (identified by operation id):

————————————————————-

   1 – SEL$1 / T1@SEL$1

Outline Data

 

PLAN_TABLE_OUTPUT

——————————————————————————–

————-

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE(‘10.2.0.5′)

      ALL_ROWS

      OUTLINE_LEAF(@”SEL$1″)

      FULL(@”SEL$1″ “T1″@”SEL$1”)

      END_OUTLINE_DATA

  */

Predicate Information (identified by operation id):

—————————————————

   1 – filter((“OBJECT_ID”<=108 AND “OBJECT_ID”>=103))

Column Projection Information (identified by operation id):

———————————————————–

   1 – “OBJECT_NAME”[VARCHAR2,128], “OBJECT_ID”[NUMBER,22]

 

PLAN_TABLE_OUTPUT

——————————————————————————–

 

42 rows selected

 

SQL> desc dbms_stats.set_index_stats;

Parameter     Type     Mode Default?

————- ——– —- ——–

OWNNAME       VARCHAR2 IN           

INDNAME       VARCHAR2 IN           

PARTNAME      VARCHAR2 IN   Y       

STATTAB       VARCHAR2 IN   Y       

STATID        VARCHAR2 IN   Y       

NUMROWS       NUMBER   IN   Y       

NUMLBLKS      NUMBER   IN   Y       

NUMDIST       NUMBER   IN   Y       

AVGLBLK       NUMBER   IN   Y       

AVGDBLK       NUMBER   IN   Y       

CLSTFCT       NUMBER   IN   Y       

INDLEVEL      NUMBER   IN   Y       

FLAGS         NUMBER   IN   Y       

STATOWN       VARCHAR2 IN   Y       

NO_INVALIDATE BOOLEAN  IN   Y       

GUESSQ        NUMBER   IN   Y       

CACHEDBLK     NUMBER   IN   Y       

CACHEHIT      NUMBER   IN   Y       

FORCE         BOOLEAN  IN   Y       

OWNNAME       VARCHAR2 IN           

INDNAME       VARCHAR2 IN           

PARTNAME      VARCHAR2 IN   Y       

STATTAB       VARCHAR2 IN   Y       

STATID        VARCHAR2 IN   Y       

EXT_STATS     RAW      IN           

STATTYPOWN    VARCHAR2 IN   Y       

STATTYPNAME   VARCHAR2 IN   Y       

STATOWN       VARCHAR2 IN   Y       

NO_INVALIDATE BOOLEAN  IN   Y       

FORCE         BOOLEAN  IN   Y

 

我先把CLUF改成100万：

SQL> exec dbms_stats.set_index_stats(ownname => ‘IPRA’,indname => ‘IDX_T1’,clstfct => 1000000,no_invalidate => false);

 

PL/SQL procedure successfully completed

 

SQL> @’E:\sosi.txt’;

SQL> set echo off

 

 

 

 

Please enter Name of Table Owner (Null = IPRA):

Please enter Table Name to show Statistics for: t1

 

***********

Table Level

***********

 

 

Table                   Number                 Empty Average    Chain Average Global User           Sample Last

Name                   of Rows   Blocks       Blocks   Space    Count Row Len Stats  Stats            Size Analyzed

————— ————– ——– ———— ——- ——– ——- —— —— ——–

T1                      57,526      802            0       0        0      93 YES    NO             57,526 2011-09-23

 

Column                    Column                       Distinct                          Number     Number Global User   Last

Name                      Details                        Values Density Histogram       Buckets      Nulls Stats  Stats  Analyzed

————————- ———————— ———— ——- ————— ——- —-

OWNER                     VARCHAR2(30)                       23       0 NONE                  1          0 YES    NO     2011-09-23

OBJECT_NAME               VARCHAR2(128)                  34,983       0 NONE                  1          0 YES    NO     2011-09-23

SUBOBJECT_NAME            VARCHAR2(30)                      346       0 NONE                  1     56,822 YES    NO     2011-09-23

OBJECT_ID                 NUMBER(22)                     57,520       0 NONE                  1          6 YES    NO     2011-09-23

DATA_OBJECT_ID            NUMBER(22)                      6,587       0 NONE                  1     50,900 YES    NO     2011-09-23

OBJECT_TYPE               VARCHAR2(19)                       42       0 NONE                  1          0 YES    NO     2011-09-23

CREATED                   DATE                            7,579       0 NONE                  1          0 YES    NO     2011-09-23

LAST_DDL_TIME             DATE                            6,876       0 NONE                  1          6 YES    NO     2011-09-23

TIMESTAMP                 VARCHAR2(19)                    7,561       0 NONE                  1          6 YES    NO     2011-09-23

STATUS                    VARCHAR2(7)                         2       1 NONE                  1          0 YES    NO     2011-09-23

TEMPORARY                 VARCHAR2(1)                         2       1 NONE                  1          0 YES    NO     2011-09-23

GENERATED                 VARCHAR2(1)                         2       1 NONE                  1          0 YES    NO     2011-09-23

SECONDARY                 VARCHAR2(1)                         1       1 NONE                  1          0 YES    NO     2011-09-23

 

                                        B                                           Average     Average

Index                                Tree    Leaf       Distinct         Number Leaf Blocks Data Blocks      Cluster Global Last

Name                      Unique    Level    Blks           Keys        of Rows     Per Key     Per Key       Factor Stats  Analyzed

————————- ——— —– ——- ————– ————– ———– ——–

IDX_T1                    NONUNIQUE     1     127         57,520         57,520           1           1    1,000,000 YES    2011-09-23

 

Index                     Column                     Col Column

Name                      Name                       Pos Details

————————- ————————- —- ————————

IDX_T1                    OBJECT_ID                    1 NUMBER(22)

 

***************

Partition Level

***************

 

***************

SubPartition Level

***************

 

从结果里可以看到，改完后虽然走的还是IRS，但成本变成了61：

SQL> select /*+ cluster_factor_example_2 */object_id,object_name from t1 where object_id between 103 and 108;

 

 OBJECT_ID OBJECT_NAME

———- ——————————————————————————–

       103 I_OBJAUTH1

       104 I_OBJAUTH2

       105 I_SYSAUTH1

       106 I_DEFROLE1

       107 I_LINK1

       108 I_COM1

 

6 rows selected

 

SQL> select sql_text, SQL_ID, VERSION_COUNT,BIND_DATA from v$sqlarea where sql_text like ‘select /*+ cluster_factor_example_2 */%’;

 

SQL_TEXT                                                                         SQL_ID        VERSION_COUNT BIND_DATA

——————————————————————————– ————- ————- ——————————————————————————–

select /*+ cluster_factor_example_2 */object_id,object_name from t1 where object djtg3dbvabsbm             1

 

SQL> select * from table(dbms_xplan.display_cursor(‘djtg3dbvabsbm’,0,’advanced’));

 

PLAN_TABLE_OUTPUT

——————————————————————————–

SQL_ID  djtg3dbvabsbm, child number 0

————————————-

select /*+ cluster_factor_example_2 */object_id,object_name from t1 where

object_id between 103 and 108

Plan hash value: 3636266709

——————————————————————————–

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time

——————————————————————————–

|   0 | SELECT STATEMENT            |        |       |       |    61 (100)|

|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     3 |    87 |    61   (0)| 00:0

|*  2 |   INDEX RANGE SCAN          | IDX_T1 |     3 |       |     2   (0)| 00:0

——————————————————————————–

Query Block Name / Object Alias (identified by operation id):

————————————————————-

   1 – SEL$1 / T1@SEL$1

   2 – SEL$1 / T1@SEL$1

 

PLAN_TABLE_OUTPUT

——————————————————————————–

Outline Data

————-

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE(‘10.2.0.5′)

      ALL_ROWS

      OUTLINE_LEAF(@”SEL$1″)

      INDEX_RS_ASC(@”SEL$1″ “T1″@”SEL$1” (“T1″.”OBJECT_ID”))

      END_OUTLINE_DATA

  */

Predicate Information (identified by operation id):

—————————————————

   2 – access(“OBJECT_ID”>=103 AND “OBJECT_ID”<=108)

Column Projection Information (identified by operation id):

———————————————————–

 

PLAN_TABLE_OUTPUT

——————————————————————————–

   1 – “OBJECT_NAME”[VARCHAR2,128], “OBJECT_ID”[NUMBER,22]

   2 – “T1”.ROWID[ROWID,10], “OBJECT_ID”[NUMBER,22]

 

45 rows selected

 

所以我只需把CLUF改成400万，就可以使走IRS的成本高于走FTS的成本183：

SQL> exec dbms_stats.set_index_stats(ownname => ‘IPRA’,indname => ‘IDX_T1’,clstfct => 4000000,no_invalidate => false);

 

PL/SQL procedure successfully completed

 

SQL> @’E:\sosi.txt’;

SQL> set echo off

 

 

 

 

Please enter Name of Table Owner (Null = IPRA):

Please enter Table Name to show Statistics for: t1

 

***********

Table Level

***********

 

 

Table                   Number                 Empty Average    Chain Average Global User           Sample Last

Name                   of Rows   Blocks       Blocks   Space    Count Row Len Stats  Stats            Size Analyzed

————— ————– ——– ———— ——- ——– ——- —— —— ——–

T1                      57,526      802            0       0        0      93 YES    NO             57,526 2011-09-23

 

Column                    Column                       Distinct                          Number     Number Global User   Last

Name                      Details                        Values Density Histogram       Buckets      Nulls Stats  Stats  Analyzed

————————- ———————— ———— ——- ————— ——- —-

OWNER                     VARCHAR2(30)                       23       0 NONE                  1          0 YES    NO     2011-09-23

OBJECT_NAME               VARCHAR2(128)                  34,983       0 NONE                  1          0 YES    NO     2011-09-23

SUBOBJECT_NAME            VARCHAR2(30)                      346       0 NONE                  1     56,822 YES    NO     2011-09-23

OBJECT_ID                 NUMBER(22)                     57,520       0 NONE                  1          6 YES    NO     2011-09-23

DATA_OBJECT_ID            NUMBER(22)                      6,587       0 NONE                  1     50,900 YES    NO     2011-09-23

OBJECT_TYPE               VARCHAR2(19)                       42       0 NONE                  1          0 YES    NO     2011-09-23

CREATED                   DATE                            7,579       0 NONE                  1          0 YES    NO     2011-09-23

LAST_DDL_TIME             DATE                            6,876       0 NONE                  1          6 YES    NO     2011-09-23

TIMESTAMP                 VARCHAR2(19)                    7,561       0 NONE                  1          6 YES    NO     2011-09-23

STATUS                    VARCHAR2(7)                         2       1 NONE                  1          0 YES    NO     2011-09-23

TEMPORARY                 VARCHAR2(1)                         2       1 NONE                  1          0 YES    NO     2011-09-23

GENERATED                 VARCHAR2(1)                         2       1 NONE                  1          0 YES    NO     2011-09-23

SECONDARY                 VARCHAR2(1)                         1       1 NONE                  1          0 YES    NO     2011-09-23

 

                                        B                                           Average     Average

Index                                Tree    Leaf       Distinct         Number Leaf Blocks Data Blocks      Cluster Global Last

Name                      Unique    Level    Blks           Keys        of Rows     Per Key     Per Key       Factor Stats  Analyzed

————————- ——— —– ——- ————– ————– ———– ——–

IDX_T1                    NONUNIQUE     1     127         57,520         57,520           1           1    4,000,000 YES    2011-09-23

 

Index                     Column                     Col Column

Name                      Name                       Pos Details

————————- ————————- —- ————————

IDX_T1                    OBJECT_ID                    1 NUMBER(22)

 

***************

Partition Level

***************

 

***************

SubPartition Level

***************

 

SQL> select sql_text, SQL_ID, VERSION_COUNT,BIND_DATA from v$sqlarea where sql_text like ‘select /*+ cluster_factor_example_3 */%’;

 

SQL_TEXT                                                                         SQL_ID        VERSION_COUNT BIND_DATA

——————————————————————————– ————- ————- ——————————————————————————–

select /*+ cluster_factor_example_3 */object_id,object_name from t1 where object grgm62vxa43zs             1

 

可以看到，对同样的sql，我仅通过修改CLUF就使其从走IRS变成了走FTS了：

SQL> select * from table(dbms_xplan.display_cursor(‘grgm62vxa43zs’,0,’advanced’));

 

PLAN_TABLE_OUTPUT

——————————————————————————–

SQL_ID  grgm62vxa43zs, child number 0

————————————-

select /*+ cluster_factor_example_3 */object_id,object_name from t1

where object_id between 103 and 108

Plan hash value: 838529891

————————————————————————–

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

————————————————————————–

|   0 | SELECT STATEMENT  |      |       |       |   183 (100)|          |

|*  1 |  TABLE ACCESS FULL| T1   |     3 |    87 |   183   (4)| 00:00:03 |

————————————————————————–

Query Block Name / Object Alias (identified by operation id):

————————————————————-

   1 – SEL$1 / T1@SEL$1

Outline Data

 

PLAN_TABLE_OUTPUT

——————————————————————————–

————-

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE(‘10.2.0.5′)

      ALL_ROWS

      OUTLINE_LEAF(@”SEL$1″)

      FULL(@”SEL$1″ “T1″@”SEL$1”)

      END_OUTLINE_DATA

  */

Predicate Information (identified by operation id):

—————————————————

   1 – filter((“OBJECT_ID”<=108 AND “OBJECT_ID”>=103))

Column Projection Information (identified by operation id):

———————————————————–

   1 – “OBJECT_NAME”[VARCHAR2,128], “OBJECT_ID”[NUMBER,22]

 

PLAN_TABLE_OUTPUT

——————————————————————————–

 

42 rows selected