Re: Forcing no changes to SQLShared Area?

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 Received on Tue Apr 23 2002 - 14:03:13 CDT