This discussion is archived
4 Replies Latest reply: Jul 22, 2013 2:59 PM by rp0428 RSS

Performance problems for PLSQL

user10177353 Newbie
Currently Being Moderated

Hi All,

We have an application written in Core Java that uses JDBC Thin Client to interact with the Oracle DB Stored Procedure within the Package.

We have created some Oracle Object Types to support the interaction since Java cannot read the PLSQL Types declared as Table Types.

The application is working as expected however we are encountering some performance issues. We have tested the PLSQL cursors queries but it seems they return values within 0.613 seconds however the Point 4 below is taking much time.

 

Below is a brief functionality of the Application interacting with the PLSQL.

START

  1. Java Client uses JDBC Thin Client to connect to  Oracle9i Enterprise Edition Release 9.2.0.8.0
  2. Java Client passes a parameter (size) to PL/SQL Package.
  3. PLSQL Package returns an Array by populating the Oracle Object type of the Employee IDs for a Java sent parameter (size).
  4. Java Client reads an Array sent by PLSQL Procedure. This takes time around 3 seconds.
  5. Send the Employee ID to inerface application in XML
  6. Disconnect from Oracle DB

END


Hence I require your guidance to see how we can overcome this performance issue. \

Eg:

a) Do we have any robost JDBC Driver for the above Oracle version?

b) Do we have any easier way to bind the Array returned by PLSQL with Array in Java.

c) Do we have to do any JVM Settings to overcome performance?

 

