| experiences in performance tuning [message #65721] |
Tue, 14 December 2004 22:18  |
Deepa
Messages: 269
Registered: November 2000
|
Senior Member |
|
|
Hi experts,
Just curious to know about your realtime experiences on ORacle Performance tuning.
Could anyone share with the a real kick off on the Performance Part.
Like how a query running for hours is been turned into minutes.
And some interesting improvements by someother tuning.
Realtime experiences please.
|
|
|
|
| Re: experiences in performance tuning [message #65730 is a reply to message #65721] |
Thu, 16 December 2004 00:31  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
In performance tuning you sometimes get amazing results. Sadly, this is mostly when the offending query contains errors (like missing where-conditions resulting in cartesian products). These are wasy to spot, providing you use the right tools (statspack and trace/tkprof)
The best I ever got was a query running 28 hours to under 10 seconds. I must say, that really was a kick, especially when I saw the customer, who expected me to tune it to something like 1 hour or so :))
Normally, in a stable production-environment, things aren't as easy as that. In this you're talking about 10-20% performance gains at the most.
Good results are often gained by disabling the use of a certain index in a query.
As Tom Kyte always says: Go for the low hanging fruits.
Trace your session and tkprof it, using sort=prsela fchela exeela.
This will sort the statements, displaying the longest running ones first.
Concentrate on these and remember there is always 1 statement that takes the most time. (aka you must know when to stop).
hth
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
