Thursday, April 17, 2008

How to find the SQL use massive shared pool

Live case learned today.

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