Is that database affected with "Bind Peeking"!!!

Complaint:
The procedure that was written couple of years ago was running fine and smooth until we upgraded to 10g (Rel#1).
Even, in 10g it was fine for the first few weeks and then slowly started seeing lot of slowness in the system.
The stats found out this particular sql as the main culprit.
Amazingly 3GB TEMP tablespace in 8i is not enough in 10G. The TEMP tablespace grows even upto 24G (well thank god thats the max limit they set) and then fails.
Whats going on...
Din't think about this so called feature "bind peeking" from 9i but started looking into the culprit sql.
The SQL is not that complex. It has group by and one sub query. Thats all. And yeah, this SQL is the cursor though. Since its a cursor, there is a variable assignment for a where clause to filter out the records and that variable is of "date" type and the column compares this variable also is of "date" type in the table. So, everything is good there and I don't have to go for converting my variable to to_date etc.,
Well, so, why is that this particular SQL taking too much of TEMP tablespace!!!
Ran the query alone by passing the actual value instead of the variable in where clause to see how many records I get. Interestingly I get only couple of hundred rows.
That made me to think of the variable being binded in the cursor at the runtime.
So, this time my test was little different. Made a pl/sql block with the cursor and passed the variable value at run time (basically pulled out the portion of the code from the procedure) and the query hangs while being busy in increasing the temp space.
Removed the variable in the where clause and changed to get the value directly from the table instead of assigning that at the run time. Boom... query comes back in seconds...
So, my guess was right. Its the bind variable which was causing the issue and some how oracle keep increasing the temp tablespace.
Solution:
Basically the main issue here was to do with the so called feature "bind peeking". In this example while oracle was trying to get the bind variable value from the list when it was issued at the very first time after the database was started and never looked into the current value set. So, the data set returning was huge...
Well, time to disable this feature "bind peeking".
Here is the way to disable it:
There is an Oracle Hidden parameter which allows to disable it and that parameter is
"_optim_peek_user_binds" by default it is enabled.
alter system set "_optim_peek_user_binds" = false scope=both; --> system wide immediate effect and also modifies the spfile
alter session set "_optim_peek_user_binds" = false --> only for the session so that you can test your code.

Good luck with "bind peeking".

Killing a Session in RAC database

I wonder why oracle not thought about it and implemented a mechanism to kill a session in RAC database. Killing a session in oracle database is easy with "alter system kill session". This works gr8 in normal databases.

I had a situation where I had to kill a database session in RAC environment and I was keep getting error stating that the specified sid and session could not be found.

That's when I realized that the instance that I connected to kill a session is different than the session instance.

Hmm...

So, now I need to find the instance where the session exists and then make a connection to that instance and then issue a command to kill that session... what a pain...

Its ok as long as you need to kill session once in a while but not often (don’t ask me why do you need to kill sessions so often as all the work environments are not same).

So, wrote a small package to accept sid, session and instance# and it will take care of killing that session irrespective of instance you connected to it.

Sweet, now I don’t have to wait for Oracle to come with a mechanism anymore!!!

Drop me a note if you are in need of the code and I shall send it you.