Re: Library Cache

On Dec 29, 12:14 pm, ddf <orat..._at_msn.com> wrote:
> On Dec 29, 11:10 am, mrdjmag..._at_aol.com wrote:
>
>
>
> > On Dec 29, 11:03 am, "gym dot scuba dot kennedy at gmail"
>
> > <kenned..._at_verizon.net> wrote:
> > > <mrdjmag..._at_aol.com> wrote in message
>
> > >news:3e0d392e-2c18-4ad2-b9d2-36273b652062_at_q30g2000prq.googlegroups.com...
> > > On Dec 29, 9:09 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
> > > > On Dec 29, 9:53 am, mrdjmag..._at_aol.com wrote:
>
> > > > > Hi,
>
> > > > > We installed this Spotlight for Oracle software. The software shows
> > > > > an 85% re-parse rate against the Library Cache. Here is the biggest
> > > > > issue we have. I do not think anything can be done, but maybe another
> > > > > brain can figure something out:
>
> > > > > We have a table with stored queries:
> > > > > Row 1: SELECT emp_num FROM employees
> > > > > Row 2: SELECT address FROM customer_address
>
> > > > > We have a criteria table which holds any criteria that the query MAY
> > > > > use:
> > > > > Row 1: WHERE emp_first =
> > > > > Row 1: AND emp_last =
> > > > > Row 2: WHERE customer_id =
>
> > > > > So, the PHP code can pass any number of parameters to the procedure,
> > > > > which the procedure can parse and form the query and open a cursor for
> > > > > the PHP code to read.
>
> > > > > This means that the queries may/may not be the same, and need to be re-
> > > > > parsed. So, we lose on that. Is there anything that can be done to
> > > > > tune these and make them faster? An index against every possible
> > > > > combination is not possible, and we have hundreds of these stored
> > > > > queries........
>
> > > > > Any smarter people have any ideas?
>
> > > > > Arthur
>
> > > > If the SQL built from the logic includes constants for the where
> > > > clause conditions you could change it to use bind variables in the
> > > > code. This would give you some reuse.
>
> > > > You could replace the SQL in tables with SQL housed in stored
> > > > procedures that determines the SQL to be submitted based on the
> > > > parameters passed in. These statements would all use bind variables.
> > > > The procedures could pass cursors back to the application.
>
> > > > Take a look at the value of your database parameter cursor_sharing.
> > > > The default is EXACT. You might be able to get some benefit from
> > > > changing it to SIMILAR or FORCE. SQL plan changes are possible if
> > > > this parameter is changed so some tuning may be required.
>
> > > > HTH -- Mark D Powell --
>
> > > Mark,
>
> > > How about a query done like this:
>
> > >    v_optin_str :=  'SELECT COUNT(*) FROM email_list a
> > >                     WHERE '||v_date_clause||' '||v_adid_clause||
> > >                           ' AND customer_id IS NOT NULL
> > >                             AND adid IS NOT NULL
> > >                             AND EXISTS (SELECT user_session
> > >                                         FROM EMAIL_ALERTS b
> > >                                         WHERE alert_type = ''OI''
> > >                                         AND b.user_session =
> > > a.user_session)';
>
> > >    EXECUTE IMMEDIATE v_optin_str
> > >    INTO stats_tab(199)
> > >    USING p_b_date, p_e_date;
>
> > > Is that correctly using the bind variables?
>
> > > Ugly, very ugly.  No that isn't using bind variables.  You need
> > > :bind_variable_name.
>
> > Yeah, we had some seriously shit programmers........time for an ALL
> > STORED PROCEDURE fix.
>
> > Basically where ever there is dynamic SQL, it can be replaced by bind
> > variables.
>
> > Just curious, what is the real difference between using "v_variable"
> > and "USING :v_variable" if the values get replaced?- Hide quoted text -
>
> > - Show quoted text -
>
> One becomes a string literal and one does not:
>
> SQL> create or replace procedure nobind_example(p_var in varchar2)
>   2  is
>   3          v_sqlstr varchar2(2000);
>   4          v_rec_ct number;
>   5
>   6  begin
>   7          v_sqlstr:='select count(*) from emp where ename = '''||
> p_var||'''';
>   8
>   9          dbms_output.put_line(v_sqlstr);
>  10
>  11          execute immediate v_sqlstr
>  12          into v_rec_ct;
>  13
>  14          dbms_output.put_line(v_rec_ct);
>  15
>  16  end;
>  17  /
>
> Procedure created.
>
> SQL>
> SQL> create or replace procedure bind_example(p_var in varchar2)
>   2  is
>   3          v_sqlstr varchar2(2000);
>   4          v_rec_ct number;
>   5
>   6  begin
>   7          v_sqlstr:='select count(*) from emp where ename = :1';
>   8
>   9          dbms_output.put_line(v_sqlstr);
>  10
>  11          execute immediate v_sqlstr
>  12          into v_rec_ct
>  13          using p_var;
>  14
>  15          dbms_output.put_line(v_rec_ct);
>  16
>  17  end;
>  18  /
>
> Procedure created.
>
> SQL>
> SQL> set serveroutput on size 1000000
> SQL>
> SQL> exec nobind_example('ALLEN')
> select count(*) from emp where ename = 'ALLEN'
> 1
>
> PL/SQL procedure successfully completed.
>
> SQL> exec bind_example('ALLEN')
> select count(*) from emp where ename = :1
> 1
>
> PL/SQL procedure successfully completed.
>
> SQL>
>
> Thus the second example generates a query which can be reused with
> differing passed parameter values; the first does not.
>
> David Fitzjarrell

David,

Maybe I'm just not seeing it. But if you use a bind varialbe (:1) or you use concatenation (|| v_variable), don't both queries get dynamically generated as both are replacing the variable with a value? Received on Tue Dec 30 2008 - 09:01:56 CST