RE: Space reclamation

and…  

IF you have many repeating values on a column you are compressing (or set of columns), it *may* be useful to physically order your data in that order. It will save space, especially if you have many blocks that can end up with a single substantial in storage size value per block by doing this.  

IF you have time based partitions that become static, the appropriate rebuild may only need to be done once after it becomes static and you won’t see a downside from packing very tightly (zero percent free) in the partitions that no longer change.  

IF there is also a correspondence and compatibility of the compressed columns and a commonly used index, this *may* significantly shrink the amount of disk your need to read to retrieve row data as well as informing the CBO that the cluster factor is optimal.  

Please don’t miss any of the IFs, and only get on a rebuilding treadmill for a very good and well understood reason. Rebuilding once, or once per partition, is a very different proposition from recurrent rebuilding. Often folks get a dramatic improvement in both size and performance from a “first rebuilding” of an object that has accumulated. The mental trap is to therefore add wholesale rebuilding to your operational schedule when the benefit is not recurrent.  

This message is best evaluated AFTER getting up to speed in accordance with the explanations on JL’s pages.  

Good luck!  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis Sent: Tuesday, March 02, 2021 12:02 PM
To: mohamed.houri_at_gmail.com
Cc: Shane Borden; Lok P; Oracle L
Subject: Re: Space reclamation  

Mohamed,  

Thanks for mentioning that one - but you should have mentioned that you've also published variations on the theme to address partitoined indexes, which I never got around to doing:

https://www.red-gate.com/simple-talk/sql/oracle/oracle-partitioned-index-efficiency/      

Regards

Jonathan Lewis      

On Tue, 2 Mar 2021 at 13:11, Mohamed Houri <mohamed.houri_at_gmail.com> wrote:

Lock,

As already mentioned by Shane, I think it is a better idea to focus your attention, first, on the index size. If you want to find out indexes occupying more space than they should then you can use the following script supplied by Jonathan Lewis

https://jonathanlewis.wordpress.com/index-sizing/

 I have used this script several times in real life running systems, like financial software Calypso, where rebuilding 7 or 8 indexes reduce the overall database size by 570GB.

In addition, the prevision of this script is very precise.

https://hourim.wordpress.com/2015/05/12/index-efficiency/

However, as you can read it in the comment part of Jonathan’s script, only b-tree, and function based indexes are targeted by this script

Best regards.

Mohamed Houri      

Le mar. 2 mars 2021 à 13:42, Shane Borden <dmarc-noreply_at_freelists.org> a écrit :

Focus your efforts on indexes that consume more space than its table and also utilize avg_row_len x num_rows compared to bytes used by the table to determine tables that need attention.

It’s always worth maintaining your database.

Shane Borden
sborden76_at_yahoo.com
Sent from my iPhone

> On Mar 2, 2021, at 5:48 AM, Lok P <loknath.73_at_gmail.com> wrote:
>
> 
> Hello Listers, We have got three different Oracle databases with rdbms version 11.2.0.4, 12.1.0.2.0 and 19C. We got an ask from management suggesting to look into all the objects(Tables , Indexes) having >25% free space and get those reclaimed to have some storage back to disk. The intention is two fold 1)To have unused space back to the storage/disk(which in turns saves cost) 2) It may help us in improving the table/index scan because of compact size. And the team wants us to have those reclaim jobs set/run on a regular basis so that it can automatically find and run the reorg on its own . I have two questions here
>
> 1) Should we go by checking the empty_blocks column and compare it with the total block to find the eligible segments and then rebuild those?
>
> 2)I read a few blog posts which suggest reclaiming space may not worth the effort because those spaces will eventually be consumed again by data insert/manipulation. So is that true and we should not take up this effort in the first place?
>
> Regards
> Lok

--
http://www.freelists.org/webpage/oracle-l






 

-- 

Houri Mohamed 

Oracle DBA-Developer-Performance & Tuning 

Visit My         - Blog <http://www.hourim.wordpress.com/> 

Let's Connect - <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>  Linkedin Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/> 

My  <https://twitter.com/MohamedHouri> Twitter      - MohamedHouri <https://twitter.com/MohamedHouri> 





--
http://www.freelists.org/webpage/oracle-l