Des Cartes Must Die – a Killer App for PL/SQL

Text books, manuals, experts and assorted gurus are fairly insistent on the point that it’s probably a good idea to keep your OLTP systems and Data Warehouses in separate instances ( ideally on separate servers).
Meanwhile, back in the real world, you’ve got a bit of a problem.
Not only do you have a Data Warehouse competing with your OLTP App for resources on the same instance, the DW users have access to the database via SQL and are extremely reluctant to give it up.
Your reasoned explanation as to why this is a bad idea and that you can’t guarantee performance of any application on the database as a result has fallen on deaf ears.
The application often grinds to a halt when one of the DW users generates a cartesian product, or just runs a humping great query at a peak time.

The process for killing such rogue sessions has been complicated somewhat by your company’s enthusiastic embracing of outsourcing. Indeed, getting this done involves the user raising a call with the help desk, who pass it to you. You have to raise another call to the DBA’s via a call-management system and hope that someone is up as it’s early/late/the middle of the night wherever they are.
Even if all runs smoothly, it still takes around half-an-hour to get the runaway session terminated and all the while, users are complaining about performance and that clammy feeling on the back of your neck is the laboured breathing of a boss who JUST WANTS IT FIXED.

Having gone through this particular loop several times it becomes apparent that (a) no-one is going to sign-off on the new hardware/Oracle license/time and effort to split out these warring applications and (b) the fact that you can’t guarantee performance for anything running on this setup is accepted without question… right up to the time things grind to a halt. All of which means that desperate measures are called for.

Yep, the users running those rogue queries are going to get the facility to kill their own sessions.

At this point, it’s probably worth outlining a couple of requirements ( if only to stop your DBA hyperventilating after reading that last sentence) :-

  1. Users need to be able to terminate their own sessions WITHOUT being granted the ALTER SYSTEM privilege
  2. Users will only be allowed to kill their own sessions, not anyone elses
  3. Users won’t be able to kill the current session as that will obviously be the result of a user error
  4. Users need to see enough information about their own current sessions to work out which one they want to kill.

Now Baldrick, the raw materials for this cunning plan are :-

  • A Global Temporary Table
  • A stored procedure to populate the GTT
  • A stored procedure to kill the session

Our target audience is proficient enough in SQL that you can point them at the DBMS_APPLICATION_INFO package and get them to add identifiers to their sessions using this package.

Now for our GTT :-

CREATE GLOBAL TEMPORARY TABLE tmp_user_sessions (
audsid NUMBER,
status VARCHAR2(8),
program VARCHAR2(64),
session_name VARCHAR2(48),
action VARCHAR2(32),
client_info VARCHAR2(64) )
ON COMMIT DELETE ROWS
/

Simple enough, create a Global Temporary Table where all the rows are cleared down at the end of a transaction.
Granting Select, Insert, and Delete on this table will enable a user to see their own rows in this table, but only their own.
The GTT consists of columns taken from V$SESSION. You may have noticed that these do not include the sid and serial# that you need to kill the session. Well, you can get this from V$SESSION by including AUDSID in the predicate, so we’ll keep things simple by allowing the user to work off one piece of information rather than two.
NOTE – the module column in V$SESSION has been changed to SESSION_NAME in this table so it’s obvious to users where the session name they set using DBMS_APPLICATION_INFO can be found.
Next step – give the users a mechanism to populate the table.

CREATE OR REPLACE PROCEDURE get_session_info AS
BEGIN
   --
   -- First cleardown the table without creating a 
   -- transaction boundary i.e. can't do a 
   -- TRUNCATE here
   --
   DELETE FROM tmp_user_sessions;
   --
   -- Now populate the table with details of the 
   -- current sessions
   --   
   INSERT INTO tmp_user_sessions
   SELECT audsid, status, program, module, 
      action, client_info
   FROM v$session
   WHERE username = USER;
END;
/

Nice and simple to call this, no parameters required, simply exec get_session_info or, if they’re TOAD ( or SQLDeveloper) users – begin get_session_info; end;

Now they should have some data to look at in the table.


SQL> exec get_session_info


PL/SQL procedure successfully completed.


SQL> select audsid, session_name
2 from tmp_user_sessions;


AUDSID SESSION_NAME
---------- --------------------------------
1771 SQL*Plus
1765 SQL Developer
1770 huge query

SQL>

Now all they need to do is pass the relevant audsid value to the killer procedure, which looks like this :-

CREATE OR REPLACE PROCEDURE kill_session( 
   p_audsid IN NUMBER) IS
   l_sid v$session.sid%TYPE;
   l_serial v$session.serial#%TYPE;
   l_uname v$session.username%TYPE;

   CURSOR c_target( cp_audsid NUMBER) IS
      SELECT sid, serial#, username
      FROM v$session
      WHERE audsid = cp_audsid;

   l_command VARCHAR2(200);
   e_current_session EXCEPTION;
   e_invalid_session EXCEPTION;
   e_session_owner EXCEPTION;

BEGIN
   --
   -- First, make sure that we're not trying to kill the 
   -- current session
   --
   IF p_audsid = USERENV('sessionid') THEN
      RAISE e_current_session;
   END IF;
   --
   -- Now get the session details
   --
   OPEN c_target( p_audsid);
   FETCH c_target INTO l_sid, l_serial, l_uname;
   IF c_target%NOTFOUND THEN
      CLOSE c_target;
      RAISE e_invalid_session;
   END IF;
   CLOSE c_target;
   --
   -- Check that the user actually owns the session 
   -- we're going to kill
   --
   IF l_uname != USER THEN
      RAISE e_session_owner;
   END IF;
   --
   -- If we're still here, then kill the session
   --
   l_command := 'ALTER SYSTEM KILL SESSION '
      ||''''||l_sid||', '||l_serial||''''||' IMMEDIATE';
   EXECUTE IMMEDIATE( l_command);
EXCEPTION
   WHEN e_current_session THEN
      RAISE_APPLICATION_ERROR(-20000, 
         'You cannot kill the current session.');
   WHEN e_invalid_session THEN
      RAISE_APPLICATION_ERROR(-20001, 
         'Could not find this session.');
   WHEN e_session_owner THEN
      RAISE_APPLICATION_ERROR(-20002, 
         'Cannot Kill a session you do not own.');
END;
/

NOTE – this procedure MUST be created under a schema which has ALTER SESSION granted to it directly – i.e. not via a role.

You can then grant execute on this procedure to the relevant users.

So, now the user can kill their own “huge query” by simply entering :-

SQL> exec kill_session(1770)

PL/SQL procedure successfully completed.

SQL>

The target session will then be terminated and the next time the user interacts with it they should get an ORA-03113 : end-of-file on communication channel.

Whilst this utility isn’t a panacea, it does make the time lag between recognising the approach of Mr Des Cartes, and his ultimate demise, somewhat shorter.