RE: [sqr-users] Performance Issue with Oracle9i

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

Thanks to a suggestion from Rick I modified the SQL to include setid in the
where clause. (Btw Rick "AND JC.SETID = JB.SETID_JOBCODE" did not help but
"AND JC.SETID = $SetID" did.) This alone was not enough but placed me on the
path to actually try tuning the SQL.  I remember reading a paper on simple
tuning techniques which suggests adding all keys in the select list to help
Oracle select the most effective select path.  Now the SQL runs faster than
before.


SELECT JB.EMPLID, JB.EMPL_RCD, JB.EFFDT, JB.EFFSEQ, JC.SETID,
JC.JOBCODE, JC.EFFDT, 
         JB.JOBCODE, JC.JOB_FUNCTION  FROM  PS_JOB JB,
         PS_JOBCODE_TBL JC WHERE JB.EMPLID = :1 AND JB.EFFDT = (Select
         MAX(JB2.EFFDT) from PS_JOB JB2 where JB2.EMPLID = JB.EMPLID and
         JB2.EMPL_RCD = JB.EMPL_RCD and JB2.EFFDT <=
         to_date('03/31/2005','mm/dd/yyyy')) AND JB.EFFSEQ = (Select
         MAX(JB3.EFFSEQ) from PS_JOB JB3 where JB3.EMPLID = JB.EMPLID and
         JB3.EMPL_RCD = JB.EMPL_RCD and JB3.EFFDT = JB.EFFDT) AND
         JB.JOB_INDICATOR = 'P' AND JB.JOBCODE = JC.JOBCODE 
AND JC.SETID = :2 
         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')


When I ran the original SQL in SQP*PLUS, I received the same result as I did
from the SQR but in less than a second compared to 15 or 20 minutes in SQR.
I still do not understand why Oracle would chose a vastly different
execution plan in SQL*Plus and SQR.  Can anyone shed light on this issue?

Gina Bencke
216-875-9630
 
 

-----Original Message-----
From: sqr-users-bounces+gina.work=bencke.com@sqrug.org
[mailto:sqr-users-bounces+gina.work=bencke.com@sqrug.org] On Behalf Of Gina
Bencke
Sent: Tuesday, April 05, 2005 11: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