Oracle – for when it was like that when you got there

Main menu

Post navigation

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) :-

Users need to be able to terminate their own sessions WITHOUT being granted the ALTER SYSTEM privilege

Users will only be allowed to kill their own sessions, not anyone elses

Users won’t be able to kill the current session as that will obviously be the result of a user error

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.

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.