1 2 Previous Next 22 Replies Latest reply: Apr 23, 2012 5:26 AM by misterimran RSS

    Listener consuming too much CPU resources!

    misterimran
      Dear All,

      My database is Oracle 11gR2, 2 Node RAC on Linux.

      I have a strange problem on one of the node of my RAC.

      The listener of one node i.e racnode 1 is consuming too much CPU resources and the response from the database becomes too slow. Even I feel enterprise manager working very slow. As soon as I stop the listener on this node. The database becomes efficient as it should be. I have tried a lot of things like restarting the server, delete automatic advisories and trying to efficient the application queries but nothing worked.

      These are the contents of my listener.ora file:

      LISTENER =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS =
      (PROTOCOL=IPC)
      (KEY=LISTENER)
      )
      )
      )# lines added by Agent

      LISTENER_SCAN1 =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS =
      (PROTOCOL=IPC)
      (KEY=LISTENER_SCAN1)
      )
      )
      )# lines added by Agent

      ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent
      ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent

      Can you please suggest where from I can start the troubleshooting and what could be the reason for this issue. If you need any information please let me know.

      Regards,
      Imran
        • 1. Re: Listener consuming too much CPU resources!
          sybrand_b
          As usual your post doesn't contain any information, only value judgements.

          What is 'too much cpu resources'?
          What is 'response from the database became too slow'? Did you ever run AWR at a time of slowness? If so what were the results and why didn't you post them?
          What is 'the database became efficient as it should be'?

          Remember this is not a chat room, we can't look over your shoulder.
          As you are quite of often asking this type of question, one must ask why you force this forum to tear the information out of you.
          After many questions you know how this forum works, so you must provide information upfront. Also 11gR2 is only a marketing label, not a version.

          ----------
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: Listener consuming too much CPU resources!
            misterimran
            Too much CPU resources means that in the performance tab of OEM, The average CPU active session for CPU cores raises up to 5 to 6 on y axis. This raise somehow slows down the database. Response of the queries becomes 300 times slower. And when I stop listener on this node, the result of the queries becomes fine as it was before starting the listener.

            I will let you know the other details soon.

            Thanks for taking your time to reply.
            • 3. Re: Listener consuming too much CPU resources!
              713555
              check both listener.log and scan listener log

              see whats coming in.

              run the linux top command, narrow it down to exact process.
              • 4. Re: Listener consuming too much CPU resources!
                misterimran
                Hi,

                There is nothing in the logs. Scan listener is running on the second node and not on this node that is giving problem.
                When I start this listener, Database is unable to make new connections.

                I cant keep this listener start because this is a production database. I have compared all the parameters on node 1 and 2 they are the same. Even the tnsnames.ora and listener.ora files are the same.

                When I check status it also take some time for the status to come back, even when I stop it takes time.

                Kindly help
                • 5. Re: Listener consuming too much CPU resources!
                  713555
                  how do you know its the listener though other then just stopping and starting it? did the top command show lsnrctl as the main culprit?

                  It should only be slowing up the database if top shows 0% idle, which you can tell from the top command as well.

                  how many connections have you coming in?

                  netstat -an | grep 1521 | wc -l
                  • 6. Re: Listener consuming too much CPU resources!
                    misterimran
                    Listener of node 2 is OK. when i start listener on node 1 CPU level is raised and new connections are not established, as soon as i stop this listener, applications can connect again.

                    SCAN_LISTENER is running on the second node

                    This is the listener.log from start to stop of listener:

                    Started with pid=12300
                    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
                    Listener completed notification to CRS on start

                    TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
                    WARNING: Subscription for node down event still pending
                    19-APR-2012 10:47:10 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=racnode1)(USER=grid))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186646784)) * status * 0
                    19-APR-2012 10:47:10 * version * 0
                    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.168.20.31)(PORT=1521)))
                    Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.168.20.32)(PORT=1521)))
                    19-APR-2012 10:47:10 * service_register * LsnrAgt * 0
                    19-APR-2012 10:47:10 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=racnode1)(USER=grid))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186646784)) * status * 0
                    19-APR-2012 10:47:20 * (CONNECT_DATA=(SID=etisldb1)(CID=(PROGRAM=perl@racnode1)(HOST=racnode1)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.168.20.32)(PORT=35636)) * establish * etisldb1 * 0
                    19-APR-2012 10:47:20 * ping * 0
                    19-APR-2012 10:47:20 * service_register * +ASM1 * 0
                    19-APR-2012 10:47:20 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=racnode1)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=(ADDRESS=(PROTOCOL=TCP)(HOST=racnode1-vip)(PORT=1521)))(VERSION=186646784)) * status * 0
                    Thu Apr 19 10:47:23 2012
                    19-APR-2012 10:47:23 * service_update * +ASM1 * 0
                    19-APR-2012 10:47:26 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=racnode1)(USER=grid))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186646784)) * status * 0
                    19-APR-2012 10:47:28 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=racnode1)(USER=grid))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186646784)) * status * 0
                    19-APR-2012 10:47:29 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=racnode1)(USER=grid))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186646784)) * status * 0
                    Thu Apr 19 10:48:12 2012
                    19-APR-2012 10:48:07 * service_register * etisldb1 * 0
                    19-APR-2012 10:48:12 * service_update * etisldb1 * 0
                    19-APR-2012 10:48:12 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=racnode1)(USER=grid))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186646784)) * status * 0
                    19-APR-2012 10:48:20 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=root))(SERVICE_NAME=ETISLDB)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=root))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.168.20.92)(PORT=56531)) * establish * ETISLDB * 0
                    19-APR-2012 10:48:20 * service_update * etisldb1 * 0
                    Thu Apr 19 10:48:25 2012
                    19-APR-2012 10:48:25 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=root))(SERVICE_NAME=ETISLDB)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=root))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.168.20.92)(PORT=56533)) * establish * ETISLDB * 0
                    19-APR-2012 10:48:26 * service_update * etisldb1 * 0
                    Thu Apr 19 10:48:36 2012
                    19-APR-2012 10:48:36 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=root))(SERVICE_NAME=ETISLDB)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=root))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.168.20.92)(PORT=56535)) * establish * ETISLDB * 0
                    19-APR-2012 10:48:37 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=root))(SERVICE_NAME=ETISLDB)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=root))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.168.20.92)(PORT=56537)) * establish * ETISLDB * 0
                    19-APR-2012 10:48:42 * (CONNECT_DATA=(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=root))(SERVICE_NAME=ETISLDB)(CID=(PROGRAM=JDBC Thin Client)(HOST=__jdbc__)(USER=root))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.168.20.92)(PORT=56539)) * establish * ETISLDB * 0
                    19-APR-2012 10:48:42 * service_update * etisldb1 * 0
                    No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
                    No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.168.20.31)(PORT=1521)))
                    No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.168.20.32)(PORT=1521)))
                    Listener completed notification to CRS on stop
                    19-APR-2012 10:48:42 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=racnode1)(USER=grid))(COMMAND=stop)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186646784)) * stop * 0

                    Regards,
                    Imran
                    • 7. Re: Listener consuming too much CPU resources!
                      sybrand_b
                      You were asked several times to run top

                      Why don't you answer questions asked?

                      Your diagnosis it is the listener only exists in your imagination.

                      You have provided NO PROOF though being asked several times.

                      No help is possible.
                      Please mark this thread as answered and close it.

                      Stop posting 'information'whicjh does not contribute to solution of the problem.
                      You are wasting the time of volunteers, and this is also not a free support forum.


                      -----------
                      Sybrand Bakker
                      Senior Oracle DBA
                      \
                      • 8. Re: Listener consuming too much CPU resources!
                        misterimran
                        Sorry, actually it is a production database, I had to stop the instance of the culprit node to run my applications. Now I have changed the connection of my applications to the second node that is working fine and have started the listener on node 1 again. Now I am in a position to describe the problem.

                        I have checked the top command, its less than 10% most of the time and it hardly reach 50% but this is a very rare case.

                        When I try to connect from the clients I get:

                        SQL> connect radius/radius@ETISLDB;
                        ERROR:
                        ORA-12547: TNS:lost contact

                        On the enterprise manager I am getting : Internal Error has occurred. Check the log file for details.

                        When i check the alert log file, these are the last lines:

                        Fatal NI connect error 12537, connecting to:
                        (LOCAL=NO)

                        VERSION INFORMATION:
                        TNS for Linux: Version 11.2.0.1.0 - Production
                        Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production
                        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production
                        Time: 22-APR-2012 11:44:59
                        Tracing not turned on.
                        Tns error struct:
                        ns main err code: 12537

                        TNS-12537: TNS:connection closed
                        ns secondary err code: 12560
                        nt main err code: 0
                        nt secondary err code: 0
                        nt OS err code: 0
                        opiodr aborting process unknown ospid (19980) as a result of ORA-609

                        The database errors generated by the Enterprise Manager that are emailed to me are:

                        Target Name=etisldb_etisldb1
                        Target Type=Database Instance
                        Host=racnode1
                        Metric=Status
                        Metric Value=0
                        Timestamp=Apr 22, 2012 11:39:10 AM AST
                        Severity=Critical
                        Message=Failed to connect to database instance: ORA-12547: TNS:lost contact (DBD ERROR: OCIServerAttach).
                        Notification Rule Name=Database Availability and Critical States
                        Notification Rule Owner=SYSMAN
                        Notification Count=1

                        These are the contents of listener.log file when connection fails:

                        22-APR-2012 11:49:09 * (CONNECT_DATA=(SERVICE_NAME=ETISLDB)(CID=(PROGRAM=C:\Program Files\PHP\php-cgi.exe)(HOST=WWW)(USER=NETWORK?SERVICE))(SERVER=dedicated)(INSTANCE_NAME=etisldb1)) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.168.20.149)(PORT=3901)) * establish * ETISLDB * 12518
                        TNS-12518: TNS:listener could not hand off client connection
                        TNS-12547: TNS:lost contact
                        TNS-12560: TNS:protocol adapter error
                        TNS-00517: Lost contact
                        Linux Error: 32: Broken pipe


                        Please let me know if you require any other information.

                        Thanks,
                        Imran
                        • 9. Re: Listener consuming too much CPU resources!
                          user503699
                          misterimran wrote:
                          These are the contents of listener.log file when connection fails:

                          22-APR-2012 11:49:09 * (CONNECT_DATA=(SERVICE_NAME=ETISLDB)(CID=(PROGRAM=C:\Program Files\PHP\php-cgi.exe)(HOST=WWW)(USER=NETWORK?SERVICE))(SERVER=dedicated)(INSTANCE_NAME=etisldb1)) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.168.20.149)(PORT=3901)) * establish * ETISLDB * 12518
                          TNS-12518: TNS:listener could not hand off client connection
                          TNS-12547: TNS:lost contact
                          TNS-12560: TNS:protocol adapter error
                          TNS-00517: Lost contact
                          Linux Error: 32: Broken pipe


                          Please let me know if you require any other information.
                          Imran,

                          As others have already suggested, you may want to determine what exactly is the problem here.
                          Based on what you have posted above, a simple search on this forum took me to TNS-12547:TNS:lost contact
                          As stated in that thread, you may want to check if your listener log file has grown too large (which may cause this issue).
                          • 10. Re: Listener consuming too much CPU resources!
                            misterimran
                            I checked that already, listener log file is only 23 MB.
                            • 11. Re: Listener consuming too much CPU resources!
                              user503699
                              misterimran wrote:
                              I checked that already, listener log file is only 23 MB.
                              Not sure if it is relevant, but you may want to check is there is any issue with the underlying network (e.g. network card) on this node.
                              • 12. Re: Listener consuming too much CPU resources!
                                misterimran
                                Both network cards are ok
                                • 13. Re: Listener consuming too much CPU resources!
                                  932281
                                  hi guys,
                                  please check ,how many your db process numbers when your db running in peek period and double check db parameter process and sessions parameter whether too small. because when user session number more than your db process and sessions parameter.It's may be result to the error. Btw,please double check your application whether exist use wrong database account connect to your db, if so,it will result to listener cpu comsuming high cpu . Because you know that oracle 11gR2 account security new feature. good luck.
                                  • 14. Re: Listener consuming too much CPU resources!
                                    misterimran
                                    Thanks Dear.

                                    SQL> select count() from v$session;*
                                    COUNT(*)
                                    ----------
                                    93

                                    SQL> select count() from v$process;*
                                    COUNT(*)
                                    ----------
                                    103

                                    SQL> show parameter process;
                                    NAME TYPE VALUE
                                    ------------------------------------ ----------- ------------------------------
                                    processes integer 150

                                    SQL> show parameter sessions;
                                    NAME TYPE VALUE
                                    ------------------------------------ ----------- ------------------------------
                                    sessions integer 248

                                    However this command returns:

                                    *[grid@racnode1 ~]$ netstat -an | grep 1521 | wc -l*
                                    245

                                    Last lines from the alert log file

                                    Applications are connecting with correct username and passwords.

                                    Fatal NI connect error 12537, connecting to:
                                    (LOCAL=NO)

                                    VERSION INFORMATION:
                                    TNS for Linux: Version 11.2.0.1.0 - Production
                                    Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production
                                    TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production
                                    Time: 23-APR-2012 10:50:51
                                    Tracing not turned on.
                                    Tns error struct:
                                    ns main err code: 12537

                                    TNS-12537: TNS:connection closed
                                    ns secondary err code: 12560
                                    nt main err code: 0
                                    nt secondary err code: 0
                                    nt OS err code: 0
                                    opiodr aborting process unknown ospid (4186) as a result of ORA-609

                                    Hope that helps.
                                    1 2 Previous Next