Home » RDBMS Server » Performance Tuning » Indexing and Tuning (Oracle 10g)
Indexing and Tuning [message #615068] Thu, 29 May 2014 20:47 Go to next message
Messages: 253
Registered: April 2013
Location: Ajman
Senior Member
Hi experts , recently our oracle server crashed and we had to install a new server and using import we manager to up the server but the problem is all my queries are running slow , i came to know from our dba that indexing needs to be done at database side, kindly advice how can i learn more on tuning and indexing and also the basic methods of tuning sql queries.Where to start with.
Re: Indexing and Tuning [message #615069 is a reply to message #615068] Thu, 29 May 2014 21:03 Go to previous messageGo to next message
Messages: 26759
Registered: January 2009
Location: SoCal
Senior Member
Ever consider to just Read The Fine Manuals?

Re: Indexing and Tuning [message #615081 is a reply to message #615068] Fri, 30 May 2014 01:36 Go to previous messageGo to next message
John Watson
Messages: 8502
Registered: January 2010
Location: Global Village
Senior Member
Many things my change when you exp/imp to a new server, but indexes are not one of them. You should start with complete statistics collection, both object statistics and system statistics. Your DBA (you mentioned that you have one) will know all about this, you must have misunderstood his advice.
Re: Indexing and Tuning [message #615377 is a reply to message #615068] Tue, 03 June 2014 00:23 Go to previous messageGo to next message
Messages: 621
Registered: July 2006
Senior Member
IMHO - some of indexes (these supporting unnamed constraints ) may disappear during EXP/IMP scenario.

So I would start by checking the indexes.

Re: Indexing and Tuning [message #615383 is a reply to message #615068] Tue, 03 June 2014 01:13 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3171
Registered: May 2013
Location: World Wide on the Web
Senior Member
1. Are both the old and new servers having same configuration?
2. Are the system and object stats up to date?
3. Are all queries having slower response than earlier? Or few?
4. Did your DBA share with you the queries which needs to be tuned and where he suggests indexing. Can you post it.

Follow the sticky on top of this forum to post the required details.
Re: Indexing and Tuning [message #615899 is a reply to message #615068] Tue, 10 June 2014 20:33 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
I assume that the old system is no longer available based on your description of the problem. In this case, there is no quick fix for you. You may not have access to previous history of runtimes and plans (assuming you kept that information in the first place). So you will have to approach this as a basic tuning project. To that end, all the basic things you are supposed to do you will have to do again.

1. check your database and make sure it has all the indexes and constraints defined that it is supposed to have.  When in doubt, index all PK/UK/FK constraints for an OLTP system.  If this is a reporting system or an analytic system then indexing could be more or different.  But you have not indicated what type of database this is.

2. check you statistics.  It may be best simply to recollect them.  To that end you should do some simple testing to see how much time you are willing to spend on stats.  For example, one rule we had in my shops was for tables < 20 million rows do 100% stats collection, otherwise do 10% or 1% as dictated by time constraints.  This was for 9i/10g databases.  On 11g there are other options (synopsis based statistics).  Collect just simple stats to start (no histograms or column groups) then work your way up from there to handle specific issues like skew/out-of-bounds.  Or on 11g use synopsis based stats and go to town.

3. make sure you have a realistic set of performance targets to shoot for.  Do you have prior runtimes you can refer to or are you just "feeling like the SQL is slow now".  Try to be somewhat scientific in finding SQL that has truly regressed.

4. if you have the database parameter settings for the old database, compare them to the new database.

5. check server setting and configuration.  Do you have the same amount of memory and cpu configured the same way?  Is this server on the same network?

5. after the above, do top-n sql reports for each of CPU / PHYSICAL IO / BUFFER GETS and tune down these queries till they go fast or cannot be made to easily go faster.

You are just going to have to do work. Kevin
Previous Topic: Table with 40 million records problem
Next Topic: how to troubleshoot slow running queries?
Goto Forum:

Current Time: Mon Mar 08 21:37:08 CST 2021