Re: Who and What is using the shared pool.

Terry Ball wrote:
>
> Recently we have had occurances where our shared pool fills up and we
> have to flush the shared pool. The head of the product support group
> wants us to kill any process that is using "too much" of the shared
> pool. Is there any way we can identify which user/what query is using
> the shared pool by percentage of shared pool used? Steve?
>

You're talking about the shared memory pool, right?

The shared memory pool stores any query that you do. When you run a query, ORACLE checks the shared memory pool to see if that query is stored. If so, it doesn't bother parsing, which saves you some time. Eventually, the shared memory pool fills up and if I understand it correctly, is flushed automatically.

Problems occur when you have a lot of queries that are unique, and this most often happens when bind variables are not used, and where literal values are plugged into a query.

For example, you have a statement:

Insert into tablea (cola, colb) values ('A1','C1');

The next insert is:

Insert into tablea (cola, colb) values ('A2','C2');

followed by an insert for A3, C3, and so on.

If you are inserting 2000 rows into the table, this query will be stored 2000 times, and you will parse 2000 times. If you're inserting 10,000 rows, it will be - well, you get the picture. Your shared memory pool can fill up quickly.

The other way to do this would be to use bind variables in some way. You can write a stored procedure or package, or use one of the pre-compilers, or an anonymous block. A very crude example (this would probably be my least preferred option - I'd probably use a stored object):

declare

value1 varchar2(2) := '&1';
value2 varchar2(2) := '&2';

begin

   insert into tablea (cola, colb) values (value1, value2);

end;
/

The call would be:

@script.sql 'A1' 'C1'
@script.sql 'A2' 'C2'

and so on. The query would be stored once, and even if you inserted 10,000 rows, you'd still only parse once.

For more complicated stuff, you can use dynamic SQL.

At various sites, we've found that sometimes client GUI software such as GAIN <spit> Momentum (sorry) and Powerbuilder does not allow the use of bind variables. In that case, if it's at all possible, put the SQL in a package or some way to use bind variables.

It should be fairly easy to pinpoint the code that is doing two zillion unique SQL statements by examining the shared memory pool. I can't remember offhand where to look; anybody know? The usual culprit is a cursor loop of some sort where a lot of records are selected, and for each one, a value is being constructed or calculated, and the programmer has plugged the value directly into the statement. In my experience, more experienced programmers don't do this unless for a very good reason; they'd move the entire loop to a package or stored procedure if at all possible. Usually, when this happens, it's because the application programmer isn't aware of the problem or the options and is trying to do too much in the client-side code.

Your slave-driv ::: erase erase ::: boss is perhaps approaching this bass-ackwards. It would be better to try to find and fix the offending code than to try to perform alchemy by backing into finding out who's running the SQL statement a lot. IMGLO, of Received on Mon Aug 21 2000 - 11:20:15 CDT