Home » RDBMS Server » Performance Tuning » Performance Tuning (All)
Performance Tuning [message #532592] Wed, 23 November 2011 09:39 Go to next message
lynndba
Messages: 23
Registered: November 2011
Junior Member
I learned there a several methods for doing Performance Tuning right, I would like to know which method you use for tuning or which you consider best. When to tune, how often stuff like that?

Thanks to REAL responders
Re: Performance Tuning [message #532597 is a reply to message #532592] Wed, 23 November 2011 09:46 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
when all else fails Read The Fine Manuals!

http://www.oracle.com/pls/db112/search?remark=quick_search&word=tuning
Re: Performance Tuning [message #532598 is a reply to message #532592] Wed, 23 November 2011 09:54 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Sorry Lynne, this is a rather vague question and the absolute BEST you can hope for is vague answers. Looking at your other questions, it looks like you are not really willing to put much effort in on your own by reading up and learning. The reason I say this is that every single question has been very non specific and includes no evidence that you have tried to research the issue / question for youself. The more of these questions that you ask, the less likely you are to get people wanting to help you. How about, to show us that you actually have researched this a little bit, list the "several methods for doing Performance Tuning " that you have found and maybe even add in where you think one method might be more appropriate than another. you will be MUCH more likely to start off discussion if you show willing to get involved rather than expeccting to be spoon fed.
GL

PS, in case you don't realise, this is a REAL response
Re: Performance Tuning [message #532599 is a reply to message #532598] Wed, 23 November 2011 09:59 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
+1
Re: Performance Tuning [message #532615 is a reply to message #532598] Wed, 23 November 2011 10:54 Go to previous messageGo to next message
lynndba
Messages: 23
Registered: November 2011
Junior Member
I believe asking these questions is part of research, if you have been a DBA for a while and have tried several methods, share your outcome, I do my research but I believe that TRUE answers would be beneficial to not only me but someone else, like my last one. If you don't have an answer ignore and don't judge
Re: Performance Tuning [message #532618 is a reply to message #532592] Wed, 23 November 2011 10:56 Go to previous messageGo to next message
Michel Cadot
Messages: 67946
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database Performance Tuning Guide.

Is this a REAL answer?

Regards
Michel
Re: Performance Tuning [message #532625 is a reply to message #532618] Wed, 23 November 2011 11:18 Go to previous messageGo to next message
lynndba
Messages: 23
Registered: November 2011
Junior Member
Are there no other methods except the ones in oracle documentation
Re: Performance Tuning [message #532626 is a reply to message #532625] Wed, 23 November 2011 11:23 Go to previous messageGo to next message
Michel Cadot
Messages: 67946
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Optimizing Oracle Performance by Cary Millsap.

If you master both then you are better than 99% of DBA.

Regards
Michel
Re: Performance Tuning [message #532628 is a reply to message #532626] Wed, 23 November 2011 11:28 Go to previous messageGo to next message
lynndba
Messages: 23
Registered: November 2011
Junior Member
See now was that so hard
Re: Performance Tuning [message #532629 is a reply to message #532626] Wed, 23 November 2011 11:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
For SERIOUS readers only
SQL Tuning by Dan Tow
http://www.amazon.com/SQL-Tuning-Dan-Tow/dp/0596005733/ref=sr_1_1?ie=UTF8&qid=1322069360&sr=8-1
Re: Performance Tuning [message #532851 is a reply to message #532629] Fri, 25 November 2011 03:21 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
The answer to almost every tuning situation is: It depends.

If there was a silver bullet, then it would be built into the database.
Re: Performance Tuning [message #537353 is a reply to message #532851] Tue, 27 December 2011 21:20 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
All that being so... and if we are talking about tuning SQL, then there are a couple of tips we can throw at you.

1) tuning takes a long time. So, if you want to tune, make sure you are tuning the right stuff. This might sound obvious but in reality, many people find things to tune that they should not bother with. There are essentially two ways to know if you are tuning the right stuff:

a) SHOTGUN METHOD
b) SQUEAKY WHEEL METHOD

SHOTGUN METHOD is basically a TOP-N analysis. You run a TOP-N report on your instances to find expensive SQL or you look at some AWR reports, and find the most expensive queries running on your database and tune them. This method works because it relies on the fact that for most databases, 90% of the work done on an Oracle box is done by less than 1% of the SQL. If you tune the 1%, you can get your database instances to do up to 10 times the work.

SQUEAKY WHEEL METHOD is bascially listening to your customers that you support. Your business people will complain about performance. When it gets bad enough they will get loud so you need to pay attention and do something about it by tuning those specific SQLs that are part of their SQL WORKLOAD. This might be a batch process to readies data for a report, an online screen that takes too many seconds to jump to the next page, or a report that takes to long to generate. This will often mean tuning SQL queries that are not the most expensive queries on the box, but that does not matter because your customers by their complaints, have informed you that these are the most important queries to them.

The two methods are different. The SHOTGUN method is a system wide enhancing method driven by IT techies (DBAs usually). SQUEAKY WHEEL is a targeted method driven by customer need. In general, SQUEAKY WHEEL is the preferred method because it usually provides a much better business value to your business customer. After all, no one cares about long running queries, unless they are keeping them from doing their job. Whereas good SQUEAKY WHEEL tuning can actually change the way people do their jobs for the better. For example, they might get important reports by 8am instead of noon which means they can use real information up to a day earlier as most people or most productive during the first half of the day.

2) get the right basic query plan. This means paying attention to three details:

a) how do you access a table? with an INDEX or via FULL TABLE SCAN
b) how do you join tables? NESTED LOOP or HASH JOIN?
c) in what order do you join tables?

In general you will learn that for long running queries, FULL TABLE SCAN and HASH JOIN are better than NESTED LOOP w/ INDEXES; the opposite being true for short queries. As general rules of thumb (yes there are lots of ways to construct exceptions to these rules but there rules work exceptionally well for me)

a) if you are fetching more than 1% of the data from a table, use FULL TABLE SCAN, otherwise an index might be useful.

b) if your join will visit more than 1% of the target (eg. probed) table in a join, then use HASH JOIN, otherwise nested loop if supported by an index might be better.

c) the order of tables taken in a query should usually be those which do the most filtering up front (this is often called filter ratio and is a focal topic of Dan Tow's book (a must read if you ask me)). This keeps your intermediary row sets as small as possible. For example, given a choice of SCAN a table with 1 million rows and filter out 900 thousand (10% remaining) vs. accessing a 100 row table which filters 1 out of 2 rows (50% remaining) you most likely will want to see the million row table near the begining of the query and the 100 row table later. As filtering out 90% of the expected result then filtering out 50% of what remains is more efficient than filtering out 50% and then 90% of of the remaining 50%. Think about it, or read Dan's book.

Notice how these three rules match the three concepts above.

3) Dont jump into the advanced features of Oracle as a quick way to try and fix performance. Partitioning and Parallel Query and BITMAP INDEXES are things that need planning, not hacking; so you will not be affective at performance tuning by using them. You should already know you need them or not because of the design of your system. If you don't know you need them, then odds are you don't. I have actually never solved a single performance issue by bringing any of these features to bear, after the fact.

4) hints are almost never needed. I have been doing tuning in my current position for 18 months almost exclusively and I would say that 1 in 25 queries maybe less, actually needed a hint in order to be fixed. And that is only of the queries I actually had to tune, not any of the 50 thousand or so queries in my box's workload that didn't need to be looked at.

5) get database basics right to begin with and 99% of all tuning problems in Oracle will never happen in the first place. This means the following:

a) start with a normalized relational database design for a typical database, and dimensional model for a warehouse design. But... if you are going to do dimensional, make sure you understand that you have a true warehouse and not just a lot of data. Big does not mean warehouse, it just means big. Specialized need for SLICE&DICE querying is a warehouse.

b) use constraints. Primary Key, Unique Key, Foreign Key, correct nullablility, and proper datatype. Rely constraints are useful in a warehouse and you don't incur the cost of enforcement but you get the benefit of the information.

c) collect statistics poperly. This mean COMPUTE if possible and WITH SKEWONLY if you suspect skew in the data.

d) collect those system stats that tell Oracle about I/O cost etc., forget what they are called but someone can help. You System DBA should be doing this when an instance is stood up.

OK, so that is a pretty good introduction to tuning I think. It may not be what you were expecting but it does offer insight into the tuning process (well at least my basic process anyway).

Anyone what to comment or give a disenting opinion?

Kevin

[Updated on: Tue, 27 December 2011 21:28]

Report message to a moderator

Previous Topic: Performance access direct table vs view
Next Topic: Query running long time
Goto Forum:
  


Current Time: Tue Sep 21 09:39:03 CDT 2021