1 2 Previous Next 17 Replies Latest reply on Sep 27, 2013 9:52 AM by 836961

    How to set Apex Listener on WEBLOGIC SERVER FOR DEDICATED connections?

    Jcgo-Oracle
      I am getting poor performance issues on my apex environment...
      Getting some virtual circuit errors and I want to know if any way to configure Apex Listener for Dedicated connections instead of using Shared servers?
        • 1. Re: How to set Apex Listener on WEBLOGIC SERVER FOR DEDICATED connections?
          Jcgo-Oracle
          stimated benefit is 1.01 active sessions, 43.11% of total activity.
          --------------------------------------------------------------------
          Action
          Investigate the cause for high "virtual circuit wait" waits with P1
          ("circuit#") value "8" and P2 ("type") value "2".

          Recommendation 5: Application Analysis
          Estimated benefit is .68 active sessions, 28.95% of total activity.
          -------------------------------------------------------------------
          Action
          Investigate the cause for high "virtual circuit wait" waits in Module
          "JDBC Thin Client".

          Symptoms That Led to the Finding:
          ---------------------------------
          Wait class "Network" was consuming significant database time.
          Impact is 2.31 active sessions, 99.01% of total activi
          • 2. Re: How to set Apex Listener on WEBLOGIC SERVER FOR DEDICATED connections?
            Udo
            Hello Jeff,
            I am getting poor performance issues on my apex environment...
            Getting some virtual circuit errors and I want to know if any way to configure Apex Listener for Dedicated connections instead of using Shared servers?
            I don't think so, but I wouldn't know how this would solve your problem. These virtual circut errors indicate that your database is overloaded. This is usually more a problem of some sessions/applications using more resources than expected. I'd recommend to find out which applications cause these troubles and debug them.
            If you find out that you have some applications that have to have long-running statements (in foreground), you could start to think about tuning your JDBC settings.
            Did you figure out whether your database would actually be able to solve more requests at the time you receive that error? If so, you could increase the number of sessions in the connection pool used by APEX Listener, but I doubt this would be the case.
            I really doubt you'll get better performance when "hammering" with dedicated connections. Connection Pooling is usually more efficient than managing separate connections for each end user, and I'm not sure APEX is a very good example for a clear mapping of end users to a database session... Despite that, you might also get a problem with the number of database sessions and hence processes you'll see when you do that in an environment with a few hundred clients. Of course, you can configure your connection pool in a way so there is (almost) a dedicated session for every expected client.

            For a more detailed answer on your pool setup, you need to provide your current values and some knowledge about your applications and end users...

            -Udo
            • 3. Re: How to set Apex Listener on WEBLOGIC SERVER FOR DEDICATED connections?
              Jcgo-Oracle
              Hi Udo,
              Issue is: There is NO load at all on the database.
              And I see this wait event even when there are only 2 or 3 users accessing the Apex application.

              Again, its leading me to beleieve it can be how the Apex Listener ( JDBC thin client) interacts with SCAN.

              In another perspective, the listener version is somewhat old and I have been told its been buggy.

              Version:1.1.2.131.15.23


              maybe upgrade will help?
              • 4. Re: How to set Apex Listener on WEBLOGIC SERVER FOR DEDICATED connections?
                Udo
                Hi Jeff,
                Again, its leading me to beleieve it can be how the Apex Listener ( JDBC thin client) interacts with SCAN.
                This tells me you are using a RAC database. Could you please post your DB version? And could you also post your JDBC connection string and possibly your other JDBC settings?
                I don't think this is an APEX Listener specific issue, but more a general JDBC connection problem. Usually, this is caused by some problem in the network configuration. One example has been documented in this blog entry: [url http://learnwithme11g.wordpress.com/2011/02/18/jdbc-connection-issue-with-oracle-database-11gr2-rac-scan-listener/]JDBC Connection Issue with Oracle Database 11gR2 RAC SCAN listener.
                In another perspective, the listener version is somewhat old and I have been told its been buggy.
                Version:1.1.2.131.15.23
                Who told you that?
                It's close to the most recent available version, which would be 1.1.3.243.11.40. There has been only one change from 1.1.2 to 1.1.3 and as far as we know this only affected 10.2 XE databases and concerned a totally different issue, so I'd say it won't help you solve your problem, though updating shouldn't have a negative impact either...

                -Udo
                • 5. Re: How to set Apex Listener on WEBLOGIC SERVER FOR DEDICATED connections?
                  Jcgo-Oracle
                  HI Udo
                  Thanks for your insights. Exactly what i am looking for.

                  I dont think it was the listener but couldnt hurt to upgradr.
                  And i dont use jdbc....use the basic setting.

                  Ill review the docs.

                  Jeff
                  • 6. Re: How to set Apex Listener on WEBLOGIC SERVER FOR DEDICATED connections?
                    Jcgo-Oracle
                    Udo,
                    I am running 11.2.0.3 database with RAC.

                    1). Thin JDBC connection
                    2). Pool size is 50
                    Max connection:1000
                    Max statements: 100


                    thanks
                    • 7. Re: How to set Apex Listener on WEBLOGIC SERVER FOR DEDICATED connections?
                      Jcgo-Oracle
                      the apex-config.xml copy:


                      ***********
                      <entry key="apex.db.username">APEX_PUBLIC_USER</entry>
                      <entry key="apex.db.password">@058D44F26F78940D1EF84F6BA8AA7FA6E24B46DE3DE7FD15F
                      B</entry>
                      <entry key="apex.db.connectionType">basic</entry>
                      <entry key="apex.db.hostname">server-cluster.us.oracle.com</entry>
                      <entry key="apex.db.port">1521</entry>
                      <entry key="apex.db.sid"></entry>
                      <entry key="apex.db.servicename">dbservicename.us.oracle.com</entry>
                      <entry key="apex.db.tnsAliasName"></entry>
                      <entry key="apex.db.tnsDirectory"></entry>
                      <entry key="apex.db.customURL"></entry>
                      <entry key="apex.jdbc.DriverType">thin</entry>
                      <entry key="apex.jdbc.InitialLimit">50</entry>
                      <entry key="apex.jdbc.MinLimit">10</entry>
                      <entry key="apex.jdbc.MaxLimit">1000</entry>
                      <entry key="apex.jdbc.MaxStatementsLimit">100</entry>
                      <entry key="apex.jdbc.InactivityTimeout">1800</entry>
                      <entry key="apex.jdbc.AbandonedConnectionTimeout">900</entry>
                      <entry key="apex.jdbc.MaxConnectionReuseCount">1000</entry>

                      <entry key="apex.jdbc.DriverType">thin</entry>
                      <entry key="apex.jdbc.InitialLimit">50</entry>
                      <entry key="apex.jdbc.MinLimit">10</entry>
                      <entry key="apex.jdbc.MaxLimit">1000</entry>
                      <entry key="apex.jdbc.MaxStatementsLimit">100</entry>
                      <entry key="apex.jdbc.InactivityTimeout">1800</entry>
                      <entry key="apex.jdbc.AbandonedConnectionTimeout">900</entry>
                      • 8. Re: How to set Apex Listener on WEBLOGIC SERVER FOR DEDICATED connections?
                        Udo
                        Hi Jeff,
                        11.2.0.3 database with RAC.
                        <entry key="apex.db.servicename">dbservicename.us.oracle.com</entry>
                        Well, as I guessed a RAC database. Could it be that not all nodes are able to see each other using the FQDN you've supplied in your service name? You could try to workaround that by providing a custom JDBC URL for your RAC using the Advanced connection type. See this thread for details: {thread:id=2201868}.
                        On the other hand, your database release is 11.2.0.3 already, whereas the drivers shipped with APEX Listener are still 11.2.0.2. In general, this shouldn't be a problem, but since RAC with SCAN Listener is a more complex scenario, it could be worth trying to update the drivers to 11.2.0.3 as well. At least, there was one issue in your scenarios context was adressed in that release ("10385510 JDBC TCPS CONNECTIONS DO NOT FAILOVER USING SCAN LISTENERS"). See the [url http://download.oracle.com/otn/utilities_drivers/jdbc/11203/Readme.txt]JDBC 11.2.0.3 Readme for more details.
                        Note that "patching" APEX Listener yourself is not officially supported, but I think you can't do any worse with trying.
                        Before you start, make a backup of your current deployment. (Which JEE Container did you deploy your APEX Listener onto anyway?)
                        Then, download the most recent drivers:
                        - [url http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html]ojdbc6.jar for 11.2.0.3
                        - [url http://www.oracle.com/technetwork/database/enterprise-edition/downloads/ucp-112010-099129.html]ucp.jar for 11.2.0.3

                        Then create a copy of your apex.war, unpack it, replace the drivers with the downloaded versions, repack the patched content and deploy the patched archive, e.g. (assuming you start from the directory where your apex.war resides and your new drivers are in +/var/tmp/jdbc+ - replace with the directory you choose)
                        mkdir /var/tmp/apex_listener_patch
                        mkdir /var/tmp/apex_listener_patch/tmp
                        cp apex.war /var/tmp/apex_listener_patch
                        cd /var/tmp/apex_listener_patch/tmp
                        jar -xf ../apex.war
                        rm ../apex.war
                        rm WEB-INF/lib/ojdbc6.jar
                        rm WEB-INF/lib/ucp.jar
                        cp /var/tmp/jdbc/ucp.jar WEB-INF/lib/
                        cp /var/tmp/jdbc/ojdbc6.jar WEB-INF/lib/
                        jar -cf ../apex.war .
                        ... and deploy the apex.war from +/var/tmp/apex_listener_patch+ .
                        This example assumes you have Unix/Linux environment. It'll also work with the corresponding commands and adapted paths for Windows machines.
                        If you like (and didn't do so before), you could also edit the web.xml in WEB-INF before repacking the archive, so you'll have your config.dir already configured before your first deployment.

                        If your network configuration is fine, using a custom url to workaround possible SCAN problems is no option, and patching to the latest drivers doesn't help, you should open a MOS-ticket to get that issue addressed "officially". But I'd expect at least one of these actions to be successful.

                        -Udo
                        • 9. Re: How to set Apex Listener on WEBLOGIC SERVER FOR DEDICATED connections?
                          Jcgo-Oracle
                          Hi Udo,

                          Thanks for the suggestions.

                          1). Yes, I did see the thread few days back about the ODJBC.jar upgrade. I wanted to try and upgrade the listener first and see if that helped, which it didnt..:)

                          2). I am also trying to tackle this on App Apex side, besides the component sections...
                          SO far, my developer has tried setting the Region reports display and that seemed to work.

                          I tell you, this is a good learning experience for me..:)

                          I will keep you posted and am seriously thinking about driver upgrade...

                          thanks
                          • 10. Re: How to set Apex Listener on WEBLOGIC SERVER FOR DEDICATED connections?
                            Udo
                            Hi Jeff,
                            SO far, my developer has tried setting the Region reports display and that seemed to work.
                            Interesting. What exactly happend in that region?
                            I will keep you posted and am seriously thinking about driver upgrade...
                            Any news on that one yet? I'm curious to know if this issue could be fixed with "just" an updated driver...

                            -Udo
                            • 11. Re: How to set Apex Listener on WEBLOGIC SERVER FOR DEDICATED connections?
                              Jcgo-Oracle
                              Hi Udo,

                              As we "suspected" in some form, the JDBC ( Ojdbc.jar 11.2.0.2) does not seem to be performing properly.
                              By this , i mean , most apex queries are being sent to the 2nd node and not the 1st node.

                              I did some load testing and CPU spiked to nearly 100% usage on 2nd node. * Note, this node is also a VM*

                              So, the question is, probably better to update to latest 11.2.0.3 JAR file, but you mentioned this is NOT officially supported.
                              What does this actually mean?

                              I am happy to create an SR/BUG number to have someone assist me.

                              thanks
                              • 12. Re: How to set Apex Listener on WEBLOGIC SERVER FOR DEDICATED connections?
                                Udo
                                Hi Jeff,

                                as you've reported in {message:id=10231634}, it seems as if all load is going to the same node. Now, I don't think this is the JDBC drivers fault if it connects to a SCAN listener, as this listener should be the one to decide which node to connect a client request to.
                                Did you ever check whether that SCAN listener actually balances as you expect? Perhaps there actually is some network issue that makes it ignore the first node, or the first node has been flagged down/unavailable for some reason? This could be done by some shell skript running sqlplus or something and query the current instance to get the node used for the connection...

                                Despite that SCAN listener investigation, you could try whether setting the RAC-connection manually (as in {thread:id=2201868} which I referenced earlier) would distribute your load better.

                                To answer your last questions anyway:
                                So, the question is, probably better to update to latest 11.2.0.3 JAR file, but you mentioned this is NOT officially supported.
                                What does this actually mean?
                                It's not officially supported in terms of:
                                a) there is no official download that would include that JAR
                                b) there is no officially published document that describes that procedure.
                                So, if you open a ticket for that configuration, it'll be rejected until you go back to the most recent version available from Oracle.
                                From personal experience I can tell you that updating the driver won't do any harm. But as mentioned before, I'm not sure if it will solve your load balancing problem. It's just a few minutes to try, though.
                                I am happy to create an SR/BUG number to have someone assist me.
                                If you have support for the system you run this setup on, this might be a good idea. Probably you'll be asked to provide additional information like actual TNS configuration, routing/tnsping results, etc.

                                -Udo

                                Edited by: Udo on 26.03.2012 13:51
                                • 13. Re: How to set Apex Listener on WEBLOGIC SERVER FOR DEDICATED connections?
                                  Jcgo-Oracle
                                  Udo,

                                  I just downloaded the JAR files. Its on OTN.

                                  2). I set the JDBC THIN URL and still see the behavior

                                  entry key="apex.db.tnsDirectory"></entry>
                                  <entry key="apex.db.customURL">jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=on)
                                  (ADDRESS=(PROTOCOL=TCP)(HOST=server1-vip.us.oracle.com) (PORT=1521))
                                  (ADDRESS=(PROTOCOL=TCP)(HOST=server2-vip.us.oracle.com) (PORT=1521))
                                  (CONNECT_DATA=(SERVICE_NAME=service.us.oracle.com)))</entry>
                                  <entry key="apex.jdbc.DriverType">thin</entry>
                                  • 14. Re: How to set Apex Listener on WEBLOGIC SERVER FOR DEDICATED connections?
                                    Udo
                                    Hi Jeff,
                                    I just downloaded the JAR files. Its on OTN.
                                    I know - I provided the links above. ;)
                                    Did you patch the apex.war yet?
                                    2). I set the JDBC THIN URL and still see the behavior
                                    Your JDBC-URL looks good to me. When this is not working in the way that one node isn't getting anything at all, either the node isn't visible or it's not configured correctly.
                                    But going through some issues with RAC and UCP, I also found a hint that Fast Connection Failover (FCF) should be turned on for Run-Time Connection Load Balancing. This can be done at driver side and/or as part of the connect url, e.g.
                                    jdbc:oracle:thin:@(DESCRIPTION=
                                                        (LOAD_BALANCE=on)
                                                        (FAILOVER=on)
                                                        (ADDRESS=(PROTOCOL=TCP)(HOST=server1-vip.us.oracle.com) (PORT=1521))
                                                        (ADDRESS=(PROTOCOL=TCP)(HOST=server2-vip.us.oracle.com) (PORT=1521))
                                                        (CONNECT_DATA=(SERVICE_NAME=service.us.oracle.com))
                                                       )
                                    Note that you also need to prepare your service accordingly, as described in [url http://docs.oracle.com/cd/E18283_01/java.112/e12265/rac.htm#CHDEEHJI]Using Oracle RAC Features.

                                    If this all doesn't help, I'd be interested in knowing if a different client would use both nodes from the host your APEX Listener is located on, e.g. using a script with sqlplus calls as mentioned before. If that doesn't work either, you should really check your RAC (and possibly open a thread in {forum:id=384}).

                                    -Udo
                                    1 2 Previous Next