Reorg Tables with or w/o tools 3

[HeathRamos]

We recently deleted numerous entries from a single table and it has been suggested to be that I need to reorg the table now for performance reasons.

I have never reorg'd a table before (running Oracle 8i, btw).

From my understanding, I can do this one of two ways, either using an oracle or third party tool or command line.

I have a copy of Space Manager (Quest software) and it can be used to create a script to do this. I am just not sure the options I should choose. (pretty sure I would have to create a table in a temp tablespace since the table is large).

I could also export the table, drop it and then import it back into the tablespace. I don't know the options I should use. Also...I know people can't be logged on for this option but do I have to take it offline? Put the database in a mount state?

 

[SantaMufasa]

Heath,

...No need for special tools...no need for time consuming export/import...no need to create table in a temp tablespace (if you have enough room accommodate extents during reorg)...no need to put the database in MOUNT state...no need to take the table off-line, although it would be nice to have no one updating the table during the reorg.

The absolutely, possitively lightning-fastest method to reorg a table with the least fuss and muss is:

alter table <owner>.<table_name> move parallel nologging;

This should work for you at a rate of 6MB+/- per second.

To reorg the index, you can use this code:

alter index <owner>.<index_name>rebuild parallel;

If you do want to place the reorganised object(s) in a different tablespace, you may do so by including the clause "tablespace <other ts-name>" somewhere before the ";" and following the object-name specification.

Let us know how this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[HeathRamos]

Can I assume I have to reorg every index associated with the table?

Example: select index_name from dba_indexes where table_name='TABLE';

 

[SantaMufasa]

That's a good question, Heath. I'm not certain on that one. But one thing for sure: if you have deleted a lot of rows from the table, then the index has a bunch of "Swiss Cheese", as well...I highly recommend reorging the index, as well.



Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[HeathRamos]

Well...I will reorg the indexes just in case.

Now...it looks like I won't have enough free space to reorg the table within the tablespace (table is 903mb and free space is 135mb...not sure if I would have to coalesce free space in order to use the full amount or not).

I guess I could create a new tablespace and allocate 1gb to it, issue the alter table command to reorg it and then move it back to the original tablespace afterwards. Should I reorg the indexes after the 2nd move?

Also...how would I determine which tables need to be reorganized so that I will know if it worked?

 

[Beilstwh]

Why not just extend the original tablespace by 1 gig? If you are down to only 135mb available, I would add more space to the tablespace.

Bill
Oracle DBA/Developer
New York State, USA

 

[SantaMufasa]

Agreed, Bill, but I've seen as much as 2GB of "Swiss Cheese" squeezed out of tables that have "high transience" (lots of INSERTS then DELETES). It's also good to "(re-)use whatcha already got," in addition to throwing more space at the problem.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[HeathRamos]

I am still unsure when I need to reorg a table.

Oracle doc:
In the following cases, it may be worthwhile to
a. rebuild a table:
--> CHAIN_CNT is getting worse due to migrated rows
--> AVG_SPACE is getting worse because of deletes
b. retrieve unused blocks when the HWM (high water mark) remains far below the size of the segment: EMPTY_BLOCKS stays high

The table in question has 81 chained rows (row count: 3,416,367) and the avergae free space per block is 1,015. (stats from report in Space Manager software).

 

[carp]

And yes, you will need to rebuild your indexes. The reason is that all of the rowids in your table are changing and Oracle doesn't update the tables to reflect this.

Here is a package I wrote to implement this operation - we use it quite a bit here, especially during data conversions when we are running large volumes of data through Oracle's interface tables. It moves the specified table, rebuilds the indexes, and then resets the statistics on the table. It's not pretty code - something that I dashed off, it worked the first time, and we slapped it into production in the middle of a project. So feel free to clean it up to suit your needs ("if thine global variables offend thee, pluck them out!", etc). Note that the procedure is overloaded so that you can invoke it from within code/interactively or run it as a concurrent request. If you are not using Oracle Apps, then you will probably need to get rid of all of the calls to fnd_file (or modify them to write output to wherever you want).
Of course, if you are dealing with a busy table, it is likely to have a lock on it. If this happens, you will have an exception raised and have to try again later.

CREATE OR REPLACE PACKAGE "XX_REBUILD_TABLE" AS
-- VERSION 1.0 
-- PURPOSE: REBUILD A TABLE AND ITS INDEXES 

   -- USE FROM COMMAND LINE 
   PROCEDURE rebuild(p_schema IN VARCHAR2,
                     p_table  IN VARCHAR2);
   -- USE FOR CONCURRENT REQUESTS 
   PROCEDURE rebuild(errbuf OUT VARCHAR2, retcode OUT VARCHAR2,
                     p_schema IN VARCHAR2,
                     p_table  IN VARCHAR2);
