0

I have a Procedure:

CREATE OR REPLACE PROCEDURE COMMON.UPDATE_ANEXE (p_app_id NUMBER)
IS
BEGIN
    DELETE FROM COMMON_EXT.COM_CERERE_APLICATIE_ANEXA@racdb
    WHERE FK_COM_CERERE_OP = p_app_id;

    INSERT INTO COMMON_EXT.COM_CERERE_APLICATIE_ANEXA@racdb
    (id, fk_aplicatie_anexa, fk_com_cerere_op)
    SELECT id, fk_aplicatie_anexa, fk_com_cerere_op
    from COM_CERERE_APLICATIE_ANEXA
    WHERE FK_COM_CERERE_OP = p_app_id;

END;
/

that I call by:

private static class UpdateAnexeProcedure extends  StoredProcedure {
        private static final String PROCEDURE_NAME = "UPDATE_ANEXE";
        private static final String PARAM = "p_app_id";

        public UpdateAnexeProcedure(DataSource dataSource) {
            setDataSource(dataSource);
            setFunction(false);
            setQueryTimeout(300);
            setSql(PROCEDURE_NAME);
            declareParameter(new SqlParameter(PARAM, Types.NUMERIC));
            compile();
        }
    }

public synchronized void syncAnexeIntToExt(Long idCerere) {
        LOGGER.debug("syncAnexeIntToExt(idCerere={})...", idCerere);
        UpdateAnexeProcedure procedure = new UpdateAnexeProcedure(jdbcTemplate.getDataSource());
        Map<String, Long> paramMap = new HashMap<String, Long>();
        paramMap.put(AddApplicationProcedure.PARAM, idCerere);
        procedure.execute(paramMap);
        LOGGER.debug("DONE >> syncAnexeIntToExt(idCerere={}).", idCerere);
    }

and I get often an error... "Transaction timed out". The database / application is used by many people... What can I do to speed things up?

WDrgn
  • 521
  • 10
  • 29
  • 2
    Check first the execution plans of SQL statements you execute in the code and the size of COMMON_EXT.COM_CERERE_APLICATIE_ANEXA table on remote and local database. If your DELETE or SELECT statement in INSERT perform full table scan instead of index access this can be a reason. – Dmitry Nikiforov Aug 13 '14 at 07:36
  • thanks for your answer but I don't know how to verify the execution plan of the SQL... I've searched over the internet but I'm not too enlightened... I was thinking to put my sql into the Java code... – WDrgn Aug 14 '14 at 08:55
  • If you have problems with SQL performance then putting SQL into Java is not a clue. Looking at your SQL syntax I believe this Oracle platform, so you can check the documentation describing the subject: http://docs.oracle.com/cd/B19306_01/server.102/b14211/toc.htm. But the better way is to ask DBA to advise - this can safe your day. There may be other reasons why your transation lasts too long - for example locking the resource by other user transactions or network issues. – Dmitry Nikiforov Aug 14 '14 at 10:14
  • I will ask the DBA... Sometimes I get "org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{call UPDATE_ANEXE(?)}]; SQL state [72000]; error code [1013]; ORA-01013: user requested cancel of current operation ORA-06512: at "COMMON.UPDATE_ANEXE", line 4 ORA-06512: at line 1 ; nested exception is java.sql.SQLException: ORA-01013: user requested cancel of current operation ORA-06512: at "COMMON.UPDATE_ANEXE", line 4 ORA-06512: at line 1". – WDrgn Aug 14 '14 at 11:32
  • ORA-01013 seems is the indicator of transaction cancellation made by Spring. – Dmitry Nikiforov Aug 14 '14 at 13:19

0 Answers0