RE: [sqr-users] Performance Issue with Oracle9i

["Lynds,Rick" <rlynds@mwdh2o.com>]

I noticed that you are not using SETID on the JOBCODE_TBL.  That is the
primary key field.  Could it be that some quirk is causing the code to
perform a table scan where it had not done so before?

You can modify your join (and subquery) to join on JC.SETID =
JB.SETID_JOBCODE

HTH

Rick 

-----Original Message-----
From: sqr-users-bounces+rlynds=mwdh2o.com@sqrug.org
[mailto:sqr-users-bounces+rlynds=mwdh2o.com@sqrug.org] On Behalf Of Gina
Bencke
Sent: Tuesday, April 05, 2005 8:34 AM
To: sqr-users@sqrug.org
Subject: [sqr-users] Performance Issue with Oracle9i

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

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