END;
/

CREATE OR REPLACE PACKAGE BODY "XX_REBUILD_TABLE" AS
-- VERSION: 1.0
-- PURPOSE: REBUILD A SPECIFIED TABLE, ITS INDEXES, AND STATISTICS
-- HISTORY: 11-JUN-05 - VERSION 1.0 - IOC
   g_row     dba_tables%ROWTYPE;
   g_extents NUMBER;
   g_bytes   NUMBER;
   g_phase   VARCHAR2(30);
--===================================================================================================
   PROCEDURE rebuild_table(p_schema IN VARCHAR2, p_table IN VARCHAR2) IS
   -- PURPOSE: MOVE THE TABLE
   BEGIN
      g_phase := 'rebuild_table';
      fnd_file.put_line(fnd_file.output,'<LI>Moving '||p_schema||'.'||p_table);
      EXECUTE IMMEDIATE 'ALTER TABLE '||p_schema||'.'||p_table||' MOVE';
      fnd_file.put_line(fnd_file.output,'<LI><FONT COLOR="green">Done!</FONT>');
   END rebuild_table;
--===================================================================================================
   PROCEDURE rebuild_indexes(p_schema IN VARCHAR2, p_table IN VARCHAR2) IS
   -- PURPOSE: REBUILD INDEXES FOR TABLE
   BEGIN
      g_phase := 'rebuild_indexes';
      FOR i IN (SELECT owner, index_name FROM dba_indexes
                 WHERE table_owner = upper(p_schema)
                   AND table_name = upper(p_table)
                   AND status != 'VALID'
                 ORDER BY 1,2) LOOP
         fnd_file.put_line(fnd_file.output,'<LI>Rebuilding Index '||i.owner||'.'||i.index_name);
         BEGIN
            EXECUTE IMMEDIATE 'ALTER INDEX '||i.owner||'.'||i.index_name||' REBUILD';
            fnd_file.put_line(fnd_file.output,'<LI><FONT COLOR="green">Success!</FONT>');
         EXCEPTION
            WHEN OTHERS THEN
               fnd_file.put_line(fnd_file.output,'<LI><FONT COLOR="red">'||sqlerrm||'</FONT>');
               RAISE_APPLICATION_ERROR(-20001,sqlerrm);
         END;
      END LOOP;
   END rebuild_indexes;
