大表更新如何调整性能

又是违规昵称 2008-10-29 01:48:01
好久不提问了,大家这次帮我

大表更新的性能问题

最简单的更新一个列,供测试对比时间用的:


13:37:43 SQL> update t_employees set headcount = 2;



70236 rows updated.



Elapsed: 00:00:42.45



Execution Plan

----------------------------------------------------------

Plan hash value: 926905594



----------------------------------------------------------------------------------

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

----------------------------------------------------------------------------------

|   0 | UPDATE STATEMENT   |             | 70236 |   137K|  1015   (1)| 00:00:13 |

|   1 |  UPDATE            | T_EMPLOYEES |       |       |            |          |

|   2 |   TABLE ACCESS FULL| T_EMPLOYEES | 70236 |   137K|  1015   (1)| 00:00:13 |

----------------------------------------------------------------------------------





Statistics

----------------------------------------------------------

         73  recursive calls

      87397  db block gets

      85612  consistent gets

       9698  physical reads

   26424568  redo size

        555  bytes sent via SQL*Net to client

        560  bytes received via SQL*Net from client

          4  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

      70236  rows processed


这个42秒,慢了点

然后是需要调整的sql,分两部分,一部分是插入临时表t_emp_headcount,另一部分是更新目标表t_employees:
插入不慢,更新很慢,不能容忍:


10:47:15 SQL> INSERT INTO T_EMP_HEADCOUNT

10:47:22   2    SELECT MAX(A.LV)-1 HEADCOUNT, A.ROOTEMP FROM

10:47:22   3      ( SELECT LEVEL LV, CONNECT_BY_ROOT CORPID ROOTEMP

10:47:22   4        FROM T_EMPLOYEES

10:47:22   5        CONNECT BY NOCYCLE PRIOR CORPID = MGR_CORPID) A

10:47:22   6       GROUP BY A.ROOTEMP;



70236 rows created.



Elapsed: 00:00:08.90



Execution Plan

----------------------------------------------------------

Plan hash value: 1388359246



----------------------------------------------------------------------------------------------

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

----------------------------------------------------------------------------------------------

|   0 | INSERT STATEMENT               |             | 70236 |  1714K|  1017   (1)| 00:00:13 |

|   1 |  HASH GROUP BY                 |             | 70236 |  1714K|  1017   (1)| 00:00:13 |

|   2 |   VIEW                         |             | 70236 |  1714K|  1013   (1)| 00:00:13 |

|*  3 |    CONNECT BY WITHOUT FILTERING|             |       |       |            |          |

|   4 |     TABLE ACCESS FULL          | T_EMPLOYEES | 70236 |  1097K|  1013   (1)| 00:00:13 |

----------------------------------------------------------------------------------------------



Predicate Information (identified by operation id):

---------------------------------------------------



   3 - access("MGR_CORPID"=PRIOR "CORPID")





Statistics

----------------------------------------------------------

         46  recursive calls

       1010  db block gets

       4808  consistent gets

        285  physical reads

     200924  redo size

        750  bytes sent via SQL*Net to client

       1127  bytes received via SQL*Net from client

          6  SQL*Net roundtrips to/from client

          2  sorts (memory)

          0  sorts (disk)

      70236  rows processed



10:47:33 SQL> UPDATE T_EMPLOYEES SET HEADCOUNT =

10:47:44   2  ( SELECT T_EMP_HEADCOUNT.HEADCOUNT

10:47:44   3        FROM T_EMP_HEADCOUNT

10:47:44   4        WHERE T_EMPLOYEES.CORPID = T_EMP_HEADCOUNT.ROOTEMP

10:47:45   5  );



70236 rows updated.



Elapsed: 00:13:27.44



Execution Plan

----------------------------------------------------------

Plan hash value: 2707919579



--------------------------------------------------------------------------------------

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

--------------------------------------------------------------------------------------

|   0 | UPDATE STATEMENT   |                 | 70236 |   685K|  1015   (1)| 00:00:13 |

|   1 |  UPDATE            | T_EMPLOYEES     |       |       |            |          |

|   2 |   TABLE ACCESS FULL| T_EMPLOYEES     | 70236 |   685K|  1015   (1)| 00:00:13 |

|*  3 |   TABLE ACCESS FULL| T_EMP_HEADCOUNT |     1 |    25 |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------------------



Predicate Information (identified by operation id):

---------------------------------------------------



   3 - filter("T_EMP_HEADCOUNT"."ROOTEMP"=:B1)





Statistics

----------------------------------------------------------

         81  recursive calls

     125523  db block gets

   15640727  consistent gets

         31  physical reads

   32018776  redo size

        756  bytes sent via SQL*Net to client

       1053  bytes received via SQL*Net from client

          6  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

      70236  rows processed


