The Triora Group

Have you ever been watching the output of TOP or say GLANCE and noted the high resource usage of a particular process? The other day a co-worker and I were watching just such an event. We run in an HP-UX environment, so we were using GLANCE to monitor resource consumption. We had recently implemented some new functionality and expected an increase in load on the system, but were not sure to what extent. So when we finally saw a process consuming substantial resources (CPU, IO and/or Memory) we wanted to dive in and get more pointed information. Now, for those of you that have ever attended Cary Millsap’s PD101 class, you know he is fond of saying (paraphrased?) “The fastest way to do anything is to not do it at all”. Well, I can’t not look at the resource hog so therefore I’m already in the hole. So, to not compound the issue I decided that I would show my co-worker how to use OAM (Oracle Application Manager) to find detailed information. But, how do I make the match between what we are seeing in TOP and / or GLANCE and OAM?

Let’s start by determining which connections we are going to cover for this article. There will be many different processes running on your servers, but for the sake of timeliness in this article, let’s focus on database sessions and forms sessions. Java sessions are important as well, but would require more time than I have here to adequately detail. However, you are in luck, as I’m going to be presenting at Collaborate 09 on Java and the Apps DBA so I will describe them there.

I like to put some detail into these articles, but I really don’t want to get bogged down with contextually un-important information; therefore, I am going to use the short form name of the database and forms session connection, but will not spend a lot of time detailing why they are named that way. The system we are using for this scenario is an 11.5.10.2 system where all tiers are shared on the same server, which is the HP-UX variant.

The first type of connection, a database session, is named oracle<dbsid> and carries with it a tag of (LOCAL=NO) when viewed through the ps –ef command. For example:

As you can see, we are in a system named “oaprod” so our process name is oracleoaprod. Now, let’s say we are monitoring our GLANCE window and we see a session that is consuming an inordinate amount of resources. Let’s see how we can determine what that session is doing.

*** Quick note: The session we are walking through may not necessarily be consuming a lot of resources. We chose this session randomly for walk through purposes only ***

Step 1: Observation of interesting session:

From this image you can see that PID# 23020 has jumped to the very top of our monitoring window. Since we believe this to be interesting, we need to determine how to tie this PID into something to look at in OAM. There are a couple of tables you’ll need to get to know to make this work: v$process and v$session. The following script ties these tables together and uses your input to return expanded information:

select substr(vs.audsid,1,1)||’.'||substr(vs.audsid,2)||’E7′ AUDSID,

