案例简述
数据库：
10.129.130.179
用户：
tlstat_newbg_studio
语句：
update DY_USER_ANALYSIS_LEVEL set LOGON_CNT_GUID = LOGON_CNT_GUID + to_number(0) where STAT_TIME = to_date('2016-07-09','yyyy-mm-dd') and game_type='tlbb' and ROLE_LEVEL=3 and DJ_GROUP_TYPE=1 and GROUP_ID=to_number(-1)
这个语句执行的很慢，不走索引。
另一个用户下面的这个表走索引：
tlstat_newbg.DY_USER_ANALYSIS_LEVEL
分析
1、把SQL语句改写一下：
select LOGON_CNT_GUID from DY_USER_ANALYSIS_LEVEL where STAT_TIME = to_date('2016-07-09','yyyy-mm-dd') and game_type='tlbb' and ROLE_LEVEL=3 and DJ_GROUP_TYPE=1 and GROUP_ID=to_number(-1)
2、查看这个表上面的索引 两个都是可用的
SQL> select TABLE_OWNER,INDEX_NAME,STATUS from dba_indexes where TABLE_NAME='DY_USER_ANALYSIS_LEVEL';
TABLE_OWNER INDEX_NAME STATUS
TLSTAT_NEWBG SYS_C00106565 VALID
TLSTAT_NEWBG IND_USER_LEVLE_DD_DATE_GAME VALID
TLSTAT_NEWBG_STUDIO IND_USER_LEVLE_DD_DATE_GAME VALID
TLSTAT_NEWBG_STUDIO SYS_C00145980 VALID
3、查看索引所在的列，可以看到的在where条件这两列上面有索引，而且stat_time 这一列是前导列
SQL> select COLUMN_NAME,COLUMN_POSITION from dba_ind_columns where INDEX_NAME='IND_USER_LEVLE_DD_DATE_GAME' and TABLE_OWNER='TLSTAT_NEWBG_STUDIO';
COLUMN_NAME
COLUMN_POSITION
STAT_TIME
1
GAME_TYPE
2
PS：选择性比较好的应该做为前导列
4、执行计划的查看（trace的方法会执行，explain plan for不会执行），可以看到全表扫描，后来做的实验现象是---除了当where条件都是这两列并select的语句是这两列其中的一列的时候才会走索引，别的时候不会走索引。（相当于索引失效）
PS：不要用update语句去测试 下面的不要模仿
SQL> explain plan for update DY_USER_ANALYSIS_LEVEL_TEST set LOGON_CNT_GUID = LOGON_CNT_GUID + to_number(0) where STAT_TIME = to_date('2016-07-09','yyyy-mm-dd') and game_type='tlbb' and ROLE_LEVEL=3 and DJ_GROUP_TYPE=1 and GROUP_ID=to_number(-1)
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
Plan hash value: 3783326914
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
)| Time |
PLAN_TABLE_OUTPUT
| 0 | UPDATE STATEMENT | | 1 | 26 | 593 (2
)| 00:00:08 |
| 1 | UPDATE | DY_USER_ANALYSIS_LEVEL | | |
| |
|* 2 | TABLE ACCESS FULL| DY_USER_ANALYSIS_LEVEL | 1 | 26 | 593 (2
)| 00:00:08 |
PLAN_TABLE_OUTPUT
Predicate Information (identified by operation id):
2 - filter("ROLE_LEVEL"=3 AND "GROUP_ID"=(-1) AND "STAT_TIME"=TO_DATE('
2016-07-09 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "DJ_GROUP_TYPE
"=1 AND
"GAME_TYPE"='tlbb')
16 rows selected.
5、这时候我们怀疑是数据分散的太离散，可以去看一下他的集群因子（集群因子和行数比值越小越好，和块数的比值越接近1越好）
[oracle@s2nstatdb dbm_lite]$ sh showcluster_fac.sh tlstat_newbg_studio DY_USER_ANALYSIS_LEVEL
TABLE_NAME INDEX_NAME DISTINCT_KEYS CLUSTERING_FACTOR BLOCKS NUM_ROWS LAST_ANALYZED
DY_USER_ANALYSIS_LEVEL SYS_C00145980 484895 51717 3898 484895 2017-08-28 11:46:44
DY_USER_ANALYSIS_LEVEL IND_USER_LEVLE_DD_DATE_GAME 35 54081 3898 493731 2017-08-28 11:46:44
[oracle@s2nstatdb dbm_lite]$ sh showcluster_fac.sh tlstat_newbg DY_USER_ANALYSIS_LEVEL
TABLE_NAME INDEX_NAME DISTINCT_KEYS CLUSTERING_FACTOR BLOCKS NUM_ROWS LAST_ANALYZED
DY_USER_ANALYSIS_LEVEL IND_USER_LEVLE_DD_DATE_GAME 16281 130726 56506 8162321 2017-08-21 22:01:08
DY_USER_ANALYSIS_LEVEL SYS_C00106565 7981757 74016 56506 7981757 2017-08-21 22:00:59
我们对这两个表的集群因子作对比
#tlstat_newbg_studio.DY_USER_ANALYSIS_LEVEL
SQL> select 54081/3898 from dual;
54081/3898
13.874038
SQL> select 54081/493731 from dual;
54081/493731
.109535354
#tlstat_newbg.DY_USER_ANALYSIS_LEVEL
SQL> select 130726/56506 from dual;
130726/56506
2.31348883
SQL> select 130726/8162321 from dual;
130726/8162321
.016015788
看出来不走索引表的集群因子和块的比值明显大于1，列的无序性很明显
PS:可以查看部分数据，发现不走数据的表上面stat_time分布的很奇怪 一阵26号一阵30穿插分布。
解决
定制解决方案：将表重建，将列按序排列，这种解决方式基于表数据不大（线上操作不会产生太大的IO消耗），并且和使用这个表的人提前告知一下。
1、查看表是否有依赖关系
（REFERENCED_NAME写table name（被关联的那个表），name写tablename（作为主动去关联别人的表））
下面的结果可以看出，连接关系和本案例的用户无关
SQL> desc dba_dependencies
Name Null? Type
OWNER NOT NULL VARCHAR2(30)
NAME NOT NULL VARCHAR2(30)
TYPE VARCHAR2(17)
REFERENCED_OWNER VARCHAR2(30)
REFERENCED_NAME VARCHAR2(64)
REFERENCED_TYPE VARCHAR2(17)
REFERENCED_LINK_NAME VARCHAR2(128)
DEPENDENCY_TYPE VARCHAR2(4)
SQL> select OWNER,name,type,REFERENCED_TYPE,REFERENCED_OWNER,DEPENDENCY_TYPE from dba_dependencies where REFERENCED_NAME='DY_USER_ANALYSIS_LEVEL';
OWNER NAME TYPE
REFERENCED_TYPE REFERENCED_OWNER DEPE
TLSTAT_NEWFG DY_USER_ANALYSIS_LEVEL SYNONYM
TABLE TLSTAT_NEWBG HARD
SQL> select OWNER,name,type,REFERENCED_TYPE,REFERENCED_OWNER,DEPENDENCY_TYPE from dba_dependencies where name='DY_USER_ANALYSIS_LEVEL';
OWNER NAME TYPE
REFERENCED_TYPE REFERENCED_OWNER DEPE
TLSTAT_NEWFG DY_USER_ANALYSIS_LEVEL SYNONYM
TABLE TLSTAT_NEWBG HARD
2、查看表空间是否够用，我们希望规范的将表的数据空间放在TLBB_DATA中，索引放在TLBB_INDEX中
a.查看用户允许除了默认使用的表空间可以使用的表空间,空的。
SQL> select TABLESPACE_NAME from dba_ts_quotas where USERNAME=UPPER('tlstat_newbg_studio');
TABLESPACE_NAME
TLBB_DATA
TLBB_INDEX
b.查看用户默认使用的表空间
SQL> select DEFAULT_TABLESPACE from dba_users where USERNAME=UPPER('TLSTAT_NEWBG_STUDIO');
DEFAULT_TABLESPACE
USERS
c.授权使用表空间
SQL> alter user TLSTAT_NEWBG_STODIO quota unlimited on TLBB_INDEX;
SQL> alter user TLSTAT_NEWBG_STODIO quota unlimited on TLBB_DATA;
SQL> select TABLESPACE_NAME from dba_ts_quotas where USERNAME=UPPER('tlstat_newbg_studio');
TABLESPACE_NAME
TLBB_DATA
TLBB_INDEX
d.查看表空间使用，表空间是自动扩张的，现在已经用的和最大使用空间，
BYTES会比MAXBYTES大的原因是有两个非扩张的大小的表空间文件。
空间大小这么看是没有问题的
SQL> select SUM(BYTES)/(1024*1024),SUM(MAXBYTES)/(1024*1024) from dba_data_files where TABLESPACE_NAME='TLBB_INDEX';
SUM(BYTES)/(1024*1024) SUM(MAXBYTES)/(1024*1024)
8420 65534.9844
SQL> select AUTOEXTENSIBLE from dba_data_files where TABLESPACE_NAME='TLBB_INDEX';
AUT
YES
YES
SQL> select SUM(BYTES)/(1024*1024),SUM(MAXBYTES)/(1024*1024) from dba_data_files where TABLESPACE_NAME='TLBB_DATA';
SUM(BYTES)/(1024*1024) SUM(MAXBYTES)/(1024*1024)
208568.984 162812.984
SQL> select BYTES,MAXBYTES,AUTOEXTENSIBLE from dba_data_files where TABLESPACE_NAME='TLBB_DATA';
BYTES MAXBYTES AUT
3.4360E+10 3.4360E+10 YES
3.4359E+10 3.4359E+10 YES
3.4359E+10 3.4359E+10 YES
3.4359E+10 3.4359E+10 YES
3.2212E+10 0 NO
3.2212E+10 0 NO
1.6840E+10 3.3286E+10 YES
7 rows selected.
3、查看建表和建索引语句
SQL> set line 200
SQL> set pagesize 0
SQL> set long 99999
SQL> select dbms_metadata.get_ddl('TABLE','DY_USER_ANALYSIS_LEVEL','TLSTAT_NEWBG_STUDIO') from dual;
CREATE TABLE "TLSTAT_NEWBG_STUDIO"."DY_USER_ANALYSIS_LEVEL"
( "ID" NUMBER(11,0) NOT NULL ENABLE,
"STAT_TIME" DATE,
"GAME_TYPE" VARCHAR2(50),
"ZONE_ID" NUMBER DEFAULT -1,
"GROUP_ID" NUMBER DEFAULT -1,
"ROLE_LEVEL" NUMBER DEFAULT 0,
"DJ_GROUP_TYPE" NUMBER DEFAULT 0,
"CONSUME_CNT" NUMBER DEFAULT 0,
"CONSUME_LOST_CNT" NUMBER DEFAULT 0,
"LOGON_CNT" NUMBER DEFAULT 0,
"LOGON_LOST_CNT" NUMBER DEFAULT 0,
"CONSUME_AMOUNT" NUMBER DEFAULT 0,
"LOGON_CNT_GUID" NUMBER DEFAULT 0,
PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
STORAGE(INITIAL 16384 NEXT 8192 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
4、DDL语句改写
a.建表
CREATE TABLE "TLSTAT_NEWBG_STUDIO"."DY_USER_ANALYSIS_LEVEL_REBUILD"
( "ID" NUMBER(11,0) NOT NULL ENABLE,
"STAT_TIME" DATE,
"GAME_TYPE" VARCHAR2(50),
"ZONE_ID" NUMBER DEFAULT -1,
"GROUP_ID" NUMBER DEFAULT -1,
"ROLE_LEVEL" NUMBER DEFAULT 0,
"DJ_GROUP_TYPE" NUMBER DEFAULT 0,
"CONSUME_CNT" NUMBER DEFAULT 0,
"CONSUME_LOST_CNT" NUMBER DEFAULT 0,
"LOGON_CNT" NUMBER DEFAULT 0,
"LOGON_LOST_CNT" NUMBER DEFAULT 0,
"CONSUME_AMOUNT" NUMBER DEFAULT 0,
"LOGON_CNT_GUID" NUMBER DEFAULT 0
)
TABLESPACE "TLBB_DATA"
b.建主键
CREATE UNIQUE INDEX "TLSTAT_NEWBG_STUDIO"."PK_DY_USER_ANALYSIS_LEVEL" ON "TLSTAT_NEWBG_STUDIO"."DY_USER_ANALYSIS_LEVEL_REBUILD" ("ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
alter table TLSTAT_NEWBG_STUDIO.DY_USER_ANALYSIS_LEVEL_REBUILD add constraint PK_DY_USER_ANALYSIS_LEVEL primary key(ID) using index tablespace TLBB_INDEX;
c.建索引
CREATE INDEX TLSTAT_NEWBG_STUDIO.IND_USER_LEVLE_DD_DATE_GAME_RE on TLSTAT_NEWBG_STUDIO.DY_USER_ANALYSIS_LEVEL_REBUILD(STAT_TIME,GAME_TYPE) TABLESPACE TLBB_INDEX;
d.插入数据
SQL> insert into TLSTAT_NEWBG_STUDIO.DY_USER_ANALYSIS_LEVEL_REBUILD select * from TLSTAT_NEWBG_STUDIO.DY_USER_ANALYSIS_LEVEL order by stat_time,game_type;
SQL〉COMMIT;
e.将原来的表改名
alter table TLSTAT_NEWBG_STUDIO.DY_USER_ANALYSIS_LEVEL rename to DY_USER_ANALYSIS_LEVEL_OLD
f.将新表改名
alter table TLSTAT_NEWBG_STUDIO.DY_USER_ANALYSIS_LEVEL_REBUILD rename to DY_USER_ANALYSIS_LEVEL
校验
1、收集一下统计信息
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'TLSTAT_NEWBG_STUDIO',tabname=>'DY_USER_ANALYSIS_LEVEL');
2、
查看一下集群因子是否降下来了，可以看出来已经很大的降低了
[oracle@s2nstatdb dbm_lite]$ sh showcluster_fac.sh TLSTAT_NEWBG_STUDIO DY_USER_ANALYSIS_LEVEL
TABLE_NAME INDEX_NAME DISTINCT_KEYS CLUSTERING_FACTOR BLOCKS NUM_ROWS LAST_ANALYZED
DY_USER_ANALYSIS_LEVEL IND_USER_LEVLE_DD_DATE_GAME_ 35 3723 3772 493731 2017-08-30 16:07:04
RE
DY_USER_ANALYSIS_LEVEL PK_DY_USER_ANALYSIS_LEVEL 493731 114430 3772 493731 2017-08-30 16:07:03
3、再看一下执行计划是否走索引
SQL> explain plan for select LOGON_CNT_GUID from DY_USER_ANALYSIS_LEVEL where STAT_TIME = to_date('2016-07-09','yyyy-mm-dd') and game_type='tlbb' and ROLE_LEVEL=3 and DJ_GROUP_TYPE=1 and GROUP_ID=to_number(-1);
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
Plan hash value: 2737695412
| Id | Operation | Name | Rows | B
ytes | Cost (%CPU)| Time |
PLAN_TABLE_OUTPUT
| 0 | SELECT STATEMENT | | 1 |
88 | 74 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| DY_USER_ANALYSIS_LEVEL | 1 |
88 | 74 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_USER_LEVLE_DD_DATE_GAME_RE | 8143 |
| 56 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
Predicate Information (identified by operation id):
1 - filter("ROLE_LEVEL"=3 AND "DJ_GROUP_TYPE"=1 AND "GROUP_ID"=(-1))
2 - access("STAT_TIME"=TO_DATE(' 2016-07-09 00:00:00', 'syyyy-mm-dd hh24:mi:s
s') AND
"GAME_TYPE"='tlbb')
PLAN_TABLE_OUTPUT
Note
- dynamic sampling used for this statement
20 rows selected.
