executing_packages.sql

In our in-house application we are developing new features and fixing bugs with approximately 40 developers in total. Sometimes the installation of a new version of a database package "hangs" and eventually times out with a ORA-04021: timeout occurred while waiting to lock object". This is caused by another session that is currently executing the same package. When there's no time pressure, the developer just postpones the installation to the end of the day, or early next morning. But every now and then, a high priority bugfix or a project nearing its deadline cannot wait this long. The developer calls up the DBA and asks to bounce the developer database. The DBA sends an e-mail to warn everybody and kills all developers' sessions in the process of course. And some five or ten minutes later the DBA sends an e-mail to inform that everything is back to normal. This ritual is causing not only annoyance to 40 developers and a DBA, it also costs money: 41 times 10 minutes times X euros/hour.

Two or three years ago, I was wondering whether it is visible in the data dictionary who is executing the package that needed a new version. I stumbled upon this priceless script by Steve Adams, called executing_packages.sql. It lists the sessions that are currently executing stored code. He had found out that an object that is currently executing, has the sys.x$kglob.kglhdpmd column set to 2. See for example this question and answer. From then on, whenever I received an e-mail announcing the bounce of the developer database, I quickly turned to the developer experiencing the problem asking him the name of the package. I ran executing_packages.sql which revealed who is executing this package. Next, I called the DBA to stop the bounce and just kill one session instead. And I could hand over the sid/serial# of the session to be killed. Or even better, we just asked the responsible developer to end his session manually if possible.

But then we did an upgrade to 10.2.0.4. The script was originally developed for 8.0 and 8.1 according to Steve Adams' site, and I noticed more than once that it worked perfectly in 9.2.0.7. On 10.2.0.4 however, there are two problems:

1. I used a special DBA-account to be able to execute the script. The same upgraded account is now unable to see the sys.x$ tables.


2. Even when logged in as SYS, the sys.x_$ tables (note the underscore) are not there anymore.

I was surprised to see very little information about this when googling. I had imagined that such a useful script would be used anywhere and that several people would have bumped into the same problems by now. So this gives me the opportunity to find these things out myself at home.

It would be nice to address the first problem by defining a view under the SYS-schema and giving access to the view to all developers, and not just the DBA-account. The only thing that might worry the DBA's, is that the view might be used in the application. This can be addressed by granting access to the view to a role that every developer has. You cannot base new database objects on objects that have not been granted directly to you, so this will appease the DBA's.

The second problem was easy. Just remove the underscores in the table names and the query works: sys.x$kglob, sys.x$kglpn and sys.x$ksuse are still there in 10 and 11. I just want to extend the session information a little more. The existing script only shows the sid and serial#. In the new query, I added the username, program, module, action and client_info as well, as this additional information will help me in my conversation with the developer who has to end his session. I could have used the v$session view for this, but in the same style as the original script, I used sys.x$ksusex to get the "dbms_application_info" fields module, action and client_info. This results in this view:

sys@ORA11GR1> create view v_executing_packages
  2  as
  3  select
  4    decode(o.kglobtyp,
  5      7, 'PROCEDURE',
  6      8, 'FUNCTION',
  7      9, 'PACKAGE',
  8      12, 'TRIGGER',
  9      13, 'CLASS'
 10    )  "TYPE",
 11    o.kglnaown  "OWNER",
 12    o.kglnaobj  "NAME",
 13    s.indx  "SID",
 14    s.ksuseser  "SERIAL",
 15    s.ksuudnam "USERNAME",
 16    s.ksuseapp "PROGRAM",
 17    x.app "MODULE",
 18    x.act "ACTION",
 19    x.clinfo "CLIENT_INFO"
 20  from
 21    sys.x$kglob  o,
 22    sys.x$kglpn  p,
 23    sys.x$ksuse  s,
 24    sys.x$ksusex x
 25  where
 26    o.inst_id = userenv('Instance') and
 27    p.inst_id = userenv('Instance') and
 28    s.inst_id = userenv('Instance') and
 29    x.inst_id = userenv('Instance') and
 30    p.kglpnhdl = o.kglhdadr and
 31    s.addr = p.kglpnses and
 32    s.indx = x.sid and
 33    s.ksuseser = x.serial and
 34    o.kglhdpmd = 2 and
 35    o.kglobtyp in (7, 8, 9, 12, 13)
 36  order by 1,2,3
 37  /

View is aangemaakt.

Normally I won't include an order by clause in a view, but in this case I only want to do a "select * from sys.v_executing_packages", and not bother about the ordering there.

For now I'll just grant the select privilege directly to my account. As said, granting to a developer role is better in real life.

sys@ORA11GR1> grant select on v_executing_packages to rwijk
  2  /

Toekennen is geslaagd.

Below is a small test to see how this view can be used. In session 1 I'm creating a package that executes a long time:

rwijk@ORA11GR1> select sid
  2       , serial#
  3    from v$session
  4   where sid in (select sid from v$mystat)
  5  /

       SID    SERIAL#
---------- ----------
       136         35

1 rij is geselecteerd.

rwijk@ORA11GR1> create package mypck
  2  as
  3    procedure test;
  4  end mypck;
  5  /

Package is aangemaakt.

rwijk@ORA11GR1> create package body mypck
  2  as
  3    procedure test
  4    is
  5    begin
  6      dbms_lock.sleep(3600);  -- an hour
  7      dbms_output.put_line('version 1.0');
  8    end test;
  9  end mypck;
 10  /

Package-body is aangemaakt.

rwijk@ORA11GR1> exec mypck.test

And now session 1 is asleep for an hour.

In session 2 I'm trying to create a second version of the package, that will wait for session 1 to complete. This will eventually lead to a ORA-04021, after some 15 minutes:

rwijk@ORA11GR1> select sid
  2       , serial#
  3    from v$session
  4   where sid in (select sid from v$mystat)
  5  /

       SID    SERIAL#
---------- ----------
       139         37

1 rij is geselecteerd.

rwijk@ORA11GR1> create or replace package body mypck
  2  as
  3    procedure test
  4    as
  5    begin
  6      dbms_lock.sleep(3600);  -- an hour
  7      dbms_output.put_line('version 1.1');
  8    end test;
  9  end mypck;
 10  /
create or replace package body mypck
*
FOUT in regel 1:
.ORA-04021: Time-out tijdens wachten op vergrendeling van object .

Now I will use the view in a third session (or in session 2 after the time-out) to find out who is executing which package:

rwijk@ORA11GR1> select * from sys.v_executing_packages
  2  /

TYPE      OWNER NAME                                  SID     SERIAL
--------- ----- ------------------------------ ---------- ----------
USERNAME                       PROGRAM
------------------------------ ------------------------------------------------
MODULE
------------------------------------------------
ACTION
--------------------------------
CLIENT_INFO
----------------------------------------------------------------
PACKAGE   RWIJK MYPCK                                 136         35
RWIJK                          SQL*Plus
SQL*Plus



PACKAGE   SYS   DBMS_LOCK                             136         35
RWIJK                          SQL*Plus
SQL*Plus




2 rijen zijn geselecteerd.

And this points to the SQL*Plus session 1.