rmoff

October 10, 2011

Instrumenting OBIEE – the final chapter

 


 

This article has been superseded by a newer version: Instrumenting OBIEE Database Connections For Improved Performance Diagnostics


 

(Previously on this blog: 1, 2, 3)

Summary

Instrument your code.
Stop guessing.
Make your DBA happy.
Make your life as a BI Admin easier.

The Problem

OBIEE will typically connect to the database using a generic application account.
(Hopefully, you’ll have isolated it to an account used only for this purpose – if you haven’t, you should.)

The problem is that you lose a lot of visibility of work both up and down the stack.

  • An OBIEE query is causing a problem on the database – how do you identify the originator?
  • You want to investigate the performance of an OBIEE query, but how do you identify which DB session it is?

We know SID 491 is causing a problem, but how do we identify the originating OBIEE user?

You could start piecing together Usage Tracking and NQQuery.log files, but it’s hardly convenient or instantaneous is it?

The solution

By taking advantage of native Oracle procedures, we can instrument our OBIEE code to pass through lots of valuable information:

Now we can see which OBIEE user fired the query resulting in SID 491, and not only the user, but the dashboard and request name they are running.

This works in both OBIEE 10g and 11g.

See my previous post here for further background, and discussion of the procedures used.

Implementing it – overview

In essence, we harness internal OBIEE session variables which hold the user ID, name, dashboard and report name. We put a set of database calls on the connection pool(s) associated with query requests.

We have to do a bit of trickery to work around two issues.

Firstly, the variables may not be set (you may not have saved your new request yet, or may be running it outside of a dashboard). To get around this, we create two dummy session variables with the same names, and populate them with dummy init blocks.

Secondly, there is a limitation to the number of characters that can be passed through, and so we manipulate the string if necessary to use the right-most characters.

Implementing it – Init Block and Dummy Variables

Summary:

Create two init block/session variable pairs:

Session Variables

Initialisation Blocks

Be sure to use a connection pool which isn’t used for queries.

Step-by-step

Load up your RPD. If you haven’t already, create a new connection pool that is just for these init blocks. It can be to any database – in the examples below it’s an Oracle one, but any that supports selecting from a dummy table like DUAL in Oracle.

Go to Manage -> Variables, click on Session -> Initialisation Blocks. Right click in the section to the right, and select New Initialization Block.

Call the init block Dummy_SawSrcPath_InitBlock, and then click on “Edit Data Source”

Set the Data Source Type to Database, and the init string to

select '[unsaved request]' from dual

Click on Browse to set the Connection Pool used. The connection pool should be one exclusively for init blocks (not the same you use for queries). If you try to use the same connection pool as for queries, you’ll most likely get an error when you logon.

Once you’ve set the connection pool, click on Test – you should get a result as shown:

If the Test doesn’t succeed then you need to fix the problem before you continue.

Assuming it’s worked, click OK to return to the Init Block creation window. We now want to define the dummy variable, so to do so click on “Edit Data Target”:

Click on New to create a new variable, and give it the name SAW_SRC_PATH. Make sure you get the name exactly correct, no typos.
Give it a default initializer, and then click OK.

Make sure your init block setup now looks like this:

Click on Test, and expect to get this returned:

Assuming it works, then click OK to save the new Init Block and Variable.

Repeat as above to create an init block/variable pair for SAW_DASHBOARD, looking like this:

When you’ve finished, you should have two init block/variables pairs set up like this:

Session Variables

Initialisation Blocks

Implementing it – connection pool

Add these three SQL statements to the “Execute before query” of “Connection Scripts” of each Connection Pool which is used for queries.
Do not add them to ones which are used for init blocks / authentication etc.

call dbms_application_info.set_client_info(client_info=>'VALUEOF(NQ_SESSION.DISPLAYNAME)')
call dbms_session.set_identifier('VALUEOF(NQ_SESSION.USER)')
call dbms_application_info.set_module(module_name=>'OBIEE: ' || case when length('VALUEOF(NQ_SESSION.SAW_DASHBOARD)')<40 then 'VALUEOF(NQ_SESSION.SAW_DASHBOARD)' else '...' || substr('VALUEOF(NQ_SESSION.SAW_DASHBOARD)',-37) end,action_name=>case when length('VALUEOF(NQ_SESSION.SAW_SRC_PATH)')<31 then 'VALUEOF(NQ_SESSION.SAW_SRC_PATH)' else '...' || substr('VALUEOF(NQ_SESSION.SAW_SRC_PATH)',-28) end);

This sets values as follows:

  • Client Identifier is the OBIEE login user id
  • Client Info is the user’s display name.
  • Module and Action are populated with the dashboard name (prefixed by “OBIEE”) and report names respectively, truncated to the left if necessary to fit into the field size.

NB CLIENT_IDENTIFIER and CLIENT_INFO have a larger capacity so could be used if you want to view more of the report/dashboard detail:

V$SESSION column      Max value length
MODULE                47
ACTION                31
CLIENT_INFO           63
CLIENT_IDENTIFIER     63

Reference:
DBMS_APPLICATION_INFO
DBMS_SESSION

Testing the changes

If you’re currently logged into Answers, logout and log back in. This is necessary for the dummy session variables to populate.

Run this sql*plus SQL script below to look at any existing OBIEE queries running on the database.


set linesize 170
col program for a30
col client_info for a20
col client_identifier for a18
col module for a47
col action for a31

SELECT SID,PROGRAM, CLIENT_IDENTIFIER, CLIENT_INFO, MODULE, ACTION FROM V$SESSION WHERE LOWER(PROGRAM) LIKE 'nqsserver%';

Now login to Answers, and run an existing report, or create a new one. When you re-run the SQL script you should see your session now listed:

Not a fan of sql*plus?

If for some strange reason you don’t love sql*plus, you can obviously use the above SQL in any other SQL client. Or, you can fire up Enterprise Manager and see the same set of information:

(run at a different time from the SQL above, so different report and dashboard names)

Warning

It’s occurred to me that by parsing in user-provided values to a string that’s executed on the database, there could be the potential for a breach through SQL Injection via a maliciously named report or dashboard.

I’ve not been able to find a report name which does cause trouble, but I have never tried exploiting SQL injection before.

It is another good reason to make sure that you’re using a DB account solely created for reporting queries from OBIEE, because then its privileges can be greatly restricted. This isn’t an excuse not to test for SQL Injection, but a reminder of why good practices such as granting of least privileges exist.

