Skip to Main Content

Breadcrumb

Warning

Before you submit your comment...

Remember - this is about feedback to the original question. AskTOM is about building a strong Oracle community, not stealing time away from others needing assistance by sneaking in a brand new question under the guise of "Comment".

If your comment requires a response that might include a query, please supply very simple CREATE TABLE and INSERT statements. Nice simple scripts so we can replicate your issue simply.

Remember to take a look at Legal Notices and Terms of Use before supplying a comment...

Don't forget, your comments are public. If you want to send a comment to just the AskTOM team, please use the feedback option

Comment

Highlight any SQL, PL/SQL, or fixed-width text and click the <code> button
 (will never be published or used to spam you)

Question and Answer

Tom Kyte

Thanks for the question, kishor.

Asked: April 09, 2007 - 2:36 pm UTC

Last updated: April 10, 2007 - 9:22 pm UTC

Version: 9.2.0.5

Viewed 1000+ times

You Asked

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

and Tom said...

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.


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment