| Performance Tuning [message #532592] |
Wed, 23 November 2011 09:39  |
 |
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 #532598 is a reply to message #532592] |
Wed, 23 November 2011 09:54  |
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 #537353 is a reply to message #532851] |
Tue, 27 December 2011 21:20 |
 |
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
