Re: Library Cache

On Dec 30, 1:58 pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> 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 --

Mark,

One issue is that our entire application is written this way. Most of the queries are stored in a table. An acronym is passed into the package and the query is retrieved from the table and a cursor is opened for PHP to use. Take a look at the procedure:

PROCEDURE get_data (

   p_line        IN OUT  REF_CURSOR,
   p_var      IN      VARCHAR2 DEFAULT NULL,
   p_name IN      VARCHAR2) IS

BEGIN
   SELECT query, order_by
   INTO v_query, v_order
   FROM report_sql
   WHERE name = p_name;

   IF p_var IS NULL THEN

      NULL;
   ELSE
      v_query := v_query || p_var || v_order;    END IF; OPEN p_line FOR v_query;
END;
/

So, if I had a query like this: SELECT portfolio_id, portfolio_name FROM portfolio WHERE customer_id =
The code above would create this: SELECT portfolio_id, portfolio_name FROM portfolio WHERE customer_id = 12345

If I changed the query to this: SELECT portfolio_id, portfolio_name FROM portfolio WHERE customer_id = :1
Would I be able to use bind variables??? Would it properly perform the substitution?

Much Thanks! Received on Tue Dec 30 2008 - 15:47:01 CST