The kernel caching of statements and their query plans is how Oracle works,
no need to turn it on. There is tuning involved in how much space you
allocate to such things but it is not usually the parameter that needs to be
tweaked to speed things up.
The reason you want to use prepared statements is that the Oracle cache
contains the SQL statement and a hash code and it'll try and match an
incoming statement against the cache via hashCode and then an exact match
including case and spacing. Obviously, if you've got "... X = 7 ..." and
"... X = 11 ..." they won't match and will be considered two unique
statements. Something like "... X = ? ..." will of course match independent
of the value of the host variable (?).
Oracle stores the execution plan in the cache so it doesn't need to parse
the statement and develop an execution plan if there is a match. This
shortens the code path within Oracle quite a lot.
I don't believe that any other databases do this (for good or for bad)
though I don't know enough about their internals to judge. Oracle exposes
information on this cache via some V$ views, specifically v$sql_area if my
memory serves me right.
Cheers
-----Original Message-----
From: Bill Burke
To: [EMAIL PROTECTED]
Sent: 5/7/01 5:35 PM
Subject: RE: [JBoss-dev] TODO: JBossCMP 1.1 FAST!
> -----Original Message-----
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Jay
> Walters
> Sent: Monday, May 07, 2001 4:43 PM
> To: '[EMAIL PROTECTED]'
> Subject: RE: [JBoss-dev] TODO: JBossCMP 1.1 FAST!
>
>
> We've been down this path before. It is clearly not advisable to be
> updating the primary keys of CMP objects, I consider that a JAWS
problem
> rather than a good feature. There are many people who consider the
change
> of primary key to be cool, unfortunately for them (and good for
> us) EJB 2.0
> CMP currently states that you cannot change the primary key of a
> CMP entity
> bean. This is not enforcable within the EJB 1.1 specification so I'm
not
> sure what the answer is here, maybe an option in jaws.xml to let
> one disable
> update of primary key with the promise to be good...
>
The point I'm trying to make is that with CMPs current state, we should
not
be encouraging people to have tuned-updates turned off because they will
most certainly run into deadlock problems if they have more than one
concurrent user.
> Oracle caches prepared statements in the kernel of the database and
their
> execution is dramatically more efficient than the execution of simple
> statements. This is true across all Oracle APIs, sql*plus, OCI,
Pro*C,
> JDBC... As an Oracle consultant this was one of the first things I
always
> checked when a customer had a performance problem. They have also
added
> caching of JDBC prepared statements into the latest JDBC 8.1.7 driver
so
> there is a need to be able to turn this feature of Minerva off.
>
Does the Oracle database kernel automagically cache prepared statements,
or
is this something you have to turn on?
What about the oracle 8.1.7 driver, does it implicitly cache prepared
statements or do you have to explicitly turn this on? If not , Minerva
will
need some tweaking because there is currently no hooks to do this.
Please
correct me if I'm wrong about Minerva, because I'm not 100% sure... TIA.
> The crucial nut to understand here is that something like 75-85%
> of the time
> spent processing a client request is usually spent in communications
with
> the database. Changing Minerva to avoid allocating a new object, or
> updating 12 fields instead of 13 fields is like putting buckets of
water
> into the ocean to raise the water level. You need to melt some
icebergs
> here.
>
Yeah. That's why I'm hoping Dan implements his suggested extension to
finders so that they select entire rows rather than just the primary
keys,
thus avoiding multiple unnecessary reads.
Regards,
Bill
> Now, for every other database in the world all this stuff is
different, so
> it's not like one could lock all this stuff down for the way Oracle
works.
>
> Cheers
>
> -----Original Message-----
> From: Bill Burke [mailto:[EMAIL PROTECTED]]
> Sent: Monday, May 07, 2001 4:30 PM
> To: [EMAIL PROTECTED]
> Subject: RE: [JBoss-dev] TODO: JBossCMP 1.1 FAST!
>
>
> BTW, running with tuned updates off is really, really, bad. With
tuned
> updates off, CMP will update your primary keys as well. Updating
primary
> keys is a crazy thing to do because the database needs to obtain
> write-locks
> on any indexes/constraints attached to that row/table. This can
> cause major
> deadlock problems fairly easily with multiple concurrent users. Has
the
> update of primary keys in CMP been removed? I know there was some
talk
> about it.
>
> Also, if you're using Minerva, Minerva has PreparedStatement
> caching and CMP
> uses PreparedStatements.
>
> Regards,
> Bill
>
> -----Original Message-----
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Jay
> Walters
> Sent: Monday, May 07, 2001 8:06 AM
> To: 'danch '; '[EMAIL PROTECTED] '
> Subject: RE: [JBoss-dev] TODO: JBossCMP 1.1 FAST!
>
>
> I'm not sure what this Oracle raw thing is, but I am pretty sure you
don't
> want to be doing it. Use the indexes on primary key it'll be plenty
fast.
>
> Just make sure tuned updates can be turned off since with Oracle it'll
> probably run faster with tuned updates off because the database won't
have
> to be parsing all those odd statements. Of course for those people
whoses
> rows are 32K long YMMV. By the way, does it use prepared
> statements either
> way?
>
> Where in the code does this select version thing go? Is this just
done to
> see if you've got the right version in cache?
>
> Cheers
>
> -----Original Message-----
> From: danch
> To: [EMAIL PROTECTED]
> Sent: 5/7/01 1:01 AM
> Subject: Re: [JBoss-dev] TODO: JBossCMP 1.1 FAST!
>
> I was about to tackle the most significant performance related problem
> in JAWS (see the discussion and feature request on "CMP and finders:
> optimized" and "optimize collection finders", respectively)
>
> I've been building database applications for quite a while now (damn
> near the only thing i've ever done, software-wise) and am willing to
> take this task on.
>
> More comments follow in-line.
>
> marc fleury wrote:
>
> > OK,
> >
> > I now believe that I am staring at a simple fact. The PERFORMANCE
IN
> > CONTAINERS IS BOLONEY!
> >
> > Our container can run in 0.05ms (yes **m**s) and still excercise 80
of
> its
> > features. what costs a lot is **serialization** and we find that in
> JDBC
> > calls and network calls.
> >
> > It appears that our JDBC calls are not optimized at all. We know it
> is just
> > JawsCMP but it is what people really look at when they work on
JBoss.
> There
> > are tons of optimizations such as using "raw" ids in the database
> (Oracle).
> > The way we would work this is that we could then map the primary
keys
> to the
> > raw ID (simple map) and the lookups would be kick ass fast.
>
> As a first step here, I'll make the 'command factory' used by JAWS for
a
>
> container configurable. This will make it easier to make DBMS specific
> implementations of certain commands.
>
>
> >
> > I believe we also implement correctly the "isModified", but we could
> really
> > do a "getModified" that would return the names of the modified
fields.
> We
> > could then bypass the "tuned" updates.
>
> Personally, I'd rather keep the 'tuned' updates code, since it
requires
> no-more container specific code. What's done now mirrors the behavior
of
>
> Borland's CMP implementation, which is a very developer-friendly
system.
>
> What do you see that's bad in the tuned updates?
>
>
> >
> > Finally the load today is very blind and very stupid. We just load
> > everyone. A simple implementation of the "versioning" pattern in
the
> > database (where we add a field that is the version of the record)
> enables us
> > to get the right version in little time and saves us time in loading
> since
> > we can just "select version where rawID" for example :)
>
> This is a fine optional feature. I've implemented this in BMP beans
and
> it shouldn't be too hard to add.
>
>
> >
> > If you are interested in leading this effort and have serious db
> background
> > please let the board know
> >
> > marc
> >
> >
> > _________________
> > Marc Fleury, Ph.D
> > [EMAIL PROTECTED]
> > _________________
> >
> >
> > _______________________________________________
> > Jboss-development mailing list
> > [EMAIL PROTECTED]
> > http://lists.sourceforge.net/lists/listinfo/jboss-development
>
>
>
> _______________________________________________
> Jboss-development mailing list
> [EMAIL PROTECTED]
> http://lists.sourceforge.net/lists/listinfo/jboss-development
>
> _______________________________________________
> Jboss-development mailing list
> [EMAIL PROTECTED]
> http://lists.sourceforge.net/lists/listinfo/jboss-development
>
>
>
> _______________________________________________
> Jboss-development mailing list
> [EMAIL PROTECTED]
> http://lists.sourceforge.net/lists/listinfo/jboss-development
>
> _______________________________________________
> Jboss-development mailing list
> [EMAIL PROTECTED]
> http://lists.sourceforge.net/lists/listinfo/jboss-development
>
_______________________________________________
Jboss-development mailing list
[EMAIL PROTECTED]
http://lists.sourceforge.net/lists/listinfo/jboss-development
_______________________________________________
Jboss-development mailing list
[EMAIL PROTECTED]
http://lists.sourceforge.net/lists/listinfo/jboss-development
