Answer Wiki:
The first thing that you need to do is familiarize yourself with any of the new features that were made in 10g from a SQL perspective
What type of environment are we speaking about here? OLTP, Data Warehousing? Let me know, I will be able to provide better direction.
Thanks
Scott
To see all answers submitted to the Answer Wiki: View Answer History.
Here are some things to keep in mind when tuning on 10g.
1. 10g now uses a new cost based optimizer by default
2. tkprof and explain plan help a ton you may also want to check out ADDM with its sql tuning advisors.
3. Don’t forget to run analyze command on your tables, if you do not have a good set of statistics the optimizer will often choose a bad path.
4. Take advantage of the AWR reports (similar to good old statspack) run a snapshot prior to and following your query for additional tuning information.
5. Be wary of any joins done on the v$ tables, the new cost optimizer does not always handle them well and you will need to add the use hints to get decent results. I have done an explain on some queries where I join two or three v$ tables and it results in a cartesian joins. Oracle has provided hints so these queries actually complete in a timely fashion, as opposed to the 45 minutes it was taking before.
I hope this helps you with some things to think about.
Keith