Monday, May 14, 2012

Apex Listener jdbc/security setup

In talking to a few customers, I realized lots of people run with the default settings. So, I thought I should point out a few things that should be conscious decisions vs. accept the defaults.  The defaults are some limits that are safe and will run a modest size app just fine.  When the app gets a few long running spots or  high concurrency it's time to look at changing those defaults.

1.  There are fewer and fewer reasons to use the OCI jdbc driver over thin but it is something that if there's a reason you can swap out.

<entry key="apex.jdbc.DriverType">thin</entry>

2. The pool of connections has a few things that can be adjusted.
a)  The initial size of the pool.  This is when you first start up the listener how many connections to establish so that user #1,2,3 will not be waiting on a new connection to be established.
<entry key="apex.jdbc.InitialLimit">3</entry>

b) The min is how low the pool is allowed.  If things like inactivity is set to idle down the connection this many will stay available.
<entry key="apex.jdbc.MinLimit">1</entry>

c) The max is what it sounds like the most connections that will be allowed.  For a system with high concurrent or some long running pages this will need to be increased.  The effect if not is that user #11 ( when set to 10 ) will be watching a browser spin.  Then the training we all have that the web is fast kicks in, the user cancels the page, and requests again thinking something was wrong and surly it'll be faster the next time.  That causes the next request.  Pretty soon there's a lot of queue requests waiting.  That all turns into phone calls/ emails to the sys admin , dba , and developers that the app is slow or broken.
    Talking to one user, he upped this to 100 and it made a huge difference no more queuing.  Another had to go well over 100 due to a combination of slow queries and high concurrency.  There's not 1 setting that covers everything.

<entry key="apex.jdbc.MaxLimit">10</entry>

d) InactivityTimeout will idle the connection pool back down to the minLimit over time as the connections are idle for the value provided.
<entry key="apex.jdbc.InactivityTimeout">1800</entry>

e) This one covers in case the connection gets lost due to anything at all.  If the connection pool doesn't get the connection back for some reason for this amount of time and is idle for this time, it will be reclaimed automatically
<entry key="apex.jdbc.AbandonedConnectionTimeout">900</entry>

f) Lastly for jdbc, this is how many request a connection will service before it is closed and new one opened.
<entry key="apex.jdbc.MaxConnectionReuseCount">50000</entry>

3) For an APEX install, the security function should always be set. This blocks non-apex procedures.  These could be procedures that happen to be granted to public by accident or intentionally.

<entry key="">wwv_flow_epg_include_modules.authorize</entry>