7 Replies Latest reply: Jun 24, 2008 7:54 AM by 464406 RSS

    Performance troubleshooting, shared server

    464406
      Greetings all!

      I have oracle 10.2.0.2 installed on windows x64, 2 dual core Intel CPUS. I have 10GB of RAM for each of 2 servers. I have 3 databases running on 1 node and 2 on the other. I am using Oracle failsafe (MS cluster).

      I am currently troubleshooting some speed issues. Our application users are reporting slow response times. (We have 100-300 simultaneous shared connections usually during the day). I think it is database related, more specifically shared server connections. I have noticed that when I log on to the server to query sessions and other things it sometimes hangs for a few seconds before giving a result, as opposed to usually getting an instantaneous response. I do the same, but log on with a dedicated connection, and I always get a response without delay.
      Also establishing a shared connection sometimes takes a few seconds (dedicated is fast). Grid control is reporting in some alerts that some connections take a long time ie: 5 seconds.

      I am thinking at looking at listener issues or more likely dispatcher issues. I have run a query :
      select network, decode(sum(totalq),0,'No Response', sum(wait)/sum(totalq) || ' 100ths secs') "AVERAGE WAIT " from v$queue Q inner join v$dispatcher D on (D.paddr = Q.paddr) where Q.type = 'DISPATCHER' group by network

      and the average dispatcher wait is is reported 0.013 100ths of a second max.

      My question is basically: how do I continue troubleshooting this? What do I need to look at to dig deeper? Keep in mind that I am still new at performance troubleshooting.

      Best wishes to all!
        • 1. Re: Performance troubleshooting, shared server
          585319
          Hi,

          guess you've found the issue...

          how many ora_s000 process do you have? all your shared connections are served by one of those, using a shared time schema.

          You may
          1) increase the number of dispatchers (see DISPATCHERS DB parameter syntaxis within the Database Reference)
          2) make high priority sessions connect thru dedicated

          Regards
          Ignacio

          http://oracledisect.blogspot.com
          • 2. Re: Performance troubleshooting, shared server
            464406
            Thanks for the quick reply Ignacio.

            This is what I have tried so far.
            I had 2 dispatchers, I increased it to 3 dynamically, and the situation did not improve.

            I have shared servers = 2 as init parameter. Currently, v$process lists S000, S001, S004. Would it be beneficial to put the init parameter at 3 or 4? The max shared servers is 0 right now. I am guessing the system decides when to start additional shared server processes. But would it help to give it 4 to start?

            I am also wondering if some perf. views would help me in narrowing down the issue?
            Could it be a non-oracle problem affecting the DB? How would I know?

            As a note, CPU usage revolves around 5% typically. So the system is not that busy.
            Thanks!
            • 3. Re: Performance troubleshooting, shared server
              585319
              Well, the CPU usage is an average figure... how many CPUs your server has?
              and processes receive a share of time, then return to idle.

              You'll find subparameters for DISPATCH like POOL or SESSIONS that may help your conection time , or the way sessions are distributed among dispatchers.

              Need to query V$DISPATCHER_RATE...

              Regards
              Ignacio
              http://oracledisect.blogspot.com
              • 4. Re: Performance troubleshooting, shared server
                Brian Bakula
                How big are you listener logs? If they get large (> 1GB), I have seen it impact the performance of establishing a connection. With Fail Safe databases, the internal heart beat that checks to see if the resources are available once per minute. If you are using grid control, you would also see connections from it. These would be on top of your applications' connections. We typically rotate our listener logs weekly to keep them pruned. I can send you the steps if you think that this could be the problem.
                HTH,
                Brian
                • 5. Re: Performance troubleshooting, shared server
                  464406
                  The listener log is right now 62MB.

                  AS a note, dedicated connections are always instantaneous (very fast), shared server connections attempts sometimes hang for a few seconds before it is established.

                  Please do send the steps, as this is good practice. I also have another big size log file SQLNET.LOG ( 89MB).

                  As for V$DISPATCHER_RATE, I will have to read on it and see if the issues seem to come from dispatchers. The dispatchers could explain why the sessions sometimes hang I guess.

                  I was looking at V$service_event and some items stood out, don't know if they are an issue, but here they are.

                  enq: TX - row lock contention: 300
                  rdbms ipc message: 296
                  SQL*Net message from client: 186 ( if network, then probably ok).

                  Thanks so far for the advice.
                  • 6. Re: Performance troubleshooting, shared server
                    Brian Bakula
                    Here are the steps that we use.
                    Thanks,
                    Brian

                    D:\ORACLE\ADMIN\ORCL\ADHOC>type DEL_LSNR_LOG.BAT
                    D:
                    CD \ORACLE\ADMIN\ORCL\ADHOC
                    REM SETTING THE LISTENER LOG TO A TEMP FILE
                    ECHO SETTING THE LISTENER LOG TO ORCL_TMP.LOG >> ..\LOGBOOK\ORCL_DEL_LSNR_LOG.LOG
                    ECHO. >> ..\LOGBOOK\ORCL_DEL_LSNR_LOG.LOG
                    LSNRCTL @D:\ORACLE\ADMIN\ORCL\ADHOC\DEL_LSNR_LOG.TXT > D:\oracle\product\v1020\NETWORK\LOG\ORCL_STATUS.LOG

                    REM MOVING THE LISTENER LOG FILE TO OLD
                    ECHO MOVING THE ORCL.LOG FILE TO OLD. >> ..\LOGBOOK\ORCL_DEL_LSNR_LOG.LOG
                    ECHO. >> ..\LOGBOOK\ORCL_DEL_LSNR_LOG.LOG
                    REM WAIT TO ENSURE THAT THE FILE IS NOT IN USE
                    SLEEP 300

                    REM CREATE AN OLD FILE TO MAKE SURE ONE EXISTS TO AVOID POSSIBLE ERROR ON DELETE
                    NOW > D:\oracle\product\v1020\NETWORK\LOG\ORCL.LOG.OLD
                    DEL D:\oracle\product\v1020\NETWORK\LOG\ORCL.LOG.OLD >> ..\LOGBOOK\ORCL_DEL_LSNR_LOG.LOG
                    MOVE D:\oracle\product\v1020\NETWORK\LOG\ORCL.LOG D:\oracle\product\v1020\NETWORK\LOG\ORCL.LOG.OLD >> ..\LOGBOOK\ORCL_DEL_LSNR_LOG.LOG
                    SET ERROR=%errorlevel%
                    IF %error% EQU 0 ECHO THE LISTENER LOG WAS SUCCESSFULLY MOVED >> ..\LOGBOOK\ORCL_DEL_LSNR_LOG.LOG
                    IF %error% NEQ 0 ECHO ERROR!!! THE LISTENER LOG WAS NOT MOVED >> ..\LOGBOOK\ORCL_DEL_LSNR_LOG.LOG
                    ECHO. >> ..\LOGBOOK\ORCL_DEL_LSNR_LOG.LOG

                    REM SETTING THE LISTENER LOG BACK TO THE ORIGINAL FILE BY RELOADING THE LISTENER
                    ECHO SETTING THE LISTENER LOG FILE BACK TO ORCL.LOG. >> ..\LOGBOOK\ORCL_DEL_LSNR_LOG.LOG
                    ECHO. >> ..\LOGBOOK\ORCL_DEL_LSNR_LOG.LOG
                    LSNRCTL RELOAD ORCL >> ..\LOGBOOK\ORCL_DEL_LSNR_LOG.LOG
                    LSNRCTL STATUS ORCL >> D:\oracle\product\v1020\NETWORK\LOG\ORCL_STATUS.LOG

                    REM DELETING THE TEMP LISTENER LOG FILE
                    ECHO DELETING THE ORCL_TMP.LOG FILE. >> ..\LOGBOOK\ORCL_DEL_LSNR_LOG.LOG
                    ECHO. >> ..\LOGBOOK\ORCL_DEL_LSNR_LOG.LOG
                    DEL D:\oracle\product\v1020\NETWORK\LOG\ORCL_TEMP.LOG >> ..\LOGBOOK\ORCL_DEL_LSNR_LOG.LOG


                    D:\ORACLE\ADMIN\ORCL\ADHOC>type DEL_LSNR_LOG.TXT
                    SET CURRENT_LISTENER ORCL
                    STATUS
                    SET LOG_FILE D:\ORACLE\PRODUCT\V1020\NETWORK\LOG\ORCL_TEMP.LOG
                    STATUS
                    • 7. Re: Performance troubleshooting, shared server
                      464406
                      Brian,
                      Thanks for the listener log change code!
                      That's a smart way to do it.

                      Does anyone here have a good reference manual/book on performance troubleshooting beside's oracle's performance tuning guide? I would like to get a good handle on performance troubleshooting.

                      Thanks!