Home » RDBMS Server » Server Administration » Defragmentation of partitioned indexes to reclaim space. (Oracle 12C)
Defragmentation of partitioned indexes to reclaim space. [message #685828] Mon, 04 April 2022 14:01 Go to next message
kurki
Messages: 22
Registered: December 2013
Junior Member
Hello All,

Recently, We have noticed a huge fragmentation of tables in oracle 12c and those tables have partitioned indexes.
I tried to move partitions and sub-partitions within the tablespace. But still the fragmentation percentage is the same.
Can anyone suggest what is the best way to defragment partitioned indexes please ?

Thanks,
Kurki
Re: Defragmentation of partitioned indexes to reclaim space. [message #685829 is a reply to message #685828] Mon, 04 April 2022 14:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68259
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What do you call fragmentation?
What do you mean by "huge"? What is the figure?
How do you compute it?

[Updated on: Mon, 04 April 2022 14:09]

Report message to a moderator

Re: Defragmentation of partitioned indexes to reclaim space. [message #685830 is a reply to message #685829] Mon, 04 April 2022 14:16 Go to previous messageGo to next message
kurki
Messages: 22
Registered: December 2013
Junior Member
Hi Michel,

Actual table size is 1013 MB and we have only 113 MB of data and rest is all fragmented as we have done purge on some tables.
and the percentage is almost 98 above.

Thanks,
Kurki
Re: Defragmentation of partitioned indexes to reclaim space. [message #685831 is a reply to message #685830] Mon, 04 April 2022 14:26 Go to previous messageGo to next message
EdStevens
Messages: 1375
Registered: September 2013
Senior Member
kurki wrote on Mon, 04 April 2022 14:16
Hi Michel,

Actual table size is 1013 MB and we have only 113 MB of data and rest is all fragmented as we have done purge on some tables.
and the percentage is almost 98 above.

Thanks,
Kurki
If the tables are expected to grow, then just leave it alone. That 'fragmented' space still belongs to the table and indexes, and will be used as needed.

The amount of space you are talking about "saving" is less than a rounding error on a cheap thumb drive.
Re: Defragmentation of partitioned indexes to reclaim space. [message #685832 is a reply to message #685831] Mon, 04 April 2022 14:33 Go to previous messageGo to next message
kurki
Messages: 22
Registered: December 2013
Junior Member
Hi Michel,

From a single schema, we have nearly 658 GB fragmented space from around 200 + tables.
So by reorganizing these tables and partitioned indexes, good amount of space can be released to the disk.

Thanks,
Kurki
Re: Defragmentation of partitioned indexes to reclaim space. [message #685833 is a reply to message #685832] Tue, 05 April 2022 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68259
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 04 April 2022 21:07

What do you call fragmentation?
What do you mean by "huge"? What is the figure?
How do you compute it?

Re: Defragmentation of partitioned indexes to reclaim space. [message #685834 is a reply to message #685828] Tue, 05 April 2022 06:46 Go to previous messageGo to next message
John Watson
Messages: 8738
Registered: January 2010
Location: Global Village
Senior Member
kurki wrote on Mon, 04 April 2022 20:01
Hello All,

Recently, We have noticed a huge fragmentation of tables in oracle 12c and those tables have partitioned indexes.
I tried to move partitions and sub-partitions within the tablespace. But still the fragmentation percentage is the same.
Can anyone suggest what is the best way to defragment partitioned indexes please ?

Thanks,
Kurki
alter table ... enable row movement;
alter table ... shrink space cascade;
It may take a while but there is no down time.


Re: Defragmentation of partitioned indexes to reclaim space. [message #685835 is a reply to message #685834] Tue, 05 April 2022 06:57 Go to previous messageGo to next message
kurki
Messages: 22
Registered: December 2013
Junior Member
Hi John,

Yes, I followed the same process along with table move but for partitioned tables, table move does not work.
So we need to move partitions individually.

But the major concern here is the space is not getting released to the disk after the shrink space command.

Please advise.


Thanks,
Kurki
Re: Defragmentation of partitioned indexes to reclaim space. [message #685836 is a reply to message #685835] Tue, 05 April 2022 06:58 Go to previous messageGo to next message
John Watson
Messages: 8738
Registered: January 2010
Location: Global Village
Senior Member
I've already told you exactly what to do.
Re: Defragmentation of partitioned indexes to reclaim space. [message #685837 is a reply to message #685835] Tue, 05 April 2022 10:55 Go to previous messageGo to next message
EdStevens
Messages: 1375
Registered: September 2013
Senior Member
Quote:
space is not getting released to the disk after the shrink space command.
Table maintenance operations do not shrink the data file, which would be required to release the space back to the OS (disk). They only rearrange the extents within the data file. Which is not a bad thing. Acquiring additional extents to accommodate segment growth is a rather expensive operation. If the tablespace (an abstraction of data files) does not have enough unallocated space to accommodate allocation of more extents, it will have to extend (grow) a data file, which is even more expensive. Unless you have performed a massive delete of table data and do not expect any future (re)growth, or you wildly over-allocated in the first place, trying to reclaim this space back to the disk is a fool's errand. Just leave it alone, and future table growth will use it.

And you still haven't responded to requests for how _you_ define 'fragmentation' and why you think it is an issue to be solved. With locally managed tablespaces (available since, I believe, 10g) "fragmentation" (un-allocated spaces that are too small to be allocated to an extent) becomes an issue only in very rare cases.
Re: Defragmentation of partitioned indexes to reclaim space. [message #685842 is a reply to message #685837] Wed, 06 April 2022 03:44 Go to previous messageGo to next message
kurki
Messages: 22
Registered: December 2013
Junior Member
Hi,

Sorry for the delayed response and Thank you all for your suggestions.

Main reason to plan for this activity is slow running queries. Actual table size is in MB's but fragmented space is in GB's.

Yesterday, I have moved all the partitions and did a shrink space for nearly 10 tables which are > 95 % fragmented and got nearly 250 GB space released to the disk.
Today, I am going to monitor the queries for next couple of the days or for a week.

Thanks,
Kurki

Re: Defragmentation of partitioned indexes to reclaim space. [message #685845 is a reply to message #685842] Wed, 06 April 2022 09:09 Go to previous message
EdStevens
Messages: 1375
Registered: September 2013
Senior Member
Quote:
Main reason to plan for this activity is slow running queries
And exactly what diagnostics did you perform, that lead you to believe that "fragmentation" was the cause?

In spite of multiple requests, you still have not told us what it is that you consider to be "fragmentation".
Previous Topic: tnsping sqlnet.ora not listing
Next Topic: SGA/PGA history
Goto Forum:
  


Current Time: Sun Aug 14 16:56:09 CDT 2022