0 Replies Latest reply: Nov 1, 2012 3:17 AM by Suddhasatwa_Bhaumik RSS

    SQL ID different for same SQL over time ??

    Suddhasatwa_Bhaumik
      Hello All

      We have a PeopleSoft HRMS application 9.00.000 running at front end of Oracle DB 10.2.0.3 on a Solaris 9 OS.

      I have the below piece of SQL which runs on the database when user opts for a particular link/option from front end. It takes the user ID as input variable.
      SELECT DISTINCT EMPLID,
                        EMPL_RCD,
                        ALTER_EMPLID,
                        NAME,
                        LAST_NAME_SRCH,
                        SECOND_LAST_SRCH,
                        NAME_AC,
                        MIDDLE_NAME,
                        EMPL_STATUS,
                        OFFICER_CD,
                        HOME_HOST_CLASS
          FROM PS_EMPLMT_SRCH_COR
         WHERE OPRID = :1 AND ALTER_EMPLID LIKE '266558%'
      ORDER BY ALTER_EMPLID, EMPLID, EMPL_RCD;
      While on one day the SQL is running fine, on other day it does not!
      Table statistics are already cross checked and they are not changing and are not being altered over time.

      However I can see different SQL IDs from AWR report(s)/V$SQLAREA for same query even if it is running with the same input parameter.

      For each of these runs the optimiser cost value as per V$SQLAREA is different, and so is the amount of buffer gets it does.

      From historical experience of this user, it has never been slow. Recently there were no changes done in the platform/database/application as well.

      Please share your thoughts on the same.

      Thanks,
      Suddhasatwa

      Edited by: SBhaumik_DBA on Nov 1, 2012 1:17 AM