As an Oracle database administrator, managing sessions is crucial for maintaining performance and availability. This script provides a comprehensive solution for monitoring and cleaning up idle and blocking sessions.
The script identifies blocking sessions exceeding a specified threshold (default: 60 minutes), kills them, and sends notification emails. It also identifies idle sessions exceeding a specified threshold (default: 60 minutes), kills them, and sends notification emails. Key components include session identification using V$SESSION , V$PROCESS , and V$TRANSACTION , threshold settings, notification email functionality using TRACK.SEND_EMAIL , and error handling.
To implement this script, you'll need to declare variables for threshold settings (minutes), notification lists, and other necessary variables. The script then monitors blocking sessions using a FOR loop, killing each blocking session and sending notifications. A similar loop monitors idle sessions.
DECLARE
-- Threshold settings (minutes)
in_blocker_threshold_minutes NUMBER := 60;
in_idle_threshold_minutes NUMBER := 60;
-- Notification list
in_notification_list VARCHAR2(100) := 'your_email@example.com';
-- Other variables
v_Body CLOB;
any_blockers_killed NUMBER := 0;
any_idlers_killed NUMBER := 0;
BEGIN
-- Monitor blocking sessions
FOR bses IN (
SELECT s.sid, s.serial#, p.spid, s.username, s.program, machine, osuser,
logon_time, last_call_et,
NVL(sbc.ses_blocking_cnt, 0) ses_blocking_cnt,
NVL2(t.used_ublk, TO_CHAR(used_ublk), 'none') used_ublk, sa.sql_text last_command
FROM v$session s, v$process p, v$transaction t, v$sqlarea sa,
(SELECT blocking_session, COUNT(*) ses_blocking_cnt FROM v$session
WHERE blocking_session IS NOT NULL GROUP BY blocking_session) sbc
WHERE last_call_et > in_blocker_threshold_minutes * 60
AND s.username IS NOT NULL
AND s.type <> 'BACKGROUND'
AND s.blocking_session IS NULL
) LOOP
-- Kill blocking session and send notification
BEGIN
EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || bses.sid || ',' || bses.serial# || ''' IMMEDIATE';
any_blockers_killed := 1;
EXCEPTION
WHEN MARKED_FOR_KILL THEN
DBMS_OUTPUT.PUT_LINE(bses.sid || ',' || bses.serial# || ' marked for kill.');
END;
END LOOP;
-- Monitor idle sessions
FOR ises IN (
SELECT s.sid, s.serial#, p.spid, s.username, s.program, machine, osuser,
logon_time, last_call_et,
NVL(sbc.ses_blocking_cnt, 0) ses_blocking_cnt,
NVL2(t.used_ublk, TO_CHAR(used_ublk), 'none') used_ublk, sa.sql_text last_command
FROM v$session s, v$process p, v$transaction t, v$sqlarea sa,
(SELECT blocking_session, COUNT(*) ses_blocking_cnt FROM v$session
WHERE blocking_session IS NOT NULL GROUP BY blocking_session) sbc
WHERE last_call_et > in_idle_threshold_minutes * 60
AND s.username IS NOT NULL
AND s.type <> 'BACKGROUND'
) LOOP
-- Kill idle session and send notification
BEGIN
EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || ises.sid || ',' || ises.serial# || ''' IMMEDIATE';
any_idlers_killed := 1;
EXCEPTION
WHEN MARKED_FOR_KILL THEN
DBMS_OUTPUT.PUT_LINE(ises.sid || ',' || ises.serial# || ' marked for kill.');
END;
END LOOP;
-- Send notification emails
IF any_blockers_killed = 1 OR any_idlers_killed = 1 THEN
TRACK.SEND_EMAIL('oracle@your_host', in_notification_list, 'Killed sessions on your_instance', '<pre>' || v_Body || '</pre>');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error checking idle and blocking sessions in your_instance');
TRACK.SEND_EMAIL('oracle@your_host', in_notification_list, 'Error checking idle and blocking sessions in your_instance', '<pre>' || SQLERRM || '</pre>');
RAISE;
END;
/
To maximize the effectiveness of this script, consider the following best practices:
- Schedule the script to run regularly (e.g., every 30 minutes).
- Adjust threshold settings according to your database requirements.
- Monitor notification emails for killed sessions.
Hope this helps.
No comments:
Post a Comment