process become slower

If these are PL/SQL procedures, you maigh want to check what dbms_profiler can do for you

Regards
Boris

As said by SlimDave, Statspack is the good tool to analyze the databases whose performance degrades over the time period.

Quote:

---

Originally Posted by PSoni

1. we exported our erp database
2. we uninstall oracle 8.1.7 (on w2k plateform)
3. we install oracle 8.1.7 again
4. we import our erp database

---

Don't take it wrong, I trust what you say but it's really hard to believe reinstallation of Oracle binaries has something to do with getting better performance.

As per previous postings who have said you already tried exp/imp with no success.

Lets try to take Oracle binaries out of the equation. Could you please do the following two tests?

++ Test #1

1-- Shutdown your database
2-- Reboot your server
3-- Startup your database
4-- Check performance... how is it now?
IF performance is better either Oracle services recycle or O/S
recycling fixed it.

If Test #1 does not does the trick...try test #2...

++ Test #2

1-- Export your erp database (just in case)
2-- Uninstall Oracle software
3-- Reinstall Oracle software
4-- Create your erp database pointing to the old datafiles
5-- DO NOT IMPORT the data, lets Oracle work with the old data
6-- Startup your database and check performance... how is it now?

Quote:

---

Originally Posted by PAVB

...
4-- Create your erp database pointing to the old datafiles
5-- DO NOT IMPORT the data, lets Oracle work with the old data
...

---

I think you'll find that you can't do this.

Anyway, run statspack.

Why not Dave? You mean like that cannot be done, or you doubt the poster can do it

I agree Dave also.

Quote:

---

Originally Posted by Bore

Why not Dave? You mean like that cannot be done, or you doubt the poster can do it

---

I agree with Dave....it cannot be done..ie creating a new database with datafiles from another database...
There is something like datafile header markers that are only recognized by that database that they come from. In other words you cannot take datafiles out of database A and plug into database B.

Strange that someone has to uninstall Oracle each time to do a refresh....whoaa!!!
I think what is is happening is, they are not running stats.
On exporting/importing Oracle implicitly does an analyze for them..that's why is good immediately after each new import. Now after months of deletes,updates,inserts the stats fall behind and it begins to crawl.
Check that your stats are updated?

Quote:

---

Originally Posted by slimdave

I think you'll find that you can't do this.

---

Sorry Dave but you are wrong, I do this all the time, at least once a week.

This is our preferred method to refresh UAT environments.

:cool:

Quote:

---

Originally Posted by PAVB

Sorry Dave but you are wrong, I do this all the time, at least once a week.

This is our preferred method to refresh UAT environments.

:cool:

---

Oh OK, you're not just talking about the data files but the control files etc. as well .. ie the whole database. Gotcha.

Still, it sounds more like a benefit doe to a reorganisation based on the import than anything else. Maybe a row migration problem, but statspack ought to identify that.

No problem at all Dave.

Original poster insists he has exp/imp the whole database with no performance gains therefore, I wanted to take "Oracle reinstall" out of the equation once and forever :) That's the reason I've asked for these two tests ;)