none: performance

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

27 Kasım 2014 Perşembe

a lucene tip

Retrieving large resultsets with lucene may degrade performance as in here: http://stackoverflow.com/questions/9868419/how-to-get-total-count-in-hibernate-full-text-search

You can choose to show the first top k results, or you can paginate lucene as follows:

// can get the resultsize easily, so you know how many pages would be..
Integer resultSize = jpaQuery.getResultSize(); 
// I use 1000 as max results, but it depends.. just try to find the optimum.
Integer pageCount = resultSize / MAX_RESULTS + 1;
List<Object[]> rawQueryResultList = new ArrayList<>();
for(int page = 0; page < pageCount; page++) {  
 jpaQuery.setFirstResult(page * MAX_RESULTS);
 jpaQuery.setMaxResults(MAX_RESULTS);
  
 rawQueryResultList.addAll(jpaQuery.getResultList());
}