RE: [sqr-users] Oracle Insert/Select From issue

["Graeme Steyn " <g.steyn@cqu.edu.au>]

Ed,

As Richard has indicated, this would not be related to the number of
trips.  The problem is more likely related to the way that Oracle's Cost
Based Optimizer generates the SQL execution plan.  To demonstrate this,
run the SQR with the -S command line option to see all of the SQL
cursors actually set-up and used by the SQR. You will find that SQR
variables have been replaced by bind variables, e.g. :1 and :2 in the
select columns section and the from (:3) and to dates (:4) below.  When
you execute this in TOAD or SQLPlus you will get a SQL plan being
generated (call this plan 1) with a given response time.

                         INSERT INTO PS_HIG_RPT7X_DTL SELECT
                         0,
                         '71F',
                         :1,
                         B.BANK_SETID,
                         B.BANK_CD ,
                         B.BANK_ACCT_KEY,
                         A.ACCOUNTING_DT,
                         1,
                         3,
                         C.HIG_CCPS_IND,
                         C.HIG_SRS_IND,
                         :2,
                         SUM(A.MONETARY_AMOUNT - ABS(D.MONETARY_AMOUNT))

                         FROM PS_VCHR_ACCTG_LINE A, PS_PYMNT_VCHR_XREF B
, PS_HIG_BPROFL_VW C, PS_VCHR_ACCTG_LINE D
                         WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT
                         AND A.VOUCHER_ID = B.VOUCHER_ID
                         AND A.PYMNT_CNT = B.PYMNT_CNT
                         AND B.BUSINESS_UNIT = C.BUSINESS_UNIT
                         AND C.ORIGIN = 'ONL'
                         AND a.monetary_amount > 0
                         and b.pymnt_gross_amt > 0
                         AND a.accounting_dt between :3 and :4
                         And a.appl_jrnl_id = 'PAYMENTS'
                         And a.posting_process = 'PYMN'
                         And a.dst_acct_type = 'APA'
                         And a.pymnt_cnt = (select min(z.pymnt_cnt)
                                            from ps_vchr_acctg_line z
                                            where z.business_unit =
a.business_unit
                                            and z.voucher_id =
a.voucher_id
                                            and z.appl_jrnl_id =
'PAYMENTS'
                                            and z.posting_process =
'PYMN'
                                            and z.dst_acct_type = 'APA')
                         And d.business_unit = a.business_unit
                         And d.voucher_id = a.voucher_id
                         And d.accounting_dt < a.accounting_dt
                         And d.appl_jrnl_id = 'ACCRUAL'
                         And d.posting_process = 'ACCR'
                         And d.dst_acct_type = 'APA'
                         And d.unpost_seq = a.unpost_seq
                         And abs(a.monetary_amount) <>
abs(d.monetary_amount)
                         GROUP BY B.BANK_SETID, B.BANK_CD ,
B.BANK_ACCT_KEY, a.accounting_dt,C.HIG_CCPS_IND,C.HIG_SRS_IND

Now take exactly the same statement without bind variables and execute
it in TOAD, i.e. replace the variables with constants for testing (see
below).  You will more than likely get a different SQL execution plan
(plan 2) and response time.

                         INSERT INTO PS_HIG_RPT7X_DTL SELECT
                         0,
                         '71F',
                         '10-JUN-2005',
                         B.BANK_SETID,
                         B.BANK_CD ,
                         B.BANK_ACCT_KEY,
                         A.ACCOUNTING_DT,
                         1,
                         3,
                         C.HIG_CCPS_IND,
                         C.HIG_SRS_IND,
                         'X',
                         SUM(A.MONETARY_AMOUNT - ABS(D.MONETARY_AMOUNT))

                         FROM PS_VCHR_ACCTG_LINE A, PS_PYMNT_VCHR_XREF B
, PS_HIG_BPROFL_VW C, PS_VCHR_ACCTG_LINE D
                         WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT
                         AND A.VOUCHER_ID = B.VOUCHER_ID
                         AND A.PYMNT_CNT = B.PYMNT_CNT
                         AND B.BUSINESS_UNIT = C.BUSINESS_UNIT
                         AND C.ORIGIN = 'ONL'
                         AND a.monetary_amount > 0
                         and b.pymnt_gross_amt > 0
                         AND a.accounting_dt between '10-JUN-2005' and