Troubleshooting

  • Make sure you don’tsuffix the database calls with semi-colons (statement terminators). If you do you’ll probably get an error like this:
    [nQSError: 17001] Oracle Error code: 911, message: ORA-00911: invalid character at OCI call OCIStmtExecute
    
  • If you’re having problems implementing this, or doing further playing around with it, you can see the exact SQL that’s executed on connection by bumping up LOGLEVEL and checking NQQuery.log.
  • Don’t use the same connection pool for the init blocks as you do for queries. If you try this, then the init blocks will fire and try to submit a command on the database which requires the variables that the very init blocks are trying to populate. Confused? OBIEE certainly will be too.
  • If someone creates a report or dashboard with single quote in the name, it causes problems. The error will be ambiguous too:

    State: HY000. Code: 10058. [NQODBC

February 2, 2011

Instrumenting OBIEE for tracing Oracle DB calls

Filed under: log, monitoring, obiee, oracle, performance, usagetracking — rmoff @ 15:04

Cary Millsap recently published a paper “Mastering Performance with Extended SQL Trace” describing how to use Oracle trace to assist with troubleshooting the performance of database queries.
As with all of Cary Millsap’s papers it is superbly written, presenting very detailed information in a clear and understandable way. (and yes I do have a DBA crush ;-))
It discusses how you can automate the tracing of specific sessions on the database, and requiring the application to be appropriately instrumented. This reminded me of a post that I made almost exactly 12 months ago here, where I explained how to pass through the username of the OBIEE user to the database. Initially I thought it would be useful simply for being able to pin a rogue query to an end-user, but reading Cary’s paper made me realise there is more potential to it.

Why would you use it in OBIEE?

Essentially, it enables you to precisely identify DB connections coming in from OBIEE. Since you can identify the connections, you can then trace them or collect additional statistics on them.

In Production, this would be useful for helping with troubleshooting. If a query is behaving badly, the responsible user can be easily identified, and through the login ID matched back to Usage Tracking data (you do collect Usage Tracking data, right?). Conversely, if a user is complaining (unlikely, I know 😉 ) of performance issues you can easily spot their queries running on the database and get a head start on identifying the problem.

As well as tracing, you can use these attributes to collect statistics (eg I/O wait time, db time, etc) for specific users or application areas. You use the DBMS_MONITOR CLIENT_ID_STAT_ENABLE procedure and then view the stats in V$CLIENT_STAT. Similar proc & V$ table exist for module-targeted statistics collecting.

Implementation

In essence, all you do is use the OBIEE Connection Scripts setting in the appropriate Connection Pool to call one or more of the PL/SQL packages. The values that you can set on the connection are as follows:

