Re: Literal SQL versus Packaged Procedure performance

jocave_at_yahoo.com (Justin Cave) wrote in message news:<233b7a65.0211291347.674db2ad_at_posting.google.com>...
> Have you tried analyzing the tables in question. I have seen cases
> where dynamic SQL was significantly slower than static SQL when
> statistics on the various tables weren't up to date. I'm unclear as
> to why this would be the case, however.
>
> Justin Cave
>
> pjo97_at_btopenworld.com (Paddy O) wrote in message news:<95ead45c.0211290700.2201989_at_posting.google.com>...
> > Hi
> >
> > Can anyone help. I'm using 8.1.7.0.0 on Win2k. I've a requirement to
> > allow a user to perform a search depending on which values he chooses.
> > I've run into a problem with dynamic SQL performance. I'll try and
> > demonstrate how I've come to this conclusion.
> >
> > I've a package which contains the following simple procedure for
> > testing.
> >
> > PROCEDURE find3(INP_table_name IN varchar2,
> > column_name IN varchar2,
> > col_value IN varchar2,
> > free_sql IN varchar2 := NULL,
> > form_name varchar2,
> > inp_sess_details IN varchar2,
> > the_cursor OUT utility_ref_cur)
> >
> > IS
> >
> > BEGIN
> >
> > OPEN the_cursor FOR
> > SELECT ID "111",
> > NAME "85",
> > BIRTH_DATE "118",
> > F_ID "117",
> > M_ID "119"
> > ,rtrim(CURR_H_NUM,' ') ||' '|| rtrim(CURR_HD_NUMBER,' ')"92",
> > A.ROWID "1"
> > from TEST a
> > WHERE a.ID = '036349910001' order by 1;
> >
> > END find3;
> >
> > The parameters are meaningless in this example. This returns the
> > cursor within 1 sec on table test containing 700,000 rows.
> >
> > The next example uses a function to produce the same sql as above
> > dynamically.
> >
> > PROCEDURE find2(INP_table_name IN varchar2,
> > column_name IN varchar2,
> > col_value IN varchar2,
> > free_sql IN varchar2 := NULL,
> > form_name varchar2,
> > inp_sess_details IN varchar2,
> > the_cursor OUT utility_ref_cur)
> >
> > IS
> >
> > BEGIN
> >
> > OPEN the_cursor FOR
> > QUERY2(INP_table_name,column_name,col_value,free_sql,form_name,inp_sess_details,0);
> >
> > END find2;
> >
> > This example using dynamic sql takes around 9 secs to return the
> > cursor when there are 700,000 records in TEST but returns within 1 sec
> > with around 100 to 200 records in test. The overhead in the query
> > function is negligible.
> >
> > It would appear there is a significant overhead in using the sql
> > produced dynamically as opposed to using static SQL.
> >
> > Can anyone shine any light on this prob.
> >
> > TIA
> >
> > Paddy

Sorry,

This was my fault. The dynamic SQL was actually putting the upper function around the id, therefore not using the PK index on id, hence the difference in performance. Apologies again. Very red faced. Thanks for the help

Paddy Received on Sat Nov 30 2002 - 13:19:32 CET