Re: Connection pool issues
Date: Wed, 4 Mar 2015 06:05:55 -0700
Message-ID: <CAJzM94CuPkTaqzbvD2cid0EHV-g12cNG29hDk6U2ROeKokynXg_at_mail.gmail.com>
Quick update. We completed creating the standby in the wee hours Sunday morning. Our connection pool problem is persisting. The connection pool allows only 9 connections, a number determined long before I came on board. I asked about the feasibility/impact of increasing the pool slightly. We decided it was best to keep it at its current level. We have identified a specific query, variations of the predicates, that can run more than 24 hours without finishing which, of course, means the connections are not released. According to the developer, this is a known problem with this database/query. His recommendation was to limit the search criteria and the number of people doing the search--this query is run by employees, not customers. In the meantime, sql analysis suggested a new index which I have put into the development environment for testing. I also will be looking at the query for additional optimization opportunities.
Thank you all for your suggestions.
Sandy
On Mon, Mar 2, 2015 at 2:38 AM, Osborne, Chris <Chris.Osborne_at_bskyb.com> wrote:
>
>
> Hi, How big are you connection pools at the moment?
>
>
>
> They could be, counterintuitively, too big at the moment.
>
>
>
> Chris
>
>
>
>
>
> *Christopher Osborne*
>
> Lead Technical Specialist, Performance Engineering
>
> *British Sky Broadcasting*
>
> Email:chris.osborne_at_bskyb.com
>
> Desk: +44 1506 325069 | *Mobile: +44 7720 308941
> <%2B44%207720%20308941>*
>
> *Please note new Mobile number. *
>
>
>
> [image: oebanner4ps_gap2_620]
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Andrew Kerber
> *Sent:* 27 February 2015 01:26
> *To:* sbecker6925_at_gmail.com
> *Cc:* MARK BRINSMEAD; oracle-l
> *Subject:* Re: Connection pool issues
>
>
>
> It is extremely unlikely to be an issue with the standby unless you are
> copying from active. If you are, you could be overloading network and disk.
> Of course, that's just a shot from the hip with very little information.
>
> Sent from my iPhone
>
>
> On Feb 26, 2015, at 2:51 PM, Sandra Becker <sbecker6925_at_gmail.com> wrote:
>
> We are discussing increasing the connection pool. Most everyone is
> concerned that creating a standby from the active 19T database is causing
> the application queries to run longer. Looking into it, but this is our
> 5th try and no one reported any issues on the previous 4. The recovery
> phase always failed. Still trying to figure that out, but that's another
> discussion.
>
> Sandy
>
>
>
> On Thu, Feb 26, 2015 at 12:05 PM, MARK BRINSMEAD <mark.brinsmead_at_gmail.com>
> wrote:
>
> End users (and application developers) are almost always "positive"
> that it is a database issue. :-)
>
> And it might be, too. Its just way too soon to know.
>
> In the meantime, if the database server is not busting at the seams, you
> might want to consider configuring your app servers to allow the connection
> pools to grow a little larger. The REAL error is that the app server is
> not allowing the app to open a new connection.
>
>
>
> On Thu, Feb 26, 2015 at 1:43 PM, Sandra Becker <sbecker6925_at_gmail.com>
> wrote:
>
> Thank you. Definitely some things I can look at. I was told there were
> no changes to the code or app servers. Of course, the end user is positive
> it's a database issue. I can definitely do AWR before/after. We did have
> some network blips earlier in the week, but nothing today.
>
> Sandy
>
>
>
> On Thu, Feb 26, 2015 at 11:39 AM, MARK BRINSMEAD <mark.brinsmead_at_gmail.com>
> wrote:
>
> It sounds to me like you should be as interested (or more) in
> connected sessions (as reported by gv$session).
>
> There is no need for *anything* to have changed in the database -- it is
> entirely possible that a change in the application code, or even in the
> application server can cause the problems you are encountering.
>
> Possible issues would be:
>
> (*) Queries running longer than they used to, meaning that applications
> need to hold connections from the pool open longer. There is no reason to
> expect that these would necessarily appear as "longops", though.
>
> (*) Application threads not releasing connections when (as soon as) they
> are done with them.
>
> (*) More application threads running that before, perhaps due to increased
> user population or other factors.
>
> (*) JDBC connection pool management, causing the maximum number of
> available connections to be fewer, or causing "idle" connections to be
> returned to the pool more slowly.
>
> (*) Changes to limits on session-idle-time, forcing idle sessions to
> disconnect from the database.
>
> I bet I could think of at least 5 more causes if I thought about it for
> another 10 minutes. I haven't even considered networking problems yet!
>
> Rebuilding indexes would cause execution plans to be invalidated, and
> might cause new plans to be chosen. It is possible that you have seen an
> adverse change in plans on a commonly run query. (Check AWR and Statspack
> for top queries by elapsed time, before and after the "event".) But its
> just as possible that the problem has nothing to do with the database at
> all.
>
>
>
> On Thu, Feb 26, 2015 at 12:30 PM, Sandra Becker <sbecker6925_at_gmail.com>
> wrote:
>
> Oracle - EE 11.2.0.2
>
> This week users started having issues connecting to a database. Error
> below:
>
> Could not retrieve documents from the docStore: exception was
> com.ghx.docstore.DocStoreException: Cannot get Connection to: db_arch
>
>
>
> In the log someone also saw
>
> Caused by: oracle.ucp.UniversalConnectionPoolException: All connections
> in the Universal Connection Pool are in use
>
>
>
> Obviously, something isn't releasing the connection back to the pool.
> I've queried longops to no avail. I've seen sessions using a tremendous
> amount of CPU or I/O. When those sessions were killed, we see an immediate
> uptick in processing. The only change to the database occurred last
> Friday--we removed the oldest year of data from partitioned tables and
> rebuilt unusable indexes. No code releases were done.
>
> My question: Is there anything I can query to see what session(s) are
> causing the problem other than what I'm already doing?
>
>
> Any suggestions are appreciated.
>
> --
>
> Sandy
> GHX
>
>
>
>
>
> --
>
> Sandy
> GHX
>
>
>
>
>
>
> --
>
> Sandy
> GHX
>
> Information in this email including any attachments may be privileged,
> confidential and is intended exclusively for the addressee. The views
> expressed may not be official policy, but the personal views of the
> originator. If you have received it in error, please notify the sender by
> return e-mail and delete it from your system. You should not reproduce,
> distribute, store, retransmit, use or disclose its contents to anyone.
> Please note we reserve the right to monitor all e-mail communication
> through our internal and external networks. SKY and the SKY marks are
> trademarks of Sky plc and Sky International AG and are used under licence.
> Sky UK Limited (Registration No. 2906991), Sky-In-Home Service Limited
> (Registration No. 2067075) and Sky Subscribers Services Limited
> (Registration No. 2340150) are direct or indirect subsidiaries of Sky plc
> (Registration No. 2247735). All of the companies mentioned in this
> paragraph are incorporated in England and Wales and share the same
> registered office at Grant Way, Isleworth, Middlesex TW7 5QD.
>
-- Sandy GHXReceived on Wed Mar 04 2015 - 14:05:55 CET
-- http://www.freelists.org/webpage/oracle-l