V$SESSION column Corresponding connection command to set Max value length
MODULE dbms_application_info.set_module(module_name=>'[…]’,action_name =>NULL) 47
ACTION dbms_application_info.set_module(module_name=>'[…]’,action_name=>'[…]’)
or
dbms_application_info.set_action(action_name=>'[…]’)
31
CLIENT_INFO dbms_application_info.set_client_info 63
CLIENT_IDENTIFIER dbms_session.set_identifier 63

(Ref: DBMS_APPLICATION_INFO, DBMS_SESSION)

For example, to pass through the OBIEE username and display name (NQ_SESSION.USER and NQ_SESSION.DISPLAYNAME respectively) you would use the following code:

Connection Pool settings

When you look at V$SESSION for the connection from OBIEE, it would show up something like this:

V$SESSION showing values from OBIEE connection

Instrument individual reports

What would be really cool would be if we could pass through the details of the report being executed.
A rather clunky way of doing this is by setting a custom session variable in the Logical SQL that gets sent to BI Server:

Then add a script to the connection pool to pass this value through in the database connection:

When run this then shows up on V$SESSION as:

col client_identifier for a20
col client_info for a20
SELECT PROGRAM, CLIENT_IDENTIFIER, CLIENT_INFO, MODULE, ACTION FROM V$SESSION WHERE LOWER(PROGRAM) LIKE 'nqsserver%';

You get an error if you’ve not set a value for the variable that is referenced in the connection script (in the above example, “REPORT”). So what you could do is create a dummy session variable called REPORT with a default value (eg “<unspecified report>”), which will then be used if a report doesn’t override it:

Dummy session variable


Dummy init block

Variables

It may be my misunderstanding of the subtleties of the flavours of OBIEE variables, but the behaviours seem inconsistent to me. For example, even though I am issuing a SET VARIABLE in my logical SQL, the value of the variable REPORT doesn’t change from its default (in this example ‘NONE’) when listed in the Session Manager or queried via Narrative view. It isn’t even shown if I don’t create it as a session variable in the RPD.

Unchanging session variable value

Session variables displayed in Answers

Despite this, the modified value of the variable is what gets passed through correctly in the DB connection.

SAW_SRC_PATH

This is a variable (along with QUERY_SRC_CD) that is passed automagically by Presentation Services to BI Server in the Logical SQL it executes:

Logical SQL, as seen in NQQuery.log

If this could be harnessed and manipulated (eg right-most 63 chars) then the report details of any report could be automatically included with the DB connection string. But – try as I have I can’t access the variable through VALUEOF. Anyone know how?

References

December 6, 2010

Adding OBIEE monitoring graphs into OAS

Filed under: hack, jmx, monitoring, OAS, obiee, unix — rmoff @ 21:30

Introduction

This is the third part of three detailed articles making up a mini-series about OBIEE monitoring. It demonstrates how to capture OBIEE performance information, and optionally graph it out and serve it through an auto-updating webpage.

This final article describes how to bolt on to OAS a simple web page hosting the graphs that you created in part 2, plotting data from OBIEE collected in part 1.

The webpage

This is just an old-school basic HTML page, with a meta-refresh tag (which note that Chrome doesn’t work with) and img tags:

<html>
<meta http-equiv="refresh" content="60">
<head>
<title>OBIEE Servers</title>
</head>
<body>
<img src="server01.png"></br>
<img src="server02.png"></br>
</body>
</html>

I shan’t patronise you nor embarrass myself with my rusty HTML skills any further – I’ll leave you how to build your dashboard how you want it.

OAS

This is hack! I am not an expert at Apache, so please don’t take my word for it that this is the best way to do it. It worked for me, but mightn’t for you.

If you’ve got OAS installed for your OBIEE installation, you can tweak it to serve up your new graphs too. If you’re using OC4J, IIS, or another webserver, then you’ll have to figure this bit out yourself.

Assuming that your OBIEE JMX graphs and HTML files are in /tmp/obieejmx, make sure that they’re readable by all:

chmod -R o+rx /tmp/obieejmx

Now go to your OAS folder, navigate to Apache/Apache/conf, and MAKE A BACKUP of httpd.conf

cd /your/path/to/OAS/here
cd Apache/Apache/conf
cp httpd.conf httpd.conf.bak

Open httpd.conf in vi (or if you’re not a real man then FTP the file to Windows and open it in Notepad 😉 )

  1. Search for
    <IfModule mod_alias.c>
  2. Add the following beneath it:
        Alias /obieejmx/ "/tmp/obieejmx/"
        <Directory "/obieejmx/">
            AllowOverride None
            Options None
            Order allow,deny
            Allow from all
        </Directory>
    
    • Here’s where you’d change the location of your graphs and HTML file if you needed to
  3. Save httpd.conf
  4. Restart Apache
    opmnctl restartproc ias-component=HTTP_Server

    or if that doesn’t work restart OAS

    opmnctl shutdown
    opmnctl startall

Assuming you normally access OBIEE through http://myserver:7777/analytics/ then you should now be able to go to http://myserver:7777/obieejmx/ and view the fruits of your hard-earned work.

What next

Obviously, the gnuplot/OAS hack is a bit crude, but for me was the quickest way to get “to market” the power of the OBIEE systems management metric collection by jmx that is possible for anyone with some basic *nix skills and some time to put it together.

The BI Management Pack for Enterprise Manager probably provides some if not all of this functionality but isn’t always available to use (and also has licensing implications).

Whether you collect metrics for day-to-day monitoring of OBIEE, capacity planning, or investigative work, I’ve hopefully demonstrated how easy it is to work with once you’ve got the basics mastered. And the beauty of doing it with shell scripts is that you can customise it to your heart’s content.

There’s a whole bunch of analysis that I’d like to do now, around things like our registered user count vs logged on users vs active users (to determine what actually is our concurrent user rate), as well as profiling BI Server load against database load.

It would also be fun to develop the HTML just a little bit further to create a mock drill-down on the graphs, although if you’re anything like me be aware of “just tweaking for a minute” turning into far too long given then throwaway nature of the solution.

Finally, bear in mind this is now dated technology – some of it may be on the junk heap for OBI11g.

Charting OBIEE performance data with gnuplot

Filed under: gnuplot, jmx, monitoring, obiee, unix, visualisation — rmoff @ 21:30

Introduction

This is the second part of three detailed articles making up a mini-series about OBIEE monitoring. It demonstrates how to capture OBIEE performance information, and optionally graph it out and serve it through an auto-updating webpage.

This article takes data that part one showed you how to collect into a tab-separated file that looks something like this:

2010-11-29-14:48:18     1       0       11      0       3       2       1       676     340     0       53      1       0       41      0       3       0
2010-11-29-14:49:18     1       0       11      0       3       2       1       676     0       0       0       1       0       0       0       3       0
2010-11-29-14:50:18     2       0       16      1       4       3       1       679     0       0       0       1       0       0       0       4       0
2010-11-29-14:51:18     2       2       19      1       4       3       1       679     32      0       53      1       0       58      0       4       0
2010-11-29-14:52:18     2       1       19      1       4       3       4       682     0       0       0       1       0       0       0       4       0
2010-11-29-14:53:18     2       1       19      1       4       3       4       682     0       0       0       1       0       0       0       4       0
2010-11-29-14:54:18     2       0       19      1       4       3       1       682     0       0       0       1       0       0       0       4       0

and plot it into something like looks like this:

gnuplot

Depending on what you’re wanting to use the data for, and your level of comfort with command-line tools, you may just want to take your datafile and plot it with something like Excel for ad-hoc analysis. However, for fully automated data capture and rendering, I’d recommend giving gnuplot a go. It’s not the simplest of tools and the documentation is so vast as to be overwhelming – but if you’ve the time to tweak it then you will find it very flexible.

gnuplot can be run interactively (which is useful for trialling settings) or as part of batch script, and can output to screen or file (eg. png). It assumes that your data is tab separated (which it should be if you’ve used my script).

The key bit to understand is the “plot xxx using yyy” statement. In this, xxx is your data file, whilst yyy is the columns to plot for x and y data respectively. So assuming that your x-axis is time and the first column in your datafile, and you want to plot the number of Active Sessions (which in my script is the fourth metric, so the fifth column – time being the first), you would have “using 1:5”.

plot "datafile.jmx" using 1:5 with lines

To plot to a PNG file, use a script like this:

gnuplot <<EOF
set xdata time
set timefmt "%Y-%m-%d-%H:%M:%S"
set format x "%d %b\n%H:%M"
set yrange[0:30]
set grid
set key box outside below
set terminal png font "arial, 7" size 410,200 enhanced truecolor
set output "graph01.png"
set title "Oracle BI PS Sessions\nActive Sessions"
plot "datafile.jmx" using 1:5 with lines

This line invokes gnuplot, and <<EOF tells it to pass the subsequent lines to gnuplot until end of file (or you put EOF literal in the file).

gnuplot <<EOF

This sets the x-axis as a time axis, gnuplot then scales it appropriately. The timefmt defines the format of the time data in the input file.

set xdata time
set timefmt "%Y-%m-%d-%H:%M:%S"

The format of the axis labels on the x-axis is defined thus:

set format x "%d %b\n%H:%M"

This hard-codes the y-axis range. Use “set autoscale” to revert to the default of gnuplot setting the range.

set yrange[0:30]

This puts a grid on the chart, and defines the title

set grid
set title "Oracle BI PS Sessions\nActive Sessions"

This tells gnuplot to write to a png file, using arial 7pt font, 410×200 pixels, and higher resolution. The “set output” command defines the actual filename.

set terminal png font "arial, 7" size 410,200 enhanced truecolor
set output "graph01.png"

If you omit the “set terminal png […]” line you’ll get an interactive graph from gnuplot, which can be useful for testing different settings.

Plotting multiple datasets

You can visualise multiple datasets alongside each other easily in gnuplot. You might want to do this for related metrics (eg Active Sessions vs Current Sessions), or a clustered OBIEE deployment:

The same metric on two servers in a load-balanced OBIEE deployment

Related metrics from a single server

To do this you append multiple statements to the “plot” command, separated by a comma:

plot "datafile_server01.jmx" using 1:4 with lines title "Server 01"\
, "datafile_server02.jmx" using 1:4 with lines title "Server 02"

(the \ is a line-continuation character)

The title for each dataset is defined as part of the statement as is seen above. This can be shown in a key which is enabled by default and can be moved to the bottom of the graph by using:

set key box outside below

Generating gnuplot scripts automagically

You may have realised by now that this is all quite fiddly to set up. Here are two scripts that will help generate gnuplot scripts. It’s based on the tcl script from part one and uses the first item in the array, ID, to determine the column number that a metric will appear in in the datafile.

This first one writes a series of gnuplot statements to plot each metric out on an individual graph, and assumes you have a two-server cluster so plots the datafiles from two servers on the same graph.

cat gnuplot_header
grep lappend obieejmx_server01.tcl|\
sed s/{//g|\
sed s/}//g|\
sed s/"lappend mbeanattrs "//g|\
sed s/"Oracle BI Management:Oracle BI=Performance,AppName="//g|\
sed s/"Oracle BI Server,"/"BI"/g|\
sed s/"name=Oracle BI General"/""/g|\
sed s/"type=Oracle BI Physical DB,name=Oracle Data Warehouse"/"DB"/g|\
sed s/"type=Oracle BI DB Connection Pool,name=Star_Oracle Data Warehouse_Oracle Data Warehouse Connection Pool"/"Conn Pool"/g|\
sed s/"Oracle BI Presentation Server,name=Oracle BI PS "/"BIPS"/g|\
sed s/"Connection"/"Conn"/g|\
sed s/"Current"/"Curr"/g|\
sed s/"Sessions"/"Sess"/g|\
awk 'FS="\t" {
        $1+=1
        print "set output \""$1".png\""
        print "set title \"" $2 "\\n" $3 "\""
        print "plot \"server01.jmx\" using 1:" $1 " with lines title \"server01\",\"server02.jmx\" using 1:" $1 " with lines title \"server02\""
}'

gnuplot_header is a file you need to create in the same directory, and can just be a copy of the example gnuplot statement above.

This script plots multiple metrics for a single server onto the same graph. You may want to break the statements up into separate graph plots to group together related metrics or ones with similar ranges – this script will at least give you the correct column and titles to get you started.

cat gnuplot_header
grep lappend obieejmx_server01.tcl|\
sed s/{//g|\
sed s/}//g|\
sed s/"lappend mbeanattrs "//g|\
sed s/"Oracle BI Management:Oracle BI=Performance,AppName="//g|\
sed s/"Oracle BI Server,"/"BI"/g|\
sed s/"name=Oracle BI General"/""/g|\
sed s/"type=Oracle BI Physical DB,name=Oracle Data Warehouse"/"DB"/g|\
sed s/"type=Oracle BI DB Connection Pool,name=Star_Oracle Data Warehouse_Oracle Data Warehouse Connection Pool"/"Conn Pool"/g|\
sed s/"Oracle BI Presentation Server,name=Oracle BI PS "/"BIPS"/g|\
sed s/"Connection"/"Conn"/g|\
sed s/"Current"/"Curr"/g|\
sed s/"Sessions"/"Sess"/g|\
awk 'BEGIN {
        FS="\t"
        print "set output \"server01.png\""
        print "set title \"server01\""
        printf "plot "
     }
     {
        $1+=1
        if (NR>1) {printf "\\\n,"}
        printf " \"server01.jmx\" using 1:" $1 " with lines title \"" $2 ":" $3 "\""
     }
     END {print ""}'

