Re: Library Cache

On Dec 30, 2:37 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Dec 30, 7:57 am, mrdjmag..._at_aol.com wrote:
>
>
>
>
>
> > 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
>
> Actually, it is a bit more complex than that, since straight timing
> will show one thing, but putting a system under load with various
> bottlenecks can show something completely different.  These kinds of
> things can spin wildly out of control if you have software written
> idiosyncratically to make things worse.  Add in bugs,
> misconfigurations, and the way things work changing over versions that
> Jonathan mentioned, you can have a real disaster that products like
> Spotlight can't properly figure out.
>
> Please read Tom Kyte's writings about designing for performance.
> Being able to generate any report based on parameters may be cool, but
> it has to be done right.  Maybe if you analyse (in a business sense)
> which reports are actually being used and write those into stored
> procedures...
>
> I always hesitate to disagree with Mark Powell, but that
> cursor_sharing parameter... google for problems.
>
> jg
> --
> @home.com is bogus.http://net-security.org/malware_news.php?id=1019- Hide quoted text -
>
> - Show quoted text -

Joel, I do not think you are disagreeing with me. I responsed to the initial post which had less data than now available, and I did suggest placing the SQL into stored procedures instead so that SQL with bind variables would be used in place of dynamic code. I just added the mention on cursor_sharing because if it is too late in the project to fix the design then the parameter might help. The basic design really needs fixing: no disagreement there.

And you are right in that over the years (versions) there have been numerous issues with trying to use SIMILAR or FORCE as the value of cursor_sharing. We ran into performance issues. There however do appear to be some sites that have had good results with using values other than EXACT.

HTH -- Mark D Powell -- Received on Tue Dec 30 2008 - 13:58:49 CST