Re: JDBC,soft and hard parses, hit ratios

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/06/10
Message-ID: <8htjh7$r6d$1@nnrp1.deja.com>#1/1

In article <oe03ks0rpv3jfh4c984vl8ijvgg11cufhp_at_4ax.com>,   Doug Cowles <dcowles_at_i84.net> wrote:
> I noticed something alarming in my database last week.
> During a scalability test, I doubled the size of a batch job and ended
> up with a fragmented shared pool. Could of just made the pool bigger,
> but noticed that THOUSANDS of sql statements called with a JDBC thin
> driver were not using bind variables and were highly repetitive.
> Such as
> delete from tablex where id = 5
> delete from tablex where id = 6
> ...hundreds more
> etc.,
> Sent back a request to the developers that they rework things to use
> bind variables (which I got the impression was a little involved..
> anyone care to comment on that?).. so that we could keep a nice and
> tidy shared pool.
>

its trivial AND its crucial. Its vital. You won't go anywhere without doing it.

> But.. it got me thinking about parsing in general. If someone is not
> using a bind variable, it is a hard parse every time a statement rolls
> through, is that right?
>

oh yeah.

> If someone is using a bind variable, then it is a hard parse the first
> time the statemnet is parsed, and a soft parse each time thereafter as
> long as the statement remains in the pool.. is that right?
>

correct.

You'll find that right now without bind variables -- probably 90% of your time spent doing inserts, updates and deletes is spent parsing the query. Your system will run so much more smoothly when they fix that delete.

In fact, they should go 1 step further and parse the statement ONCE per program execution, keep the statement around and just reuse it over and over and over (prepared statements). Your goal is to get TKPROF to tell you that you

PARSED once
EXECUTED many many times....

> So.. what I was wondering, other than the other questions above, is
> whether a soft parse counts against by hit ratios (library cache.. ).
> I would think it doesn't, as I would imagine a statement using a bind
> variable would be a hit, or a pin hit, or would it not? I find it a
> little hard to swallow with so many tuned up databases reporting hit
> ratios of .99, .98 etc., that the statements are identical in form and
> data, so I'm guessing a soft parse with different variables inserted
> in them is a hit.. Am I right? Clearly..I'm not sure...
>

yes its a hit. thats the way shared sql works. do the work ONCE and then reuse it over and over and over...

Here is something to help you out. I use it to find those nasty "need bind variables" statement.

You will :

create table t1 as select * from v$sqlarea; alter table t1 add sql_text_wo_constants varchar2(1000);

create or replace function remove_constants( p_query in varchar2 ) return varchar2
as

    l_query long;
    l_char  varchar2(1);
    l_in_quotes boolean default FALSE;

begin

    for i in 1 .. length( p_query )
    loop

        l_char := substr(p_query,i,1);

        if ( l_char = '''' and l_in_quotes )
        then
            l_in_quotes := FALSE;
        elsif ( l_char = '''' and NOT l_in_quotes )
        then
            l_in_quotes := TRUE;
            l_query := l_query || '''#';
        end if;

        if ( NOT l_in_quotes ) then
            l_query := l_query || l_char;
        end if;

    end loop;

    l_query := translate( l_query, '0123456789', '@@@@@@@@@@' );

    for i in 0 .. 8 loop

        l_query := replace( l_query, lpad('@',10-i,'@'), '@' );
        l_query := replace( l_query, lpad(' ',10-i,' '), ' ' );

    end loop;
    return upper(l_query);
end;
/

update t1 set sql_text_wo_constants = remove_constants(sql_text);

select sql_text_wo_constants, count(*)
  from t1
 group by sql_text_wo_constants
having count(*) > 100
 order by 2
/

---

We are starting with 100 but as they fix them over time -- you would lower the 100 in the last query to 10, then to 1. They should be using bind variables *everywhere*.

> Thanks for any help,
>
> - Dc.
>
>

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Sat Jun 10 2000 - 00:00:00 CDT