Home » RDBMS Server » Performance Tuning » Delete Advice
Delete Advice [message #156554] Wed, 25 January 2006 13:23 Go to next message
rkl1
Messages: 97
Registered: June 2005
Member
we need to delete about 2% of rows of a non-partitioned table containing more than 100 million rows. I am just wondering, if the delete would leave the tables with empty blocks randomly and there by lower the performance particulary during the full table scan. Is it worth to move the table to existing tablespace: alter table X move tablesapce Existing. Does it reorganize the rows and space. Of course it would take time and knock out all the indexes which we have to rebuild. What other startegy we could take.

Will aprreciate any info.

Thanks.
Re: Delete Advice [message #156555 is a reply to message #156554] Wed, 25 January 2006 13:49 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
The full table scans won't go any slower than now. What might happen is that they don't go any faster. But it might if the data you are deleting is all neatly packed in continuously. Also, if you add rows to that table ever again (assuming normal inserts) then you'll get to reuse that space that was deleted, even if it was scattered around all over the table.

If you go with the move route, it is up to you to tell if the time and rebuild index time is worth it. But if you do go that route, don't do the delete first. Instead, move only the records that you want to keep instead of doing a delete command.

See also

http://asktom.oracle.com/pls/ask/f?p=4950:8:13857059653258795100::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:2345591157689

and

http://asktom.oracle.com/pls/ask/f?p=4950:8:13857059653258795100::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:6407993912330
Re: Delete Advice [message #156605 is a reply to message #156555] Thu, 26 January 2006 04:31 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
smartin wrote on Thu, 26 January 2006 06:49

But it might if the data you are deleting is all neatly packed in continuously.


I am prepared to be called a bare-faced-liar, but I'm pretty sure this is false (at least it was last time I tested it, which was a while ago). If the deleted rows are nicely placed so that the blocks in which they reside drop below the PCTUSED threshold, then they will be placed back on the free-list, but that won't help a FTS. FTS reads every block up to the high-water-mark.

This is easily demonstrated with the following script:
CREATE TABLE hwmtest AS SELECT * FROM dba_objects;
DELETE FROM hwmtest;
COMMIT;
ALTER SESSION SET SQL_TRACE=true;
SELECT * FROM hwmtest;
ALTER SESSION SET SQL_TRACE=false;
Run TK*Prof over the trace file, and it should show 0 rows but several blocks read. (I don't have Oracle at the moment, so cannot include the results).


In 10g, you have the SHRINK clause of ALTER TABLE that will reclaim the blank space - this should be a bit faster than moving it to another tablespace.

smartin wrote on Thu, 26 January 2006 06:49

Also, if you add rows to that table ever again (assuming normal inserts) then you'll get to reuse that space that was deleted, even if it was scattered around all over the table.


Taking my rep into my own hands now, but I'm going to disagree again. Unless the percentage of space used in a block falls below the PCTUSED threshold, it will not go back on the free-list, and the the space will remain unused. This is most likely to happen if the rows are scattered all over the place.

_____________
Ross Leishman
Re: Delete Advice [message #156615 is a reply to message #156605] Thu, 26 January 2006 08:27 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Shrink in 10g is applicable only in ASSM.
Alter table move tablespace moves by the block.
Shrink will move the rowids ( and only if row movement is enabled).
Regarding which is faster among the two, i have to test!.
Only advantage i have seen is , SHRINK is online.
Thanks & Regards
Re: Delete Advice [message #156622 is a reply to message #156554] Thu, 26 January 2006 09:11 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Yes...wow was I wrong, wonder what I was thinking yesterday. Thanks for posting Ross to correct me. Damn...

Ok, here is the test:

MYDBA > create table test as select * from all_objects;

Table created.

MYDBA > delete from test;

51001 rows deleted.

MYDBA > commit;

Commit complete.

MYDBA > set autotrace traceonly statistics;
MYDBA > select * from test;

no rows selected


Statistics
----------------------------------------------------------
        267  recursive calls
          0  db block gets
       1202  consistent gets
        310  physical reads
      30672  redo size
        995  bytes sent via SQL*Net to client
        370  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

Re: Delete Advice [message #156625 is a reply to message #156554] Thu, 26 January 2006 09:30 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Now for the second point, I'm not ready to concede my complete wrongness, only partial in that I assumed something that had no business being assumed. With manual segment space management, yes you must take pctused into account. But not with ASSM:

MYDBA > create table test as select * from all_objects;

Table created.

MYDBA > select sum(bytes) from user_segments where segment_name = 'TEST';

SUM(BYTES)
----------
   6291456

MYDBA > delete from test where mod(object_id,5) = 0;

10191 rows deleted.

MYDBA > commit;

Commit complete.

MYDBA > insert into test select * from all_objects where rownum <= 10191;

10191 rows created.

MYDBA > commit;

Commit complete.

MYDBA > select sum(bytes) from user_segments where segment_name = 'TEST';

SUM(BYTES)
----------
   6291456


The above is probably not the most conclusive of tests by any means, depending as it does on size of the extent that was last allocated during the initial creation. But a similar test could be drawn up easily I think to show the reuse.
Re: Delete Advice [message #156628 is a reply to message #156622] Thu, 26 January 2006 09:40 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
@smartin,
you're not wrong on your first point either, yes there are still gets when doing the fts but fewer than there were before the delete (I notice you didn't put that into your test)

 SQL> create table t as select e.* from hr.employees e,  hr.departments
  2  ;

Table created.

SQL> set autotrace traceonly statistics;
SQL> select * from t;

2889 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        252  consistent gets
         61  physical reads
          0  redo size
     199653  bytes sent via SQL*Net to client
       2615  bytes received via SQL*Net from client
        194  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       2889  rows processed

SQL> /

2889 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        252  consistent gets
         56  physical reads
          0  redo size
     199653  bytes sent via SQL*Net to client
       2615  bytes received via SQL*Net from client
        194  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       2889  rows processed

SQL> delete t;

2889 rows deleted.


Statistics
----------------------------------------------------------
         74  recursive calls
       3325  db block gets
        101  consistent gets
         56  physical reads
     927248  redo size
        621  bytes sent via SQL*Net to client
        508  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
       2889  rows processed

SQL> select * from t;

no rows selected


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         64  consistent gets
          0  physical reads
          0  redo size
        801  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

I had also thought that it was the case that no gain would be made on the fts. This would be bourne out from the common test of

SQL> set autotrace traceonly statistics;
SQL> select count(*) from t;


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         64  consistent gets
          0  physical reads
          0  redo size
        380  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> delete from t;

2889 rows deleted.

SQL> select count(*) from t;


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         64  consistent gets
          0  physical reads
          0  redo size
        378  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed



Re: Delete Advice [message #156637 is a reply to message #156554] Thu, 26 January 2006 10:59 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Ahh, but check this out:

MYDBA >
MYDBA > create table test as select * from all_objects;

Table created.

MYDBA > insert into test select * from test;

51007 rows created.

MYDBA > commit;

Commit complete.

MYDBA > set autotrace traceonly statistics;
MYDBA > select * from test;

102014 rows selected.


Statistics
----------------------------------------------------------
          6  recursive calls
          1  db block gets
       2206  consistent gets
          4  physical reads
      13136  redo size
   10151481  bytes sent via SQL*Net to client
       5991  bytes received via SQL*Net from client
        512  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     102014  rows processed

MYDBA > set autotrace off
MYDBA > delete from test;

102014 rows deleted.

MYDBA > commit;

Commit complete.

MYDBA > set autotrace traceonly statistics;
MYDBA > select * from test;

no rows selected


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2847  consistent gets
        918  physical reads
     100988  redo size
        995  bytes sent via SQL*Net to client
        370  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

MYDBA > alter table test move;

Table altered.

MYDBA > select * from test;

no rows selected


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        995  bytes sent via SQL*Net to client
        370  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

Re: Delete Advice [message #156639 is a reply to message #156637] Thu, 26 January 2006 11:18 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Martin,
We had little discussion ( not directly related) here.
http://www.orafaq.com/forum/m/150807/42800/#msg_150807
As you stated before,
It depends on whether ASSM is used or not.

Updated:
Wrong URL.

[Updated on: Thu, 26 January 2006 11:27]

Report message to a moderator

Re: Delete Advice [message #156684 is a reply to message #156639] Fri, 27 January 2006 00:55 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Excellent. I told you I was prepared to be called a bare-faced-liar, but I will have to amend that to igorant-of-ASSM-bare-faced-liar.

So, it looks like HWM is still a problem for FTS, and if you're using ASSM then delete and update with gay abandon. (But not too gay - not that there's anything wrong with that... Smile )

BTW. For the ASSM newbies (like me):

SELECT tablespace_name, SEGMENT_SPACE_MANAGEMENT
FROM dba_tablespaces


AUTO is good. MANUAL means you're a dinosaur - start evolving.

_____________
Ross Leishman
Re: Delete Advice [message #156698 is a reply to message #156684] Fri, 27 January 2006 03:07 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
@smartin
Those results puzzle me somewhat. more PIO AND LIO after the delete. Really didn't expect to see that. I ran the same test and got results similar to my original post i.e. a significant decrease in the number of consistent gets after the delete.

No probs with the results after the move obviously, totally expected result there.

Hmmmmm.

Jim
Re: Delete Advice [message #156756 is a reply to message #156554] Fri, 27 January 2006 08:44 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Jim, yeah it surprised me too and I havn't investigated further, or even looked at the link provided by Mahesh. I don't understand the delete causing more...my test was on 10gR2 if that matters.

Ross, in reading through Jonathan's new CBO book, he does a lot still with manual segment space management. I came into oracle at version 9i, so pretty much skipped right by it and went straight to ASSM, and am inclined to assume ASSM if I'm not careful. But he discusses how ASSM is more likely to scatter data around physically. This is "good" for concurrency issues, but "bad" for clustering issues. Manual may always have a place depending on the situation.

Always more to learn isn't there? Such fun, these are the threads I really like.

Re: Delete Advice [message #156803 is a reply to message #156756] Fri, 27 January 2006 22:05 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
To paraphrase Douglas Adams, I trust AUTOTRACE about as far as I can comfortably spit out a rat.

I wonder if TK*Prof could shed some light on the PIO/LIO issue. Once again, no Oracle DB right now, so I cannot do it myself.

@smartin, thanks for the ref. More reading required...
_____________
Ross Leishman
Previous Topic: finding oracle guidelines for performance and tuning
Next Topic: statistics - to Update or Not
Goto Forum:
  


Current Time: Thu Mar 28 20:37:29 CDT 2024