I'm going to look into the driver detection Monday and see if I can
determine why the driver isn't recognized, if I can articulate the problem
I'm create a bug.
We're using the stock ojdbc7.jar Oracle thin driver so I'm not aware of any
reason why detection should not work.
I found the resource folder with the driver-named files that refer to the
correct resource adapter class but I haven't found where the correct
filename is generated from the driver class. I'm assuming it's reflection
somewhere.
On Fri, Feb 2, 2018 at 9:15 PM, Tim Bain <tbai...@gmail.com> wrote:
> I'm glad you found a solution (one that's already in the codebase, even).
>
> Is the driver one that we should be recognizing as an Oracle driver? Should
> we be writing a bug against the fact that we didn't recognize the driver?
> Or are you using a driver that's custom or exotic in some way?
>
> Tim
>
> On Feb 2, 2018 12:45 PM, "Nathan Wray" <nw...@detroitsci.com> wrote:
>
> > To wrap this up, this issue is known and has been fixed via the
> > OracleJDBCAdapter class.
> >
> > The problem we're seeing is due to AMQ not recognizing our driver as an
> > Oracle driver, which is not unheard of.
> > We should be able to force the correct adapter with something like:
> >
> >
> > <persistenceAdapter>
> >
> > <jdbcPersistenceAdapter adapter="oracleJDBCAdapter"
> > dataDirectory="${activemq.base}/activemq-data" dataSource="#oracle-ds"
> > lockKeepAlivePeriod="3000">
> >
> >
> >
> >
> > which will override limitQuery with an inner select and a ROWNUM clause:
> >
> > @Override
> > public String limitQuery(String query) {
> > return "SELECT * FROM (" + query + ") WHERE ROWNUM <= " +
> > getMaxRows();
> > }
> >
> > Thank you again Gary and Tim for your help.
> >
> > Nathan
> >
> >
> >
> > On Fri, Feb 2, 2018 at 11:29 AM, Nathan Wray <nw...@detroitsci.com>
> wrote:
> >
> > > Gary, great leads, thank you.
> > >
> > > I'm planning to verify the performance of replacing the stock query:
> > >
> > > SELECT
> > > ID
> > > ,MSG
> > > FROM
> > > table
> > > WHERE
> > > CONTAINER=?
> > > AND ID < ?
> > > AND ID > ?
> > > AND XID IS NULL
> > > ORDER BY
> > > ID
> > >
> > > With something oracle-specific that will limit the rows in the
> database,
> > > and only perform the inner query on ID.
> > > I'm assuming ID is the PK and is unique w/o container, I'll verify.
> > > (The inner query below should be less expensive as a constraint without
> > > the MSG column, it's required in order to not break the order by
> > semantic.)
> > >
> > > SELECT
> > > t1.ID
> > > ,t1.MSG
> > > FROM
> > > table t1
> > > WHERE
> > > t1.ID IN (
> > > SELECT t2.ID FROM table t2 WHERE t2.CONTAINER=? t2.ID < ? AND
> > > t2.ID > ? AND t2.XID IS NULL ORDER BY t2.ID
> > > )
> > > AND ROWNUM < maxRows
> > >
> > > BR
> > > Nathan
> > >
> > >
> > >
> > >
> > > On Fri, Feb 2, 2018 at 10:09 AM, Gary Tully <gary.tu...@gmail.com>
> > wrote:
> > >
> > >> there was some work on limiting queries:
> > >> https://issues.apache.org/jira/browse/AMQ-6049
> > >>
> > >> also - the statements can be configured - so you can provide your own
> > >> statement via configuration.
> > >>
> > >> http://activemq.2283324.n4.nabble.com/Statements-in-Activemq
> > >> -Xml-Jdbcpersistence-adapter-td4668983.html
> > >>
> > >> On Fri, 2 Feb 2018 at 14:27 Nathan Wray <nw...@detroitsci.com> wrote:
> > >>
> > >> > Tim, thanks for writing.
> > >> >
> > >> > Does AMQ support DB-speciifc queries? I could likely write the
> patch
> > >> > myself if pointed in the right direction.
> > >> >
> > >> > DefaultJDBCAdapter.doRecoverNextMessages looks like a method that
> > >> would be
> > >> > called at start-up, can you verify?
> > >> > Or, under what circumstances is that invoked?
> > >> >
> > >> > The default install had an index on ID; we added an index on
> Container
> > >> plus
> > >> > ID that changed the plan:
> > >> >
> > >> >
> > >> > *Temp Space*
> > >> >
> > >> > *Plan*
> > >> >
> > >> > *SELECT STATEMENT *ALL_ROWS Cost: 4
> > >> >
> > >> > *3 *
> > >> >
> > >> >
> > >> >
> > >> > *3 **FILTER *
> > >> >
> > >> > *2 *
> > >> >
> > >> >
> > >> >
> > >> >
> > >> >
> > >> > *2 **TABLE ACCESS BY INDEX ROWID TABLE *ACTIVEMQ.ACTIVEMQ_MSGS
> Cost: 4
> > >> > Bytes: 1,802 Cardinality: 1
> > >> >
> > >> > *1 *
> > >> >
> > >> >
> > >> >
> > >> >
> > >> >
> > >> >
> > >> >
> > >> > *1 **INDEX RANGE SCAN INDEX (UNIQUE) *ACTIVEMQ.SYS_C0010280 Cost: 3
> > >> > Cardinality: 1
> > >> >
> > >> >
> > >> >
> > >> > *Temp Space*
> > >> >
> > >> > *Plan*
> > >> >
> > >> > *SELECT STATEMENT *ALL_ROWS Cost: 7
> > >> >
> > >> > *4 *
> > >> >
> > >> >
> > >> >
> > >> > *4 **SORT ORDER BY *Cost: 7 Bytes: 7,208 Cardinality: 4
> > >> >
> > >> > *3 *
> > >> >
> > >> >
> > >> >
> > >> >
> > >> >
> > >> > *3 **FILTER *
> > >> >
> > >> > *2 *
> > >> >
> > >> >
> > >> >
> > >> >
> > >> >
> > >> >
> > >> >
> > >> > *2 **TABLE ACCESS BY INDEX ROWID BATCHED TABLE
> *ACTIVEMQ.ACTIVEMQ_MSGS
> > >> > Cost:
> > >> > 6 Bytes: 7,208 Cardinality: 4
> > >> >
> > >> > *1 *
> > >> >
> > >> >
> > >> >
> > >> >
> > >> >
> > >> >
> > >> >
> > >> >
> > >> >
> > >> > *1 **INDEX RANGE SCAN INDEX *ACTIVEMQ.ACTIVEMQ_MSGS_CIDX Cost: 4
> > >> > Cardinality: 4
> > >> >
> > >> >
> > >> >
> > >> > Thank you
> > >> > Nathan
> > >> >
> > >> >
> > >> >
> > >> >
> > >> > On Fri, Feb 2, 2018 at 9:08 AM, Tim Bain <tb...@alumni.duke.edu>
> > wrote:
> > >> >
> > >> > > I've never heard of this particular problem being reported, but it
> > >> should
> > >> > > be fairly straightforward to turn it into a top-N query by doing a
> > >> > > sub-select as described in
> > >> > > http://www.oracle.com/technetwork/issue-archive/
> > >> > > 2006/06-sep/o56asktom-086197.html.
> > >> > > Would you please submit an enhancement request in JIRA for this
> > >> change?
> > >> > >
> > >> > > BTW, have you had your DBA ensure that an optimal index is in
> place
> > on
> > >> > the
> > >> > > table and that it's in good repair (Oracle indexes get cluttered
> > with
> > >> > > deleted rows over time when your use pattern is frequent
> insertions
> > >> and
> > >> > > deletions, so semi-regular rebuilds may be necessary.)
> > >> > >
> > >> > > Tim
> > >> > >
> > >> > > On Feb 1, 2018 10:00 AM, "nathanwray" <nw...@detroitsci.com>
> wrote:
> > >> > >
> > >> > > > We recently had over 1M messages back up in a container.
> > >> > > >
> > >> > > > For reasons that aren't completely clear, AMQ executed the query
> > >> found
> > >> > in
> > >> > > > Statements.getFindNextMessagesStatement mid-morning:
> > >> > > >
> > >> > > > SELECT ID, MSG
> > >> > > > FROM activemq.ACTIVEMQ_MSGS
> > >> > > > WHERE CONTAINER = :1 AND ID > :2 AND ID < :3 AND XID IS NULL
> > >> > > > ORDER BY ID;
> > >> > > >
> > >> > > > With the parameters:
> > >> > > > 1 queue://generic_createContract
> > >> > > > 2 -1
> > >> > > > 3 183893253
> > >> > > >
> > >> > > > This call appears to originate from
> > >> > > > DefaultJDBCAdapter.doRecoverNextMessages, which calls
> setMaxRows
> > >> on the
> > >> > > > PreparedStatement with (apparently) 200 rows.
> > >> > > >
> > >> > > > However it appears the Oracle thin driver makes no attempt to
> > limit
> > >> the
> > >> > > > result set based on the max rows value; instead it selects and
> > sorts
> > >> > the
> > >> > > > entire 1M row plus values and makes them available to the
> client,
> > >> which
> > >> > > > stops creating objects after reading the first 200.
> > >> > > >
> > >> > > > The net result was that our Oracle server spiked to 90%+ on this
> > one
> > >> > > query
> > >> > > > and caused a complete AMQ failure in production, knocking over a
> > >> number
> > >> > > of
> > >> > > > critical systems.
> > >> > > >
> > >> > > > We wound up stopping all of our brokers and manually dropping
> the
> > >> > > messages
> > >> > > > in this container in order to resolve the 3 hour outage.
> > >> > > >
> > >> > > > Is there a known issue with using Oracle persistence with AMQ
> that
> > >> > > > precludes
> > >> > > > having more than thousands of messages? If the "max rows"
> > approach
> > >> > with
> > >> > > > the
> > >> > > > thin driver works as it appears to, we can't be the first to
> have
> > >> seen
> > >> > > this
> > >> > > > problem.
> > >> > > >
> > >> > > > Any insight would be appreciated.
> > >> > > >
> > >> > > > Thank you
> > >> > > > Nathan
> > >> > > >
> > >> > > >
> > >> > > >
> > >> > > >
> > >> > > >
> > >> > > > --
> > >> > > > Sent from: http://activemq.2283324.n4.nabble.com/ActiveMQ-User-
> > >> > > > f2341805.html
> > >> > > >
> > >> > >
> > >> >
> > >>
> > >
> > >
> >
>
