Re: Forcing no changes to SQLShared Area?

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.

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