大表更新的性能问题
最简单的更新一个列,供测试对比时间用的:
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
盼高手指点
