OraFAQ Forum: Server Administration » How to Reclaiming Wasted Space throughly

Home » RDBMS Server » Server Administration » How to Reclaiming Wasted Space throughly (Oracle 10.2.0.5 64bit, HPUX PA 11.11 64bit)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to Reclaiming Wasted Space throughly [message #580651]

Tue, 26 March 2013 09:26

snowball
Messages: 229
Registered: April 2006
Location: China

Senior Member

Hi, Guys
In one of our Data warehousing DB, even though, all the tablespaces' space should keep on at least 1 month, but our leader want us to estimate how much space can free up with db method.

I have referenced
<Administrator Guide> - Reclaiming Wasted Space
http://docs.oracle.com/cd/B19306_01/server.102/b14231/schema.htm#sthref2092

I have several questions on reclaim space:
1. It seems that segment advisor give a really cool view to know which segment should be shrinked and how much size will free up after shrinked.
But actually, this need a very a job or manually do this. I have once heard about some query from can estimate this :

A script from MOS, but actually I found this it's not very accurate with segment advisor.
This script should report the real space the table occupy, but after shrink space, the space doesn't free up.

SELECT TABLE_NAME ,  (BLOCKS *8192 / 1024/1024 ) - (NUM_ROWS*AVG_ROW_LEN/1024/1024) 
"Data lower than HWM in MB"   FROM  DBA_TABLES WHERE  UPPER(owner) =UPPER('&OWNER') order by 2 desc;

Also there is a script evaluate the tablespace fragments from some people:

SELECT   tablespace_name,                       
           SQRT (MAX (blocks) / SUM (blocks))     
           * (100 / SQRT (SQRT (COUNT (blocks)))) 
              FSFI                                
    FROM   dba_free_space                         
GROUP BY   tablespace_name                        
ORDER BY   1;

if the value is very low, we can coalesce the tablespace. But after I coalesce the tablespace, I can't see any space free up.
From <Concepts>:
Quote:

Coalescing extents is not necessary in
locally managed tablespaces, because all contiguous free space is available for
allocation to a new extent regardless of whether it was reclaimed from one or more
extents.

I don't know if these script right or not, OR i wrongly understand these scripts. If I were, please forgive me as I am not English native speaker.

2. is there any good way to estimate how much space we should free up? ( After free up the space , dba_free_space should see this result)

Thanks very much.

Report message to a moderator

Re: How to Reclaiming Wasted Space throughly [message #580658 is a reply to message #580651]

Tue, 26 March 2013 11:23

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

DBMS_SPACE has 2 procedures UNUSED_SPACE and FREE_SPACE (depending if your tablespace is in ASSM mode or not) to help you determine if some segments can benefit of a shrink or not. This for space inside the tablespaces.
Then to get back the space to FS or diskgroup (depending if you use ASM or not), you can use DBA_FREE_SPACE and DBA_EXTENTS to know if you can shrink tablespace files or not.
Coalesce of tablespaces 1) is useless on LMT and 2) in DMT, it does not add any free space, it just coalesces adjacent free extents.

Regards
Michel

Report message to a moderator