我oracle是10.2.0.2.0,
操作系统是solaris10

盼高手指点

...全文
287 21 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
21 条回复
切换为时间正序
请发表友善的回复…
发表回复
sleepzzzzz 2008-10-30
  • 打赏
  • 举报
 回复
:),也学习了
又是违规昵称 2008-10-30
  • 打赏
  • 举报
 回复
作了两点修改, 效率有提高:

1. 加了exists子句
2. 对t_emp_headcount建了索引

诚挚感谢帮助我的同志们,
谢谢你们
codearts 2008-10-29
  • 打赏
  • 举报
 回复
T_EMP_HEADCOUNT 看你的rootemp有多少个不同的值，如果有很多不同的值，我认为还是可以建上。

我建议先把这个表先改成一个普通的表，建索引，分析，再试试你的sql.

从你的sql上来看，确实没什么好的优化了。T_EMPLOYEES应该有主键的吧，可以试试这种写法（可以看看他的执行计划）：

update

(

    SELECT A.headcount a, T_EMP_HEADCOUNT.HEADCOUNT b 

      FROM T_EMPLOYEES A, T_EMP_HEADCOUNT

     WHERE A.CORPID = T_EMP_HEADCOUNT.ROOTEMP

)

set a=b;


另外就是我猜测的高水位问题。 alter table t_employees move 是回缩高水位。 不知道这样之后你的sql执行情况是怎么样的。
又是违规昵称 2008-10-29
  • 打赏
  • 举报
 回复
[Quote=引用 17 楼 codearts 的回复:]
WHERE T_EMPLOYEES.CORPID = T_EMP_HEADCOUNT.ROOTEMP
T_EMP_HEADCOUNT.rootemp上有索引吗？合适建索引吗？

1)你的第一个sql执行要40秒，但只有7W条记录，明显不合理。
要不是你的机器太差，要不就是有HWM的问题。
sql>alter table T_EMPLOYEES move;
然后再试试，会不会快点

2)你最原始的sql语句（不用临时表的）怎么写的？
[/Quote]

我的t_emp_headcount是临时表,on commit delete rows的那种, 建索引好像不合适吧?
而且全表扫描并不慢,代价只有2



1)
t_employees的update要42秒我也觉得能优化,那麻烦你再提点建议呗?

sql>alter table T_EMPLOYEES move;

你的意思是让我转移下表空间或者是回缩高水位吗?
这个表的数据组织应该是没有问题的,刚imp进去的测试的

2) 我已经放弃原先的那个计划了


谢谢你的回复

codearts 2008-10-29
  • 打赏
  • 举报
 回复
WHERE T_EMPLOYEES.CORPID = T_EMP_HEADCOUNT.ROOTEMP
T_EMP_HEADCOUNT.rootemp上有索引吗？合适建索引吗？

1)你的第一个sql执行要40秒，但只有7W条记录，明显不合理。
要不是你的机器太差，要不就是有HWM的问题。
sql>alter table T_EMPLOYEES move;
然后再试试，会不会快点

2)你最原始的sql语句（不用临时表的）怎么写的？
又是违规昵称 2008-10-29
  • 打赏
  • 举报
 回复
[Quote=引用 12 楼 sleepzzzzz 的回复:]
SQL code感觉你的sql有问题，你试试下面：UPDATET_EMPLOYEESSETHEADCOUNT=(SELECTT_EMP_HEADCOUNT.HEADCOUNTFROMT_EMP_HEADCOUNTWHERET_EMPLOYEES.CORPID=T_EMP_HEADCOUNT.ROOTEMP);---你上面这句改为:UPDATET_EMPLOYEES aSETHEADCOUNT=(SELECTT_EMP_HEADCOUNT.HEADCOUNTFROMT_EMP_HEADCOUNTWHERET_EMPLOYEES.CORPID=T_EMP_HEADCOUNT.ROOTEMP)whereexists(select1fromT_EMP_HEADCOUNTwhereROOTEMP=a.CORPID);


[/Quote]



16:05:34 SQL> UPDATE T_EMPLOYEES EMP SET HEADCOUNT =

           2  ( SELECT T_EMP_HEADCOUNT.HEADCOUNT

           3        FROM T_EMP_HEADCOUNT

           4        WHERE EMP.CORPID = T_EMP_HEADCOUNT.ROOTEMP

           5  )

           6  WHERE EXISTS

           7  (

           8    SELECT 1 FROM T_EMP_HEADCOUNT WHERE ROOTEMP = EMP.CORPID

           9  );

 

 

Executed in 742.532 seconds


性能差不多
又是违规昵称 2008-10-29
  • 打赏
  • 举报
 回复
