分区表性能研究0802
--更新操作
SQL> update sk_nsrxx set nsrmc='AA' where jzswjg='11100';
普通表 分区表
SQL> update sk_nsrxx set nsrmc='AA' where jzswjg='11100'; SQL> update sk_nsrxx set nsrmc='AA' where jzswjg='11100';
3538944 rows updated. 3538944 rows updated.
Elapsed: 00:03:05.48 Elapsed: 00:07:54.31
Execution Plan Execution Plan
---------------------------------------------------------- ----------------------------------------------------------
Plan hash value: 4044940488 Plan hash value: 4206436227
------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 6077K| 614M| 84480 (1)| 00:16:54 | | 0 | UPDATE STATEMENT | | 5 | 110 | 2 (0)| 00:00:01 | | |
| 1 | UPDATE | SK_NSRXX | | | | | | 1 | UPDATE | SK_NSRXX | | | | | | |
|* 2 | TABLE ACCESS FULL| SK_NSRXX | 6077K| 614M| 84480 (1)| 00:16:54 | | 2 | PARTITION LIST SINGLE| | 5 | 110 | 1 (0)| 00:00:01 | KEY | KEY |
------------------------------------------------------------------------------- |* 3 | INDEX RANGE SCAN | SK_NSRXX_RANGE_INDEX_LOCAL | 5 | 110 | 1 (0)| 00:00:01 | 5 | 5 |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------- Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("JZSWJG"='11100')
3 - access("JZSWJG"='11100')
Note
-----
- dynamic sampling used for this statement (level=2) Statistics
----------------------------------------------------------
4506 recursive calls
Statistics 4672156 db block gets
---------------------------------------------------------- 27091 consistent gets
560 recursive calls 67128 physical reads
3646884 db block gets 1150397824 redo size
617487 consistent gets 836 bytes sent via SQL*Net to client
615032 physical reads 807 bytes received via SQL*Net from client
355051792 redo size 3 SQL*Net roundtrips to/from client
847 bytes sent via SQL*Net to client 2 sorts (memory)
807 bytes received via SQL*Net from client 0 sorts (disk)
3 SQL*Net roundtrips to/from client 3538944 rows processed
1 sorts (memory)
0 sorts (disk)
3538944 rows processed
--删除操作
SQL> delete from sk_nsrxx where jzswjg='11100';
普通表 分区表
SQL> delete from sk_nsrxx where jzswjg='11100'; SQL> delete from sk_nsrxx where jzswjg='11100';
3538944 rows deleted. 3538944 rows deleted.
Elapsed: 00:02:33.31 Elapsed: 00:04:48.08
Execution Plan Execution Plan
---------------------------------------------------------- ----------------------------------------------------------
Plan hash value: 2217958795 Plan hash value: 3254633785
------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 3416 | 13664 | 84303 (1)| 00:16:52 | | 0 | DELETE STATEMENT | | 5 | 120 | 1 (0)| 00:00:01 | | |
| 1 | DELETE | SK_NSRXX | | | | | | 1 | DELETE | SK_NSRXX | | | | | | |
|* 2 | TABLE ACCESS FULL| SK_NSRXX | 3416 | 13664 | 84303 (1)| 00:16:52 | | 2 | PARTITION LIST SINGLE| | 5 | 120 | 1 (0)| 00:00:01 | KEY | KEY |
------------------------------------------------------------------------------- |* 3 | INDEX RANGE SCAN | SK_NSRXX_RANGE_INDEX_LOCAL | 5 | 120 | 1 (0)| 00:00:01 | 5 | 5 |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------- Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("JZSWJG"='11100')
3 - access("JZSWJG"='11100')
Note
-----
- dynamic sampling used for this statement (level=2) Statistics
----------------------------------------------------------
4420 recursive calls
Statistics 4000329 db block gets
---------------------------------------------------------- 14827 consistent gets
4064 recursive calls 85333 physical reads
3921660 db block gets 1338492356 redo size
311839 consistent gets 845 bytes sent via SQL*Net to client
308857 physical reads 797 bytes received via SQL*Net from client
1272252040 redo size 3 SQL*Net roundtrips to/from client
849 bytes sent via SQL*Net to client 2 sorts (memory)
797 bytes received via SQL*Net from client 1 sorts (disk)
3 SQL*Net roundtrips to/from client 3538944 rows processed
1 sorts (memory)
0 sorts (disk)
3538944 rows processed
