Hello all,
we have discovered a problem in the application that mostly (but we cannot
assure only) affects Oracle databases. After some use of the application,
there is a huge amount of open cursors and there is a limit of how many
open cursors can be. Once this limit is reached, the following attempt to
open a new one will fail and it will launch an Exception. This can happen
because a PreparedStatement was not closed.
- How to detect the problem:
As explained in
http://wiki.openbravo.com/wiki/Finding_Perfomance_Issues#Open_cursors , we
can use the following select to see how many open cursors we have grouped
by select:
SELECT sql_text, COUNT(*)
FROM v$open_cursor
GROUP BY sql_text
ORDER BY COUNT(*) DESC;
We can execute it before executing some piece of code and after executing
it and we will be able to see if that piece of code leaves too many opened
cursors and which are the selects responsible of that. There should not
be a select with many open cursors.
- Some examples:
Former incorrect code:
try {
String st = "Select specific_id, generic_id, ad_client_id, ad_org_id
from ad_ref_data_loaded where ad_client_id in ('"
+ client.getId()
+ "', '0') and generic_id='"
+ id
+ "' and ad_table_id='"
+ entity.getTableId() + "'";
PreparedStatement ps = new
DalConnectionProvider(false).getPreparedStatement(st);
ps.execute();
ResultSet rs = ps.getResultSet();
...
} catch (Exception e) {
throw new OBException("Error while accessing the ad_ref_data_loaded
table", e);
}
Proper way of closing it:
PreparedStatement ps = null;
try {
String st = "Select specific_id, generic_id, ad_client_id, ad_org_id
from ad_ref_data_loaded where ad_client_id in ('"
+ client.getId()
+ "', '0') and generic_id='"
+ id
+ "' and ad_table_id='"
+ entity.getTableId() + "'";
ps = new DalConnectionProvider(false).getPreparedStatement(st);
ps.execute();
ResultSet rs = ps.getResultSet();
...
} catch (Exception e) {
throw new OBException("Error while accessing the ad_ref_data_loaded
table", e);
} finally {
try {
ps.close();
} catch (SQLException se) {
//Handle exception if needed.
}
}
There is also another way of releasing the cursor. We can execute
connectionProvider.releasePreparedStatement(st). You can see examples of
this in any ...Data.java generated through an ....xsql. For example:
String strSql = "";
strSql = strSql +
" SELECT A_O.AD_ORG_ID AS ID, A_O.NAME " +
" FROM AD_ORG A_O, AD_ROLE_ORGACCESS A_R_O, AD_ROLE R " +
" WHERE A_R_O.AD_ORG_ID = A_O.AD_ORG_ID " +
" AND A_R_O.ISACTIVE = 'Y' " +
" AND A_R_O.AD_ROLE_ID = R.AD_ROLE_ID " +
" AND R.ISACTIVE = 'Y'" +
" AND A_R_O.AD_ROLE_ID = ? " +
" AND A_O.AD_Org_ID IN(";
strSql = strSql + ((adOrgClient==null ||
adOrgClient.equals(""))?"":adOrgClient);
strSql = strSql +
")" +
" ORDER BY A_O.NAME";
ResultSet result;
Vector<java.lang.Object> vector = new Vector<java.lang.Object>(0);
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
iParameter++; UtilSql.setValue(st, iParameter, 12, null, rol);
...
} catch(SQLException e){
log4j.error("SQL error in query: " + strSql + "Exception:"+ e);
throw new ServletException("@CODE=" +
Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
} catch(Exception ex){
log4j.error("Exception in query: " + strSql + "Exception:"+ ex);
throw new ServletException("@CODE=@" + ex.getMessage());
} finally {
try {
connectionProvider.releasePreparedStatement(st);
} catch(Exception ignore){
ignore.printStackTrace();
}
}
This has caused serious performance problems in some instances. Please be
aware of this problem while developing and code reviewing. If you find a
problem of this type in existing code and you cannot fix it, please report
it as an issue.
Regards,
Ioritz.
------------------------------------------------------------------------------
Monitor your physical, virtual and cloud infrastructure from a single
web console. Get in-depth insight into apps, servers, databases, vmware,
SAP, cloud infrastructure, etc. Download 30-day Free Trial.
Pricing starts from $795 for 25 servers or applications!
http://p.sf.net/sfu/zoho_dev2dev_nov
_______________________________________________
Openbravo-development mailing list
Openbravo-development@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/openbravo-development
