Calculate free space without reorg
we are currently on AIX and Oracle 126.96.36.199.0. The SAP System is BankAnalyzer, which is an add-on to a BIW (3.5). Data is loaded into the system via STS, a tool from a third party vendor. All relevant tables (BankAnalyzer and STS) are in one tablespace, the size of the tablespace is about 1 TB, of which STS-tables occupy about 400 GB. STS produces a lot of redundant overhead which we now want to archive and delete on a regular basis.
<b>Without</b> a DB-reorg, is there a way to tell how much space is currently free in the DB, and how much space ist occupied? As I understand, DB02 will keep showing the same figures until a reorg is done, even though we have deleted 200 GB from the tables. We need a way to tell the current free space in order to control DB-load and to calculate future DB costs.
We are using database managed tablespaces. If we can't reorg the whole tablespace or DB, will fragmentation over time (archive & delete old STS-data every 3 months and load new data on a daily basis) reduce performance, even though we reorg the index tables?
If we could identify the tables affected from the regular delete and update processes, is there a way to reorg just these tables? Would this be an advisable way to reduce DB-maintenance and increase DB-performence?
Thanks for your valued input & kind regards,
PS: yes, we are planing an upgrade to 10.2 sometime soon.
Loukas Rougkalas replied
there is no rule that by deleting 200GB of data in your DB you will immediately see an effect in the performance. It all depends on your DB size and the structure of it.
Talking about reorganization, I do not know where you got this recommendations but I can tell you that it is not always highly recommended by many people in the ORACLE area:
In general, you shouldn't be reorganizing things. It's generally not necessary and generally not beneficial. On the other hand, it generally wastes resources and provides an opportunity to create a production outage when something unexpected happens.
You can certainly spend a lot of time and effort to reorganize every object in a database. Doing so, however, has substantial costs (in resources, time, planning, downtime, etc), limited benefits, and significant risks.
On the other hand, there are a many scenarios where it may make sense to reorganize a particular table or a particular index either as part of fixing some design issue or as the result of some corner case data processing, but in that case you ought to be able to point at particular objects and particular problems.
However if you have Oracle 10g, you can use the Oracle Segment Advisor for make the organization process.
Please review the next link for more information and you might find some useful thing that will assist you in taking a decision:
As I said I could writing endless here . Have a look at the link (towards the end of the page) and come back. More comments are welcome