Sunday, November 23, 2008

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.

6 comments:

  1. there is also something like dbms_ddl.execute_parallel but since I have no access to the 11gr2 software I am not sure how it works

    ReplyDelete
  2. Time to upgrade to 11g ;) PLSQL_OBJECT_ID, PLSQL_SUBPROGRAM_ID in v$session would help.

    ReplyDelete
  3. Hi Laurent,

    Interesting. 11gR2's editions will probably alleviate this problem. I'm curious ...

    Regards,
    Rob.

    ReplyDelete
  4. Hi Gints,

    Thanks, I hadn't noticed these columns yet. Also plsql_entry_object_id and plsql_entry_subprogram_id are nice. Although these four columns only give the top and bottom packages of the stack being executed. All packages in between will go off the radar. The view v_executing_packages will show those, so it is still worth having.

    Regards,
    Rob.

    ReplyDelete
  5. Yes this solution of course is more comprehensive as opposed to that out of the box one :)

    ReplyDelete
  6. But your suggestion is not a solution. That was the point I was unsuccessfully trying to make.

    You can try this test on 11g, to see why these columns won't always help you:

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

    Package is aangemaakt.

    rwijk@ORA11GR1> create package body pck3
    2 as
    3 procedure test
    4 is
    5 begin
    6 dbms_lock.sleep(60); -- a minute
    7 dbms_output.put_line('pck3');
    8 end;
    9 end pck3;
    10 /

    Package-body is aangemaakt.

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

    Package is aangemaakt.

    rwijk@ORA11GR1> create package body pck2
    2 as
    3 procedure test
    4 is
    5 begin
    6 pck3.test;
    7 end;
    8 end pck2;
    9 /

    Package-body is aangemaakt.

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

    Package is aangemaakt.

    rwijk@ORA11GR1> create package body pck1
    2 as
    3 procedure test
    4 is
    5 begin
    6 pck2.test;
    7 end;
    8 end pck1;
    9 /

    Package-body is aangemaakt.

    rwijk@ORA11GR1> remark When the procedure below is running,
    rwijk@ORA11GR1> remark try to install a new version of pck2:
    rwijk@ORA11GR1> remark create or replace package body pck2
    rwijk@ORA11GR1> remark as
    rwijk@ORA11GR1> remark procedure test
    rwijk@ORA11GR1> remark is
    rwijk@ORA11GR1> remark begin
    rwijk@ORA11GR1> remark pck3.test;
    rwijk@ORA11GR1> remark dbms_output.put_line('version 2');
    rwijk@ORA11GR1> remark end;
    rwijk@ORA11GR1> remark end pck2;
    rwijk@ORA11GR1> remark /
    rwijk@ORA11GR1> remark
    rwijk@ORA11GR1> remark And in another session run:
    rwijk@ORA11GR1> remark select o1.object_name
    rwijk@ORA11GR1> remark , o2.object_name
    rwijk@ORA11GR1> remark from v$session s
    rwijk@ORA11GR1> remark , dba_objects o1
    rwijk@ORA11GR1> remark , dba_objects o2
    rwijk@ORA11GR1> remark where s.plsql_entry_object_id = o1.object_id (+)
    rwijk@ORA11GR1> remark and s.plsql_object_id = o2.object_id (+)
    rwijk@ORA11GR1> remark and s.username = 'RWIJK'
    rwijk@ORA11GR1> remark /
    rwijk@ORA11GR1> exec pck1.test

    Regards,
    Rob.

    ReplyDelete