Both scripts use a series of sed statements to try and trim down the MBean names into something shorter for plotting on the graph key.

An example output would look something like this:

# One metric per graph, across two servers
gnuplot --persist <<EOF
set xdata time
set timefmt "%Y-%m-%d-%H:%M:%S"
set format x "%d %b\n%H:%M"
set grid
set key box outside below
set terminal png font "arial, 7" size 410,200 enhanced truecolor
set yrange [0:30]
set output "2.obiee.png"
set title "Oracle BI Presentation Server - Oracle BI PS Connection Pool\nCurrent Open Connections"
plot "server01.jmx" using 1:2 with lines title "server01","server02.jmx" using 1:2 with lines title "server02"
set output "3.obiee.png"
set title "Oracle BI Presentation Server - Oracle BI PS Query Cache\nCurrent Running Queries"
plot "server01.jmx" using 1:3 with lines title "server01","server02.jmx" using 1:3 with lines title "server02"
set output "5.obiee.png"
set title "Oracle BI Presentation Server - Oracle BI PS Sessions\nActive Sessions"
plot "server01.jmx" using 1:5 with lines title "server01","server02.jmx" using 1:5 with lines title "server02"
set output "6.obiee.png"
set title "Oracle BI Presentation Server - Oracle BI PS Sessions\nCurrent Sessions"
plot "server01.jmx" using 1:6 with lines title "server01","server02.jmx" using 1:6 with lines title "server02"
set output "7.obiee.png"
set title "Oracle BI Presentation Server - Oracle BI PS Sessions\nSessions Logged On"
plot "server01.jmx" using 1:7 with lines title "server01","server02.jmx" using 1:7 with lines title "server02"
set output "8.obiee.png"
# Single server, multiple metrics on one graph
gnuplot --persist <<EOF
set xdata time
set timefmt "%Y-%m-%d-%H:%M:%S"
set format x "%d %b\n%H:%M"
set grid
set key box outside right
set terminal png font "arial,9" size 1224,500 enhanced truecolor

set output "server01.png"
set title "server01"
set yrange[0:30]
plot  "server01.jmx" using 1:2 with lines title "BIPSConn Pool:Curr Open Conns"\
, "server01.jmx" using 1:3 with lines title "BIPSQuery Cache:Curr Running Queries"\
, "server01.jmx" using 1:5 with lines title "BIPSSess:Active Sess"\
, "server01.jmx" using 1:6 with lines title "BIPSSess:Curr Sess"\
, "server01.jmx" using 1:7 with lines title "BIPSSess:Sess Logged On"\
, "server01.jmx" using 1:8 with lines title "BIConn Pool:Curr Busy Conn Count"\
, "server01.jmx" using 1:13 with lines title "BI:Active Execute Requests"\
, "server01.jmx" using 1:14 with lines title "BI:Active Fetch Requests"\
, "server01.jmx" using 1:17 with lines title "BI:Total sessions"

Plotting subsets of data

You may well want to plot out subsets of the graph data, for example, the last 24 hours. This is simple to do, just run the jmx datafile through something like tail first:

# Create 24 hour extract of data
# Data is sampled every minute, so the last 24 hours will be 60*24 = 1440
tail -n 1440 server01.jmx > server01.24hr.jmx

Refreshing your graphs automagically

Assuming you’ve got your graph plotting script(s) in separate shell file(s), create a wrapper like this:

