WSM performance statistics

Introduction

Oracle Web Services Manager is used to secure web services, and enable security of service-oriented architecture. In addition the WSM database schema contains performance statistics that is not easily accessible through user interface.
In the following some sql’s are documented to give an idea of what information can be pulled out of WSM database.

WSM request statistics

–connect and alter session
sqlplus orawsm/[password]@(description=(address_list=(address=(protocol=tcp)(host=[hostname])(port=[port])))(connect_data=(sid=[sid])))
alter session set nls_date_format=’YYYY/MM/DD hh24:mi:ss’;

  • –selecting number of requests per hour for a given service and operation
    select trunc(invoke_time,’hh’)”hour”, count(*)”requests”
    from messagelogs, log_objects
    where message_destination=’SID0003006′ –serviceid,change
    and invoke_time >= to_date(’2011/03/16 09:00:00′)
    and invoke_time < to_date('2011/03/18 09:00:00')
    and messagelogs.logid = log_objects.logid
    and UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(log_message,2000,1)) like '%operationName%'
    group by trunc(invoke_time,'hh');
  • –selecting number of requests per minute for a given service and operation
    select trunc(invoke_time,’mi’)”minute”, count(*)”requests”
    from messagelogs, log_objects
    where message_destination=’SID0003006′ –serviceid,change
    and invoke_time >= to_date(’2011/03/16 09:00:00′)
    and invoke_time < to_date('2011/03/18 09:00:00')
    and messagelogs.logid = log_objects.logid
    and UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(log_message,2000,1)) like '%operationName%'
    group by trunc(invoke_time,'mi');
  • –selecting request, and request size, for a given interval
    select invoke_time,
    dbms_lob.getlength(log_message),UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(log_message,2000,1))|| UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(log_message,2000,2001))
    from messagelogs, log_objects
    where message_destination=’SID0003006′ –serviceid, change
    and invoke_time >= to_date(’2011/03/16 00:00:00′)
    and invoke_time < to_date('2011/03/16 00:00:05')
    and messagelogs.logid = log_objects.logid
    and UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(log_message,2000,1)) like '%operationName%';

–if size of request is > 4000 characters (varchar2 limit) you will have to handle the blob as bytes in your favourite tool.
–java sample
byte[] requestAsBytes = requestAsBlob.getBytes(1, length);
String request = new String(requestAsBytes);

  • Trackback are closed
  • Comments (1)
    • Abhijat
    • April 6th, 2011

    Hi
    Thank you for this blog posting, it was very useful.
    Based on this posting I took the liberty to create a new request.

    The request will retrieve request-response pairs where response is containing fault:

    select context_id, log_type, invoke_time,dbms_lob.getlength(log_message) as lengt,UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(log_message,2000,1))|| UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(log_message,2000,2001))
    from messagelogs, log_objects
    where messagelogs.logid = log_objects.logid
    and context_id in (
    select context_id
    from messagelogs, log_objects
    where message_source=’SID0003009′
    and invoke_time >= to_date(’2011/04/04 15:00:00′)
    and invoke_time < to_date('2011/04/06 15:00:00')
    and messagelogs.logid = log_objects.logid
    and log_type like '%Response%'
    and UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(log_message,2000,1)) like '%Fault%'
    )
    order by invoke_time asc,context_id asc;

Comment are closed.