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
- 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..