Re: [sqr-users] Dynamic SQL vs. Fixed SQL execution

["George Jansen" <GJANSEN@aflcio.org>]

Before you tune ANYTHING you should know where your time is going. As a first 
step you might try something as simple as adding a procedure that looks at 
v$mystat and seeing what the waits are.



>>> Olga.Gal@bbh.com 04/04/05 11:38 AM >>>




We're using SQR ver 8.1 with Oracle 9i in the datawarehousing environment, 
which produce reports with huge amount of data and run VERY slow.

Our SQR programs construct the Where clause and Order By clause dynamically, 
based on user's selection, and execute the same Select statement many times 
within the same job. This makes me think that the slow performance is possibly 
caused in part by
repeating parsing of these dynamically constructed Select statements.

Before taking a dramatic decision of changing the reports to have only fixed 
Select statements with bind variables ONLY in the Where clause, I would like to 
get assured that this approach will in fact eliminate unnecessary parsing 
(compilation) of SQL
statements. My question is:

Is the SQR  compiling each SQL statement before executing it, or would it do it 
only for a newly executed SQL. In other words, if my program will repeatedly 
call a routine that executes a fixed SQL statement, will this statement get 
parsed every time it's
being executed, or only the first time.

Also, are there any other ways to speed up that time of processing?

Any advice is greatly appreciated.


_______________________________________________
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