[sqr-users] Performance Issue with Oracle9i

[Gina Bencke <g.bencke@csuohio.edu>]

Good Morning,
 
We are experiencing a strange performance issue with an SQR that worked
efficiently prior to our Oracle 9i Upgrade.  We have a program that ran in <
5 minutes under Oracle 8 which is taking several hours using Oracle 9i.  We
have identified the specific procedure containing a Select Paragraph that
runs slow but can not understand why.  When the logic of the program is
changed to call this procedure using a hard coded value for one of the bind
variables (which happens to be one of the key fields) the performance issue
disappears.   (Obviously changing the bind variable to a hard coded value is
not a work around, but simply a step in trying to understand the problem.)
 
I was able to isolate the problem by writing a test program which simply
sets the values of the bind variables and then calls this procedure and the
poor performance is repeatable when isolated in this fashion.
 
I have provided the code for the procedure whose performance is unacceptable
below.  However I do not think that the problem is in the join itself .
Other programs which use the two tables found in the select paragraph are
not experiencing poor performance.  Also executing the join from SQL*Plus
does not result in a performance issue.  
 
Has anyone else experienced a similar issue?  Is there a environment
parameter that may need to be set?
 
Gina Bencke
Business Analyst
Information Services & Technology
Cleveland State University
Phone: (216) 875-9630
Fax:    (216) 687-9200
 
 
!***************************************************************************
***********
begin-procedure Get-Skip-Students 
!***************************************************************************
***********
 
   let $Skip_Student = 'Y'
 
begin-SELECT loops=1
JB.JOBCODE
JC.JOB_FUNCTION
 
   evaluate &JC.JOB_FUNCTION
     when = 'STW'
     when = 'GTA'
     when = 'NTG'
       let $Skip_Student = 'Y'  
       break
     when-other 
       let $Skip_Student = 'N' 
       break
    end-evaluate
   
 
FROM  PS_JOB JB,
      PS_JOBCODE_TBL JC
WHERE JB.EMPLID = $Old_Emplid
  AND JB.EMPL_RCD =
0                                                          !RK 02/19/2003
  AND JB.EFFDT = (Select MAX(JB2.EFFDT) from PS_JOB JB2
                   where JB2.EMPLID = JB.EMPLID
                     and JB2.EMPL_RCD =
JB.EMPL_RCD                            !RK 02/19/2003
                     and JB2.EFFDT <= $Quarter_End_Dt)
  AND JB.EFFSEQ = (Select MAX(JB3.EFFSEQ) from PS_JOB JB3
                   where JB3.EMPLID = JB.EMPLID
                     and JB3.EMPL_RCD =
JB.EMPL_RCD                            !RK 02/19/2003
                     and JB3.EFFDT = JB.EFFDT)
  AND JB.JOBCODE = JC.JOBCODE
  and JC.EFFDT   = (select max(JC1.EFFDT) from ps_JOBCODE_TBL JC1
                    where JC1.Jobcode = JC.jobcode
                    and jc1.effdt <= JB.effdt
                    and jc1.eff_Status = 'A')
end-SELECT
 
end-procedure
 
 


Gina Bencke
Business Analyst
Information Services & Technology
Cleveland State University
Phone: (216) 875-9630
Fax:    (216) 687-9200
 
 



_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users