This discussion is archived
9 Replies Latest reply: Oct 10, 2012 4:09 AM by 952768 RSS

frmweb makes Oracle Appl CPU 100% - which SQL query using resources in DB

user250195 Newbie
Currently Being Moderated
Following are the server details:

Oracle AS10g (10.1.2.3) on Windows 2003 Enterprise edition.
Oracle 9.2.0.8 on HP-UX
8 application servers AS10g which are load- balanced used.

The below screen shots are from the application server. Oracle IAS 10g (10.1.2.3). we have forms and reports application running on the application server. I have performance issue with the application server. It is getting very slow . I discovered that the CPU is getting 100% at the time when the end users are reporting slow. The reason for the slow are frmweb processes and some specific processes takes 12% of the CPU each. If 2 to 3 bad processes at a time my server can handle and if the processes increase then the CPU gets 100% and the application gets very,very slow. This problem started few days before. I have more than 100 users connected to the each eight application servers respectively.

1.     I want to get the database information of this bad process? The below query does not return any rows?


I did following things:

1. Used "Process Explorer" to trace the frmweb.exe, Result:- gives IP Address & PID information but in Database no info with this PID
2. SELECT username FROM v$session WHERE substr( process, 1, instr( process, ':' ) - 1 ) = '6704'; Result:- no rows selected.
3. Used TOAD > session browser to monitor SID, SPID
4. Rebuild indexes
5. Followed note 206681.1 but they is no FORMS_CATCHTERM=0 parameter in default.env & formsweb.cfg

