Tips & tricks re: Java + Oracle

Hi all,

I'm currently refactoring a Java service that has it's data in an Oracle DB. The main goal is to improve maintainability of the application but performance improvements are always welcome, and the latter is why I'm posting here.

The database consists of tables and indices (obviously), some views, no stored procedures, no FK constraints, some NOT NULL constraints may be missing, text data is stored in CHAR columns (and not VARCHAR, don't know how much that hurts other than disk space usage). The application basically submits a ton of JPQL statements which the OR mapper translates to SQL.

Things that I'm aware of and am currently fixing:

• have an application level cache for static data that only changes with a new deployment
• don't select all columns if I only need a few
• don't submit SELECTs in a loop but use one JOIN in the query for the loop
• the OR mapper seems to have an issue: when I have two entities in the SELECT clause then one JPQL query may be translated to multiple SQL stmts
• a new version of the application is deployed approx. once a month, and after a day usage I request an AWR report and look for obvious issues

I'd appreciate if anyone could share any best practices, tips&tricks, pitfalls and/ or warstories re: stupid things that the average Java EE application does to the database. Mostly I am looking for (and am currently in the process of fixing) low hanging fruit, I have already found some and I'm sure there's more.

Thanks for reading and cheers!