'20-JUN-2005'
                         And a.appl_jrnl_id = 'PAYMENTS'
                         And a.posting_process = 'PYMN'
                         And a.dst_acct_type = 'APA'
                         And a.pymnt_cnt = (select min(z.pymnt_cnt)
                                            from ps_vchr_acctg_line z
                                            where z.business_unit =
a.business_unit
                                            and z.voucher_id =
a.voucher_id
                                            and z.appl_jrnl_id =
'PAYMENTS'
                                            and z.posting_process =
'PYMN'
                                            and z.dst_acct_type = 'APA')
                         And d.business_unit = a.business_unit
                         And d.voucher_id = a.voucher_id
                         And d.accounting_dt < a.accounting_dt
                         And d.appl_jrnl_id = 'ACCRUAL'
                         And d.posting_process = 'ACCR'
                         And d.dst_acct_type = 'APA'
                         And d.unpost_seq = a.unpost_seq
                         And abs(a.monetary_amount) <>
abs(d.monetary_amount)
                         GROUP BY B.BANK_SETID, B.BANK_CD ,
B.BANK_ACCT_KEY, a.accounting_dt,C.HIG_CCPS_IND,C.HIG_SRS_IND

Basically, if the optimiser knows what the values are it may decide to
use index x as the best way of accessing the required rows.  When bind
variables are use it will often use a different plan as it no longer has
an indication of how restrictive your date range is likely to be (may
use a full table scan instead).  You can even try a third variant, where
the dates are specified using to_date('YYYY-MM-DD', :1) etc and you
would possibly get a different result.  The differences can be dramatic.
I recently had a case where the query with hardcoded values ran in 2.7
hours, but replacing the values with bind variables resulted in the same
query running in 2 minutes.  

The basic idea is make sure that you tune the query as it is going to be
used by SQR with bind variables.  Alternately, use Yelena's approach
with PL/SQL.  

If you interested in additional reading, look at Tom Kytes latest book,
"Expert Oracle Database Architecture: 9i and 10g Programming Techniques
and Solutions" (This is an updated version of "Expert Oracle
One-on-One").

Regards,

Graeme Steyn 
Corporate Information Systems 
Information Technology Division 
Central Queensland University 
Tel: +61 7 4930 9256 
E-mail: g.steyn@cqu.edu.au 



-----Original Message-----
From: Knapp, Richard [mailto:KnappR@umsystem.edu] 
Sent: Wednesday, 28 September 2005 6:32 AM
To: This list is for discussion about the SQR database reportinglanguage
fromHyperion Solutions.
Cc: sqr-users-bounces+yelena_kontorovich=baxter.com@sqrug.org
Subject: RE: [sqr-users] Oracle Insert/Select From issue



If the code below is all there is to the SQR, then number of trips to
the db is not the issue.  This is a single query with no branching to
other procedures which could make the additional trips to the db and
take up time you are concerned about.  So, lots of trips can be a time
sink but this doesn't look like one of those cases.

Richard Knapp
Database Programmer/Analyst
Institutional Research and Planning
University of Missouri System
573-882-8856
knappr@umsystem.edu
-----Original Message-----
From: sqr-users-bounces+knappr=umsystem.edu@sqrug.org
[mailto:sqr-users-bounces+knappr=umsystem.edu@sqrug.org] On Behalf Of
yelena_kontorovich@baxter.com
Sent: Tuesday, September 27, 2005 3:23 PM
To: This list is for discussion about the SQR database reporting
languagefrom Hyperion Solutions.
Cc: sqr-users@sqrug.org;
sqr-users-bounces+yelena_kontorovich=baxter.com@sqrug.org
Subject: Re: [sqr-users] Oracle Insert/Select From issue

Edward,
I don't know if this is going to help you, but for several SQRs we had
to put all SQR logic in PL/SQL procedure and it runs significantly
faster.
My
understanding is that in SQR it had to make a lot of trips to the
database (even when we've changed connection to BEQ in tnsnames.ora and
it should use local connection, it still spends a lot of times going
back and forth and this time is not shown in any traces).  In PL/SQL it
makes one trip to Oracle and works there.  Again, it might not be your
solution, but the fact that it runs in SQLPlus ...
Thanks,
Yelena Kontorovich
Technical Consultant
847.948.2927
yelena_kontorovich@baxter.com
The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material.  Any review, re-transmission, dissemination or other use of,
or taking of any action in reliance upon, this information by persons or
entities other than the intended recipient is prohibited.  If you
received this in error, please contact the sender and delete the
content.


|---------+--------------------------------------------------------->
|         |           "Hiney, Edward (CLAIM, Claims)"               |
|         |           <Edward.Hiney@thehartford.com>                |
|         |           Sent by:                                      |
|         |           sqr-users-bounces+yelena_kontorovich=baxter.co|
|         |           m@sqrug.org                                   |
|         |                                                         |
|         |                                                         |
|         |           09/26/2005 10:36 AM                           |
|         |           Please respond to "This list is for discussion|
|         |           about the SQR database reporting  language    |
|         |           from Hyperion Solutions."                     |
|         |                                                         |
|---------+--------------------------------------------------------->
 
