19 Şubat 2015 Perşembe

18 Şubat 2015 Çarşamba

Oracle performance tuning by Guy Harrison

I watched a great lecture on Oracle performance tuning by Guy Harrison and wanted to share it with my notes. The lecture can be watched here:
https://www.youtube.com/watch?v=Ah1xjCl6Axg   (part 1)
https://www.youtube.com/watch?v=f-C3nqaqND0 (part 2)
.. and my notes taken while watching:

  • application --(sql)--> database server --(request blocks)--> server memory --(request blocks)--> IO subsytem
          Problems in one layer may be caused or cured in the higher layer.
          Therefore, tune db top-down 
    • app code
    • sql
    • contention
    • memory
    • disk IO
  • Make Oracle work smarter, not harder.. do not load it too much (donkey example in the lecture..)
  • Nulls can not be indexed, so null equality queries work slower. Use some predefined value instead of nulls.
  • Use concatenated index while searching multiple columns, reduces the IO requests drastically. but inserts get slower at the same time.
  • Try to cache returned results, so do not go to database unless you need to.
  • Use bind variables (otherwise -> parse overhead). use PreparedStatement in java.
  • Minimize lock duration, use optimiztic locking
  • Reduce network traffic by using array interface (setFetchSize) and stored procedure for complex sql s.
  • help the optimizer (table statistics etc.)
  • fix bad sql if optimizer makes suboptimal choice.
  • Bad sql -> with highest CPU or IO consumption.
  • V$SQL -> sys.dm_exec_requests
  • order by, group by, join two tables without index -> performs a sort
  • hash joins are more efficient than sort-merge
  • not having enough memory to sort is bad..