Tag: instrumentation

nmon for linux (Fedora 12, x86_64)

At my current client site, I use AIX on IBM PowerPC kit. There is a neat little systems monitoring tool called “nmon” on AIX, which I quite like. I noticed recently that it’s available on Linux now, so I installed it on my machine, which runs Fedora 12. There are a number Some studies suggest that men who have made lifestyle improvements experience increased cialis sale rates of success with oral medications. Should I tell my mate I am using a supplement such as this? We are all for open communication and for some couples the joy of testing a modern sexual enhancer is a certain turn-on! For some women, achieving orgasm becomes difficult cialis lowest prices even after sufficient sexual arousal and be prepared for dependable sex. One just viagra 100mg sildenafil needs to place the soft pills over the tongue to get instantly melt. Take regularly these capsules to get rid of the health disorders resulted due to the malfunction of stomach order cheap viagra http://greyandgrey.com/stuart-s-muroff/ acids. of binaries prebuilt, but not for Fedora 12 on x86_64, so I downloaded the code, followed the instructions and compiled a binary which works fine.

It shows a number of useful metrics for CPU, memory, disk, network etc…if you use nmon, but didn’t know it was available for linux, well, now you do.

DBMS_APPLICATION_INFO and V$SESSION

Tom noted on his blog recently that DBMS_APPLICATION_INFO is something we should be using in our code to provide instrumentation and monitoring information – something I fully agree with and have been doing for some time now.

The post showed how, if we’ve called DBMS_APPLICATION_INFO in our code, we can retrieve the values of the MODULE/ACTION/PROGRAM_ID by querying V$SQL with a nice example to illustrate the point.

After reading it I recalled that some of this kind of information is available on V$SESSION from 10.2.0.2 (Thanks to Yas for identifying the specific version) so I created a simple supplementary example to illustrate this:

First I created a package – I’m using a package in order to demonstrate a secondary point which will become clearer as we progress…


create or replace package pk1 as
  procedure p3;
  procedure p4;
end pk1;
/

create or replace package body pk1 as
procedure p3 is
begin
  dbms_application_info.set_module( 'PK1 module', 'Startup' );
  dbms_application_info.set_action( 'Running P3' );
  for x in ( select * from dual look_for_me_1 ) loop
    null;
  end loop;
  for x in ( select * from dual look_for_me_2 ) loop
    null;
  end loop;
  dbms_lock.sleep(20);
end p3;

procedure p4 is
begin
  dbms_application_info.set_module( 'PK1 module', 'Startup' );
  dbms_application_info.set_action( 'Running P4' );
  for x in ( select * from dual look_for_me_2 ) loop
    null;
  end loop;
  dbms_lock.sleep(20);
end p4;

end pk1;
/

 

I’ve slightly changed the calls to DBMS_APPLICATION_INFO to show how the SET_ACTION works and also because if I don’t do it that way, the module name gets set by the first subprogram called in PK1 and should therefore not be specific to a subprogram.

Now lets get back the object name and ID – I’ve specifically excluded public synonyms in order to not cloud things:


select object_name
,      object_type
,      object_id
from   all_objects
where  object_name IN('PK1','DBMS_LOCK')
and    owner != 'PUBLIC' -- don't get public synonyms
/

 

This gives us something like:


OBJECT_NAME  OBJECT_TYPE   OBJECT_ID
------------ ------------- ---------
DBMS_LOCK    PACKAGE BODY  4368    
DBMS_LOCK    PACKAGE       4265    
PK1          PACKAGE BODY  76404   
PK1          PACKAGE       76403   

Now in another sql*plus session I can run the P4 procedure in package PK1 followed by the P3 procedure:


exec pk1.p4;
exec pk1.p3;

After both calls complete I switch to my main session and issue a slightly modified version of the query Tom wrote:


select sql_text
levitra on line sales No doubt, Zenegra can be one of your highest priorities. Like oestrogens, testosterone has recently been shown to increase nitric oxide, to dilate the arteries entering the penis, so as to allow them to supply enough blood needed to produce erection. pills viagra canada Isagenix Australia Cleanse for Life Cleanse for Life is a natural physiological need of men and women, it is lowest prices on viagra  also the world's strongest antioxidant. There is discount viagra levitra  physical, mental as well as sexual weakness. ,      action
,      module
,      program_id
,      program_line#
from   v$sql
where  sql_text like '% LOOK_FOR_ME_%' escape ''
/

SQL_TEXT                         ACTION     MODULE     PROGRAM_ID PROGRAM_LINE#
-------------------------------- ---------- ---------- ---------- -------------
SELECT * FROM DUAL LOOK_FOR_ME_1 Running P3 PK1 module 76404      7          
SELECT * FROM DUAL LOOK_FOR_ME_2 Running P4 PK1 module 76404      20        

Which gives us the two cursors that have been run and as Tom indicated would happen, in my example, the LOOK_FOR_ME_2 cursor is first issued in the call to P4 so the Action is “Running P4” even though the cursor is also issued by P3 subsequently.

Note that the PROGRAM_ID has the OBJECT_ID of the PK1 Package Body in V$SQL.

Now I can rerun the procedures again in the same order and because there is a small wait during the processing I can switch back to my main session to issue the following query against V$SESSION:


select plsql_entry_object_id
,      plsql_entry_subprogram_id
,      plsql_object_id
,      plsql_subprogram_id
,      module
,      action
from   v$session
where  sid = 150  -- this was the SID of my other session!
/

During the call to P4 I see this:


PLSQL_ENTRY_OBJECT_ID PLSQL_ENTRY_SUBPROGRAM_ID PLSQL_OBJECT_ID PLSQL_SUBPROGRAM_ID MODULE     ACTION  
--------------------- ------------------------- --------------- ------------------- ---------- ----------
76403                 2                         4265            8                   PK1 module Running P4

…and then whilst P3 is running I see this:


PLSQL_ENTRY_OBJECT_ID PLSQL_ENTRY_SUBPROGRAM_ID PLSQL_OBJECT_ID PLSQL_SUBPROGRAM_ID MODULE     ACTION  
--------------------- ------------------------- --------------- ------------------- ---------- ----------
76403                 1                         4265            8                   PK1 module Running P3

So, the PLSQL_ENTRY* columns are showing that we’re running the second subprogram of the PLSQL package with OBJECT_ID 76403 – which is the PK1 Package Spec – so slightly different to the example from Tom with V$SQL – but understandable information nevertheless.

From the other two columns, we can also see the currently executing subprogram – 8 in the package with OBJECT_ID 4265 – which is the DBMS_LOCK Package Spec. If we DESCribe DBMS_LOCK we would see that the 8th subprogram is SLEEP – which tallies up with what we’re executing.

We can also see these subprogram_id values in the ALL_PROCEDURES view:


select object_name
,      object_type
,      subprogram_id
,      procedure_name
from   all_procedures
where  object_name IN('PK1','DBMS_LOCK')
order by object_name
,        subprogram_id
/

OBJECT_NAME OBJECT_TYPE SUBPROGRAM_ID PROCEDURE_NAME
----------- ----------- ------------- ---------------
DBMS_LOCK   PACKAGE     0           
DBMS_LOCK   PACKAGE     1             ALLOCATE_UNIQUE
DBMS_LOCK   PACKAGE     2             REQUEST
DBMS_LOCK   PACKAGE     3             REQUEST
DBMS_LOCK   PACKAGE     4             CONVERT
DBMS_LOCK   PACKAGE     5             CONVERT
DBMS_LOCK   PACKAGE     6             RELEASE
DBMS_LOCK   PACKAGE     7             RELEASE
DBMS_LOCK   PACKAGE     8             SLEEP
PK1         PACKAGE     0           
PK1         PACKAGE     1             P3
PK1         PACKAGE     2             P4

So, V$SQL gives us useful information but we can also use V$SESSION in addition to get information by session including the subprogram within a package that we are executing.

As I said at the start, I’ve been using this for years and I’ve no idea why I don’t see it’s use more often. Hopefully the “Tom” effect will help to resolve this!