Query tuning basics..
Friends and gurus...
OS: Linux
DB: 11gR2
Recently I have found myself trapped into many sql query tuning with and without bind variables and main aim of all tuning is to reduce total elapsed time displayed in AWR report.
I found myself run all over the places just to identify problem and then try to solve it..
with near to zero experience in query tuning I'm trying to learn this art and have below questions...
Q.
1. Identify problem: could somebody please list what all internal oracle tools available just to identify problem and what should be best approach?
like sqltrace, explain plan, tkprof etc... list goes on so wanting to know which to follow and when...
2. any idea how to get all details of steps listed in explain plan? since predicate information only list few steps where filter is used but some steps which takes long (hash join, nested loops) I don't know what caused it...
3. how to identify problems in queries with bind variable?
please note I don't have any 3rd party tool and all I work on is sqlplus session.
thanks,