分区表性能窥测
分区表A 每行数据大约 1k，表中总共有 1亿 乃至 10亿条数据，对分区表 做 查询、更新、删除操作时 性能如何？
SQL> alter system flush buffer_cache;
SQL> alter system flush shared_pool;
SQL> set linesize 1000
SQL> set linesize 1000
SQL> set pagesize 1000
SQL> set timing on
SQL> set autotrace traceonly
SQL> 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.08
Execution 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
