bad runnig time after restore Options

[FraJer]

I've got one strange problem (at least for me . On short: an ERP with DB Oracle. One ERP session running time is too long and it also uses ~99% CPU.

History. Couple years ago our ERP/DB support did some things on DB (sth around statistics I suppose). And after that our "problem maker" session worked much better (from 1-2 hours to about 15-20 min).
A while after someone suggested to rename IDX1 (from main table of that session) to something else (IDX11). 'Maybe,' he said 'index is not created well and oracle will work by its brain and uses another index.' So be it. Trully, a running time went ~50% better.

Present. It happened some weeks ago that data had to be restored (around 300 tables, some of them empty). After that a running time of our nasty session was bad again (+5 hours!). And renaming an index did not help anymore. Is the problem a restore?

I know I write very messy question / problem, but maybe someone would say: hey, this is it!

[sishyadba]

Which version of the oracle you are using? Please post the version so that we could be able to suggest appropriately.

After restoring the database did you run the statistics on the database?

If it's still performing bad after statistics calculation also

simple solution is check the explain plan for the queries in the session and verify if they are using wrong indexes by comparing it with the old explain plan. use index hints in the query or outlines to force use the correct index.

Long term solution is If the so called main table has millions to billions of rows, after taking the backup of present statistics compute the histograms on the indexed columns of that table to give Oracle some insight about the data distribution on those columns.

That should fix your problem. If the table is growing enormously big consider partitioning it.

Sri

[FraJer]

Of course, I forgot. Our ORA DB is 10g Rel. 10.2.0.4.0 /64bit/.
After restore I did not run statistics (never did by myself; for now . I'm not DB admin, only unix one. But as we don't have DB guy, I have to do some work on this field, too.
So, statistics. Run for the whole database or for some tables only?

fj

[sishyadba]

run the statistics for full database and if there are huge tables with more than 100 million rows collect histograms for indexed columns.

You can try running sql analyzer on the long running SQL's.

Read about statistics and Histograms on internet.


Sri

[burleson]

>> he said 'index is not created well and oracle will work by its brain and uses another index.' So be it. Trully

Truly, so be it, sayeth the DBA, Amen. . . .



***************************
>> Is the problem a restore?


Sounds like it . . .


to find the problem, take a 5 minute elspased time STATSPACK or AWR report and post the results here.

http://www.dba-oracle.com/t_taking_awr_sta...ck_snapshot.htm