Problem : We need to run huge file which contains sequential delete statements against the database having 500+ tables in one schema. We run this job in the night when no one is using the system. The file contains 1 million to 5 million delete statements and takes 3 hrs to 7 hrs.
The table sizes vary from few MB to almost 10 GB. Biggest one contains almost 40 million rows and has 3 indexes on it. We are not sure how many records are exactly deleted from each of these tables and they vary day to day but for big tables they can be about 200,000 to ½ million.
Our database keeps on growing and these delete records also keeps growing. Is there any way we can reduce the time of running the script. Can the file be split in multiple files and run in parallel. If yes then can it be automated and how?
Here is sample ..
File name -- XXXXX.DATE.DAT
DELETE FROM
XXXX
WHERE
ACTN_TYP_IND='C' AND ALRT_CD='034' AND ALRT_DT=TO_DATE('20611117000000','YYYYMMDDHH24MISS') AND CS_ID='C949149' AND CWIN=23522
AND EDBC_SEQ=27264345 AND PGM_TYP_CD='MC';
DELETE FROM
XXXX
WHERE
ACTN_TYP_IND='C' AND ALRT_CD='034' AND ALRT_DT=TO_DATE('20611117000000','YYYYMMDDHH24MISS') AND CS_ID='C949149' AND CWIN=23522
AND EDBC_SEQ=27264302 AND PGM_TYP_CD='MC';
I am not sure how much detail I should include here but I can send more detail if required.
Thanks
Kishor
you are probably spending MUCH/MOST of your runtime PARSING SQL.
your real fix:
get this file to contain just the inputs -
C,034,20611117000000,C949149,23522,27264345,MC
....
use an external table so you can select * from this file and simply:
delete from xxx where (those columns) in (select * from that_external_table);
man - will that be fast!
you might be able to speed this up a small bit using cursor_sharing right now. (but now you know your true fix, create a small file without the hard coded deletes and just use a single sql statement!!!!!)
ops$tkyte%ORA10GR2> create table t1 as select * from all_objects;
Table created.
ops$tkyte%ORA10GR2> create table t2 as select * from all_objects;
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec runStats_pkg.rs_start;
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> alter session set cursor_sharing = exact;
Session altered.
ops$tkyte%ORA10GR2> begin
2 for x in (select rowid rid from t1)
3 loop
4 execute immediate 'delete from t1 where rowid = chartorowid(''' || x.rid || ''')';
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> exec runStats_pkg.rs_middle;
PL/SQL procedure successfully completed.
<b>
ops$tkyte%ORA10GR2> alter session set cursor_sharing = force;
Session altered.
</b>
ops$tkyte%ORA10GR2> begin
2 for x in (select rowid rid from t2)
3 loop
4 execute immediate 'delete from t2 where rowid = chartorowid(''' || x.rid || ''')';
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> exec runStats_pkg.rs_stop;
Run1 ran in 5583 hsecs
Run2 ran in 564 hsecs
run 1 ran in 989.89% of the time
Name Run1 Run2 Diff
STAT...CR blocks created 491 492 1
LATCH.KWQMN job cache list lat 1 0 -1
LATCH.ncodef allocation latch 1 0 -1
LATCH.job_queue_processes free 1 0 -1
LATCH.resmgr:actses change gro 1 0 -1
LATCH.sort extent pool 1 0 -1
LATCH.transaction branch alloc 1 0 -1
LATCH.buffer pool 1 0 -1
LATCH.session switching 1 0 -1
STAT...session cursor cache co 0 1 1
STAT...table scans (short tabl 13 12 -1
STAT...rollbacks only - consis 491 492 1
STAT...redo entries 52,211 52,212 1
STAT...calls to kcmgcs 4 5 1
STAT...write clones created in 1 0 -1
LATCH.threshold alerts latch 1 0 -1
LATCH.Shared B-Tree 2 0 -2
LATCH.transaction allocation 2 0 -2
LATCH.dummy allocation 2 0 -2
LATCH.session state list latch 2 0 -2
STAT...messages sent 19 21 2
LATCH.KMG resize request state 2 0 -2
LATCH.resmgr:free threads list 2 0 -2
STAT...bytes received via SQL* 1,879 1,877 -2
LATCH.resmgr:actses active lis 2 0 -2
STAT...table fetch continued r 2 0 -2
LATCH.kwqbsn:qsga 2 0 -2
LATCH.user lock 2 0 -2
LATCH.parameter table allocati 4 1 -3
LATCH.job_queue_processes para 3 0 -3
STAT...cursor authentications 5 2 -3
LATCH.cache buffer handles 3 0 -3
LATCH.post/wait queue 4 0 -4
LATCH.ksuosstats global area 4 0 -4
LATCH.FIB s.o chain latch 4 0 -4
LATCH.PL/SQL warning settings 10 6 -4
LATCH.FOB s.o list latch 5 0 -5
STAT...db block changes 103,850 103,845 -5
LATCH.SGA IO buffer pool latch 16 10 -6
LATCH.compile environment latc 21 15 -6
LATCH.resmgr group change latc 6 0 -6
STAT...physical reads cache pr 605 611 6
STAT...physical reads cache 697 691 -6
STAT...physical reads 697 691 -6
STAT...free buffer requested 2,633 2,626 -7
LATCH.Consistent RBA 27 20 -7
LATCH.lgwr LWN SCN 27 20 -7
LATCH.mostly latch-free SCN 27 20 -7
LATCH.hash table modification 8 1 -7
LATCH.qmn task queue latch 8 0 -8
LATCH.parallel query alloc buf 8 0 -8
LATCH.multiblock read objects 150 140 -10
LATCH.session idle bit 62 52 -10
STAT...physical read IO reques 92 80 -12
STAT...physical read total IO 92 80 -12
STAT...consistent changes 17,405 17,418 13
STAT...db block gets from cach 55,960 55,946 -14
STAT...db block gets 55,960 55,946 -14
LATCH.OS process allocation 19 2 -17
STAT...data blocks consistent 17,368 17,386 18
LATCH.session timer 23 2 -21
LATCH.object stats modificatio 24 0 -24
LATCH.client/application info 24 0 -24
STAT...change write time 78 50 -28
LATCH.active checkpoint queue 34 5 -29
LATCH.undo global data 17,953 17,923 -30
STAT...cluster key scans 37 6 -31
STAT...workarea executions - o 42 8 -34
LATCH.cache buffers lru chain 7,770 7,734 -36
STAT...cluster key scan block 43 6 -37
LATCH.dml lock allocation 46 1 -45
LATCH.library cache pin alloca 51 6 -45
LATCH.redo allocation 94 44 -50
LATCH.object queue header heap 60 5 -55
STAT...sorts (memory) 79 7 -72
LATCH.library cache lock alloc 87 8 -79
LATCH.redo writing 153 69 -84
STAT...rows fetched via callba 86 1 -85
LATCH.In memory undo latch 119 26 -93
LATCH.active service list 114 18 -96
LATCH.checkpoint queue latch 1,854 1,754 -100
STAT...redo size 18,504,656 18,504,764 108
STAT...parse count (total) 50,253 50,141 -112
STAT...buffer is pinned count 119 0 -119
STAT...index fetch by key 139 8 -131
STAT...sorts (rows) 2,514 2,382 -132
STAT...shared hash latch upgra 202 12 -190
STAT...index scans kdiixs1 202 12 -190
LATCH.simulator hash latch 6,636 6,851 215
STAT...opened cursors cumulati 266 47 -219
LATCH.channel operations paren 261 28 -233
STAT...execute count 50,396 50,145 -251
STAT...sql area evicted 258 0 -258
STAT...no work - consistent re 1,075 812 -263
STAT...undo change vector size 11,453,416 11,453,680 264
LATCH.simulator lru latch 6,172 6,437 265
STAT...calls to get snapshot s 50,452 50,184 -268
STAT...table fetch by rowid 311 22 -289
LATCH.JS queue state obj latch 396 72 -324
LATCH.KMG MMAN ready and start 399 2 -397
LATCH.Memory Management Latch 399 0 -399
STAT...dirty buffers inspected 16 426 410
STAT...consistent gets - exami 17,888 17,412 -476
STAT...buffer is not pinned co 717 123 -594
LATCH.library cache load lock 666 8 -658
STAT...hot buffers moved to he 194 976 782
STAT...table scan rows gotten 91,597 90,811 -786
STAT...consistent gets from ca 19,707 18,779 -928
STAT...consistent gets 19,707 18,779 -928
STAT...session logical reads 75,667 74,725 -942
LATCH.messages 1,236 106 -1,130
LATCH.SQL memory manager worka 1,374 142 -1,232
LATCH.object queue header oper 8,930 10,289 1,359
LATCH.shared pool sim alloc 2,241 0 -2,241
STAT...free buffer inspected 481 4,929 4,448
STAT...parse time cpu 4,601 143 -4,458
STAT...parse time elapsed 4,749 171 -4,578
STAT...recursive cpu usage 5,300 488 -4,812
STAT...CPU used when call star 5,436 550 -4,886
STAT...CPU used by this sessio 5,436 546 -4,890
STAT...DB time 5,588 567 -5,021
STAT...Elapsed Time 5,589 567 -5,022
LATCH.cache buffers chains 399,992 377,474 -22,518
STAT...physical read total byt 5,709,824 5,660,672 -49,152
STAT...physical read bytes 5,709,824 5,660,672 -49,152
STAT...session cursor cache hi 243 50,119 49,876
STAT...parse count (hard) 50,122 9 -50,113
STAT...enqueue releases 50,149 33 -50,116
STAT...enqueue requests 50,151 34 -50,117
STAT...recursive calls 104,596 51,112 -53,484
STAT...session pga memory 0 -65,536 -65,536
LATCH.session allocation 76,729 625 -76,104
LATCH.enqueues 101,724 238 -101,486
LATCH.enqueue hash chains 101,732 225 -101,507
LATCH.kks stats 179,024 15 -179,009
LATCH.library cache pin 302,365 157 -302,208
LATCH.library cache lock 303,953 251 -303,702
LATCH.shared pool simulator 517,244 71 -517,173
LATCH.row cache objects 767,770 647 -767,123
LATCH.library cache 1,199,677 50,611 -1,149,066
LATCH.shared pool 1,823,320 628 -1,822,692
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
5,831,135 482,805 -5,348,330 1,207.76%
PL/SQL procedure successfully completed.