create table tmp_object1 as select * from dba_objects;create index idx_object1_name on tmp_object1(object_name);create table tmp_object2 as select * from dba_objects;alter session set events '10046 trace name context forever, level 12';declarecursor cur_obj1 is select object_name from tmp_object1;type v_array is table of tmp_object1.object_name%type;v_obj1 v_array;beginopen cur_obj1;loopfetch cur_obj1 bulk collect into v_obj1 limit 10000;forall i in 1..v_obj1.countdelete from tmp_object1 where object_name = v_obj1(i);commit;exit when cur_obj1%notfound;end loop;close cur_obj1;end;/alter session set events '10046 trace name context off';OVERALL TOTALS FOR ALL RECURSIVE STATEMENTScall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- -------Parse 16 0.00 0.00 0 2 0 0Execute 26 9.23 9.03 254 52778 121592 51712Fetch 39 0.07 0.07 1 1363 0 51735------- ------ -------- ---------- ---------- ---------- ---------- -------total 81 9.31 9.11 255 54143 121592 103447Misses in library cache during parse: 5Misses in library cache during execute: 4Elapsed times include waiting on following events:Event waited on Times Max. Wait Total Waited---------------------------------------- Waited ---------- ----------db file sequential read 254 0.00 0.00alter session set events '10046 trace name context forever, level 12';declarev_cnt number;beginselect count(*) into v_cntfrom tmp_object2;v_cnt := floor(v_cnt/10000) + 1;for i in 1..v_cnt loopdelete from tmp_object2 where rownum <= 10000;commit;end loop;end;/alter session set events '10046 trace name context off';OVERALL TOTALS FOR ALL RECURSIVE STATEMENTScall count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- -------Parse 14 0.00 0.00 0 2 0 0Execute 24 1.15 1.70 0 2785 57665 51712Fetch 31 0.04 0.04 712 865 0 22------- ------ -------- ---------- ---------- ---------- ---------- -------total 69 1.20 1.75 712 3652 57665 51734Misses in library cache during parse: 3Misses in library cache during execute: 1Elapsed times include waiting on following events:Event waited on Times Max. Wait Total Waited---------------------------------------- Waited ---------- ----------db file sequential read 17 0.00 0.00db file scattered read 67 0.00 0.01log file switch completion 3 0.02 0.04log buffer space 1 0.11 0.11新聞熱點
疑難解答
