One user query used up to 740Mb shared pool. Surprising ? ! You can guess the impact.
Of course I am suspecting we are hitting a bug. The only things to note is that there about 260 values inside IN clause.
--Finding statement/s which use lots of shared pool memory:
SELECT substr(sql_text,1,40) "Stmt", count(*),
sum(sharable_mem) "Mem",
sum(users_opening) "Open",
sum(executions) "Exec"
FROM v$sql
GROUP BY substr(sql_text,1,40)
HAVING sum(sharable_mem) > 10485760 ;
--then join with v$session to find out the user id, program
set pagesize 24
set newpage 1 set linesize 125 column sql_text format a100 column user_name format a12 select u.sid, serial# substr(u.username,1,12) user_name, s.sql_text from v$sql s, v$session u where s.hash_value = u.sql_hash_value and sql_text like '%
--terminate the DB session
alter system kill session '
--although the status in v$session may be marked as KILLED
-- Monitor the shared pool used by the session may still keep growing , as the OS process is still alive
--join with v$process to get OS pid
--use kill -6 to kill the process gracefully, even it is a shared server process. This may take up to 15 minutes to finish, as process rollback in the background. The CPU could 100% for this process. Worth and safe to wait.
-- after this plan downtime to restart database as soon as possible , since we killed a SHARED SERVER. Otherwise may still face ora-00600 and ora-00745