I need to know which SQL statements are creating problem & eating the CPU resources. Kindly help me to resolve the issue.
  • 1. Re: frmweb makes Oracle Appl CPU 100% - which SQL query using resources in DB
    952768 Journeyer
    Currently Being Moderated
    You could use lsof.

    For example:

    OAS server:

    First you need the pid of the process
    lsof | grep pid | grep TCP
    Then you can check the tcp port to your database server.
    For example:

    TCP oasserver:52163->databaseserver:ncube-lm(ESTABLISHED)

    On the database server use:
    lsof -i:52163
    This will show the PID of your oracle dedicated server process. This is the PID you can check with TOAD.

    Edited by: specdev on 9-okt-2012 1:20
  • 2. Re: frmweb makes Oracle Appl CPU 100% - which SQL query using resources in DB
    user250195 Newbie
    Currently Being Moderated
    Thank you for the response. Dear as i mentioned, i am using Windows 2003 OS for Oracle AS10g. Also, i mentioned that i am using "Process Explorer" to find out PID but pid is not in v$session OR gv$session nor in TOAD > DATABASE > MONITOR > SESSION BROWSER.
  • 3. Re: frmweb makes Oracle Appl CPU 100% - which SQL query using resources in DB
    user250195 Newbie
    Currently Being Moderated
    Even i tried this query to find the SPID of this frmweb.exe process id.

    SQL>SELECT a.username, a.osuser, a.terminal,a.program, spid, SID, a.serial#,a.last_call_et,a.logon_time
    FROM gv$session a, v$process b WHERE a.paddr = b.addr and a.type = 'USER' and a.terminal = 'MOSA-SA1' and b.pid=10804;

    no rows selected.

    SQL>SELECT a.username, a.osuser, a.terminal,a.program, spid, SID, a.serial#,a.last_call_et,a.logon_time
    FROM gv$session a, v$process b WHERE a.paddr = b.addr and a.type = 'ORACLE' and a.terminal = 'MOSA-SA1' and b.pid=10804;

    no rows selected.

    but still no luck.
  • 4. Re: frmweb makes Oracle Appl CPU 100% - which SQL query using resources in DB
    952768 Journeyer
    Currently Being Moderated
    Maybe you can do something like this. The trick is to get the TCP port of the proces and match this on your database server.

    your database server is HP-UX right? Can you use lsof on that server?

    On windows:
    netstat -b | find "databaseServerHostname"
    TCP OASServer:3680 databaseServerHostname:1521 ESTABLISHED 1064

    The last number is the PID (1064), you already know wich PID on your OAS is using a lot of CPU right?
    You can use also:
    netstat -ano | find "1064"
    On HP-UX, i hope you can use lsof.
    lsof -i:3680
    COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
    oracle 14221482 oracle 19u IPv6 0xf1000e00006aabb8 0t389271 TCP databaseServerHostname:ncube-lm->Windows2003OASserver:npds-tracker (ESTABLISHED)
  • 5. Re: frmweb makes Oracle Appl CPU 100% - which SQL query using resources in DB
    Christian Erlinger Guru
    Currently Being Moderated
    As the frmweb processes are consuming most of the CPU you are definitely barking up the wrong tree when checking for CPU consuming database sessions as if it would be related to SQL or database PL/SQL you'd have a oracle process consuming CPU, not frmweb processes.

    So forget about rebuilding indices (?!?) and what ever else you did and focus on what happens in forms. What did happen in those (forms)sessions consuming CPU? If all your users are using different database users you could simply find out who consumes CPU by obtaining the process ID of a bad frmweb process via tasklist (or what ever else you like) and query the process column of v$session for it:

    http://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2088.htm#REFRN30223

    Then if you have the specific user ask them what they did and try to reproduce the behaviour.

    cheers
  • 6. Re: frmweb makes Oracle Appl CPU 100% - which SQL query using resources in DB
    user250195 Newbie
    Currently Being Moderated
    On hp-ux , i am unable to use lsof . However, i am able to access below details

    C:\>netstat -ano|find "9336"
    TCP    10.65.62.151:2318      10.65.62.151:2319      ESTABLISHED     9336
    TCP    10.65.xx.xxx:2430      10.65.60x.xx:1521       ESTABLISHED     9336

    $ lsof -i:9336
    sh: lsof: not found.
  • 7. Re: frmweb makes Oracle Appl CPU 100% - which SQL query using resources in DB
    952768 Journeyer
    Currently Being Moderated
    TCP 10.65.xx.xxx:2430 10.65.60x.xx:1521 ESTABLISHED 9336
    On your database server you have to check TCP port 2430.
    lsof -i:2430

    Because it is Windows 10.65.xx.xxx:2430 <-----> Database server 10.65.60x.xx:?

    -.-

    It seems that lsof can be installed on Solaris.

    Maybe you can use netstat with -p option? to show pid.

    There are some forums that describe ways to retrieve the TCP port of a process.
    For example: http://www.sybaseteam.com/lsof-alternative-in-solaris-t-339.html

    I'am working on AIX/Linux and with lsof I can follow every connection.

    It should be nice if you have the right solution to show the TCP port and related process id on your system.

    Edited by: specdev on 10-okt-2012 2:07
  • 8. Re: frmweb makes Oracle Appl CPU 100% - which SQL query using resources in DB
    user250195 Newbie
    Currently Being Moderated
    I've used netstat -a|more, but i dont understand why do we trace TCP port & what is the use?

    I need SID & which SQL statement is causing the issue by providing frmweb.exe PID
    as i said earlier, i have only PID of OAS which is got from "Process Explorer" Microsoft free monitoring tool.

    c:\>netstat -ano|find "6696"
    TCP    0.0.0.0:4125           0.0.0.0:0              LISTENING       6696
    TCP    10.65.62.151:1321      10.65.60.51:1521       ESTABLISHED     6696
    TCP    10.65.62.151:4044      10.65.62.151:4045      ESTABLISHED     6696
    TCP    10.65.62.151:4124      10.65.62.151:2724      ESTABLISHED     6696
    UDP    0.0.0.0:14021          *:*                                    6696


    Active Internet connections (including servers)
    Proto Recv-Q Send-Q Local Address Foreign Address (state)
    tcp        0      0  hp_data1.1521          .1759                   ESTABLISHED
    tcp        0      0  hp_data1.1521          .1784                   ESTABLISHED
    tcp        0      0  hp_data1.1521          .1339                   ESTABLISHED
    tcp        0      0  hp_data1.1521          .2823                   ESTABLISHED
    tcp        0      0  localhost.hacl-dlm     localhost.55565         ESTABLISHED
    tcp        0      0  hp_data1.1521          .1899                   ESTABLISHED
    tcp        0      0  hp_data1.1521          society1.55164          ESTABLISHED
    tcp        0      0  hp_data1.1521          .1650                   ESTABLISHED
    tcp        0      0  *.2006                 *.*                     LISTEN
    tcp        0      0  hp_data1.1521          .1116                   ESTABLISHED

    Edited by: VIKHARAHMED on Oct 10, 2012 4:06 AM
  • 9. Re: frmweb makes Oracle Appl CPU 100% - which SQL query using resources in DB
    952768 Journeyer
    Currently Being Moderated
    You cannot related it directly. That is also what you have seen by using only the queries in the database.

    I try to help you to make this relation on the OS level.

    The relation is:

    OAS PID --> has tcp port connection to --> Database PID

    The Database PID is what you can query within the database.

    But the other way around is to check for long running active processes in your database. When it's is related to a frmweb proces: bingo.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points