Re: Library Cache

On Dec 30, 9:45 am, ddf <orat..._at_msn.com> wrote:
> On Dec 30, 9:01 am, mrdjmag..._at_aol.com wrote:
>
>
>
> > 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?- Hide quoted text -
>
> > - Show quoted text -
>
> You're not seeing it, as the results of both operations were displayed
> in my last post:
>
> v_sqlstr:='select count(*) from emp where ename = '''|| p_var||'''';
>
> produces
>
> select count(*) from emp where ename = 'ALLEN'
>
> Each pass through with a different p_var value creates a different
> query string to parse, whereas:
>
> v_sqlstr:='select count(*) from emp where ename = :1';
>
> produces
>
> select count(*) from emp where ename = :1
>
> EVERY time this code is executed, regardless of the value of p_var
>
> If I execute the first no-bind-variable example 100 times, with 100
> different values for p_var, 100 different query strings are generated,
> each one requiring a hard parse and occupying space in the SQL area
> and none are likely to be reused; if I execute the bind-variable
> example 100 times, with the same 100 values for p_var, only ONE query
> string is generated, and it is reused 99 times.  And it's not that the
> values get replaced, it's HOW and WHEN that replacement occurs.  In
> the first example the replacement occurs before the parse, and it
> generates a unique SQL statement for each unique value supplied to
> p_var, and in the second the value is replaced after parse time (hard
> or soft) which allows for reusable code (the driving force behind
> using bind variables).  There is a big difference between the two
> operations.  Please read here:
>
> http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/sqlpls...
>
> to hopefully clear up any misunderstandings you have.
>
> David Fitzjarrell

Thanks Dave......it'd be interesting to actually see how many seconds say 1000 executions of each query take. From what I've read, hard parses are decent CPU intensive. A but of timing will answer that.

Thanks again Received on Tue Dec 30 2008 - 09:57:56 CST