Home » RDBMS Server » Performance Tuning » Table rebuild should be done after removing most of its data? (Oracle 10.2.0.4 on Solaris 10)
Table rebuild should be done after removing most of its data? [message #569234] Tue, 23 October 2012 06:32 Go to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Hi All,

We are on Oracle 10.2.0.4 on Solaris 10. There is a table in my production db that has 872944 number of rows. Most of its data is now unnecessary, we need to retain, based on a date column in the table just last one month's data and delete rest of the data. So after that the table will have just 3000 rows.

However as the table was huge earlier(872k rows prior to delete) , does the delete of data release its oracle blocks and does the size of the table reduce? If not, will it help to rebuild the table online (online redefinition) so that the query that does a full scan on this table goes faster?

I checked using an example table that just delete of data does not remove the oracle blocks - they remain in the user_tables for that table and cost of full table scan remains same. We have a query that does the full table scan so I am thinking that after this delete I should do an online table re-definition , is that the right decision?

Thanks



Re: Table rebuild should be done after removing most of its data? [message #569236 is a reply to message #569234] Tue, 23 October 2012 06:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
does the delete of data release its oracle blocks and does the size of the table reduce?


No.

Quote:
If not, will it help to rebuild the table online (online redefinition) so that the query that does a full scan on this table goes faster?


ALTER TABLE ... SHRINK

Regards
Michel
Re: Table rebuild should be done after removing most of its data? [message #569238 is a reply to message #569236] Tue, 23 October 2012 07:12 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Thank you Michel!! I just saw that this shrink operation also requires to "enable row movement". My table is heavily used by the application. So when I do this in Produciton, alter table shrink, are there any other impact?

I mean can this SHRINK operation be done online (when the table is accessible to the users?) or do we need to stop users access and run this command?

Thanks
Re: Table rebuild should be done after removing most of its data? [message #569240 is a reply to message #569238] Tue, 23 October 2012 08:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
So when I do this in Produciton, alter table shrink, are there any other impact?


It will move rows so when a row is moved, it is lockedn and so there maybe some slight slow down for your application.

Quote:
or do we need to stop users access and run this command?


It is always better to do maintenance on a table when this one is not or slighty accessed.

Regards
Michel
Re: Table rebuild should be done after removing most of its data? [message #570132 is a reply to message #569234] Mon, 05 November 2012 17:47 Go to previous message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
"alter table ... move;" invalidates indexes where "alter table ... shrink space;" does not.
Unless the table is heavily accessed, you can do the "alter table ... shrink space;" while the table is being accessed. The "alter table ... shrink space;" will
1) Move all rows in the table to the same extents.
2) Reset the rowid's in the indexes so that the indexes will NOT become unusable.
3) Remove extra extents from the table once the rows are copied.
ENDOCP1P > alter table alan enable row movement;

Table altered.

ENDOCP1P > alter table alan shrink space;

Table altered.

ENDOCP1P > alter table alan disable row movement;

Table altered.

I do snapshots on gv$segment_statistics to see if the table is being accessed. If not, I can slip in a lot of maintenance during lulls in the batch jobs.
ENDOCP1P > @logical605

GIG_PER_MINUTE INSTANC STATISTIC_NAME TYPE  OBJECT_NAME
-------------- ------- -------------- ----- ----------------------------
          .001 NDOCP1  logical reads  TABLE SYS.SEG$
          .001 NDOCP1  logical reads  INDEX SYS.I_OBJ4
          .001 NDOCP1  logical reads  INDEX SYS.I_COL3
          .001 NDOCP1  logical reads  TABLE SYS.JOB$
          .001 NDOCP1  logical reads  INDEX SYS.I_SYN2
          .001 NDOCP1  logical reads  INDEX SYS.I_TS#
          .001 NDOCP1  logical reads  INDEX SYS.I_OBJ5
          .003 NDOCP1  logical reads  INDEX SYS.I_COL1
          .003 NDOCP1  logical reads  INDEX SYS.I_COL2
          .003 NDOCP1  logical reads  TABLE SYS.FILE$
          .004 NDOCP1  logical reads  INDEX SYS.I_OBJ#
          .025 NDOCP2  logical reads  TABLE SYS.IND$
          .034 NDOCP4  logical reads  TABLE SYS.IND$
          .042 NDOCP4  logical reads  INDEX SYS.I_IND1
          .045 NDOCP3  logical reads  TABLE SYS.IND$
          .048 NDOCP3  logical reads  INDEX SYS.I_IND1
          .048 NDOCP1  logical reads  INDEX SYS.I_IND1
          .053 NDOCP2  logical reads  INDEX SYS.I_IND1
          .104 NDOCP3  logical reads  TABLE SYS.OBJ$
          .104 NDOCP4  logical reads  TABLE SYS.OBJ$
          .107 NDOCP2  logical reads  TABLE SYS.OBJ$
          .116 NDOCP3  logical reads  TABLE SYS.USER$
          .123 NDOCP4  logical reads  TABLE SYS.USER$
          .135 NDOCP2  logical reads  TABLE SYS.USER$
          .170 NDOCP1  logical reads  TABLE SYS.OBJ$
          .217 NDOCP1  logical reads  TABLE SYS.USER$
          .258 NDOCP1  logical reads  TABLE SYS.TS$
          .267 NDOCP1  logical reads  INDEX SYS.I_OBJ1
          .422 NDOCP2  logical reads  TABLE NFLPROD.DM_SYSOBJECT_R
          .595 NDOCP2  logical reads  TABLE SYS.TS$
          .602 NDOCP2  logical reads  INDEX SYS.I_OBJ1
          .608 NDOCP3  logical reads  TABLE SYS.TS$
          .624 NDOCP4  logical reads  TABLE SYS.TS$
          .640 NDOCP3  logical reads  INDEX SYS.I_OBJ1
          .642 NDOCP4  logical reads  INDEX SYS.I_OBJ1
          .776 NDOCP2  logical reads  TABLE NFLPROD.DM_SYSOBJECT_S
          .844 NDOCP2  logical reads  INDEX NFLPROD.D_1F000D5D8000010A
         1.649 NDOCP2  logical reads  INDEX NFLPROD.D_1F000D5D80000109
         1.649 NDOCP2  logical reads  INDEX NFLPROD.D_1F000D5D80000146
         2.451 NDOCP2  logical reads  INDEX NFLPROD.DM_SYSOBJECT_R_COMP2
         2.451
    ----------
sum     13.421

logical605.sql looks like the following:
set termout off
drop table gv$segment_statistic1 purge;
drop table gv$segment_statistic2 purge;
column statistic_name format a14
column object_name format a50
column object_type format a11
column gig_per_minute format 9999.999
column type format a5
column instance format a7
SET WRAP OFF
SET TRUNC OFF
SET LINES 200
break on report
compute sum of gig_per_minute break on report
compute sum of buf_per_min break on report
compute sum of buf_per_sec break on report
create table gv$segment_statistic1 as
select * from gv$segment_statistics
where upper(statistic_name) like  '%LOGICAL%'
and (upper(statistic_name) like '%READ%' or upper(statistic_name) like '%WRITE%');
execute dbms_lock.sleep(5);
create table gv$segment_statistic2 as
select * from gv$segment_statistics
where upper(statistic_name) like  '%LOGICAL%'
and (upper(statistic_name) like '%READ%' or upper(statistic_name) like '%WRITE%');
set wrap off
set pages 50
set termout on
select b.value last, a.value first,(b.value-a.value)*12 buf_per_min,
(b.value-a.value)*12/60 buf_per_sec,
(b.value-a.value)*12*8192/1024/1024/1024*1 GIG_PER_MINUTE,
i.instance_name instance,a.statistic_name,a.object_type type,
a.owner||'.'||a.object_name||' '||a.subobject_name object_name
from gv$segment_statistic2 b,gv$segment_statistic1 a,gv$instance i
where a.statistic_name=b.statistic_name and a.inst_id=b.inst_id
and b.inst_id=i.inst_id and a.inst_id=i.inst_id
and a.object_type=b.object_type
and a.owner||'.'||a.object_name||' '||a.subobject_name
=b.owner||'.'||b.object_name||' '||b.subobject_name
and a.object_name not like 'GV$SEGMENT_STATISTICS%'
and b.value-a.value > 0
order by b.value-a.value;
set termout off
drop table gv$segment_statistic1 purge;
drop table gv$segment_statistic2 purge;
set termout on
Previous Topic: Performance degrades as dB size increases
Next Topic: Long running query
Goto Forum:
  


Current Time: Thu Mar 28 19:32:55 CDT 2024