>-----------------------------------------------------------------------
-----------------------|
  |
|
  |       To:       sqr-users@sqrug.org
|
  |       cc:
|
  |       Subject:  [sqr-users] Oracle Insert/Select From issue
|
 
>-----------------------------------------------------------------------
-----------------------|




                         All,
                         Here's the problem.
                         We have an SQR with code (below) found in a
procedure that runs for a VERY LONG TIME (HOURS).I tried separating in
into a "Select" and an "Insert" but that did not help.  If anyone can
come up with a solution to break up the WHERE clause into 2 parts (doing
the 2nd join to PS_VCHR_ACCTG_LINE D ) I think that would do it.  It's
not as easy as it looks.

                         We did an Oracle SQL trace, so we know that
this is where the bottleneck is.  We also pu SHOW stmts in the SQR.

                         The caveat is that when this sql is executed in
SQL-plus or TOAD, it runs in 2 minutes!  Any ideas would be appreciated.


                         TIA.
                         ed.hiney




                         Begin-SQL on-error=170-sql-error
                         INSERT INTO PS_HIG_RPT7X_DTL SELECT
                         0,
                         '71F',
                         $Tdate,
                         B.BANK_SETID,
                         B.BANK_CD ,
                         B.BANK_ACCT_KEY,
                         A.ACCOUNTING_DT,
                         1,
                         3,
                         C.HIG_CCPS_IND,
                         C.HIG_SRS_IND,
                         &HIG_FREQ_IND,
                         SUM(A.MONETARY_AMOUNT - ABS(D.MONETARY_AMOUNT))

                         FROM PS_VCHR_ACCTG_LINE A, PS_PYMNT_VCHR_XREF B
, PS_HIG_BPROFL_VW C, PS_VCHR_ACCTG_LINE D
                         WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT
                         AND A.VOUCHER_ID = B.VOUCHER_ID
                         AND A.PYMNT_CNT = B.PYMNT_CNT
                         AND B.BUSINESS_UNIT = C.BUSINESS_UNIT
                         AND C.ORIGIN = 'ONL'
                         AND a.monetary_amount > 0
                         and b.pymnt_gross_amt > 0
                         AND a.accounting_dt between $Fdate and $Tdate
                         And a.appl_jrnl_id = 'PAYMENTS'
                         And a.posting_process = 'PYMN'
                         And a.dst_acct_type = 'APA'
                         And a.pymnt_cnt = (select min(z.pymnt_cnt)
                                            from ps_vchr_acctg_line z
                                            where z.business_unit =
a.business_unit
                                            and z.voucher_id =
a.voucher_id
                                            and z.appl_jrnl_id =
'PAYMENTS'
                                            and z.posting_process =
'PYMN'
                                            and z.dst_acct_type = 'APA')
                         And d.business_unit = a.business_unit
                         And d.voucher_id = a.voucher_id
                         And d.accounting_dt < a.accounting_dt
                         And d.appl_jrnl_id = 'ACCRUAL'
                         And d.posting_process = 'ACCR'
                         And d.dst_acct_type = 'APA'
                         And d.unpost_seq = a.unpost_seq
                         And abs(a.monetary_amount) <>
abs(d.monetary_amount)
                         GROUP BY B.BANK_SETID, B.BANK_CD ,
B.BANK_ACCT_KEY, a.accounting_dt,C.HIG_CCPS_IND,C.HIG_SRS_IND
                         end-sql



Thank you,

Ed Hiney
PeopleSoft Developer
Claims IT
The Hartford
edward.hiney@thehartford.com
860-547-3227





************************************************************************
*
This communication, including attachments, is for the exclusive use of
addressee and may contain proprietary, confidential and/or privileged
information.  If you are not the intended recipient, any use, copying,
disclosure, dissemination or distribution is strictly prohibited.  If
you are not the intended recipient, please notify the sender immediately
by return e-mail, delete this communication and destroy all copies.
************************************************************************
*

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





The information transmitted is intended only for the person(s)or entity
to which it is addressed and may contain confidential and/or legally
privileged material. Delivery of this message to any person other than
the intended recipient(s) is not intended in any way to waive privilege
or confidentiality. Any review, retransmission, dissemination or other
use of , or taking of any action in reliance upon, this information by
entities other than the intended recipient is prohibited. If you receive
this in error, please contact the sender and delete the material from
any computer.

For Translation:

http://www.baxter.com/email_disclaimer


_______________________________________________
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