Oracle海量数据优化-01分区的渊源
生活随笔
收集整理的這篇文章主要介紹了
Oracle海量数据优化-01分区的渊源
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
- 思維導圖
- 概述
- DELETE與系統資源
- delete 操作消耗的資源
- truncate 和 drop 操作消耗的資源
- DML操作會隨著數據量的增加消耗的資源也會相應的增加
- DELETE與釋放空間
思維導圖
概述
delete from t where create_time < to_date('2017-01-01','yyyy-mm-dd');當我們看到這條語句時，會想到什么呢？ 一條再簡單不過的按照條件刪除數據庫的操作。 如果大量存在，會不會引起系統性能問題呢？
當這個表的數據足夠大時，按照這樣的方式來清除數據，代價無疑是非常高昂的。
我們提出使用分區的方式來解決這個問題。比如 保留歷史數據的骨折是，刪除最早一個月的數據，按照這個需求，將需要刪除數據的表，改為分區表。 按照每個月一個分區的方式創建分區表。
這樣做的結果是，以前的delete操作就變成了下面的操作：
alter table t drop partition p_name或者 alter table t truncate partiton p_name ;這個操作對已一個幾GB數據的分區表來講，執行時間不超過10S。
比起使用分區來操作數據，DELETE方式操作數據的問題是顯而易見的，至少存在如下兩點：
- 消耗大量的系統資源
- 無法釋放空間
DELETE與系統資源
delete 操作消耗的資源
在DELETE操作中，SQL語句首先要掃描表或者索引找到符合條件的記錄，然后把他們刪除，這個過程中將消耗大量的CPU資源和產生大量的I/O，同時還會產生大量的undo數據。
下面是一個簡單的栗子，我們可以看出delete相對于drop或者truncate對資源的消耗情況：
SQL> drop table t purge ; ---> 不放入回收站，直接刪除Table droppedSQL> create table t as select a.OBJECT_ID,a.OBJECT_NAME from dba_objects a;Table createdSQL> exec dbms_stats.gather_table_stats(user,'t');---->表分析PL/SQL procedure successfully completedSQL>注釋：執行drop table xx 語句
drop后的表被放在回收站(user_recyclebin)里，而不是直接刪除掉。這樣，回收站里的表信息就可以被恢復，或徹底清除。
通過查詢回收站user_recyclebin獲取被刪除的表信息，然后使用語句
flashback table <user_recyclebin.object_name or user_recyclebin.original_name> to before drop [rename to
];
將回收站里的表恢復為原名稱或指定新名稱，表中數據不會丟失。
若要徹底刪除表，則使用語句：drop table <table_name> purge;
清除回收站里的信息
清除指定表：purge table <table_name>;
清除當前用戶的回收站：purge recyclebin;
SQL> purge recyclebin;
Done
清除所有用戶的回收站：purge dba_recyclebin;
不放入回收站，直接刪除則是：drop table xx purge;
oracle@entel1:[/oracle]$sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 17 22:17:53 2017Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> conn xgj/smart Connected. SQL> SQL> set autotrace trace exp stat; -->顯示執行計劃和統計結果 SQL> delete from t where t.object_id < 10000;9811 rows deleted.Execution Plan ---------------------------------------------------------- Plan hash value: 3335594643--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | DELETE STATEMENT | | 4420 | 22100 | 43 (0)| 00:00:01 | | 1 | DELETE | T | | | | | |* 2 | TABLE ACCESS FULL| T | 4420 | 22100 | 43 (0)| 00:00:01 | ---------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("T"."OBJECT_ID"<10000)Statistics ---------------------------------------------------------- 18 recursive calls 10465 db block gets 162 consistent gets 0 physical reads 2640572 redo size 831 bytes sent via SQL*Net to client 797 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 9811 rows processedSQL>
SET AUTOT 用法
用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]]
[STAT[ISTICS]]
SET AUTOT[RACE] OFF 停止AutoTrace SET AUTOT[RACE] ON
開啟AutoTrace，顯示AUTOTRACE信息和SQL執行結果 SET AUTOT[RACE] TRACEONLY
開啟AutoTrace，僅顯示AUTOTRACE信息 SET AUTOT[RACE] ON EXPLAIN
開啟AutoTrace，僅顯示AUTOTRACE的EXPLAIN信息 SET AUTOT[RACE] ON
STATISTICS開啟AutoTrace，僅顯示AUTOTRACE的STATISTICS信息
在這種情況下，DELETE操作產生了 10465 db block gets + 162 consistent gets 數據塊讀，同時產生了 2640572 redo size 的 redo日志 (2640572 /1024/1024 = 2.5M 大小)
即使我們給這個表創建了索引，依然無法避免這種資源消耗
SQL> rollback;Rollback complete.SQL> create index ind_t on t(object_id);Index created.SQL> exec dbms_stats.gather_index_stats(user,'ind_t');PL/SQL procedure successfully completed.SQL> delete from t where t.object_id < 10000;9811 rows deleted.Execution Plan ---------------------------------------------------------- Plan hash value: 3974964266--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | DELETE STATEMENT | | 4420 | 22100 | 11 (0)| 00:00:01 | | 1 | DELETE | T | | | | | |* 2 | INDEX RANGE SCAN| IND_T | 4420 | 22100 | 11 (0)| 00:00:01 | ---------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("T"."OBJECT_ID"<10000)Statistics ---------------------------------------------------------- 15 recursive calls 10596 db block gets 36 consistent gets 0 physical reads 2801592 redo size 837 bytes sent via SQL*Net to client 796 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 9811 rows processedSQL> rollback;Rollback complete.可以看到即使加了索引，資源的消耗情況差不多，都是 10596 db block gets + 36 consistent gets 。
truncate 和 drop 操作消耗的資源
相反的 truncate 和 drop 操作消耗的資源就小很多。
案例
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 Connected as xxx@xgjSQL> --->創建分區表 SQL> create table t_part (object_id int ,object_name varchar2(2000) )2 partition by range(object_id)3 (4 partition p1 values less than (6000),5 partition p2 values less than (12000),6 partition p3 values less than (18000),7 partition p4 values less than (24000),8 partition p5 values less than (30000),9 partition pm values less than (maxvalue)10 );Table createdSQL> insert into t_part select object_id ,object_name from dba_objects;35238 rows insertedSQL> commit;Commit completeSQL> select count(1) from t_part partition(p1);COUNT(1) ----------5995SQL>下面我們使用sql_trace來跟蹤下 drop和truncate 之類的DDL操作的相關信息。
SQL> alter session set tracefile_identifier='xgj_20170118';Session alteredSQL> alter session set sql_trace=true;Session alteredSQL> alter table t_part truncate partition p1;Table truncatedSQL> truncate table t_part;Table truncatedSQL> insert into t_part select object_id ,object_name from dba_objects;35238 rows insertedSQL> commit;Commit completeSQL> alter table t_part drop partition p1;Table alteredSQL> alter session set sql_trace=false;Session alteredSQL>開啟SQL跟蹤后，會生成一個trace文件，通過初始化參數user_dump_dest配置其所在目錄，該參數的值可以通過下面方法獲取到：
SQL> select name, value from v$parameter where name = 'user_dump_dest' ;NAME VALUE --------------------------- ----------------------------- user_dump_dest /oracle/diag/rdbms/cc/cc/trace以上是產生的trace原文件，我們通過tkprof來查看一下：
oracle@entel1:[/oracle/diag/rdbms/cc/cc/trace]$tkprof cc_ora_14104_xgj_20170118.trc cc_ora_14104_xgj_20170118.log explain=xxx/xgj sys=no ;TKPROF: Release 11.2.0.4.0 - Development on Wed Jan 18 16:35:18 2017Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.oracle@entel1:[/oracle/diag/rdbms/cc/cc/trace]$以下是截取cc_ora_14104_xgj_20170118.log中的關鍵信息：
truncate partition p1
********************************************************************************SQL ID: 4gj9z8z8707sx Plan Hash: 491940592alter table t_part truncate partition p1call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 5 0 0 Execute 1 0.01 0.38 20 1 69 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.01 0.38 20 6 69 0Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 38 (xxx) ********************************************************************************truncate table
********************************************************************************SQL ID: cv84y6hnt1dy2 Plan Hash: 491940592truncate table t_partcall count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.02 1.54 80 4 266 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.02 1.54 80 4 266 0Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 38 (xxx) ********************************************************************************drop partition p1
********************************************************************************SQL ID: 7wwjnfwgcxgza Plan Hash: 0alter table t_part drop call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.69 1 1 10 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.69 1 1 10 0Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 38 (xxx)從結果可以看到，DDL對分區操作消耗的資源要遠小于DML的
| delete | 1000+ |
| truncate | 6 |
| drop | 1 |
可以看出DDL的操作要遠小于DML消耗的資源。 DDL的操作主要是對數據字典的修改，這個值基本上是確定的，而DML會隨著數據量的增加，消耗的資源也會相應的增加
DML操作會隨著數據量的增加，消耗的資源也會相應的增加
oracle@entel1:[/oracle/diag/rdbms/cc/cc/trace]$sqlplus /nologSQL*Plus: Release 11.2.0.4.0 Production on Wed Jan 18 18:04:57 2017Copyright (c) 1982, 2013, Oracle. All rights reserved.SQL> conn xxx/xgj Connected. SQL> select count(1) from t; COUNT(1) ---------- 35249SQL> set autotrace trace exp stat ; SQL> delete from t where object_id < 10000;9811 rows deleted.Execution Plan ---------------------------------------------------------- Plan hash value: 3974964266--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | DELETE STATEMENT | | 4420 | 22100 | 11 (0)| 00:00:01 | | 1 | DELETE | T | | | | | |* 2 | INDEX RANGE SCAN| IND_T | 4420 | 22100 | 11 (0)| 00:00:01 | ---------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"<10000)Statistics ---------------------------------------------------------- 45 recursive calls 10940 db block gets 52 consistent gets 0 physical reads 2806724 redo size 831 bytes sent via SQL*Net to client 794 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 9811 rows processedSQL> rollback;Rollback complete.SQL> delete from t where object_id < 20000;13938 rows deleted.Execution Plan ---------------------------------------------------------- Plan hash value: 3974964266--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | DELETE STATEMENT | | 8842 | 44210 | 21 (0)| 00:00:01 | | 1 | DELETE | T | | | | | |* 2 | INDEX RANGE SCAN| IND_T | 8842 | 44210 | 21 (0)| 00:00:01 | ---------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"<20000)Statistics ---------------------------------------------------------- 17 recursive calls 15016 db block gets 47 consistent gets 0 physical reads 3988132 redo size 836 bytes sent via SQL*Net to client 794 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 13938 rows processedSQL> rollback;Rollback complete.SQL> delete from t where object_id <30000;14403 rows deleted.Execution Plan ---------------------------------------------------------- Plan hash value: 3974964266--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | DELETE STATEMENT | | 13263 | 66315 | 31 (0)| 00:00:01 | | 1 | DELETE | T | | | | | |* 2 | INDEX RANGE SCAN| IND_T | 13263 | 66315 | 31 (0)| 00:00:01 | ---------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"<30000)Statistics ---------------------------------------------------------- 17 recursive calls 15508 db block gets 48 consistent gets 0 physical reads 4121320 redo size 837 bytes sent via SQL*Net to client 793 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 14403 rows processedSQL>從結果可以看到，消耗的資源和刪除的數據量是同步增長的
| object_id<10000 | 10940 + 52 |
| object_id<20000 | 15016 +47 |
| object_id<30000 | 15508＋48 |
truncate的消耗
t_part 表
t_part_2表
SQL> create table t_part_2 (object_id int ,object_name varchar2(2000) )2 partition by range(object_id)3 (4 partition p1 values less than (20000),5 partition pm values less than (maxvalue)6 );Table createdSQL> insert into t_part_2 select object_id ,object_name from dba_objects;35239 rows insertedSQL> commit;Commit completeSQL> alter session set tracefile_identifier='xgj_truncate';Session alteredSQL> alter session set sql_trace=true;Session alteredSQL> alter table t_part_2 truncate partition p1;Table truncatedSQL> alter session set sql_trace=false;Session alteredSQL>我們可以看到，truncate操作時，數據塊讀的次數10次左右，所以DDL操作并沒有隨著操作的數據增加而明顯的增加，它只取決于需要操作的字典表的數據量。
DELETE與釋放空間
DELETE操作并不能釋放出空間，也就是說，刪除了哪個表的數據，疼出來的空間還是只能是那個表使用，并不能讓給其他的對象使用，因為delete操作并不能使高水位線下降。 如果需要通過清除一個表中的部分數據來騰出空間給其他的對象，這個操作時不行的。
而truncate或者drop操作，就可以釋放出空間給其他的對象使用。
下面舉例說明：
我們還是用我們上面創建的t_part分區表為例
SQL> select * from user_extents a where a.segment_name='T_PART'; SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID BYTES BLOCKS -------------------------------------------------------------------------------- ------------------------------ ------------------ ------------------------------ ---------- ---------- ---------- T_PART P1 TABLE PARTITION TAB_ZMC 0 8388608 1024 T_PART P2 TABLE PARTITION TAB_ZMC 0 8388608 1024 T_PART P3 TABLE PARTITION TAB_ZMC 0 8388608 1024 T_PART PM TABLE PARTITION TAB_ZMC 0 8388608 1024 T_PART P5 TABLE PARTITION TAB_ZMC 0 8388608 1024SQL> select count(1) from user_extents a where a.segment_name='T_PART'; COUNT(1) ----------5SQL>最初的時候，T_PART表的數據占據了5個EXTENTS（區）
我們通過delete刪除部分數據
SQL> select count(1) from t_part; COUNT(1) ---------- 35245SQL> delete from t_part a where a.object_id<30000;14403 rows deletedSQL> commit;Commit completeSQL> select count(1) from user_extents a where a.segment_name='T_PART'; COUNT(1) ---------- 5SQL>我們刪除了14403 條數據后，發現T_PART仍然占據著5個EXTENDS，也就是說雖然表中的數據刪除了，但是空間只能是T_PART自己使用，并沒有將控件返回給數據庫。
我們使用truncate 來試下 ，用T表的數據吧，比較多
SQL> select count(1) from user_extents a where a.segment_name='T'; COUNT(1) ---------- 17SQL> select count(1) from t; COUNT(1) ---------- 35245SQL> truncate table t;Table truncatedSQL> select count(1) from user_extents a where a.segment_name='T'; COUNT(1) ---------- 1SQL> select count(1) from 2 t; COUNT(1) ---------- 0SQL>可以看出 通過truncate 之后 T表占用的數據區從17個降為1個，大部分空間都返回給了數據庫，這部分空間是可以分配給其他數據使用的。
還有一種方式，就是如果這個表上有許多碎片，可以通過整理表的空間來達到釋放空間的目的，命令如下
alter table t move ;我們用T表來做下試驗，還原T表的數據
SQL> insert into t select object_id ,object_name from dba_objects;35245 rows inserted SQL> commit;Commit completeSQL> select count(1) from user_extents a where a.segment_name='T'; COUNT(1) ---------- 17SQL> delete from t ; --->通過delete方式刪除數據35245 rows deletedSQL> commit;Commit completeSQL> select count(1) from user_extents a where a.segment_name='T'; COUNT(1) ---------- 17SQL> alter table t move;--->重新整理數據Table alteredSQL> select count(1) from user_extents a where a.segment_name='T'; COUNT(1) ---------- 1SQL>我們看到，使用alter table move 操作，表的空間基本上已經全部釋放出來了，只剩下一個EXTENT（因為當前已經是一個空表了）。
通過alter table move 方式讓表中的數據重新存儲，這樣可以釋放出來一些空間，但是沒這個操作基本上等同于將表中的數據重新分布一次，如果表中的數據比較龐大，這將是一個非常耗時的操作。
實際上delete的操作只適用刪除非常少量的數據，并且需要在索引存在的情況下適用。
案例說明：
SQL> select count(1) from t;COUNT(1) ----------0 SQL> insert into t select object_id ,object_name from dba_objects;35245 rows inserted SQL> commit;Commit complete SQL> select a.index_name ,a.index_type ,a.table_name from user_indexes a where a.table_name = 'T'; --->查看表上的索引INDEX_NAME INDEX_TYPE TABLE_NAME ------------------------------ --------------------------- ------------------------------ IND_T NORMAL TSQL> drop index ind_t ;-->先刪掉索引Index droppedSQL> select a.index_name ,a.index_type ,a.table_name from user_indexes a where a.table_name = 'T';INDEX_NAME INDEX_TYPE TABLE_NAME ------------------------------ --------------------------- ------------------------------SQL>看看執行計劃吧
oracle@entel1:[/oracle]$sqlplus /nologSQL*Plus: Release 11.2.0.4.0 Production on Wed Jan 18 20:40:16 2017Copyright (c) 1982, 2013, Oracle. All rights reserved.SQL> conn xxx/xgj Connected. SQL> set autotrace trace exp stat; SQL> delete from t where object_id=888;1 row deleted.Execution Plan ---------------------------------------------------------- Plan hash value: 3335594643--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | DELETE STATEMENT | | 1 | 5 | 43 (0)| 00:00:01 | | 1 | DELETE | T | | | | | |* 2 | TABLE ACCESS FULL| T | 1 | 5 | 43 (0)| 00:00:01 | ---------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OBJECT_ID"=888)Statistics ---------------------------------------------------------- 4 recursive calls 4 db block gets 209 consistent gets 0 physical reads 0 redo size 830 bytes sent via SQL*Net to client 790 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 1 rows processedSQL>在沒有索引的情況下，即使刪除很少的數據，Oracle也會將全表掃描一遍（分區表除外）。
此時，Oracle讀取了209 個數據塊，如果我們肩上索引，效果會好很多。
SQL> create index ind_t on t(object_id);Index created.SQL> delete from t where object_id=888;1 row deleted.Execution Plan ---------------------------------------------------------- Plan hash value: 3974964266--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | DELETE STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 | | 1 | DELETE | T | | | | | |* 2 | INDEX RANGE SCAN| IND_T | 1 | 5 | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=888)Statistics ---------------------------------------------------------- 1 recursive calls 5 db block gets 2 consistent gets 4 physical reads 0 redo size 834 bytes sent via SQL*Net to client 790 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 1 rows processedSQL>我們可以看到僅僅讀取了2個數據塊，2個一致性讀，非常高效。
總結
以上是生活随笔為你收集整理的Oracle海量数据优化-01分区的渊源的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Linux-手动释放linux内存cac
- 下一篇： Linux 中复制文件到多个目录中