oracle中一个表update慢的优化

来源：互联网发布：c语言打印long 编辑：程序博客网时间：2024/06/04 18:28

案例简述

数据库：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.

阅读全文

1 0