分区表性能窥测
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了分区表性能窥测，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含10690字，纯文字阅读大概需要16分钟。
内容图文
分区表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
原文：https://www.cnblogs.com/iyoume2008/p/9174875.html
内容总结
以上是互联网集市为您收集整理的分区表性能窥测全部内容，希望文章能够帮你解决分区表性能窥测所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。