分区表A 每行数据大约 1k，表中总共有 1亿 乃至 10亿条数据，对分区表 做 查询、更新、删除操作时 性能如何？ SQL> alter system flush buffer_cache;SQL> alter system flush shared_pool;SQL> set linesize 1000SQL> set linesize 1000SQL> set pagesize 1000SQL> set timing onSQL> set autotrace traceonlySQL> set timing on*****************************************************************************************************************--插入操作SQL> insert into sk_nsrxx select * from sk_nsrxx;普通表 分区表 SQL> insert into sk_nsrxx select * from sk_nsrxx; SQL> insert into sk_nsrxx select * from sk_nsrxx;15990784 rows created. 15990784 rows created.Elapsed: 01:12:07.57 Elapsed: 01:22:35.08Execution Plan Execution Plan---------------------------------------------------------- ----------------------------------------------------------Plan hash value: 1329503975 Plan hash value: 1091440269------------------------------------------------------------------------------------- -----------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |------------------------------------------------------------------------------------- -----------------------------------------------------------------------------------------------------| 0 | INSERT STATEMENT | | 57M| 20G| 168K (1)| 00:33:43 | | 0 | INSERT STATEMENT | | 61 | 8479 | 6 (0)| 00:00:01 | | || 1 | LOAD TABLE CONVENTIONAL | SK_NSRXX | | | | | | 1 | LOAD TABLE CONVENTIONAL | SK_NSRXX | | | | | | || 2 | TABLE ACCESS FULL | SK_NSRXX | 57M| 20G| 168K (1)| 00:33:43 | | 2 | PARTITION LIST ALL | | 61 | 8479 | 6 (0)| 00:00:01 | 1 | 37------------------------------------------------------------------------------------- | 3 | TABLE ACCESS FULL | SK_NSRXX | 61 | 8479 | 6 (0)| 00:00:01 | 1 | 37 | -----------------------------------------------------------------------------------------------------Note----- - dynamic sampling used for this statement (level=2)Statistics Statistics---------------------------------------------------------- ---------------------------------------------------------- 3984 recursive calls 147758 recursive calls 3178650 db block gets 14935191 db block gets 8328674 consistent gets 1930854 consistent gets 375502 physical reads 313872 physical reads 2503246152 redo size 8292506572 redo size 837 bytes sent via SQL*Net to client 834 bytes sent via SQL*Net to client 799 bytes received via SQL*Net from client 799 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 3 SQL*Net roundtrips to/from client 33 sorts (memory) 205 sorts (memory) 0 sorts (disk) 0 sorts (disk) 15990784 rows processed 15990784 rows processed*****************************************************************************************************************--查询操作SQL> select count(1) from sk_nsrxx; 普通表 分区表 SQL> select count(1) from sk_nsrxx; SQL> select count(1) from sk_nsrxx; COUNT(1) COUNT(1) ---------- ---------- 15990784 15990784 Elapsed: 00:01:55.09 Elapsed: 00:01:54.26 Execution Plan Execution Plan ---------------------------------------------------------- ---------------------------------------------------------- Plan hash value: 524156760 Plan hash value: 946226060 ----------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | | Id | Operation | Name | Rows | Cost (%CPU)| Time | Pstart| Pstop |----------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 84245 (1)| 00:16:51 | | 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 | | | | 1 | SORT AGGREGATE | | 1 | | | | 1 | SORT AGGREGATE | | 1 | | | | || 2 | TABLE ACCESS FULL| SK_NSRXX | 16M| 84245 (1)| 00:16:51 | | 2 | PARTITION LIST ALL | | 61 | 3 (0)| 00:00:01 | 1 | 37 |----------------------------------------------------------------------- | 3 | INDEX FAST FULL SCAN| SK_NSRXX_RANGE_INDEX_LOCAL | 61 | 3 (0)| 00:00:01 | 1 | 37 | ------------------------------------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) Statistics Statistics---------------------------------------------------------- ---------------------------------------------------------- 0 recursive calls 4162 recursive calls 0 db block gets 0 db block gets 309511 consistent gets 58022 consistent gets 309503 physical reads 57165 physical reads 0 redo size 0 redo size 529 bytes sent via SQL*Net to client 529 bytes sent via SQL*Net to client 523 bytes received via SQL*Net from client 523 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 25 sorts (memory) 0 sorts (disk) 0 sorts (disk) 1 rows processed 1 rows processed Elapsed: 00:22:28.06 *****************************************************************************************************************--更新操作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
转载于:https://www.cnblogs.com/iyoume2008/p/9174875.html
相关资源：MySQL 5.5.8 分区表性能测试