9 Replies Latest reply: Oct 10, 2012 6:09 AM by 952768 RSS

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

    VIKHARAHMED
      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
          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
            VIKHARAHMED
            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
              VIKHARAHMED
              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
                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
                  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
                    VIKHARAHMED
                    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
                      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
                        VIKHARAHMED
                        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
                          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.