DB version: 11.2.0.4
Platform : Oracle Linux 6.5
3-Node RAC DB
We have a 23 TB DB and we have been encountering some performance issues lately. Its mostly due to badly written queries, huge volume of data in resultset, ..etc. But, this application's manager has asked in a meeting if I could check that the tablespaces (not tables) are fragmented . Apparently , in his old shop , the DBA over there has done some 'Defragmenting' of tablespaces for the same application to fix some of the Performance issues.
To resize datafiles we have to move around DB objects to other tablespace so that the HWM in a particular datafile is reset and then we could run the RESIZE command. But this is not 'Defragmenting'.
In the below thread, Justin Cave explains that "a locally managed tablespace effectively cannot be fragmented".
What did Justin mean by this ? Why don't we need 'defragmentation' for Locally managed Tablespaces ?
Since I've worked only on 11g, I don't know the history . Did , DBA's do some kind of defragmentation for older versions ?
https://community.oracle.com/thread/2508361