Hi,
As part of my job I often have to write queries in Oracle (10g) and I am experienced enough to see where I should be using an index where possible etc. Most of the time I just let the CBO come up with the best plan however sometimes it obviously doesn't!
Now, I know in general that full scans of large tables aren't good however I don't fully understand an execution plan - ie, I could not look at a complex piece of code, look at it's plan and say "oh, the reason that is performing badly there is because the SBO has chosen to use nested loops rather than a hash join, so if I hint it then it will run quicker!" - this ultimately is where I'm trying to get to!!

Does anyone have any suggestions as to what might be worth reading or doing? I need to understand in what situations a particular action is taken and when another action may be better etc...

Thanks

Performance Tuning Guide
10g Release 2 (10.2)
B14211-03

Around 6 MB.

End

Hi -

It's a fairly complex subject, there are no easy "one-size-fits-all" answers. As I'm sure you know, a table-scan can actually be "optimal" under some circumstances ... and is often well-neigh unavoidable under others. As you also know, indexes can improve queries under some circumstances ... and trash the performance of updates and inserts under others.

Oracle has tons of really great information, including the "Performance Tuning Guide" AnanthaP suggested.

These links might help:
http://wiki.oracle.com/page/Performance+Tuning
http://download.oracle.com/docs/cd/B...b14211/toc.htm
http://www.orafaq.com/wiki/Oracle_da...nce_Tuning_FAQ

Good luck!