when every milisecond matters
1. use OCI JDBC driver , undoubetdly they are fastest JDBC driver , especially when there is lot of fetch. I found them performing 10% faster for a bulk fetch.
2. if java application has to fecth large data then use larger fetch size .
3. When DB server and Java application server are on same box, use OCI JDBC driver. In this case db connection will be using IPC and gives best performance. And ovbvious as states in 1. above , JDBC OCI are fastest even when used for remote connection to DB.
4. if using connection pooling,there should be good number of connections available otherwise there will be more opened cursors per session.
5. perform analysis for use of preparestatement vs statement. Don't go blindly for preparestatement
6. use batch update but just be wise to choose batch size. If keep it is small no benefit and if it is large again it will need more undo in database. Similarly guys when you add statements in batch be wise loop does not iterate very much other wise you will need good memory for your java program.
some tail info not related actually to performance:
-- use the JDBC drivers matching DB server jdbc drivers version. I seen people running oracle 10.2.0.4 but their jdbc driver was version 10.2.0.1. They had issue in fetching from result set sometimes. Fix suggested was to use matching jdbc driver and it did .
-- if using connection pool,take extreme care to check result sets are closed properly and opened cursors does not exceed limit.
1. use OCI JDBC driver , undoubetdly they are fastest JDBC driver , especially when there is lot of fetch. I found them performing 10% faster for a bulk fetch.
2. if java application has to fecth large data then use larger fetch size .
3. When DB server and Java application server are on same box, use OCI JDBC driver. In this case db connection will be using IPC and gives best performance. And ovbvious as states in 1. above , JDBC OCI are fastest even when used for remote connection to DB.
4. if using connection pooling,there should be good number of connections available otherwise there will be more opened cursors per session.
5. perform analysis for use of preparestatement vs statement. Don't go blindly for preparestatement
6. use batch update but just be wise to choose batch size. If keep it is small no benefit and if it is large again it will need more undo in database. Similarly guys when you add statements in batch be wise loop does not iterate very much other wise you will need good memory for your java program.
some tail info not related actually to performance:
-- use the JDBC drivers matching DB server jdbc drivers version. I seen people running oracle 10.2.0.4 but their jdbc driver was version 10.2.0.1. They had issue in fetching from result set sometimes. Fix suggested was to use matching jdbc driver and it did .
-- if using connection pool,take extreme care to check result sets are closed properly and opened cursors does not exceed limit.
Posts
