RE: Who and What is using the shared pool.

You aren't dreaming. You are thinking of cursor_sharing=force. Default is exact, which is the pre 8.1.6 way. I haven't used it myself, but I know of someone that recommended it to a customer that had an application not using bind variables, the performance was very bad and they were already in production. From what I understand, it shouldn't be used in place of bind variables, since the cbo may choose sub-optimal plans.

Mike Killough
TUSC
>From: Diana Duncan <Diana_at_filefrenzy.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: RE: Who and What is using the shared pool.
>Date: Mon, 21 Aug 2000 12:09:42 -0800
>
>One of the most compelling things I've read about 8i (maybe just 8.1.6, I
>can't remember which version it applied to) is that Oracle is now
>supposedly
>much smarter at identifying when to bind in the situation you describe. It
>actually will bind the values now.
>
>Disclaimer: I've read a LOT of stuff recently, so don't quote me - I may
>have been dreaming...
>
>-----Original Message-----
>Sent: Monday, August 21, 2000 1:33 PM
>To: Multiple recipients of list ORACLE-L
>
>
>
>
>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
>course.
>
>KLZ
>Certified ORACLE Geezer
>zklee_at_huskynet.com
>--
>Author: Kathryn L. Zimmerman
> INET: kzimmer1_at_csc.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
>--
>Author: Diana Duncan
> INET: Diana_at_fileFRENZY.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).