Re: Library Cache

From: ddf <oratune_at_msn.com>
Date: Mon, 29 Dec 2008 10:14:31 -0800 (PST)
Message-ID: <378f1af7-2550-4e58-a65e-e2ccce9c61ce@k19g2000yqg.googlegroups.com>

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 Received on Mon Dec 29 2008 - 12:14:31 CST