Re: Library Cache

On Dec 30, 2:37 pm, joel garry <joel-ga...@xxxxxxxx> wrote:
On Dec 30, 7:57 am, mrdjmag...@xxxxxxx wrote:





On Dec 30, 9:45 am, ddf <orat...@xxxxxxx> wrote:

On Dec 30, 9:01 am, mrdjmag...@xxxxxxx wrote:

On Dec 29, 12:14 pm, ddf <orat...@xxxxxxx> wrote:

On Dec 29, 11:10 am, mrdjmag...@xxxxxxx wrote:

On Dec 29, 11:03 am, "gym dot scuba dot kennedy at gmail"

<kenned...@xxxxxxxxxxx> wrote:
<mrdjmag...@xxxxxxx> wrote in message

news:3e0d392e-2c18-4ad2-b9d2-36273b652062@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Dec 29, 9:09 am, Mark D Powell <Mark.Pow...@xxxxxxx> wrote:

On Dec 29, 9:53 am, mrdjmag...@xxxxxxx 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 *** 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 --
.