[Quote=引用 12 楼 sleepzzzzz 的回复:]
SQL code感觉你的sql有问题，你试试下面：UPDATET_EMPLOYEESSETHEADCOUNT=(SELECTT_EMP_HEADCOUNT.HEADCOUNTFROMT_EMP_HEADCOUNTWHERET_EMPLOYEES.CORPID=T_EMP_HEADCOUNT.ROOTEMP);---你上面这句改为:UPDATET_EMPLOYEES aSETHEADCOUNT=(SELECTT_EMP_HEADCOUNT.HEADCOUNTFROMT_EMP_HEADCOUNTWHERET_EMPLOYEES.CORPID=T_EMP_HEADCOUNT.ROOTEMP)whereexists(select1fromT_EMP_HEADCOUNTwhereROOTEMP=a.CORPID);


[/Quote]

我这个update的sql没加exists子句看起来确实比较别扭,
但是更新功能确实都实现了并且验证过了

但是不知道没有exists会不会引起性能的问题,
我现在加了正在测, sql暂时还没跑完
YY_MM_DD 2008-10-29
  • 打赏
  • 举报
 回复
还可以通过插入速度比更新速度快，以及简单更新比条件更新快的特点创建表来操作。


－－改正下

UPDATE (SELECT [需要的列名..T_EMP_HEADCOUNT.HEADCOUNT,T_EMPLOYEES.HEADCOUNT ]

          FROM T_EMP_HEADCOUNT,T_EMPLOYEES

        WHERE T_EMPLOYEES.CORPID = T_EMP_HEADCOUNT.ROOTEMP) a SET a.T_EMP_HEADCOUNT.HEADCOUNT =a.T_EMPLOYEES.HEADCOUNT 



－－方法二

create table ttt as SELECT  [需要的列名..T_EMP_HEADCOUNT.HEADCOUNT,T_EMPLOYEES.HEADCOUNT ] FROM T_EMP_HEADCOUNT WHERE T_EMPLOYEES.CORPID = T_EMP_HEADCOUNT.ROOTEMP;

delete from T_EMPLOYEES WHERE T_EMPLOYEES.CORPID = T_EMP_HEADCOUNT.ROOTEMP;

update ttt set ttt.T_EMP_HEADCOUNT.HEADCOUNT=ttt.T_EMPLOYEES.HEADCOUNT;

insert into T_EMPLOYEES as select * from ttt;
YY_MM_DD 2008-10-29
  • 打赏
  • 举报
 回复
--虚拟一张表来进行操作，在数据量大的情况下效率还可以


UPDATE (SELECT T_EMP_HEADCOUNT.HEADCOUNT

          FROM T_EMP_HEADCOUNT,T_EMPLOYEES

        WHERE T_EMPLOYEES.CORPID = T_EMP_HEADCOUNT.ROOTEMP) a SET HEADCOUNT =a.HEADCOUNT
sleepzzzzz 2008-10-29
  • 打赏
  • 举报
 回复 
感觉你的sql有问题，你试试下面：



UPDATE T_EMPLOYEES SET HEADCOUNT =

 (SELECT T_EMP_HEADCOUNT.HEADCOUNT

        FROM T_EMP_HEADCOUNT

       WHERE T_EMPLOYEES.CORPID = T_EMP_HEADCOUNT.ROOTEMP);



---你上面这句改为:



UPDATE T_EMPLOYEES a SET HEADCOUNT =

 (SELECT T_EMP_HEADCOUNT.HEADCOUNT

        FROM T_EMP_HEADCOUNT

       WHERE T_EMPLOYEES.CORPID = T_EMP_HEADCOUNT.ROOTEMP)

where exists(select 1 from T_EMP_HEADCOUNT where ROOTEMP = a.CORPID);


YY_MM_DD 2008-10-29
  • 打赏
  • 举报
 回复
很简单的问题呀。简单的update语句相当与逐行执行，所以他相当于是搜索条件最好的情况
带条件的他就得对你的表整表扫描后在执行更新数据。如果记录有2万条，恰好你找的记录在
最后一条你说速度会慢不？这就是一个时间复杂度问题了。。
YY_MM_DD 2008-10-29
  • 打赏
  • 举报
 回复
采用oracle Stream吧!
又是违规昵称 2008-10-29
  • 打赏
  • 举报
 回复
[Quote=引用 7 楼 sleepzzzzz 的回复:]
update t_employees set headcount = 2;
这你句都不能快的话,那没有其它相同功能的语句能更快!

索引用不上,analyze table statistics再试试
也有可能象你所说的需要update的数据太多了，占到表总记录一定比例．

[/Quote]

表已经用dbms_stats.gather_table_stats分析过了

我觉得简单的update能在一分钟内完成,
为什么按条件update会要13分钟,因为t_emp_headcount是小表,还是临时表啊

大家再帮想想办法,谢谢了
又是违规昵称 2008-10-29
  • 打赏
  • 举报
 回复
