Home » RDBMS Server » Performance Tuning » experiences in performance tuning
experiences in performance tuning [message #65721] Tue, 14 December 2004 22:18 Go to next message
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 Go to previous message
Messages: 7897
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).

Previous Topic: URGENT:-Tips for Tuning Stored Procedures
Next Topic: Query not executing
Goto Forum:

Current Time: Mon Feb 06 05:53:50 CST 2023