Re: Forcing no changes to SQLShared Area?

In article <uu1qfte0f.fsf_at_rcn.com>, Galen says...
>
>On 13 Apr 2002, tkyte_at_oracle.com wrote:
>
>> what is the goal or reasoning behind such a concept?
>>
>> Instead of giving us the "answer" -- I want to do such and such --
>> give us the question "How can I do ....."
>>
>> Perhaps you think you don't want the sql in the shared sql area (which
>> just isn't in the realm of possibility) but there may very well be a
>> way to accomplish your GOAL.
>
>An html front-end app allows users to choose columns and rows that they
>want to return (grid like structure). It is alot more complicated than
>that, but thats the jist. The java code creates dynamic sql to return
>what they need.
>
>Nobody on the database side had much of a hand in writing the SQL, but I
>do know that there was no thought of binding variables or reuse of the
>SQL. Every piece of SQL in the logs have hard-coded values in where
>clauses and different select clauses. So, when this particular
>programming code submits its SQL, I was hoping that we could set some
>session parameter that would never even consult the shared pool, and, as
>I've said, most importantly, not add the SQL that's being executed to
>the shared pool therefore pushing other SQL out.
>
>Sort of akin to setting a particular rollback segment within a
>transaction. IE, leave the shared sql area alone, we are almost assured
>the SQL will not be there.
>
>Hopefully, I will be able to take a long look at the code, so that we
>can actually reuse sql, but right now, its already written and the app
>is almost out the door.
>

Ok, try this instead -- not 100% assured to work (if you have my book see pages 441-448 for the caveats) perfectly but... it could help you tons:

You wanted:

alter session set shared_sql=off; (doesn't exist)

we give you:

alter session set cursor_sharing=force;

use the "auto binder". This will dramatically reduce the number of unique sql statements they create -- within limits you can deal with. It is a bandage, not a complete fix, but better then a "set shared_sql=off" setting even.

There are caveats with "auto binding" -- so you need to test this.

>--
>Galen deForest Boyer
>Sweet dreams and flying machines in pieces on the ground.

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp