Re: Parsing in SQR

[Sumathy Muthuswamy <sumathy_muthuswamy@PEOPLESOFT.COM>]

Mark,

Sql's with bind variables also get parsed more. Here is an example from my
trace.

But this does not happen with all the sqls , some are parsed less than the
# of executions. One more thing I observed was if I run my process with for
say 1000 records most of the sqls with binds are parsed twice or thrice ,
but if I run the process for say 5000 records the parses increase.  That is
the reason I tried to increase my shared_pool size but as I said it did
not make any difference.  Does sqr have any sql area of it own that
interfaces between the sqr and the database ?

Thanks,
Sumathy

INSERT INTO PS_PERS_ADDR_TYPES (EMPLID, ADDR_TYPE)
VALUES
 (:1, :2)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse     2601      1.65       1.66          0          0          0           0
Execute   2600      8.34       8.42         10       2606      10534        2600
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     5201      9.99      10.08         10       2606      10534        2600

select PDE.EFFDT
FROM
  PS_PERS_DATA_EFFDT PDE WHERE PDE.EMPLID = :1 AND PDE.EFFDT <= :2 ORDER BY
  PDE.EFFDT DESC


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse     2601      1.11       1.13          0          0          0           0
Execute   2600      1.90       1.87          0          0          0           0
Fetch     2600      6.32       6.29         16      49838          0        2600
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     7801      9.33       9.29         16      49838          0        260







Pietersz Mark x8394 <mpieters@CITIPOWER.COM.AU>@list.iex.net> on 06/15/99
08:29:11 PM

Please respond to SQR-USERS@list.iex.net

Sent by:  "Discussion of SQR, SQRIBE Technologies's database reporting
      language" <SQR-USERS@list.iex.net>


To:   Multiple recipients of list SQR-USERS <SQR-USERS@list.iex.net>
cc:
Subject:  Re: Parsing in SQR


Sumathy,

One thing that can affect parsing is how the SQL is represented in the
program.  If you format the statements so that bind variables are used
rather than literals you will reduce the number of times an SQL statement
is
parsed.

The following is taken from the sqr guide

SQL Error Checking
SQR checks and reports database errors for SQL statements. When an
SQR program is compiled, SQR checks the syntax of the SELECT,
UPDATE, INSERT, and DELETE SQL statements in your program. Any
SQL syntax error will be detected and reported at compile time, before the
execution of the report begins.

Rgds

Mark Pietersz

> -----Original Message-----
> From: Sumathy Muthuswamy [SMTP:sumathy_muthuswamy@PEOPLESOFT.COM]
> Sent: Wednesday, 16 June 1999 6:57
> To:   Multiple recipients of list SQR-USERS
> Subject:      Parsing in SQR
>
> I am running SQR programs on a Oracle platform. I have observed that all
> the sql statements in an SQR (even the ones in the #includes) gets parsed
> irrespective of whether they are executed or not.  Also a very simple sql
> statement gets parsed almost the number of times it gets executed.
> Increasing the size of the shared pool did not help reduce the parses.
> Has
> anyone come across this problem. If so ,can someone please give me leads
> on
> how to get rid of the parses.
>
> Thanks,
> Sumathy
**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This email message has been swept for the presence of computer viruses.

CitiPower Pty ACN 064 651 056
**********************************************************************