nvl(substr(vs.action,5,instr(vs.action,’:',1,2)-5),’NO FORMS USER’) form_user_name,

vp.spid,

vs.sid,

vs.serial#,

vs.status,

vs.process,

vs.type,

vs.module,

vs.action,

vs.logon_time,

vs.blocking_session_status,

vs.event,

vs.state,

vp.pga_used_mem,

vp.pga_alloc_mem,

vp.pga_freeable_mem,

vp.pga_max_mem

from v$session vs,

v$process vp

where vs.paddr = vp.addr

– use vs.spid for database processes

and vp.spid = 23020

Note that we used the 20320 PID from the GLANCE session to narrow our information. My output is coming from TOAD so if you are using this script in SQL*PLUS your output will look different:

** break **

Both of these lines would be a single line in your query but I have to obey the laws of margins and formats… Also, those of you who are eagle-eyed may notice that there are a few columns that are contained in the query but are not depicted here. They were excluded here for the sake of brevity and easier display.

Okay, now that we have our query results, let’s put it to work. Launch Oracle E-Business Suite and login. Once logged in you’ll need to use the System Administrator responsibility, or whichever responsibility gets you into OAM. I generally navigate to the “Dashboard” function near the bottom

which gives us the following screen:

Note that the value for Database Sessions is underlined, which is the format for a drill down point in Oracle Apps. Clicking this link gives us:

Now, how did I get this info? Well, your screen, before you put in your search criteria will display a line for each of those 252 Database Sessions we saw in the previous image. My screen shows the results of the search I performed. But, where did I get this information, you ask? Well, let’s go back to the script output we had just a minute ago. Do you remember this information:

I simply COPIED the contents from the AUDSID field and used it to narrow down my search. Now, before moving on, I would like to note that the script provides additional detail including some of the PGA memory statistics that may have additional value to you. For instance, I can tell that this session is actually an ODBC session from a desktop (see where the module is msqry32.exe). I can also tell that this session does not have an associated “Forms User” as noted in the field “FORM_USER” (I’ll explain this one a little later). Okay, moving on…

Looking closer at the single row returned by or AUDSID search, you can again see where the value in the AUDSID field is underlined. This, again, is a drill point. But before moving there I would like to point out that this screen shows us some useful information such as the USER and where the connection originated, MACHINE. Now, drilling down we see the following:

This definitely provides a quick way to see expanded information on what this session is, who is running it, and what it is doing. As an added bonus, you can turn tracing on right from here. The left bottom of the screen has your tracing options, while the bottom right contains the “Apply” button. Beyond the boundaries of my screen print, in the lower right hand corner, is a button to “Terminate” the session. Their actions are beyond what I wanted to cover in this article, so let’s move on to some additional information. Again, notice that there is a drill down point for the value of the “SQL Hash” field in the 2^nd section: 3043814816. Drilling here gives us the actual SQL being executed, as well as the execution plan when available.

Bingo… We now have the SQL statement being executed as well as the execution plan and object analysis information. This is a good start to determining why this particular session is consuming an inordinate amount of resources. And, now that you have all of the pieces, you can fly through the data and get what you need. Realistically, you could identify the resource hog in less than a minute! I am also going to show you how to get the same level of detail for a forms session but will skip some of the explanation, as the overall process is very similar.

Let’s start with the general glance screen highlighting a forms session:

Here we can see that our forms session name is f60webmx. We will assume that this forms session is consuming a bunch of resources. So, we plug in the PID of this session, 1665, to the same script, but with a slight variation.

select substr(vs.audsid,1,1)||’.'||substr(vs.audsid,2)||’E7′ AUDSID,

nvl(substr(vs.action,5,instr(vs.action,’:',1,2)-5),’NO FORMS USER’) form_user_name,

vp.spid,

vs.sid,

vs.serial#,

vs.status,

vs.process,

vs.type,

vs.module,

vs.action,

vs.logon_time,

vs.blocking_session_status,

vs.event,

vs.state,

vp.pga_used_mem,

vp.pga_alloc_mem,

vp.pga_freeable_mem,

vp.pga_max_mem

from v$session vs,

v$process vp

where vs.paddr = vp.addr

– use vs.process for forms or java processes

–and vs.process = ‘1665′

Here we see that I am providing input for the VS.PROCESS field of the v$session table, whereas before, with the DB session, I was providing input into the VP.SPID field of the v$process table. So, running this query gets me:

**Break**

Again, this output would be a single line for you. And, again, I have opted to leave out some of the fields for ease of display. Now, you might be thinking that you could use the AUDSID here to search for the expanded information, but you would be wrong. Searching for the AUDSID via the forms search is not an option. Instead, you have the ability to search for the name of a FORM NAME, USERNAME, RESPONSIBILITY or APPLICATION. Earlier in this article I said I would provide detail on my derived field called “FORM_USER_NAME”. I created it to allow a quicker search by the USERNAME of the form. So, from the query above we are going to search for forms being used by STACEYF

In our scenario we now see that STACEYF has a single process that has 2 different sessions. Remember that in Oracle E-Business Suite, forms processes can spawn multiple sessions, which is why the init.ora value of sessions is twice the number allocated to processes. Technically speaking, if you get good at reading the output of the above script, you would have enough information to call the user and see what they were doing. You would also have a majority of the information needed to diagnose problems using the above screen, as it contains the specific form name. But, let’s take a look at the detailed info anyway. Using the button name “Session Details” provides you with the detailed look at what is happening:

And there it is. Note that you can click on the drill down provided on the USERNAME above to get additional information on who this user actually is. This is very helpful if your usernames are not descriptive enough to know who the user is by looking at their username. Clicking on the drill down will show you their full name as well.

There you have it. A very fast way to tie resource consuming processes to actual users or activities. Have fun….

If you enjoyed this post, make sure you subscribe to my RSS feed!