OraFAQ Forum: Server Administration » Defragmentation of partitioned indexes to reclaim space.

Home » RDBMS Server » Server Administration » Defragmentation of partitioned indexes to reclaim space. (Oracle 12C)

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

Defragmentation of partitioned indexes to reclaim space. [message #685828]

Mon, 04 April 2022 14:01

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

Report message to a moderator

Re: Defragmentation of partitioned indexes to reclaim space. [message #685829 is a reply to message #685828]

Mon, 04 April 2022 14:07

Michel Cadot
Messages: 68500
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

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

Report message to a moderator

Re: Defragmentation of partitioned indexes to reclaim space. [message #685831 is a reply to message #685830]

Mon, 04 April 2022 14:26

EdStevens
Messages: 1376
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.

Report message to a moderator

Re: Defragmentation of partitioned indexes to reclaim space. [message #685832 is a reply to message #685831]

Mon, 04 April 2022 14:33

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

Report message to a moderator

Re: Defragmentation of partitioned indexes to reclaim space. [message #685833 is a reply to message #685832]

Tue, 05 April 2022 00:51

Michel Cadot
Messages: 68500
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?

Report message to a moderator

Re: Defragmentation of partitioned indexes to reclaim space. [message #685834 is a reply to message #685828]

Tue, 05 April 2022 06:46

John Watson
Messages: 8869
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.

Report message to a moderator

Re: Defragmentation of partitioned indexes to reclaim space. [message #685835 is a reply to message #685834]

Tue, 05 April 2022 06:57

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

Report message to a moderator

Re: Defragmentation of partitioned indexes to reclaim space. [message #685836 is a reply to message #685835]

Tue, 05 April 2022 06:58

John Watson
Messages: 8869
Registered: January 2010
Location: Global Village

Senior Member

I've already told you exactly what to do.

Report message to a moderator

Re: Defragmentation of partitioned indexes to reclaim space. [message #685837 is a reply to message #685835]

Tue, 05 April 2022 10:55

EdStevens
Messages: 1376
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.

Report message to a moderator

Re: Defragmentation of partitioned indexes to reclaim space. [message #685842 is a reply to message #685837]

Wed, 06 April 2022 03:44

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

Report message to a moderator

Re: Defragmentation of partitioned indexes to reclaim space. [message #685845 is a reply to message #685842]

Wed, 06 April 2022 09:09

EdStevens
Messages: 1376
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".

Report message to a moderator

Previous Topic:	tnsping sqlnet.ora not listing
Next Topic:	SGA/PGA history

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 22 16:59:22 CDT 2023