I intent to carry out a database re-organization and performance tunning. What are the steps I have to follow to achieve the best out of this exercise. I am working with Oracle 8i and 9i running on a Sun server.
With Oracle, you don't need to perform periodic database re-orgs. You might occasionally need to re-org the odd table or index here or there, but that would be to address a specific issue.
Regarding performance tuning, you need to:
1) Identify an actual problem that needs addressing, one that's problematic to the business, not one that only exists in some statistic or in one's imagination
2) Determine what's actually causing the problem as identified in step 1.
3) Address the specific issue as identified in step 2.
It all sounds rather obvious but you would be amazed at all those who don't follow these 3 basic steps and attempt to jump straight to Step 3.
Unless you perform step 1, you can't accurately perform Step 2 which means you'll only be guessing when performing Step 3.
The secret to performance tuning is not to guess ...