如何让index range scan变成full table scan
Posted: December 12, 2011 | Author: Cui Hua | Filed under: Oracle | Tags: cluster factor | 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
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
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
OBJECT_NAME VARCHAR2(128) 34,983 0 NONE 1 0 YES NO
SUBOBJECT_NAME VARCHAR2(30) 346 0 NONE 1 56,822 YES NO
OBJECT_ID NUMBER(22) 57,520 0 NONE 1 6 YES NO
DATA_OBJECT_ID NUMBER(22) 6,587 0 NONE 1 50,900 YES NO
OBJECT_TYPE VARCHAR2(19) 42 0 NONE 1 0 YES NO
CREATED DATE 7,579 0 NONE 1 0 YES NO
LAST_DDL_TIME DATE 6,876 0 NONE 1 6 YES NO
TIMESTAMP VARCHAR2(19) 7,561 0 NONE 1 6 YES NO
STATUS VARCHAR2(7) 2 1 NONE 1 0 YES NO
TEMPORARY VARCHAR2(1) 2 1 NONE 1 0 YES NO
GENERATED VARCHAR2(1) 2 1 NONE 1 0 YES NO
SECONDARY VARCHAR2(1) 1 1 NONE 1 0 YES NO
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
Index
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(‘
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(‘
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 VARCHAR
INDNAME VARCHAR
PARTNAME VARCHAR
STATTAB VARCHAR
STATID VARCHAR
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 VARCHAR
NO_INVALIDATE BOOLEAN IN Y
GUESSQ NUMBER IN Y
CACHEDBLK NUMBER IN Y
CACHEHIT NUMBER IN Y
FORCE BOOLEAN IN Y
OWNNAME VARCHAR
INDNAME VARCHAR
PARTNAME VARCHAR
STATTAB VARCHAR
STATID VARCHAR
EXT_STATS RAW IN
STATTYPOWN VARCHAR
STATTYPNAME VARCHAR
STATOWN VARCHAR
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
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
OBJECT_NAME VARCHAR2(128) 34,983 0 NONE 1 0 YES NO
SUBOBJECT_NAME VARCHAR2(30) 346 0 NONE 1 56,822 YES NO
OBJECT_ID NUMBER(22) 57,520 0 NONE 1 6 YES NO
DATA_OBJECT_ID NUMBER(22) 6,587 0 NONE 1 50,900 YES NO
OBJECT_TYPE VARCHAR2(19) 42 0 NONE 1 0 YES NO
CREATED DATE 7,579 0 NONE 1 0 YES NO
LAST_DDL_TIME DATE 6,876 0 NONE 1 6 YES NO
TIMESTAMP VARCHAR2(19) 7,561 0 NONE 1 6 YES NO
STATUS VARCHAR2(7) 2 1 NONE 1 0 YES NO
TEMPORARY VARCHAR2(1) 2 1 NONE 1 0 YES NO
GENERATED VARCHAR2(1) 2 1 NONE 1 0 YES NO
SECONDARY VARCHAR2(1) 1 1 NONE 1 0 YES NO
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
Index
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(‘
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
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
OBJECT_NAME VARCHAR2(128) 34,983 0 NONE 1 0 YES NO
SUBOBJECT_NAME VARCHAR2(30) 346 0 NONE 1 56,822 YES NO
OBJECT_ID NUMBER(22) 57,520 0 NONE 1 6 YES NO
DATA_OBJECT_ID NUMBER(22) 6,587 0 NONE 1 50,900 YES NO
OBJECT_TYPE VARCHAR2(19) 42 0 NONE 1 0 YES NO
CREATED DATE 7,579 0 NONE 1 0 YES NO
LAST_DDL_TIME DATE 6,876 0 NONE 1 6 YES NO
TIMESTAMP VARCHAR2(19) 7,561 0 NONE 1 6 YES NO
STATUS VARCHAR2(7) 2 1 NONE 1 0 YES NO
TEMPORARY VARCHAR2(1) 2 1 NONE 1 0 YES NO
GENERATED VARCHAR2(1) 2 1 NONE 1 0 YES NO
SECONDARY VARCHAR2(1) 1 1 NONE 1 0 YES NO
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
Index
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(‘
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
Recent Comments