Kind Regards,

  • 1. Re: Performance problems for PLSQL
    rp0428 Guru
    Currently Being Moderated

    >

    We have tested the PLSQL cursors queries but it seems they return values within 0.613 seconds

    >

    Maybe so but you haven't posted anything for us to look at that would support that.

     

    How did you test them? What did they return values to (since it wasn't your Java code)?

     

    So even though you provided more info than many provide the info doesn't include all of the specifics that are needed to understand your case.

     

    1. What version of Java are you using?>

    2. What is the jar name and version of the Oracle JDBC driver are you using?

    3. What is the Java code you are using for this that shows how the connection is made, the package call and how the result set is being processed?

     

    You haven't given us anything to look at.

    >

    a) Do we have any robost JDBC Driver for the above Oracle version?

    >

    Yes - the Oracle JDBC driver - but you didn't tell us which one you are using.

     

    See the Oracle JDBC FAQ for the details of the various combinations of DB, Java and JDBC driver that are supported.

    >

    b) Do we have any easier way to bind the Array returned by PLSQL with Array in Java.

    >

    Easier than what? You haven't provided the code that shows how you are doing it now. Post the code so we can see what you are doing.

    >

    c) Do we have to do any JVM Settings to overcome performance?

    >

    Yes - but it depends on WHAT your code is actually doing and you haven't posted the code so we don't know.

     

    And why are you still using such an ancient, non-supported version of Oracle? You need to upgrade to version 11.

  • 2. Re: Performance problems for PLSQL
    user10177353 Newbie
    Currently Being Moderated

    >

    We have tested the PLSQL cursors queries but it seems they return values within 0.613 seconds

    >

    Maybe so but you haven't posted anything for us to look at that would support that.

    How did you test them? What did they return values to (since it wasn't your Java code)?

    >>

    We tested via PLSQL developer SQL Window and SQL Plus. It returned 0.62 seconds

    We also tested it via Java code, it was not closer to above, however it was negligible difference in some 69 milliseconds more.

    >>

    So even though you provided more info than many provide the info doesn't include all of the specifics that are needed to understand your case.

    1. What version of Java are you using?>

    >> JDK 1.6

    2. What is the jar name and version of the Oracle JDBC driver are you using?

    >> ojdbc14

    3. What is the Java code you are using for this that shows how the connection is made, the package call and how the result set is being processed?

    You haven't given us anything to look at.

    >> getConnection for Oracle Connection

    public Connection getConnection() throws SQLException, LnpEMAException {
      final String methodName = ".getConnection()";
      LogMgr logger = LogMgr.getInstance();
      EMAConfigMgr config = EMAConfigMgr.getInstance();
      String dbUser = null;
      String dbPass = null;
      String dbUrl = null;
      String dbName = null;
      logger.log(LogSeverityType.DEBUG, CLASSNAME, methodName, NPConstants.ENTRY);
      //get DB details from configuration file
      dbUser = config.getProperty(NPConstants.user);
      dbPass = config.getProperty(NPConstants.pass);
      dbName = config.getProperty(NPConstants.dbName);
      dbUrl = config.getProperty(NPConstants.dbUrl);
      String connectString = dbUser + "/" + dbPass + "@" + dbName;
      if (isConnected() == false) {
       try {
        //register for JDBC drive
        DriverManager
          .registerDriver(new oracle.jdbc.driver.OracleDriver());
        logger.log(LogSeverityType.DEBUG, CLASSNAME, methodName,
          "Attempting to connect Oracle.");
        //get connection
        conn = DriverManager.getConnection(dbUrl, dbUser, dbPass);
        logger.log(LogSeverityType.DEBUG, CLASSNAME, methodName,
          "Successfully connected to Oracle.");
        setConnectString(connectString);
        conn.setAutoCommit(false);
        Connected = true;
       } catch (SQLException ex) {
        throw new SQLException("Failed to Connect to Oracle. " + ex);
       } catch (Exception lnpEx) {
        throw new LnpEMAException(lnpEx);
       }
      } else {
       //checking for connection whether already connected or not
       if (getConnectString().equals(connectString)) {
        logger.log(
          LogSeverityType.WARN,
          CLASSNAME,
          methodName,
          "Already connected to Oracle using \""
            + connectString
            + "\", you don't need to do it again.  Action Ignored!");
       } else {
        LnpEMAException dbe = new LnpEMAException(
          "Attempting to connect to Oracle using \""
            + connectString
            + "\" is already connected using \""
            + getConnectString() + "\".");
        throw dbe;
       }
      }
      logger.log(LogSeverityType.DEBUG, CLASSNAME, methodName,
        NPConstants.EXIT);
      return conn;
     }// end of getConnection method

    >>

    /**
      * @param eventArraySize
      * @param globalRec
      * @return eventList
      * @throws SQLException
      * @throws LnpEMAException
      */
     public ArrayList<Long> getEMAEventsLists(long eventArraySize,
       GlobalRecords globalRec) throws SQLException, LnpEMAException {
      ArrayList<Long> eventList = new ArrayList<Long>();
      final String method = ".getEMAEventsLists()";
      Connection connection = null;
      OracleCallableStatement cStatement = null;
      ARRAY message_display = null;
      LogMgr logger = LogMgr.getInstance();
      logger.log(LogSeverityType.DEBUG, CLASSNAME, method, NPConstants.ENTRY);
      try {
       // Retrieve a pooled connection
       if (!isConnected()) {
        connection = getConnection();
       } else {
        connection = getConn();
       }
       connection.setAutoCommit(false);
       logger.log(LogSeverityType.DEBUG, CLASSNAME, method,
         NPConstants.ConnectionStart);
       try {
        logger.log(LogSeverityType.DEBUG, CLASSNAME, method, "Get Events");
        // Set the call able statement
        cStatement = (OracleCallableStatement) connection
          .prepareCall("{call " + globalRec.getPackageName()
            + NPConstants.GET_EVENTS_LIST + "}");
        // Set the values of the parameters.
        cStatement.setLong(1, eventArraySize);
        cStatement.registerOutParameter(2, OracleTypes.ARRAY,
          globalRec.getSchema() + NPConstants.GET_EVENT_REQ_TAB);
        // Executing Statement.
        cStatement.execute();
        // Get the array details from DB parameter
        message_display = cStatement.getARRAY(2);
        if (message_display != null) {
         Datum[] arrMessage = message_display.getOracleArray();
         Object[] a;
         for (int i = 0; i < arrMessage.length; i++) {
          oracle.sql.STRUCT os = (oracle.sql.STRUCT) arrMessage[i];
          if (os != null) {
           a = os.getAttributes();
           logger.log(LogSeverityType.DEBUG, CLASSNAME,
             method, a[0].toString());
           eventList.add(Long.parseLong(a[0].toString()));
          }
         }
        }
        // Close the statement
        cStatement.close();
        cStatement = null;
        logger.log(LogSeverityType.DEBUG, CLASSNAME, method,
          NPConstants.Closed_Statement);
        // Commit the transaction and close the connection
        connection.commit();
        logger.log(LogSeverityType.DEBUG, CLASSNAME, method,
          NPConstants.Committed_Transaction);
       } catch (SQLException e) {
        // If any exceptions occur, roll back the transaction
        try {
         logger.log(
           LogSeverityType.ERROR,
           CLASSNAME,
           method,
           "Oracle error while calling "
             + globalRec.getPackageName()
             + NPConstants.GET_EVENTS_LIST);
         // 17002 is socket error.
         if (connection != null && e.getErrorCode() != 17002) {
          recordJobError(connection, e.getErrorCode(),
            e.getMessage());
          connection.rollback();
          throw new LnpEMAException(e);
         } else {
          logger.log(
            LogSeverityType.FATAL,
            CLASSNAME,
            method,
            "The connection to database is lost. "
              + " COMPLETE THE JOB IN T003 TABLE MANUALLY BEFORE RESTARTING SYSTEM. "
              + "******Exiting System******** ",
            e.getErrorCode(), NPConstants.PROCESSNAME, e);
          db.releaseConnections(true);
          System.exit(2000);
         }
        } catch (Exception ex) {
         logger.log(LogSeverityType.ERROR, CLASSNAME, method,
           "Exception occurred rolling back transaction. " + e);
         throw new LnpEMAException(ex);
        }
       }
      } catch (LnpEMAException lnpEx) {
       throw new LnpEMAException(lnpEx);
      }
      logger.log(LogSeverityType.DEBUG, CLASSNAME, method, NPConstants.EXIT);
      return eventList;
     }// getEMAEventsLists
     /**
      * Get FNN List
      * 
      * @param eventID
      * @return fnn list record
      * @throws SQLException
      * @throws LnpEMAException
      */
     public EMA_COMMON_RECORD getEMAFNNList(long eventID,
       GlobalRecords globalRec, String callType) throws SQLException,
       LnpEMAException {
      EMA_COMMON_RECORD currentRecord = new EMA_COMMON_RECORD();
      final String method = ".getEMAFNNList()";
      Connection connection = null;
      OracleCallableStatement cStatement = null;
      ARRAY message_display = null;
      String endFnn = null;
      String cac = null;
      String flag = null;
      String routingnbr = null;
      String nr_flag = null;
      LogMgr logger = LogMgr.getInstance();
      logger.log(LogSeverityType.DEBUG, CLASSNAME, method, NPConstants.ENTRY);
      try {
       // Retrieve a pooled connection
       if (!isConnected()) {
        connection = getConnection();
       } else {
        connection = getConn();
       }
       connection.setAutoCommit(false);
       logger.log(LogSeverityType.DEBUG, CLASSNAME, method,
         NPConstants.ConnectionStart);
       try {
        logger.log(LogSeverityType.DEBUG, CLASSNAME, method,
          "Get FNN List");
        // Set the call able statement
        cStatement = (OracleCallableStatement) connection
          .prepareCall("{call " + globalRec.getPackageName()
            + NPConstants.GET_FNN_LIST + "}");
        // Set the values of the parameters.
        cStatement.setLong(1, eventID);
        cStatement.registerOutParameter(2, OracleTypes.VARCHAR);
        cStatement.registerOutParameter(3, OracleTypes.NUMERIC);
        cStatement.registerOutParameter(4, OracleTypes.ARRAY,
          globalRec.getSchema() + NPConstants.GET_FNN_REQ_TAB);
        cStatement.registerOutParameter(5, OracleTypes.NUMERIC);
        cStatement.setString(6, callType);
        cStatement.registerOutParameter(6, OracleTypes.VARCHAR);
        cStatement.registerOutParameter(7, OracleTypes.VARCHAR);
        // Executing Statement.
        cStatement.execute();
        // set values from DB parameter
        currentRecord.setAction(cStatement.getString(2));
        currentRecord.setCallReqID(cStatement.getInt(3));
        currentRecord.setFlowRate(cStatement.getInt(5));
        currentRecord.setEndOfEvent(cStatement.getString(6));
        if (cStatement.getString(7) != null) {
         currentRecord.setNr_service_name(cStatement.getString(7));
        } else {
         currentRecord
           .setNr_service_name(NPConstants.NO_NR_SERVICE_NAME);
        }
        logger.log(
          LogSeverityType.DEBUG,
          CLASSNAME,
          method,
          "|ServiceName:" + currentRecord.getAction()
            + "|CallReqId:"
            + currentRecord.getCallReqID() + "|FlowRate:"
            + currentRecord.getFlowRate()
            + "|EndOfEvent:"
            + currentRecord.getEndOfEvent()
            + "|NRServiceName:"
            + currentRecord.getNr_service_name());
        if(currentRecord.getAction() != null && currentRecord.getCallReqID() > 0){
        // Get array details from DB parameter
        message_display = cStatement.getARRAY(4);
        if (message_display != null) {
         Datum[] arrMessage = message_display.getOracleArray();
         for (int i = 0; i < arrMessage.length; i++) {
          oracle.sql.STRUCT os = (oracle.sql.STRUCT) arrMessage[i];
          if (os != null) {
           Object[] a = os.getAttributes();
           RequestTableRecord tableRecord = new RequestTableRecord();
           tableRecord.setReqParam(Long.parseLong(a[0].toString()));
           tableRecord.setFNN(a[1].toString());
           if (a[2] == null) {
            endFnn = NPConstants.NO_END_FNN;
           } else {
            endFnn = a[2].toString();
           }
           tableRecord.setEndFNN(endFnn);
           if (a[3] == null) {
            routingnbr = NPConstants.NO_ROUTING_NBR;
           } else {
            routingnbr = a[3].toString();
           }
           tableRecord.setRoutingNbr(routingnbr);
           if (a[4] == null) {
            //If CAC is null for setLNP, setLNPRange, delLNP and delLNPRange
            //assign the routing number to CAC if routing number not null
            if (currentRecord.getAction().equals(
              NPConstants.SETLNP)
              || currentRecord.getAction().equals(
                NPConstants.SETLNPRANGE)
              || currentRecord.getAction().equals(
                NPConstants.DELLNP)
              || currentRecord.getAction().equals(
                NPConstants.DELLNPRANGE)) {
             if (a[3] == null) {
              cac = NPConstants.NO_CAC;
             } else {
              cac = a[3].toString();
             }
            } else {
             cac = NPConstants.NO_CAC;
            }
           } else {
            cac = a[4].toString();
           }
           tableRecord.setCAC(cac);
           if (a[5] == null) {
            nr_flag = NPConstants.NO_NR_FLAG;
           } else {
            nr_flag = a[5].toString();
           }
           tableRecord.setNr_flag(nr_flag);
           if (a[6] == null) {
            flag = NPConstants.FLAG;
           } else {
            flag = a[6].toString();
           }
           tableRecord.setDonarBlockingFlag(flag);
           //add the table record to current record
           currentRecord.add(tableRecord);
           logger.log(
             LogSeverityType.DEBUG,
             CLASSNAME,
             method,
             "|ReqParam:"
               + tableRecord.getReqParam()
               + "|FNN:"
               + tableRecord.getFNN()
               + "|endFnn:"
               + tableRecord.getEndFNN()
               + "|Cac:"
               + tableRecord.getCAC()
               + "|Routing_Nbr:"
               + tableRecord.getRoutingNbr()
               + "|NR_flag:"
               + tableRecord.getNr_flag());
           tableRecord = null;
          }
         }
        }
        }
        // Close the statement
        cStatement.close();
        cStatement = null;
        logger.log(LogSeverityType.DEBUG, CLASSNAME, method,
          NPConstants.Closed_Statement);
        // Commit the transaction and close the connection
        connection.commit();
        logger.log(LogSeverityType.DEBUG, CLASSNAME, method,
          NPConstants.Committed_Transaction);
       } catch (SQLException e) {
        // If any exceptions occur, rollback the transaction
        try {
         logger.log(
           LogSeverityType.ERROR,
           CLASSNAME,
           method,
           "Oracle error while calling "
             + globalRec.getPackageName()
             + NPConstants.GET_FNN_LIST);
         // 17002 is socket error.
         if (connection != null && e.getErrorCode() != 17002) {
          recordJobError(connection, e.getErrorCode(),
            e.getMessage());
          connection.rollback();
          throw new LnpEMAException(e);
         } else {
          logger.log(
            LogSeverityType.FATAL,
            CLASSNAME,
            method,
            "The connection to database is lost. "
              + " COMPLETE THE JOB IN T003 TABLE MANUALLY BEFORE RESTARTING SYSTEM. "
              + "******Exiting System******** ",
            e.getErrorCode(), NPConstants.PROCESSNAME, e);
          db.releaseConnections(true);
          System.exit(2000);
         }
        } catch (Exception ex) {
         throw new LnpEMAException(ex);
        }
       }
      } catch (LnpEMAException lnpEx) {
       throw new LnpEMAException(lnpEx);
      }
      logger.log(LogSeverityType.DEBUG, CLASSNAME, method, NPConstants.EXIT);
      return currentRecord;
     }// end of getEMAFNNList

    >

    a) Do we have any robost JDBC Driver for the above Oracle version?

    >

    Yes - the Oracle JDBC driver - but you didn't tell us which one you are using.

    >> The once supplied by Oracle 1.6 ojdbc14

    See the Oracle JDBC FAQ for the details of the various combinations of DB, Java and JDBC driver that are supported.

    >

    b) Do we have any easier way to bind the Array returned by PLSQL with Array in Java.

    >

    Easier than what? You haven't provided the code that shows how you are doing it now. Post the code so we can see what you are doing.

    >

    c) Do we have to do any JVM Settings to overcome performance?

    >

    Yes - but it depends on WHAT your code is actually doing and you haven't posted the code so we don't know.

     

    And why are you still using such an ancient, non-supported version of Oracle? You need to upgrade to version 11.

  • 3. Re: Performance problems for PLSQL
    user10177353 Newbie
    Currently Being Moderated

    Can someone please help me on the above problem

     

     

    Regards

  • 4. Re: Performance problems for PLSQL
    rp0428 Guru
    Currently Being Moderated

    Sorry - but I don't see anything in that code that shows those methods being called and I don't see any timing being performed that might show what it is that you are calling the execution time.

     

    That code also doesn't show what the actual callable statement query is, the actual parameters being passed or any information about the array or columns being returned (such as the size of the array: 1 item or 1 million?).

     

    Can you explain, using the line numbers in the code, what it is that you say is taking the 3 seconds you posted about, give us the details and the call and the return and tell us what the package code and the java code are supposed to be doing?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points