Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> bad runnig time after restore
FraJer
post Apr 23 2012, 09:14 AM
Post #1


Newbie
*

Group: Members
Posts: 2
Joined: 15-April 12
Member No.: 47,081



I've got one strange problem (at least for me smile.gif. 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!
Go to the top of the page
 
+Quote Post
sishyadba
post Apr 23 2012, 11:19 AM
Post #2


Member
**

Group: Members
Posts: 21
Joined: 14-February 08
Member No.: 17,407



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

Go to the top of the page
 
+Quote Post
FraJer
post Apr 23 2012, 04:07 PM
Post #3


Newbie
*

Group: Members
Posts: 2
Joined: 15-April 12
Member No.: 47,081



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 smile.gif. 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

Go to the top of the page
 
+Quote Post
sishyadba
post Apr 26 2012, 01:14 PM
Post #4


Member
**

Group: Members
Posts: 21
Joined: 14-February 08
Member No.: 17,407



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
Go to the top of the page
 
+Quote Post
burleson
post Apr 27 2012, 06:08 AM
Post #5


Advanced Member
***

Group: Members
Posts: 11,700
Joined: 26-January 04
Member No.: 13



>> 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


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 20th November 2014 - 03:32 PM