bulk_collect結合dbms_application_info監控資料處理進度
bulk collect的效率極高，原因是批次操作大量減少了pl/sql引擎與sql引擎context的切換，降低cpu使用.而且透過合理設定limit,可以使得pga使用和undo空間使用非常節制
Tom說過單條資料處理不能完成，不能滿足需求的時候採用bulk collect是不錯的選擇.
下面的例子處理600多萬條記錄的插入操作,應用此技術在1分半鐘左右搞定.
Oracle 9.2.0.6 64bit For AIX
4C 6G
儲存一般
ps.這次做了一個450G的資料遷移，用的就是RMAN duplicate DB ，而 bulk collect 做schema的拆分
11小時搞定schema的拆分
[@more@]
儲存過程
CREATE OR REPLACE PROCEDURE p_migration_cfg
IS
l_array_size number default 15000;
l_cnt number default 1;
type tb_cfg_type is table of ecifupdate.CFG_ATTR_MODIFY_HISTORY%rowtype;
ref_cfg_bulk tb_cfg_type;
Cursor c_cfg_bulk is
select *
from ecifupdate.CFG_ATTR_MODIFY_HISTORY partition(part_00);
BEGIN
open c_cfg_bulk;
loop
dbms_application_info.set_client_info( 'Processing ' || l_cnt || ' thru ' || (l_cnt+l_array_size-1)||' ... Start at '||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')||' !!!');
fetch c_cfg_bulk bulk collect into ref_cfg_bulk limit l_array_size;
forall i in 1 .. ref_cfg_bulk.count
insert into CFG_ATTR_MODIFY_HISTORY values ref_cfg_bulk(i);
exit when c_cfg_bulk%notfound;
l_cnt := l_cnt + l_array_size;
end loop;
close c_cfg_bulk;
END;
我這裡每15000條記錄提交一次
所以430次左右提交就可以完陳.
在session A中執行
session A:
11:16:07 SQL> exec p_migration_cfg
PL/SQL procedure successfully completed.
Elapsed: 00:01:29.04
11:17:44 SQL>
session B:
在session B中透過查詢資料字典監控資料處理進度
col CLIENT_INFO for a100
set linesize 150
select sid,client_info from v$session where username='CS_USER'
11:16:17 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 210001 thru 225000 ... Start at 2006-11-16 11:16:18 !
41
Elapsed: 00:00:00.00
11:16:18 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 285001 thru 300000 ... Start at 2006-11-16 11:16:19 !
41
Elapsed: 00:00:00.01
11:16:20 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 360001 thru 375000 ... Start at 2006-11-16 11:16:20 !
41
Elapsed: 00:00:00.01
11:16:21 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 450001 thru 465000 ... Start at 2006-11-16 11:16:22 !
41
Elapsed: 00:00:00.00
11:16:22 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 525001 thru 540000 ... Start at 2006-11-16 11:16:23 !
41
Elapsed: 00:00:00.00
11:16:23 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 600001 thru 615000 ... Start at 2006-11-16 11:16:24 !
41
Elapsed: 00:00:00.01
11:16:24 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 660001 thru 675000 ... Start at 2006-11-16 11:16:24 !
41
Elapsed: 00:00:00.00
11:16:25 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 735001 thru 750000 ... Start at 2006-11-16 11:16:25 !
41
Elapsed: 00:00:00.00
11:16:26 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 795001 thru 810000 ... Start at 2006-11-16 11:16:26 !
41
Elapsed: 00:00:00.00
11:16:26 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 855001 thru 870000 ... Start at 2006-11-16 11:16:27 !
41
Elapsed: 00:00:00.00
11:16:27 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 915001 thru 930000 ... Start at 2006-11-16 11:16:28 !
41
Elapsed: 00:00:00.01
11:16:28 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 990001 thru 1005000 ... Start at 2006-11-16 11:16:29
41
Elapsed: 00:00:00.00
11:16:29 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 1050001 thru 1065000 ... Start at 2006-11-16 11:16:30
41
Elapsed: 00:00:00.00
11:16:30 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 1200001 thru 1215000 ... Start at 2006-11-16 11:16:32
41
Elapsed: 00:00:00.00
11:16:32 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 1335001 thru 1350000 ... Start at 2006-11-16 11:16:34
41
Elapsed: 00:00:00.00
11:16:34 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 1530001 thru 1545000 ... Start at 2006-11-16 11:16:37
41
Elapsed: 00:00:00.01
11:16:37 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 1620001 thru 1635000 ... Start at 2006-11-16 11:16:38
41
Elapsed: 00:00:00.00
11:16:38 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 1710001 thru 1725000 ... Start at 2006-11-16 11:16:39
41
Elapsed: 00:00:00.00
11:16:39 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 1965001 thru 1980000 ... Start at 2006-11-16 11:16:42
41
Elapsed: 00:00:00.00
11:16:43 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 2115001 thru 2130000 ... Start at 2006-11-16 11:16:44
41
Elapsed: 00:00:00.01
11:16:45 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 2190001 thru 2205000 ... Start at 2006-11-16 11:16:46
41
Elapsed: 00:00:00.00
11:16:46 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 2325001 thru 2340000 ... Start at 2006-11-16 11:16:47
41
Elapsed: 00:00:00.00
11:16:48 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 2475001 thru 2490000 ... Start at 2006-11-16 11:16:49
41
Elapsed: 00:00:00.00
11:16:49 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 2580001 thru 2595000 ... Start at 2006-11-16 11:16:51
41
Elapsed: 00:00:00.00
11:16:51 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 2685001 thru 2700000 ... Start at 2006-11-16 11:16:52
41
Elapsed: 00:00:00.00
11:16:52 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 2895001 thru 2910000 ... Start at 2006-11-16 11:16:55
41
Elapsed: 00:00:00.01
11:16:55 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 3105001 thru 3120000 ... Start at 2006-11-16 11:16:58
41
Elapsed: 00:00:00.01
11:16:58 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 3240001 thru 3255000 ... Start at 2006-11-16 11:17:00
41
Elapsed: 00:00:00.00
11:17:00 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 3450001 thru 3465000 ... Start at 2006-11-16 11:17:02
41
Elapsed: 00:00:00.01
11:17:03 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 3705001 thru 3720000 ... Start at 2006-11-16 11:17:06
41
Elapsed: 00:00:00.00
11:17:06 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 3810001 thru 3825000 ... Start at 2006-11-16 11:17:07
41
Elapsed: 00:00:00.00
11:17:07 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 3990001 thru 4005000 ... Start at 2006-11-16 11:17:10
41
Elapsed: 00:00:00.00
11:17:10 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 4275001 thru 4290000 ... Start at 2006-11-16 11:17:14
41
Elapsed: 00:00:00.01
11:17:14 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 4350001 thru 4365000 ... Start at 2006-11-16 11:17:15
41
Elapsed: 00:00:00.00
11:17:15 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 4590001 thru 4605000 ... Start at 2006-11-16 11:17:18
41
Elapsed: 00:00:00.00
11:17:18 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 4815001 thru 4830000 ... Start at 2006-11-16 11:17:21
41
Elapsed: 00:00:00.00
11:17:21 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 4920001 thru 4935000 ... Start at 2006-11-16 11:17:22
41
Elapsed: 00:00:00.00
11:17:23 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 5025001 thru 5040000 ... Start at 2006-11-16 11:17:24
41
Elapsed: 00:00:00.00
11:17:24 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 5175001 thru 5190000 ... Start at 2006-11-16 11:17:26
41
Elapsed: 00:00:00.00
11:17:26 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 5340001 thru 5355000 ... Start at 2006-11-16 11:17:28
41
Elapsed: 00:00:00.01
11:17:28 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 5460001 thru 5475000 ... Start at 2006-11-16 11:17:30
41
Elapsed: 00:00:00.00
11:17:30 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 5565001 thru 5580000 ... Start at 2006-11-16 11:17:32
41
Elapsed: 00:00:00.00
11:17:32 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 5685001 thru 5700000 ... Start at 2006-11-16 11:17:33
41
Elapsed: 00:00:00.00
11:17:33 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 5745001 thru 5760000 ... Start at 2006-11-16 11:17:34
41
Elapsed: 00:00:00.00
11:17:34 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 5820001 thru 5835000 ... Start at 2006-11-16 11:17:35
41
Elapsed: 00:00:00.01
11:17:35 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 5910001 thru 5925000 ... Start at 2006-11-16 11:17:36
41
Elapsed: 00:00:00.00
11:17:37 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 6000001 thru 6015000 ... Start at 2006-11-16 11:17:38
41
Elapsed: 00:00:00.00
11:17:38 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 6195001 thru 6210000 ... Start at 2006-11-16 11:17:40
41
Elapsed: 00:00:00.00
11:17:41 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 6300001 thru 6315000 ... Start at 2006-11-16 11:17:42
41
Elapsed: 00:00:00.00
11:17:42 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 6420001 thru 6435000 ... Start at 2006-11-16 11:17:44
41
Elapsed: 00:00:00.00
11:17:44 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 6435001 thru 6450000 ... Start at 2006-11-16 11:17:44
41
Elapsed: 00:00:00.00
11:17:45 SQL> /
SID CLIENT_INFO
---------- ----------------------------------------------------------------------------------------------------
16 Processing 6435001 thru 6450000 ... Start at 2006-11-16 11:17:44
41
Elapsed: 00:00:00.00
11:17:47 SQL> l
1* select sid,client_info from v$session where username='CS_USER'
11:21:36 SQL>
tkprof分析報告
********************************************************************************
SELECT *
FROM
ECIFUPDATE.CFG_ATTR_MODIFY_HISTORY PARTITION(PART_00)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 430 35.31 41.15 59558 60003 0 6449169
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 431 35.31 41.15 59558 60003 0 6449169
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 67 (recursive depth: 1)
********************************************************************************
INSERT INTO CFG_ATTR_MODIFY_HISTORY
VALUES
(:B1 ,:B2 ,:B3 ,:B4 ,:B5 ,:B6 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 430 39.30 39.54 2 102529 524989 6449169
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 430 39.30 39.54 2 102529 524989 6449169
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 67 (recursive depth: 1)
********************************************************************************
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/76065/viewspace-877753/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 監控批量操作進度
- PHP 結合 MySQL 千萬級資料處理PHPMySql
- 監控資料備份恢復完成進度（EXPDP/IMPDP/RMAN）
- 使用aop來監控方法進行增強處理
- 如何監控工程專案進度？
- Linux下使用pv監控進度Linux
- 資料庫效能監控瓶頸理論資料庫
- 資料處理之欄位合併
- Ajax 處理時進度條使用
- 2 關於資料倉儲維度資料處理的方法探究系列——無變化維度處理
- ss命令結合zabbix對socket做監控
- 資料庫監控資料庫
- 橋樑保護與監控-開發進度（一）
- 如何做好專案進度監控與跟蹤
- 利用掙值法監控專案成本和進度
- spring boot admin系統監控處理Spring Boot
- 結合Ansible技術監控Storm叢集ORM
- 資料庫效能監控資料庫
- 前端資料監控到底在監控什麼？前端
- MySQL監控-Datadog資料庫監控調研MySql資料庫
- 汙水處理自動化裝置資料採集與遠端監控解決方案
- 西門子PLC水處理系統如何實現資料採集遠端監控？
- 利用資料庫trigger對安全進行監控資料庫
- 如何使用表格儲存控制檯進行資料監控
- Oracle對資料進行加密&解密處理Oracle加密解密
- 一個好的產品經理如何把控專案進度和跟進進度
- 監控並處理一直不釋放資源的會話會話
- oracle rman備份驗證和備份進度監控Oracle
- 前端錯誤監控 -【Vue】與【Sentry】的結合前端Vue
- 監控採集上報和儲存監控資料策略
- 系統監控&JVM監控指標資料查詢JVM指標
- 依賴反向傳播改進神經網路資料處理的精確度反向傳播神經網路
- 利用Python Pandas進行資料預處理-資料清洗Python
- 海量資料處理_使用外部表進行資料遷移
- Python資料處理（二）：處理 Excel 資料PythonExcel
- 資料庫繁忙程度監控資料庫
- 資料庫監控軟體資料庫
- SQL Server資料庫監控SQLServer資料庫