Picking out, validating and improving slow or resource-eating SQL statements is part of any responsible DBA's work who takes his/her job serious. Identification alone can be tough, but it will be even harder to improve something you have no chance to understand the purpose of. This way, you will never be in the position to improve elapsed time by factors of 1 million or 100.000 - that's a privilege of the designers and coders. But there is a generic way for us database whisperers to approach bad-behaving SQL, and improve it by factor 2, 5 or 10. Sometimes this is all we will get, so let me show you how: We are avoiding expensive tools, voodoo features, concatenated heuristics and one-engine-fixes-the-other. An unspectacular, real-world, common-sense approach, reaching the goals with grassroots technology.
Objective 1: Understand the basic components of parsing SQL in Oracle Databases
Objective 2: Pick the culprit, understand its execution plan
Objective 3: Weed them out, avoid collateral damage.