9 Replies Latest reply: Jul 14, 2014 8:09 PM by Marc Fielding RSS

    exadata performance

    user569151

      In our exachk results, there is one item for shared_server.

      our current production environment has shared_server set to 1. shared_server=1.

       

      Now I got those from exachk:

       

      Benefit / Impact:

      As an Oracle kernel design decision, shared servers are intended to perform quick transactions and therefore do not issue serial (non PQ) direct reads. Consequently, shared servers do not perform serial (non PQ) Exadata smart scans.

      The impact of verifying that shared servers are not doing serial full table scans is minimal. Modifying the shared server environment to avoid shared server serial full table scans varies by configuration and application behavior, so the impact cannot be estimated here.

      Risk:

      Shared servers doing serial full table scans in an Exadata environment lead to a performance impact due to the loss of Exadata smart scans.

      Action / Repair:

      To verify shared servers are not in use, execute the following SQL query as the "oracle" userid:

      SQL>  select NAME,value from v$parameter where name='shared_servers';

      The expected output is:

      NAME            VALUE

      --------------- ------------------------------

      shared_servers  0

       

       

      If the output is not "0", use the following command as the "oracle" userid with properly defined environment variables and check the output for "SHARED" configurations:

      $ORACLE_HOME/bin/lsnrctl service

      If shared servers are confirmed to be present, check for serial full table scans performed by them. If shared servers performing serial full table scans are found, the shared server environment and application behavior should be modified to favor the normal Oracle foreground processes so that serial direct reads and Exadata smart scans can be used.

       

      Oracle lsnrctl service on current production environments shows all 'Local Server'.

       

      What should I proceed here?

       

      Thanks again in advance.

        • 1. Re: shared_server and exadata performance
          Marc Fielding

          Hello user569151,

           

          The SHARED_SERVERS parameter determines if shared servers can be used, but not if the are actually used;  this is determined by the connection string configured in the client.  The "lsnrctl services" output will show counts of established connections for each connection type.  In the case of shared servers, v$reqdist has histograms of connection lengths for shared server queries as well.

           

          If your sessions aren't using shared servers, you don't have this problem.  If they are, you won't be able to do direct path reads and thus smart scans.

           

          HTH!

           

          Marc

          • 2. Re: shared_server and exadata performance
            Kasey.Parker

            user569151 -

             

            This reference in the Exachk is regarding a shared server (vs dedicated server) architecture. Shared server allows you to have multiple client connections sharing a smaller number of database server processes on the OS - as opposed to each connection having a dedicated server process. You would use shared server architecture when your environment is processing may short OLTP type transactions and the application is either making many connections to the database, instead of using connection pooling, or you have so many connections that you are memory bound on your database server(s). Because you have shared_servers init parm set to a non 0 value it appears you have enabled shared servers. Are you aware of functioning in this architecture? Whether you are actually using shared connections also depends on how the dispatchers (dispatcher init parm) have been configured and whether your connections are actually using shared servers. This is why the exachk instructs looking at the lsnrctl service output. When you state your environment shows all "Local Server" from the lsnrctl service output - do you mean all the service handlers are "DEDICATED SERVER"? Do you see any service handlers using "DISPATCHER"? It may be helpful to post the output if you are able.

             

            The issue here is connections using shared servers will not do direct path reads - and thus will not use smart scans - even for full table scans that could greatly benefit by the performance of a smart scan. The real need is for you to determine whether your environment really requires shared server architecture. If it doesn't, then especially on Exadata, you would be better off disabling it to make sure you are losing performance gains by not smart scanning. If you are sure you've checked the listener(s) used by your application/client connection and all of them are showing dedicated server connections - meaning no shared connections are being used - then you are probably not being hurt by this anyway. But that still leads to the question of why you have shared servers enabled if you are not using it.

             

            If you need more information on shared server architecture see Ch. 11 of the Oracle Database Net Services Administrator's Guide on Configuring Dispatchers and Ch.5 of the Database Administrator's Guide on Managing Processes

             

            HTH

             

            Thanks,

            Kasey

            • 3. Re: shared_server and exadata performance
              user569151

              Thank you all for your help.

               

              Here is an output of lsnrctl service:

               

              $ORACLE_HOME/bin/lsnrctl service

               

               

              LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 14-JUL-2014 14:15:24

               

               

              Copyright (c) 1991, 2013, Oracle.  All rights reserved.

               

               

              Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))

              Services Summary...

              Service "+ASM" has 1 instance(s).

                Instance "+ASM2", status READY, has 1 handler(s) for this service...

                  Handler(s):

                    "DEDICATED" established:1420 refused:0 state:ready

                       LOCAL SERVER

              Service "PREME" has 1 instance(s).

                Instance "PREME2", status READY, has 1 handler(s) for this service...

                  Handler(s):

                    "DEDICATED" established:627130 refused:3 state:ready

                       LOCAL SERVER

              Service "PREMEXDB" has 1 instance(s).

                Instance "PREME2", status READY, has 1 handler(s) for this service...

                  Handler(s):

                    "D000" established:0 refused:0 current:0 max:1022 state:ready

                       DISPATCHER <machine: prodremedy, pid: 16823>

                       (ADDRESS=(PROTOCOL=tcp)(HOST=prodremedy)(PORT=61323))

              Service "PREME_ALL_USERS" has 1 instance(s).

                Instance "PREME2", status READY, has 1 handler(s) for this service...

                  Handler(s):

                    "DEDICATED" established:627130 refused:3 state:ready

                       LOCAL SERVER

              Service "PREME_TXT_APP" has 1 instance(s).

                Instance "PREME2", status READY, has 1 handler(s) for this service...

                  Handler(s):

                    "DEDICATED" established:627130 refused:3 state:ready

                       LOCAL SERVER

              Service "PREME_CORP_APP" has 1 instance(s).

                Instance "PREME2", status READY, has 1 handler(s) for this service...

                  Handler(s):

                    "DEDICATED" established:627130 refused:3 state:ready

                       LOCAL SERVER

              Service "PREME_DISCO_APP" has 1 instance(s).

                Instance "PREME2", status READY, has 1 handler(s) for this service...

                  Handler(s):

                    "DEDICATED" established:627130 refused:3 state:ready

                       LOCAL SERVER

              Service "PREME_EAST_APP" has 1 instance(s).

                Instance "PREME2", status READY, has 1 handler(s) for this service...

                  Handler(s):

                    "DEDICATED" established:627130 refused:3 state:ready

                       LOCAL SERVER

              Service "PREME_CRM" has 1 instance(s).

                Instance "PREME2", status READY, has 1 handler(s) for this service...

                  Handler(s):

                    "DEDICATED" established:627130 refused:3 state:ready

                       LOCAL SERVER

              Service "PREME_CRM_WR" has 1 instance(s).

                Instance "PREME2", status READY, has 1 handler(s) for this service...

                  Handler(s):

                    "DEDICATED" established:627130 refused:3 state:ready

                       LOCAL SERVER

              Service "PREME_RPT" has 1 instance(s).

                Instance "PREME2", status READY, has 1 handler(s) for this service...

                  Handler(s):

                    "DEDICATED" established:627130 refused:3 state:ready

                       LOCAL SERVER

              Service "PREME_WEST_APP" has 1 instance(s).

                Instance "PREME2", status READY, has 1 handler(s) for this service...

                  Handler(s):

                    "DEDICATED" established:627130 refused:3 state:ready

                       LOCAL SERVER

              The command completed successfully

              • 4. Re: shared_server and exadata performance
                user569151

                Some more output:

                 

                SQL> show parameter dispatch

                 

                 

                NAME                                 TYPE        VALUE

                ------------------------------------ ----------- ------------------------------

                dispatchers                          string      (PROTOCOL=TCP) (SERVICE=PREMEXDB)

                max_dispatchers                      integer

                SQL> select * from  v$reqdist;

                 

                 

                    BUCKET      COUNT

                ---------- ----------

                         8          0

                         1          0

                         4          0

                         5          0

                         7          0

                        11          0

                         3          0

                        10          0

                         0          0

                         2          0

                         6          0

                 

                 

                    BUCKET      COUNT

                ---------- ----------

                         9          0

                 

                 

                12 rows selected.

                • 5. Re: shared_server and exadata performance
                  Marc Fielding

                  Thanks for the output.  We can see that there is only a single shared-server service:

                   

                  Service "PREMEXDB" has 1 instance(s).

                    Instance "PREME2", status READY, has 1 handler(s) for this service...

                      Handler(s):

                        "D000" established:0 refused:0 current:0 max:1022 state:ready

                   

                  You have zero connections established, meaning the service is not being used.  All other services have dedicated servers, which are all capable of doing smart scans.

                   

                  Marc

                  • 6. Re: shared_server and exadata performance
                    user569151

                    Thanks. How do I know we never had shared connection? Maybe this is only for right now?  maybe happened before or will happen in fugure?

                     

                    To be safe, should I disable shared_server to set it to 0?

                    • 7. Re: shared_server and exadata performance
                      Kasey.Parker

                      The established number gives a count of all connections the service handler has established - even past connections. However, I'm not honestly sure when/how the counter ever gets reset... but for sure if the listener, service or handler has ever been recreated it would reset. So it's difficult to say that the environment has never used a shared connections - and you're right you can't be sure nothing will use it if something is configured to. Because it's count is 0 and you have so many established connections on the dedicated handlers - at the very least it's rarely, if ever, used. Do you know any information about the PREMEXDB service for PREME2 - it looks like it is the XDB service fore the database... but why is it set to use shared services? Was this a requirement for something you are doing with XDB? If you don't need it then I would say you should disable shared servers by setting shared_server to 0; however, you'll want to confirm first why it was enabled to begin with and if it is ok to disable.

                       

                      Based on the usage... it appears that you aren't being impacted by it from a performance standpoint for smart scans - so no concern from the flag the exachk originally raised.

                      • 8. Re: shared_server and exadata performance
                        user569151

                        I think this XDB is a default funcction of some components of database. It is created when dispatcher is set. It is not related to any app.

                        • 9. Re: shared_server and exadata performance
                          Marc Fielding

                          XMLDB is an optional component on Oracle 11gR2, but included in the default Exadata install.  But regardless, keeping the system as-is avoids any risk of impacting XMLDB.

                           

                          Marc