[Quote=引用 3 楼 linzhangs 的回复:]
SQL codeUPDATET_EMPLOYEESSETHEADCOUNT=(SELECTT_EMP_HEADCOUNT.HEADCOUNTFROMT_EMP_HEADCOUNTWHERET_EMPLOYEES.CORPID=T_EMP_HEADCOUNT.ROOTEMP）whereexistsselect1fromT_EMP_HEADCOUNT,T_EMPLOYEESwhereT_EMPLOYEES.CORPID=T_EMP_HEADCOUNT.ROOTEMP
[/Quote]

UPDATE T_EMPLOYEES SET HEADCOUNT =
( SELECT T_EMP_HEADCOUNT.HEADCOUNT
FROM T_EMP_HEADCOUNT
WHERE T_EMPLOYEES.CORPID = T_EMP_HEADCOUNT.ROOTEMP)
where exists
( select 1 from T_EMP_HEADCOUNT,T_EMPLOYEES where T_EMPLOYEES.CORPID = T_EMP_HEADCOUNT.ROOTEMP);

忘了说, exists我这里用不上,
因为t_employees和t_emp_headcount表里的corpid是一一对应的,每个都找得到

但还是谢谢你的回复
sleepzzzzz 2008-10-29
  • 打赏
  • 举报
 回复
update t_employees set headcount = 2;
这你句都不能快的话,那没有其它相同功能的语句能更快!

索引用不上,analyze table statistics再试试
也有可能象你所说的需要update的数据太多了，占到表总记录一定比例．



范佩西_11 2008-10-29
  • 打赏
  • 举报
 回复
定时commit；
又是违规昵称 2008-10-29
  • 打赏
  • 举报
 回复
以前是没有临时表的,
插入和更新在一个sql里做,
这个一共执行了75分钟

后来才建的这个临时表

最简单的update都需要42.45秒, 这个大家能不能想下优化的办法?
范佩西_11 2008-10-29
  • 打赏
  • 举报
 回复
这种建临时表的应该不如不建吧
范佩西_11 2008-10-29
  • 打赏
  • 举报
 回复 
UPDATE T_EMPLOYEES SET HEADCOUNT =

 ( SELECT T_EMP_HEADCOUNT.HEADCOUNT

      FROM T_EMP_HEADCOUNT

      WHERE T_EMPLOYEES.CORPID = T_EMP_HEADCOUNT.ROOTEMP） where exists select 1 from T_EMP_HEADCOUNT,T_EMPLOYEES where T_EMPLOYEES.CORPID = T_EMP_HEADCOUNT.ROOTEMP
又是违规昵称 2008-10-29
  • 打赏
  • 举报
 回复
t_employees表的记录和t_emp_headcount的记录都在7W左右,
前一个是普通表,有60多个列,
后一个是临时表,只有两个列

加载更多回复（1）
数据库开发 维护 性能调优 备份还原 各种复杂问题的解决方案
这包括选择合适的数据库管理系统（如MySQL、Oracle、SQL Server或MongoDB），设计数据模型（如关系型模型或NoSQL模型），编写SQL语句来定义表结构，设置索引以优化查询性能，以及创建存储过程和触发器以实现复杂的...
phpbb论坛2.03-2.04升级文件
- 完成数据库和表结构的更新，这可能涉及添加、修改或删除某些字段，以适应新版本的功能需求。 - 更新配置文件，可能涉及到核心设置的调整，如URL路径、邮件设置等。 - 验证升级成功，测试新功能和界面是否正常，...
MySQL-性能优化_大表和大事务的常用处理方案
文章目录影响MySQL性能的几大因素大表带来的风险大事务带来的风险 影响MySQL性能的几大因素 通常来说 ，有以下几点 慢SQL（重点） 、 主机的硬件资源（CPU、内存、磁盘I/O等）、网卡流量等等 超高的QPS和 TPS ...
怎样在Postgres中优化查询性能，特别是对于大数据量的表？
PostgreSQL（通常简称为Postgres）作为一个...通过合理的索引设计、查询优化、分区表、硬件配置和参数调整以及定期维护，可以显著提高大数据量表的查询性能。这可以提高查询性能，因为查询可以只针对相关的分区进行。
MySQL 大表性能优化
除非单表数据未来会一直不断上涨，否则不要一开始就考虑拆分，拆分会带来逻辑、部署、运维的各种复杂度，一般以整型值为主的表在千万级以下，字符串为主的表在五百万以下是没有太大问题的。而事实上很多时候MySQL单...
Oracle 高级技术

3,494

社区成员

18,710

社区内容

发帖
与我相关
我的任务
社区描述
Oracle 高级技术相关讨论专区
社区管理员
  • 高级技术社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章