| Delete Advice [message #156554] |
Wed, 25 January 2006 13:23  |
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 #156605 is a reply to message #156555] |
Thu, 26 January 2006 04:31  |
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;
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 #156622 is a reply to message #156554] |
Thu, 26 January 2006 09:11 |
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 |
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 |
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 |
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 #156698 is a reply to message #156684] |
Fri, 27 January 2006 03:07 |
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 |
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
)