# Uncomment this for debug, to echo each statement as it's executed
# set -x
#
# This will loop for ever and ever
# (or until one becomes less than two)
while [ 1 -lt 2 ]
do
        # create any subset data files, using tail, as described above 
        ./create_6hr_logs.sh
        ./create_24hr_logs.sh
        # plot your graph(s)
        ./plot_by_server.sh
        ./plot_by_server_summary.sh
        ./plot_by_server_6hr.sh
        ./plot_by_server_6hr_summary.sh
        ./plot_6hr_graphs.sh
        ./plot_24hr_graphs.sh
        # Copy all output png files to a tmp folder
        # from where they'll be served up as a web page
        # (I told you it was a hack)
        cp *.png /tmp/obieejmx
        chmod g+r /tmp/obieejmx/*.png
        #
        # Write the current time, for debug purposes
        date
        # Wait for a minute
        sleep 60
done

And then run your wrapper in the same way as the metric collection, using nohup and &

nohup ./run_graphs_forever.sh &

What next?

So you’ve got your data … you’ve got your graphs … now why not serve them up through a convenient webpage that refreshes automatically? Click here for part three that shows you how to bolt this onto an existing OAS installation.

Collecting OBIEE systems management data with jmx

Filed under: jmx, mbeans, monitoring, obiee, systemsmanagement — rmoff @ 21:30

Introduction

This is the first part of three detailed articles making up a mini-series about OBIEE monitoring. It demonstrates how to capture OBIEE performance information, and optionally graph it out and serve it through an auto-updating webpage.

For some background on OBIEE’s Systems Management component, along with JMX and MBeans, see here and here. The following assumes you know your mbeans from coffee beans and jmx from a bmx.

The metric collection is built around the jmxsh tool. This is similar to jmxterm and both provide command-line access to jmx. Once it’s commandline, it’s scriptable 🙂

This was developed and works on both Windows (through cygwin) and HP-UX.

The Script

jmxsh uses the a scripting language based on tcl, and with a bit of trial-and-error I developed the following script, obieejmx.tcl. It connects to a remote BI server, authenticates to the JMX agent, and then periodically polls the jmx counters and writes the values to a tab-separated file. It runs until you cancel it.

# obieejmx.tcl
# OBIEE JMX collector
# https://rnm1978.wordpress.com
#

# Set connection details
set host my-remote-server
set port 9980
set user jmx-user-name
set pw my-jmx-password

# Define the counters (userdefined ID / MBean / Attribute list, tab separated)
set mbeanattrs [list]
lappend mbeanattrs {1   Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Presentation Server,name=Oracle BI PS Connection Pool      Current Open Connections}
lappend mbeanattrs {2   Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Presentation Server,name=Oracle BI PS Query Cache  Current Running Queries}
lappend mbeanattrs {3   Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Presentation Server,name=Oracle BI PS Query Cache  Current Cache Entries}
lappend mbeanattrs {4   Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Presentation Server,name=Oracle BI PS Sessions     Active Sessions}
lappend mbeanattrs {5   Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Presentation Server,name=Oracle BI PS Sessions     Current Sessions}
lappend mbeanattrs {6   Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Presentation Server,name=Oracle BI PS Sessions     Sessions Logged On}
lappend mbeanattrs {7   Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Server,type=Oracle BI DB Connection Pool,name=Star_Oracle Data Warehouse_Oracle Data Warehouse Connection Pool     Current Busy Connection Count}
lappend mbeanattrs {8   Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Server,type=Oracle BI DB Connection Pool,name=Star_Oracle Data Warehouse_Oracle Data Warehouse Connection Pool     Current Connection Count}
lappend mbeanattrs {9   Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Server,type=Oracle BI Physical DB,name=Oracle Data Warehouse       KiloBytes/sec}
lappend mbeanattrs {10  Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Server,type=Oracle BI Physical DB,name=Oracle Data Warehouse       Queries/sec}
lappend mbeanattrs {11  Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Server,type=Oracle BI Physical DB,name=Oracle Data Warehouse       Rows/sec}
lappend mbeanattrs {12  Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Server,name=Oracle BI General      Active Execute Requests}
lappend mbeanattrs {13  Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Server,name=Oracle BI General      Active Fetch Requests}
lappend mbeanattrs {14  Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Server,name=Oracle BI General      Avg. query elapsed time}
lappend mbeanattrs {15  Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Server,name=Oracle BI General      Queries/sec}
lappend mbeanattrs {16  Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Server,name=Oracle BI General      Total sessions}
lappend mbeanattrs {17  Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Server,name=Oracle BI General      Succeeded Logins Ratio as %}

# Write the header
puts -nonewline "Timestamp"
foreach {mbeanattr} $mbeanattrs {
        # Get the mbean & attribute
        set parts [split $mbeanattr "\t"]
        set mbean [lindex $parts 1]
        set attr [lindex $parts 2]
        puts -nonewline "\t$mbean $attr"
}
puts ""

# Connect to the host
jmx_connect -h $host -p $port -U $user -P $pw

# Get the values
while {0 == 0} {
        puts -nonewline "[clock format [clock seconds] -format %Y-%m-%d-%H:%M:%S]"
        foreach {mbeanattr} $mbeanattrs {
                # Get the mbean & attribute
                set parts [split $mbeanattr "\t"]
                set mbean [lindex $parts 1]
                set attr [lindex $parts 2]

                #Uncomment for debug:
                #puts "---"
                #puts "$mbean $attr"

                # Get the metric and write to output
                puts -nonewline "\t[ jmx_get -m $mbean $attr]"

        }
        # Flush the output buffer line
        puts ""
        # Sleep for 60 seconds
        after 60000
}

You may want to tweak the polling frequency, depending on the metrics that you’re collecting and the purpose of them. For building up a general picture of system usage (active sessions, etc), then every minute – or greater – should be sufficient. For other metrics which record rates per second (eg “New Sessions / sec”) then you may well want to sample more frequently.

You invoke this via jmxsh (download link) using the following syntax:

$java -jar jmxsh-R5.jar obieejmx.tcl

or if you’ve setup the (ambiguously-named) file jmxsh, you can use this:

$./jmxsh obieejmx.tcl

It will write the counter values to stdout, so capture it to file using

./jmxsh obieejmx.tcl >> results.jmx 

To run it continually as a background process, use nohup (so it doesn’t die when you logoff) and & (to run it in the background):

# Run collector indefinitely
nohup ./jmxsh obieejmx.tcl >> results.jmx &

The output you’ll get will look like this:

2010-11-29-14:41:17     5       0       38      1       12      9       0       614     0       0       0       0       0       0       0       3       0
2010-11-29-14:42:17     5       0       33      1       12      9       0       614     0       0       0       0       0       0       0       3       0
2010-11-29-14:43:17     5       0       33      1       12      9       0       614     0       0       0       0       0       0       0       3       0
2010-11-29-14:44:17     5       0       33      1       12      9       0       614     0       0       0       0       0       0       0       3       0

To stop the collector running, you’ll need to find the process

$ps -ef|jmxsh 
userid 14695     1  2  Nov 29  ?         5:12 /opt/java6/bin/IA64N/java -jar ./jmxsh-R5.jar obieejmx.tcl

and then kill it

kill 14695

Defining the counters

You’ll have noticed in my script that I define an array of counter names. You can get a list of all the counters in various ways including through Presentation Services (saw.dll?perfmon), OAS/OC4J, or JConsole. My personal preference is using Presentation Services (saw.dll?perfmon) as it gives the list nice and neatly and with an explanation of each counter.
Once you’ve decided which you want to collect, you need to use jmxsh again to get the correct format. Counters are defined as Attributes (eg Current Open Connections) within MBeans (eg Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Presentation Server,name=Oracle BI PS Connection Pool). Different jmx interfaces seem to label the MBean in a different format, for example:

jmxsh:  Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Presentation Server,name=Oracle BI PS Sessions
jmxterm:Oracle BI Management:AppName=Oracle BI Presentation Server,Oracle BI=Performance,name=Oracle BI PS Sessions

(spot the order in which “Oracle BI=” and “AppName=” are listed)
So for using jmxsh in your script, use jmxsh to get the exact MBean names otherwise you’ll spend a long time tearing your hair out wondering why it’s not working!
To get jmxsh to list the MBeans, you use it in the ‘browse’ mode. First off, run jmxsh and connect to your OBIEE server using the jmx_connect command


$ java -jar jmxsh-R5.jar
jmxsh v1.0, Tue Jan 22 16:23:12 GMT 2008


Type 'help' for help.  Give the option '-?' to any command
for usage help.


Starting up in shell mode.
% jmx_connect -h myserver -p 9980 -U myjmxuser
Password: ********
Connected to service:jmx:rmi:///jndi/rmi://myserver:9980/jmxrmi.

Hit enter at this point and it’ll switch to browse mode, and list out the MBean Domains.


%
Entering browse mode.
====================================================


 Available Domains:


       1. java.util.logging
       2. JMImplementation
       3. Oracle BI Management
       4. java.lang


  SERVER: service:jmx:rmi:///jndi/rmi://myserver:9980/jmxrmi

Select Oracle BI Management

====================================================
Select a domain: 3
====================================================

This lists all the MBeans within Oracle BI Management – there’s a lot!


 Available MBeans:


       1. Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Presentation Server,name=Oracle BI PS Chart Engine
       2. Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Server,type=Oracle BI DB Connection Pool,name=Star_Oracle Data Warehouse_Oracle Data Warehouse Writeback Connection Pool
       3. Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Presentation Server,type=Oracle BI PS Thread Pools,name=TaskScheduler
       4. Oracle BI Management:Oracle BI=Configuration,type=Presentation Server Configuration,name=Query
       5. Oracle BI Management:Oracle BI=Configuration,type=Presentation Server Configuration,name=AsyncLogon[ThreadPoolDefaults]
       6. Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Presentation Server,name=Oracle BI PS Query Cache
[...]
     134. Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Presentation Server,name=Oracle BI PS Sessions
[...]

You can filter by defining a glob filter by typing your search term at the “Select an mbean:” prompt. For example:

====================================================
Select an mbean: Performance
====================================================

 Available MBeans:

       1. Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Presentation Server,name=Oracle BI PS Chart Engine
       2. Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Server,type=Oracle BI DB Connection Pool,name=Star_Oracle Data Warehouse_Oracle Data Ware
house Writeback Connection Pool
       3. Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Presentation Server,type=Oracle BI PS Thread Pools,name=TaskScheduler
       4. Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Presentation Server,name=Oracle BI PS Query Cache
[...]
      53. Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Server,type=Oracle BI Generic Cache,name=Star_DrillDownQuery_Cache

  SERVER: service:jmx:rmi:///jndi/rmi://myserver:9980/jmxrmi
  DOMAIN: Oracle BI Management
  GLOB:   *Performance* (space to clear)

This shows just MBeans with Performance in the name. Alternatively use a wildcard within the glob:

Select an mbean: Performance*Cache
====================================================

 Available MBeans:

       1. Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Presentation Server,name=Oracle BI PS Query Cache
       2. Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Presentation Server,name=Oracle BI PS Catalog XML Cache
       3. Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Server,type=Oracle BI Generic Cache,name=Star_DrillDownInfo_Cache
       4. Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Presentation Server,name=Oracle BI PS Security Manager Account Cache
       5. Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Server,type=Oracle BI Generic Cache,name=Star_LDAP_Cache
       6. Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Presentation Server,name=Oracle BI PS Catalog Attribute Cache
       7. Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Presentation Server,name=Oracle BI PS Security Manager Account Memberships Cache
       8. Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Server,type=Oracle BI Generic Cache,name=Star_Plan_Cache
       9. Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Server,type=Oracle BI Generic Cache,name=Star_ColumnAggrInfo_Cache
      10. Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Server,type=Oracle BI Generic Cache,name=Star_RowWiseInit_Cache
      11. Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Presentation Server,name=Oracle BI PS DXE Cache
      12. Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Presentation Server,name=Oracle BI PS Cube Cache
      13. Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Server,name=Oracle BI Data Cache
      14. Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Presentation Server,name=Oracle BI PS XML Document Caches
      15. Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Server,type=Oracle BI Generic Cache,name=Star_DrillDownQuery_Cache

  SERVER: service:jmx:rmi:///jndi/rmi://myserver:9980/jmxrmi
  DOMAIN: Oracle BI Management
  GLOB:   *Performance.*Cache* (space to clear)

If you use globs, remember to clear them by typing space and then enter, otherwise when you list Attributes you won’t see any which don’t also match your filter.

To view the Attributes for an MBean, enter the MBean’s number:

====================================================
Select an mbean: 134
====================================================

 Attribute List:

       1. -r- Integer     Current Sessions
       2. -r- Integer     Maximum Sessions
       3. -r- Integer     Sessions Logged On
       4. -r- Integer     Maximum Logged On
       5. -r- Integer     Current Embryonic Sessions
       6. -r- Integer     Maximum Embryonic Sessions
       7. -r- Integer     Active Sessions
       8. -r- Integer     Maximum Active Sessions
       9. -r- Integer     Total Lifetime Sessions
      10. -r- Integer     New Sessions / sec
      11. -r- Integer     Total Failed Logons
      12. -r- Integer     Failed Logons/sec
      13. -r- Integer     Total Logons
      14. -r- Integer     New Logons/Sec


  SERVER: service:jmx:rmi:///jndi/rmi://myserver:9980/jmxrmi
  DOMAIN: Oracle BI Management
  MBEAN:  Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Presentation Server,name=Oracle BI PS Sessions

Once you’ve chosen your MBeans and Attributes, you can incorporate them into the obieejmx.tcl script by adding additional lappend lines. The format is:

lappend mbeanattrs {<ID><tab><mbean><tab><attribute>}

ID is just a number used later on in the process, it can be whatever you like. Make sure the three values are tab-separated.
An example line is:

lappend mbeanattrs {17  Oracle BI Management:Oracle BI=Performance,AppName=Oracle BI Presentation Server,name=Oracle BI PS Sessions      Current Embryonic Sessions}

If you get the error “Cannot convert result to a string” then check your MBean and Attribute names, normally this error means it can’t find what you’ve asked for. Also check that the array member definitions (lappend) are tab separated, not just space separated.

Where next?

Now you’ve got the data, do something with it! See charting OBIEE performance data with gnuplot.

OBIEE monitoring

Filed under: hack, jmx, mbeans, monitoring, obiee, systemsmanagement — rmoff @ 21:29

Those of you who read my blog regularly may have noticed I have a slight obsession with the OBIEE systems management capability which is exposed through JMX. Venkat has blogged this week about JMX in OBI11g, and it’s clearly a technology worth understanding properly.
I’ve recently been tinkering with how to make use of it for monitoring purposes, most recently using JConsole and discussed here. What follows is an extension of this idea, cobbled together with a bit of shell scripting, awk, gnuplot, and sticky backed plastic. It’s built on OBIEE 10g – for OBI11g it may differ (although I understand that Performance MBeans still exist).

Whether you collect metrics for day-to-day monitoring of OBIEE, capacity planning, or investigative work, it’s valuable data (in my humble opinion) that will help you understand the usage of the application by the users that you support.

To whet your appetite, here’s a sample of what you can produce, in realtime:

Performance metrics from a two-server OBIEE cluster

Performance data related to Sessions in BI and Presentation Services

Before you start this, I recommend reading how to secure your jmx agent if you’re working with production systems.

Overview

There are three parts to my monitoring application, and you can pretty much pick and mix as you want. Obviously without any data collected then graphing it will be pretty dull, but you may opt to collect the data and then work with it another way (Excel, OBIEE, etc).
I’ve broken the details down into three separate blog posts:

  1. Metric collection from a remote BI Server, using jmxsh
  2. Graph rendering of the collected data, using gnuplot
  3. Web page serving of the rendered graphs, bolted onto the OAS already in place for Presentation Services.

November 4, 2010

A Poor Man’s OBIEE EM/BI Management Pack

Filed under: jmx, monitoring, obiee, systemsmanagement — rmoff @ 15:32

Folk from Yorkshire are tight, so the stereotype goes. So here’s a cheap-ass way to monitor OBIEE 10g using nothing but the OBIEE built-in systemsmanagement component, the jmx agent, and jconsole (which is part of the standard Java distribution):

From here you can also export to CSV the various counters, and then store history, plot it out with gnuplot or Excel, etc.

If anyone’s interested let me know and I’ll document a bit more about how I did this, but it’s basically building on previous work I’ve documented around jmx and OBIEE.

July 29, 2009

OBIEE performance monitoring and alerting with jManage

Filed under: jManage, jmx, monitoring, obiee, performance, systemsmanagement — rmoff @ 10:47

OBIEE’s Systems Management component exposes configuration and performance data through Java MBeans. As discussed in other posts these can be be accessed through several different ways:

Since it’s a standard java technology being used we can in theory use anything that is designed for monitoring mbeans via jmx. Doing some Googling I discovered jManage.

jmanage13

JManage (homepage / SourceForge project page) describes itself thus:

jManage 2.0 is an open source application management platform, which provides a centralized console for managing application clusters and distributed-application environments

The latest version is a Release Candidate (RC) from 2007, and whilst the website’s forum isn’t entirely a ghost town it’s evidently not in active development.

Installing JManage on Windows

(This is a bare-bones installaion and what I did to get something up and running – it is probably not how it should be done)

  1. Download from SourceForge
  2. Unzip the downloaded archive somewhere
  3. From the command line run bin/startup.cmd
  4. Enter the default password 123456 when prompted
  5. Assuming you don’t get any errors go to http://localhost:9090/ where you should get a login page.
  6. Login at admin / 123456
  7. You should get a list of Managed Applications with one entry, jManage

    Default jManage homepage

    Default jManage homepage

Adding OBIEE to jManage

NB: If you have separate BI and PS servers you’ll need to monitor both, as the performance data is local

  1. This assumes that you installed Systems Management when you installed OBIEE. If in doubt have a look for [OracleBI home]/systemsmanagement
  2. In [OracleBI home]/systemsmanagement edit the runagent.cmd (or .sh if it’s a unix installation) to make the data accessible remotely as follows:

    On the java_cmd line replace
    -Dcom.sun.management.jmxremote
    with
    -Dcom.sun.management.jmxremote -Dcom.sun.management.jmxremote.port=9980 -Dcom.sun.management.jmxremote.authenticate=false -Dcom.sun.management.jmxremote.ssl=false

    (See here for more information on configuring jmx)

  3. Start the agent by running runagent.cmd (or .sh if it’s a unix installation). You should get this kind of output:
    [...]
    ========================================
    Analytics JMX Agent
    ========================================
    Started...
    29-Jul-2009 09:01:32 oracle.bi.analytics.management.monitoring.AppPerfMon refresh
    INFO: Oracle BI Presentation Server has started. Perfcounter data is collected.
    29-Jul-2009 09:01:32 oracle.bi.analytics.management.monitoring.AppPerfMon refresh
    INFO: Oracle BI Server has started. Perfcounter data is collected.
  4. If you want to be sure it’s working, use jconsole to connect and examine the MBeans exposed. See here for more information
  5. In jManage click on Add Application (if you can’t see this make sure you’re on the http://localhost:9090/config/managedApplications.do page)
  6. Choose JSR160 as Application Type
  7. Enter a description name for your server, and then for the URL this:

    service:jmx:rmi:///jndi/rmi://YourServer:9980/jmxrmi

    (nb 9980 is the port specified in the runagent.cmd script, so change this if need be)

  8. Leave Username, Password, java.naming.factory.initial and java.naming.provider.url as they default to on the formjmanage02
  9. Click Save
  10. If it’s worked then you should be back at the Managed Applications page with your server listed and hopefully a green icon next to it, indicating that jManage has successfully connectedjmanage03

NB: There seems to be a bug in adding an application to jManage which might catch you out. If you copy and paste the service URL you get a space appended to the end, which means your application gets added but jManage can’t connect to it (so lists it as down / red icon). If you examine the console you’ll see this:

29-Jul-2009 11:07:27 org.jmanage.core.management.ServerConnector getServerConnection
INFO: Failed to connect. error=Failed to retrieve RMIServer stub: javax.naming.NameNotFoun
dException: jmxrmi

If you edit the application to remove the trailing space from the URL you’ll see in the console that it doesn’t retry the URL, so I’m guessing doesn’t register the removal of the space.
The workaround is to delete the application and re-add it, being careful not to include the trailing space.

Exploring jManage & OBIEE

Current performance numbers

From the application page, enter click on Find Managed Objects (leaving the filter as *:*). You’ll get a list of MBeans which will be familiar if you’ve already explored MBeans through jconsole or oc4j.

Click on like Oracle BI=Performance,AppName=Oracle BI Presentation Server,name=Oracle BI PS Sessions and you’ll get a list of the current values of session metrics within Presentation Services

jmanage05

Graphing performance

Now click on Plot Graph (bottom right corner of the metrics list box) and tick a handful of metrics to graph. Click on Next. Enter a name for the graph and a polling interval, and click on Save.

You’ll get taken back to the main application page, where you should now have Graphs box, with your newly created graph listed underneath. Click on the graph name.

jmanage07

You’ll get a java applet firing up for the graphing. The graph applet has a context menu (right click) through which you can customise its appearance.

jmanage08

You can graph across metric groups (eg. Sessions and Cache) by selecting Add Graph from the main application page.

See here for jManage graph reference

NB: You might get a blank graph (with no legend for the metrics you selected). If this is the case then go back to the command window where you started jManage from and you’ll probably see an error:

29-Jul-2009 10:41:38 org.mortbay.jetty.servlet.ServletHandler handle
WARNING: Error for /app/fetchAttributeValues.do;jsessionid=2an2geo5rpuib
java.lang.AssertionError
        at org.jmanage.webui.actions.app.MBeanAttributeValuesAction.execute(MBeanAttribute
ValuesAction.java:76)

This highlights that jManage is not a finished product (nor does it claim to be), so bear this in mind when considering investing time in it.
It looks like in this instance the New Logons/Sec object was causing the failure, and it’s the only one with a value of zero so maybe that caused the error?? But another object, Completed Requests/sec, has a value of zero but graphs successfully.
Looking at the output of runagent.cmd shows:

java.lang.NullPointerException
        at oracle.bi.analytics.management.monitoring.SeblPerfObject.getAttribute(SeblPerfObject.java:371)
        at oracle.bi.analytics.management.monitoring.SeblPerfObject.getAttributes(SeblPerfObject.java:510)
        at oracle.bi.analytics.management.monitoring.SeblPerfObjectInstanceMBean.getAttributes(SeblPerfObjectInstanceMBean.java:148)
        at com.sun.jmx.mbeanserver.DynamicMetaDataImpl.getAttributes(DynamicMetaDataImpl.java:125)
        at com.sun.jmx.mbeanserver.MetaDataImpl.getAttributes(MetaDataImpl.java:189)
        at com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.getAttributes(DefaultMBeanServerInterceptor.java:696)
        at com.sun.jmx.mbeanserver.JmxMBeanServer.getAttributes(JmxMBeanServer.java:686)
        at javax.management.remote.rmi.RMIConnectionImpl.doOperation(RMIConnectionImpl.java:1389)
[...]

So to be fair to jManage it could be that OBIEE’s systems management isn’t honouring the exposed metric, but it would be nice if jManage ignored it and still showed the others, or flagged up the error.

Managed Objects

I think Managed Objects are a way of “bookmarking” specific MBeans for faster access from the dashboard for querying current values and defining graphs or alerts.
For example, the object detailing performance data about our data warehouse connection pool, Oracle BI=Performance,AppName=Oracle BI Server,type=Oracle BI DB Connection Pool,name=Star_Oracle Data Warehouse_Oracle Data Warehouse Connection Pool, is going to be of more interest than all the Configuration objects, plus a bunch of performance objects that we probably won’t examine too closely that often.
From the application page, click on Add Managed Object. Enter a display name next to the object(s) you’re interested in, and click on Add (at the bottom of the page).
jManage Management Console_1248864159305
Your new object is displayed on the application home page:
jmanage15
from where you can click through to see the current metric values, and define a graph or alerts.

Alerting

You can define alerts which will fire to email and/or the jManage home page:
jmanage09
To do this click on Add Alert from the application page, or Monitor when browsing the Management Objects
jmanage10

jmanage11

To use email alerts you need to update the Email properties section of jmanage.properties in the bin folder of jManage.

Application Clustering

Application Clustering is useful for defining groups of applications. They don’t have to actually be clustered. To set it up click on Add Application Cluster from http://localhost:9090/config/managedApplications.do

jmanage14
The status of an application filters up, so for example if the BI server is marked as down then the parent application cluster is also marked as down.

This Clustering feature is very useful for being able to see side-by-side metrics from multiple OBIEE nodes:
jmanage16

If graphing could be done at a cluster level that’d be even better 🙂 (per this feature request)

Configuration

As well as performance data, OBIEE Systems Management mbeans expose all the configuration options. You can edit this through jManage (just as you can through jconsole or oc4j), but bare in mine that no backups are taken of the config files so you should be cautious when using this.

Further info

jManage documentation: http://www.jmanage.org/wiki/index.php/Documentation
Errors are logged to the console and also logs/error.log in the jManage folder.

Further thoughts

The documentation details Dashboard development so it might be possible to build up a half-decent dashboard for assessing the overall OBIEE performance & status at a high level.

Threshold alerts on OBIEE mbeans could be logged and picked up by an enterprise systems management tool (although hopefully that tool could interface with jmx and the mbeans directly?)

The command line mode could be a way of extracting performance mbean values, although would a direct native java application be more appropriate for anything other than experimentation?

The graphing functionality in jManage – which would be one of the main reasons for using this instead of oc4j or jconsole for looking at the point-in-time numbers – is immature with frustrations like not being able add or remove metrics from an existing graph.

Bottom line

Whilst an unfinished product, jManage gives an interesting option to extending OBIEE performance monitoring and alerting. However, the BI Management Pack for Enterprise Manager is obviously the proper way to monitor OBIEE at the Enterprise level, and there’d have to be a really good reason to use jManage for monitoring OBIEE in anything other than an exploratory manner.

July 16, 2009

JConsole / JMX

Filed under: jmx, monitoring, obiee, performance, unix — rmoff @ 15:20

[edit] See this post too [/edit]
On an OBIEE server run

nohup obiee/systemsmanagement/runagent.sh &amp;

and then run jconsole (make sure you’ve set the DISPLAY first if you’re running it from UNIX).
NB: if you don’t have jconsole in your path you can search for it:

$whereis jconsole
jconsole: /opt/java1.5/bin/jconsole /opt/java6/bin/jconsole&lt;/span&gt;

You should find it under your java/bin directory

You should get this kind of connection dialog:

Click connect, and the console will launch. From here click on the MBeans tab, where you’ve got access to performance and configuration data

You can connect to the agent remotely too, but to do this you need to amend runagent.sh script:
On the java_cmd line replace
-Dcom.sun.management.jmxremote
with
-Dcom.sun.management.jmxremote -Dcom.sun.management.jmxremote.port=9980 -Dcom.sun.management.jmxremote.authenticate=false -Dcom.sun.management.jmxremote.ssl=false

See here for more information on configuring jmx

The BI Management Pack uses the agent for collecting its data. The data is also accessible at http://%5Bserver%5D:%5Bport%5D/analytics/saw.dll?perfmon (hat-tip: John Minkjan)

Create a free website or blog at WordPress.com.