--===================================================================================================
   PROCEDURE rebuild_statistics(p_schema IN VARCHAR2, p_table IN VARCHAR2) IS
   -- PURPOSE: REGATHER STATS ON TABLE
      l_row          dba_tables%ROWTYPE;
      l_num_rows     NUMBER;
      l_blocks       NUMBER;
      l_empty_blocks NUMBER;
      l_avg_space    NUMBER;
      l_chain_cnt    NUMBER;
      l_avg_row_len  NUMBER;
      l_old_extents  NUMBER;
      l_old_bytes    NUMBER;
      l_new_extents  NUMBER;
      l_new_bytes    NUMBER;
   BEGIN
      g_phase := 'rebuild_statistics';
      -- REGATHER THE STATS
      EXECUTE IMMEDIATE 'ANALYZE TABLE '||p_schema||'.'||p_table||' ESTIMATE STATISTICS';

      -- GET THE AFTER PICTURE
      SELECT * INTO l_row FROM dba_tables WHERE owner = upper(p_schema) AND table_name = UPPER(p_table);
      SELECT count(*), SUM(bytes)/1024 INTO l_new_extents, l_new_bytes
        FROM dba_extents
       WHERE owner = UPPER(p_schema) AND segment_name = UPPER(p_table);
      -- OUTPUT THE BEFORE/AFTER TABLE
      fnd_file.put_line(fnd_file.output,'<H4>Statistics Comparison</H4><TABLE BORDER=1>
                                     <TR><TH>Statistic</TH><TH>Before</TH><TH>After</TH></TR>');
      fnd_file.put_line(fnd_file.output,'<TR><TD>Rows</TD><TD>'||g_row.num_rows||'</TD><TD>'||l_row.num_rows||'</TD></TR>');
      fnd_file.put_line(fnd_file.output,'<TR><TD>Blocks</TD><TD>'||g_row.blocks||'</TD><TD>'||l_row.blocks||'</TD></TR>');
      fnd_file.put_line(fnd_file.output,'<TR><TD>Empty Blocks</TD><TD>'||g_row.empty_blocks||'</TD><TD>'||l_row.empty_blocks||'</TD></TR>');
      fnd_file.put_line(fnd_file.output,'<TR><TD>Avg Space</TD><TD>'||g_row.avg_space||'</TD><TD>'||l_row.avg_space||'</TD></TR>');
      fnd_file.put_line(fnd_file.output,'<TR><TD>Chain Count</TD><TD>'||g_row.chain_cnt||'</TD><TD>'||l_row.chain_cnt||'</TD></TR>');
      fnd_file.put_line(fnd_file.output,'<TR><TD>Avg Row Length</TD><TD>'||g_row.avg_row_len||'</TD><TD>'||l_row.avg_row_len||'</TD></TR>');
      fnd_file.put_line(fnd_file.output,'<TR><TD>Extents</TD><TD>'||g_extents||'</TD><TD>'||l_new_extents||'</TD></TR>');
      fnd_file.put_line(fnd_file.output,'<TR><TD>Total kBytes</TD><TD>'||g_bytes||'</TD><TD>'||l_new_bytes||'</TD></TR>');
      fnd_file.put_line(fnd_file.output,'</TABLE>');
   EXCEPTION
      WHEN OTHERS THEN
         fnd_file.put_line(fnd_file.output,'<FONT COLOR="red">Error Collecting Stats: '||sqlerrm||'</FONT>');
   END rebuild_statistics;
--===================================================================================================
   PROCEDURE rebuild(p_schema IN VARCHAR2,
                     p_table  IN VARCHAR2) IS
   -- PURPOSE: DRIVER PROGRAM
      l_error BOOLEAN := FALSE;
      BEGIN
	     g_phase := 'rebuild (non-CCR)';
         fnd_file.put_line(fnd_file.output,'<HTML><HEAD><H3>Rebuilding Table '||p_schema||'.'||p_table||'</H3></HEAD>');
         fnd_file.put_line(fnd_file.output,'<BODY><HR><UL>');
         l_error := FALSE;
         -- SAVE THE BEFORE IMAGE OF THE STATS
         SELECT * INTO g_row FROM dba_tables WHERE owner = upper(p_schema) AND table_name = UPPER(p_table);
         SELECT count(*), SUM(bytes)/1024 INTO g_extents, g_bytes
           FROM dba_extents
          WHERE owner = UPPER(p_schema) AND segment_name = UPPER(p_table);
         -- MOVE THE TABLE
         rebuild_table(p_schema, p_table);
         -- REBUILD INDEXES
         rebuild_indexes(p_schema, p_table);
         fnd_file.put_line(fnd_file.output,'</UL>');
         -- REGATHER STATS
         rebuild_statistics(p_schema, p_table);
         fnd_file.put_line(fnd_file.output,'</BODY></HTML>');
   EXCEPTION
      WHEN OTHERS THEN 
	     fnd_file.put_line(fnd_file.output,
		                  '<LI><FONT COLOR="red"> In '||g_phase||'Error: '||sqlerrm
						  ||'</FONT></BODY></HTML>');
	     fnd_file.put_line(fnd_file.log,'In '||g_phase||'Error: '||sqlerrm);
         RAISE_APPLICATION_ERROR(-20000,'Something Went Amiss! '||sqlerrm);
    END rebuild;
--
--*****************************************************************************
--
	   PROCEDURE rebuild(errbuf OUT VARCHAR2, retcode OUT VARCHAR2,
                         p_schema IN VARCHAR2,
                         p_table  IN VARCHAR2) IS
	   BEGIN
	      g_phase := 'rebuild (CCR)';
	      rebuild(p_schema, p_table);
	   END rebuild;
END;
/

 

[SantaMufasa]

Thanks for posting, Carp. I was trying to search for the thread where your "SwissCheese" script resided, (but Tek-Tips's search feature needs lotsa work) and I couldn't find it.

And just 'cuz I always enjoy your work, please have a Purple Star for this masterpiece.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[HeathRamos]

thanks for the script. I will give it a try.

I analyzed the table in question.

dba_segments has 225,940 blocks for it as well as 20 extents.
dba_tables has 219,429 blocks, 6,510 empty_blocks and 3,416,367 rows (chain_cnt is 81 and avg_space is 1015).

the number of used blocks is 170,409.

Is this a large number of allocated blocks that are empty (below hwm)?