Oracle Expert

I am in the middle of handover process, prior to moving to a new project, and was asked to jot down some performance tuning fundamentals that I follow. It is harder than I thought, as how do you fit in 20 years of experience into a short handover document.

But I think the following points would perhaps be a good start

o Oracle is a very powerful RDBMS system. Oracle license is expensive for a good reason, i.e. it has a lot of functionalities that will benefit the applications if used correctly. Do not treat Oracle simply like a data storage
o Do not get obsessed with things you can not influence, like disk technology, database OS platform. 90% of performance improvement can be obtained by improving our own design/code.
o Ensure that your development environment is build as similarly as possible to UAT/Live.
o Be self sufficient in generating statistics for your tables. Do not rely on DBA overnight jobs. This way we will have full control on the best time and best method to generate the statistics.
o When choosing an index strategy, make sure you understand how the table is going to be used. Understand the different type of indexes and choose the right ones. Do not OVER indexed.
o Implement bind variables. Not only this will reduce parsing (memory usage), it will also allow performance investigation to be done more easily.
o When writing a query with complex joins, the single rule of thumb is, “read as little data as possible” - avoid Full Table Scan and as much as possible, filter first then join, not the other way around
o Favour bulk operations, rather than row by row, unless you are streaming intraday, or doing manual updates from a front end.
o Remember sqlldr for data loading
o Tuning is an ongoing exercise. Keep reviewing the application performance on a regular basis.
o Think outside the box, performance tuning is more than tuning individual SQL.