Services -- 3 : Monitoring Usage of Custom Services

In my previous blog post, I had demonstrated a few custom services created and started with DBMS_SERVICE.

Let's look at a couple of examples of monitoring usage of these services.

[oracle@ora12102 Desktop]$ sqlplus system/oracle@PDB1

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 20 22:51:08 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Thu Jun 16 2016 23:23:50 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> execute dbms_service.start_service('NEW_APP1');

PL/SQL procedure successfully completed.

SQL> execute dbms_service.start_service('FINANCE');

PL/SQL procedure successfully completed.

SQL> grant create table to hemant;

Grant succeeded.

SQL> grant select_Catalog_role to hemant;

Grant succeeded.

SQL> 

[oracle@ora12102 Desktop]$ sqlplus hemant/hemant@NEW_APP1

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 20 22:52:27 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Thu Jun 16 2016 23:28:01 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> create table obj_t1 tablespace hemant as select * from dba_objects;

Table created.

SQL> insert into obj_t1 select * from obj_t1;

90935 rows created.

SQL> 

[oracle@ora12102 Desktop]$ sqlplus hemant/hemant@FINANCE

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jun 20 22:53:54 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Mon Jun 20 2016 22:52:27 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> create table obj_t2_small tablespace hemant as select * from obj_T1 where rownum < 11;

Table created.

SQL> 

SQL> show user
USER is "SYSTEM"
SQL> select sid,serial#, to_char(logon_time,'DD-MON HH24:MI:SS'), service_name
  2  from v$session
  3  where username = 'HEMANT'
  4  order by logon_time
  5  /

       SID    SERIAL# TO_CHAR(LOGON_TIME,'DD-M
---------- ---------- ------------------------
SERVICE_NAME
----------------------------------------------------------------
        61      50587 20-JUN 22:52:27
NEW_APP1

        76      43919 20-JUN 22:53:54
FINANCE


SQL> 

Thus, we can see that V$SESSION tracks the SERVICE_NAME in use --- even though the USERNAME is the same in both sessions, the SERVICE_NAME is different.

SQL> col svc_name format a10
SQL> col stat_name format a25 trunc
SQL> select
  2  con_id, service_name SVC_NAME, stat_name, value
  3  from v$service_stats
  4  where service_name in ('NEW_APP1','FINANCE')
  5  and
  6  (stat_name like 'DB%' or stat_name like '%block%' or stat_name like 'redo%')
  7  order by 1,2,3
  8  /

    CON_ID SVC_NAME   STAT_NAME                      VALUE
---------- ---------- ------------------------- ----------
         3 FINANCE    DB CPU                        168973
         3 FINANCE    DB time                       771742
         3 FINANCE    db block changes                 653
         3 FINANCE    gc cr block receive time           0
         3 FINANCE    gc cr blocks received              0
         3 FINANCE    gc current block receive           0
         3 FINANCE    gc current blocks receive          0
         3 FINANCE    redo size                     100484

   CON_ID SVC_NAME   STAT_NAME                       VALUE
---------- ---------- ------------------------- ----------
         3 NEW_APP1   DB CPU                        869867
         3 NEW_APP1   DB time                     17415363
         3 NEW_APP1   db block changes               11101
         3 NEW_APP1   gc cr block receive time           0
         3 NEW_APP1   gc cr blocks received              0
         3 NEW_APP1   gc current block receive           0
         3 NEW_APP1   gc current blocks receive          0
         3 NEW_APP1   redo size                   25057520

16 rows selected.

SQL> 

So, even some statistics (unfortunately, not all -- the last time I checked in 11.2) are reported at the Service Level.  Thus, I can see that the users of NEW_APP1 consumed more CPU and DB Time and generated more changes and redo than users of FINANCE !  (Obviously, V$SERVICE_STATS reports statistics from the beginning of the instance so you should either user StatsPack (I haven't verified StatsPack reporting of statistics by individual service) or AWR (if you have the Diagnostic Pack licence) or your own collection scripts to report statistics for a specific window of time).
.
.
.