Re: How to Reclaiming Wasted Space throughly [message #580660 is a reply to message #580658]

Tue, 26 March 2013 11:36

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I have several questions on reclaim space
Reclaim space from where to where?
Reclaim space that exists due to DELETE inside 1 or more tables?
Reclaim space that exists due to TRUNCATE or DROP?
Will new data ever be added such that it can reside in what is now "free space"?

Report message to a moderator

Re: How to Reclaiming Wasted Space throughly [message #580684 is a reply to message #580660]

Tue, 26 March 2013 16:31

alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California

Senior Member

I just simply create a temporary table and see if it took less space.

ECSCDAS1S > create table b as select * from alan;

Table created.

ECSCDAS1S > select bytes/1024/1024 megabytes,owner||'.'||segment_name from dba_segments
  2  where segment_name in ('B','ALAN');

 MEGABYTES OWNER||'.'||SEGMENT_NAME
---------- -------------------------
        13 ECSCDAS1S.B
        26 ECSCDAS1S.ALAN

Report message to a moderator

Re: How to Reclaiming Wasted Space throughly [message #580689 is a reply to message #580658]

Tue, 26 March 2013 18:58

snowball
Messages: 229
Registered: April 2006
Location: China

Senior Member

Thanks, Michel.
I can see from dbms_space package, that there are 2 procedures related with space estimate:
Quote:

SPACE_USAGE Procedure
Returns information about free blocks in an auto segment space managed segment
UNUSED_SPACE Procedure
Returns information about unused space in an object (table, index, or cluster)

I try both, but don't really know how those two can give me the benefit of shrink or not.

Before delete some records of the table:

SQL> select segment_name, bytes from user_segments where segment_name='TAB2';

SEGMENT_NAME              BYTES
-------------------- ----------
TAB2                    6291456


SQL> variable unf number; 
SQL> variable unfb number; 
SQL> variable fs1 number; 
SQL> variable fs1b number; 
SQL> variable fs2 number; 
SQL> variable fs2b number; 
SQL> variable fs3 number; 
SQL> variable fs3b number; 
SQL> variable fs4 number; 
SQL> variable fs4b number; 
SQL> variable full number; 
SQL> variable fullb number; 
SQL> 
SQL> begin 
  2  dbms_space.space_usage('SCOTT','TAB2', 
  3                          'TABLE', 
  4                          :unf, :unfb, 
  5                          :fs1, :fs1b, 
  6                          :fs2, :fs2b, 
  7                          :fs3, :fs3b, 
  8                          :fs4, :fs4b, 
  9                          :full, :fullb); 
 10  end; 
 11  / 

PL/SQL procedure successfully completed.

SQL> select :fullb+:fs1b+:fs2b+:fs3b+:fs4b from dual;

:FULLB+:FS1B+:FS2B+:FS3B+:FS4B
------------------------------
                       5758976

SQL> VAR total_blocks NUMBER;
SQL> VAR total_bytes NUMBER;
SQL> VAR unused_blocks NUMBER;
SQL> VAR unused_bytes NUMBER;
SQL> VAR lastextf NUMBER;
SQL> VAR last_extb NUMBER;
SQL> VAR lastusedblock NUMBER;
EXEC DBMS_SPACE.UNUSED_SPACE('SCOTT', 'TAB2', 'TABLE', :total_blocks,:total_bytes,:unused_blocks, :unused_bytes, :lastextf, :last_extb, :lastusedblock); ck); 

PL/SQL procedure successfully completed.

SQL> 
SQL> PRINT :unused_bytes;

UNUSED_BYTES
------------
       40960

SQL> SELECT COUNT(*) FROM TAB2;

  COUNT(*)
----------
     51128

After delete the most data of the table:

SQL> SELECT COUNT(*) FROM TAB2;

  COUNT(*)
----------
     51128

SQL> DELETE FROM TAB2 WHERE ROWNUM <= 50000;

50000 rows deleted.

SQL> COMMIT;

Commit complete.


SQL> variable unf number; 
SQL> variable unfb number; 
SQL> variable fs1 number; 
SQL> variable fs1b number; 
SQL> variable fs2 number; 
SQL> variable fs2b number; 
SQL> variable fs3 number; 
SQL> variable fs3b number; 
SQL> variable fs4 number; 
SQL> variable fs4b number; 
SQL> variable full number; 
SQL> variable fullb number; 
SQL> 
SQL> begin 
  2  dbms_space.space_usage('SCOTT','TAB2', 
  3                          'TABLE', 
  4                          :unf, :unfb, 
  5                          :fs1, :fs1b, 
  6                          :fs2, :fs2b, 
  7                          :fs3, :fs3b, 
  8                          :fs4, :fs4b, 
  9                          :full, :fullb); 
 10  end; 
 11  / 

PL/SQL procedure successfully completed.


SQL>  select :fullb+:fs1b+:fs2b+:fs3b+:fs4b from dual;

:FULLB+:FS1B+:FS2B+:FS3B+:FS4B
------------------------------
                       5758976

SQL> VAR total_blocks NUMBER;
SQL> VAR total_bytes NUMBER;
SQL> VAR unused_blocks NUMBER;
SQL> VAR unused_bytes NUMBER;
SQL> VAR lastextf NUMBER;
SQL> VAR last_extb NUMBER;
SQL> VAR lastusedblock NUMBER;
SQL> EXEC DBMS_SPACE.UNUSED_SPACE('SCOTT', 'TAB2', 'TABLE', :total_blocks,:total_bytes,:unused_blocks, :unused_bytes, :lastextf, :last_extb, :lastusedblock); 

PL/SQL procedure successfully completed.

SQL> 
SQL> PRINT :unused_bytes;

UNUSED_BYTES
------------
      368640

After shrink table :

SQL> alter table scott.tab2 enable row movement;

Table altered.

SQL> alter table scott.tab2 shrink space;

Table altered.

SQL> alter table scott.tab2 disable row movement;

Table altered.

SQL> select segment_name, bytes from user_segments where segment_name='TAB2';

SEGMENT_NAME              BYTES
-------------------- ----------
TAB2                     196608



SQL> VAR total_blocks NUMBER;
SQL> VAR total_bytes NUMBER;
SQL> VAR unused_blocks NUMBER;
SQL> VAR unused_bytes NUMBER;
SQL> VAR lastextf NUMBER;
SQL> VAR last_extb NUMBER;
SQL> VAR lastusedblock NUMBER;
EXEC DBMS_SPACE.UNUSED_SPACE('SCOTT', 'TAB2', 'TABLE', :total_blocks,:total_bytes,:unused_blocks, :unused_bytes, :lastextf, :last_extb, :lastusedblock); ck); 

PL/SQL procedure successfully completed.

SQL> 
SQL> PRINT :unused_bytes;

UNUSED_BYTES
------------
       24576

SQL> 
SQL> 
SQL> 
SQL> variable unf number; 
SQL> variable unfb number; 
SQL> variable fs1 number; 
SQL> variable fs1b number; 
SQL> variable fs2 number; 
SQL> variable fs2b number; 
SQL> variable fs3 number; 
SQL> variable fs3b number; 
SQL> variable fs4 number; 
SQL> variable fs4b number; 
SQL> variable full number; 
SQL> variable fullb number; 
SQL> 
SQL> begin 
  2  dbms_space.space_usage('SCOTT','TAB2', 
  3                          'TABLE', 
  4                          :unf, :unfb, 
  5                          :fs1, :fs1b, 
  6                          :fs2, :fs2b, 
  7                          :fs3, :fs3b, 
  8                          :fs4, :fs4b, 
  9                          :full, :fullb); 
 10  end; 
 11  / 

PL/SQL procedure successfully completed.

SQL> select :fullb+:fs1b+:fs2b+:fs3b+:fs4b from dual;

:FULLB+:FS1B+:FS2B+:FS3B+:FS4B
------------------------------
                        139264

With the info from UNUSED_SPACE and UNUSED_SPACE, I can't figure out how I can get a estimate result after shrink.
Correct me if I'm wrong.

Thanks very much.

Report message to a moderator

Re: How to Reclaiming Wasted Space throughly [message #580690 is a reply to message #580660]

Tue, 26 March 2013 19:11

snowball
Messages: 229
Registered: April 2006
Location: China

Senior Member

Hi, BlackSwan
Quote:

Reclaim space from where to where?

Reclaim space from tables to tablespaces.

Quote:

Reclaim space that exists due to DELETE inside 1 or more tables?

Yes, there are some tables have delete actions, but I need to get a estimate release size after shrink.

Quote:

Reclaim space that exists due to TRUNCATE or DROP?

No. Truncate and Drop operations have been done, as this could release the space.
So we think of that if there is any possible to reclaim from table without archive or clean out the data.

Quote:

Will new data ever be added such that it can reside in what is now "free space"?

Of course, there are some new data will be added every day, but this db doesn't clean or reclaim the space with database way, but business rule.
With business rules, such as Table A should keep latest 3 months data, so after 3 months, data can be clean before latest 3 months.

Thanks very much.

Report message to a moderator

Re: How to Reclaiming Wasted Space throughly [message #580692 is a reply to message #580690]

Tue, 26 March 2013 22:06

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Of course, there are some new data will be added every day, but this db doesn't clean or reclaim the space with database way, but business rule.
>With business rules, such as Table A should keep latest 3 months data, so after 3 months, data can be clean before latest 3 months.

I contend that NOTHING needs to be done!
Table will hold a maximum of 4 months worth of data.
At the start of a new month, say 1 April then you DELETE data from December; keeping January through March (3 months of data)
April data will consume the space that the December data had consumed.

Report message to a moderator

Re: How to Reclaiming Wasted Space throughly [message #580693 is a reply to message #580692]

Tue, 26 March 2013 22:46

snowball
Messages: 229
Registered: April 2006
Location: China

Senior Member

Hi, BlackSwan
No ALL the tables has their business rule, so that's the problems.

Report message to a moderator

Re: How to Reclaiming Wasted Space throughly [message #580695 is a reply to message #580693]

Tue, 26 March 2013 23:08

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>No ALL the tables has their business rule, so that's the problems.
I do not understand.
What is example of business rule?
What is the problem?

Report message to a moderator

Re: How to Reclaiming Wasted Space throughly [message #580703 is a reply to message #580689]

Wed, 27 March 2013 01:56

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Parameter meanings are described in the documentation, fs1b, fs2b... must be weighted according to their meaning in your "select :fullb+:fs1b+:fs2b+:fs3b+:fs4b from dual;".
In short, more you have block with more free space more you are likely to get back space.
Note that shrinking segments mean more collisions on blocks as they contain more data and for indexes more block splits, so you decrease DML performances and concurrency.

Regards
Michel

Report message to a moderator

Re: How to Reclaiming Wasted Space throughly [message #580735 is a reply to message #580695]

Wed, 27 March 2013 10:36

snowball
Messages: 229
Registered: April 2006
Location: China

Senior Member

Quote:

What is example of business rule?

For example, some tables can drop every 3 months, but some tables only drop 1 years' data per year, but most of the tables, the developer say they're not sure what might be the retention, because some requirements from client.

Quote:

What is the problem?

I don't really know, what's the problem, because the data require from developers, so only a very small part of data can be cleaned at specific time.
That's the reality. I can't push or change .

Report message to a moderator

Re: How to Reclaiming Wasted Space throughly [message #580736 is a reply to message #580703]

Wed, 27 March 2013 10:44

snowball
Messages: 229
Registered: April 2006
Location: China

Senior Member

I will try to figure it out.
Thanks , Michel.

Report message to a moderator

Re: How to Reclaiming Wasted Space throughly [message #580737 is a reply to message #580735]

Wed, 27 March 2013 11:22

cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester

Senior Member

[quote title=snowball wrote on Wed, 27 March 2013 15:36]Quote:

Quote:

What is the problem?

I don't really know, what's the problem, because the data require from developers, so only a very small part of data can be cleaned at specific time.
That's the reality. I can't push or change .

Question is - why does any free space need to be reclaimed? It'll be used up again by the tables that had it in first place sooner or later.

Report message to a moderator

Previous Topic:	Problem with moving tables from older to the new DB
Next Topic:	Oracle Ocfs2 not working in Oracle enterprice linux 6.2

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 12 23:39:38 CST 2025