Re: Forcing no changes to SQLShared Area?

In article <9f9e62ec.0204231103.6dd47b24_at_posting.google.com>, mdaskalo_at_tlogica.com says...
>
>Thomas Kyte <tkyte_at_oracle.com> wrote in message
>news:<a9a2870uqp_at_drn.newsguy.com>...
>> 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.
>
>
>
>
>Hi Tom,
>do you know about any problems with
>cursor_sharing=similar or something like that in Oracle 9i ?
>
>How does it work? I can't imagine how it's actualy done.
>The docs says it consults the statistics for a column, but changes a
>value to a bind variable. To what degree statistics are consulted?
>Histograms? ...
>
>Thanks a lot for beeing with us.
>
>Regards,
>Mihail Daskalov

Haven't played with it too much -- but it's a "weaker" auto binder.

Whereas cursor sharing = force binds ALL, this binds when it thinks it won't affect the plan to much from run to run.

Need more time to see how it plays out in the real world. The compliants I've seen is "hey, its not binding" -- but that was the entire purpose, only to bind it appropriate. So people fall back to =force because =similar doesn't bind all...

--
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