RE: [sqr-users] Performance Issue with Oracle9i

["Alexander, Steve" <Steven.Alexander@sanjoseca.gov>]

I have two suggestions about your subquery to the PS_JOBCODE_TBL table that
may improve performance and accuracy.  First, you probably want the current
row as of the $Quarter_End_Dt, not as of the Effdt of the PS_JOB row.
Suppose I started a job a year ago and had no other changes in PS_JOB.  Then
the value of JOB_FUNCTION changed six months ago.  Do you want the job
function code that was in effect the last time I had a change in PS_JOB, or
do you want the code in effect today?  Second, you probably want the current
row of the PS_JOBCODE_TBL, not the most recent row that was active.

Alternately,  you could use load-lookup to bring all the PS_JOB values of
JOBCODE and all PS_JOBCODE_TBL values of JOB_FUNCTION into memory in one
pass.  If you can do that for PS_JOBCODE_TBL, you can avoid the join.  If
you can do that for PS_JOB, you avoid running the same SQL thousands of
times.



-----Original Message-----
From: Bob Stone [mailto:bstone@fastenal.com]
Sent: Tuesday, April 05, 2005 11:26 AM
To: 'This list is for discussion about the SQR database reporting
languagefrom Hyperion Solutions.'
Subject: RE: [sqr-users] Performance Issue with Oracle9i


I dont know how often this particular SQL is running, but if it's running a
lot there's a chance that it's reparsing it each time (which is slow).  It
might choose the fastest way to actually run the SQL, but if it has to
rechoose that same way each time, that slows it down.  One idea would be to
do something about the date, because an actual string character and the
'to_date' function will most likely not be recognized as a bind variable.
Then Oracle doesn't know that it's the exact same SQL line run other times
in the program, and it will reparse it.  If, earlier in the SQR, you make
$quarter_date = '03-MAR-2005' (Oracle will recognize 'DD-MON-YYYY' as a date
for comparing) and then say AND JB2.EFFDT <= $quarter_date, that would be
used as a bind variable.

just an idea.

-----Original Message-----
From: sqr-users-bounces+bstone=fastenal.com@sqrug.org
[mailto:sqr-users-bounces+bstone=fastenal.com@sqrug.org]On Behalf Of
Gina Bencke
Sent: Tuesday, April 05, 2005 1:15 PM
To: 'This list is for discussion about the SQR database reporting
language from Hyperion Solutions.'
Subject: RE: [sqr-users] Performance Issue with Oracle9i


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

_______________________________________________
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