Performance: "pl/sql" vs. "OS b-tree files"

Thread view:

Jos? Antonio Cuesta - 29 Mar 2004 16:31 GMT

Hi,

we're developing an application in which performance is a very important
issue.
Database is Oracle 9.2, application language is Java 1.4.2., OS is Solaris 8
(RAM 8GB, 4x750Mhz CPU).

The process needs to query for every object several times (around 10) the
same configuration tables, which are stored in Oracle. Every process has
millions of objects, so the number of consults made to Oracle would be
rather high.
No INSERT statements would be made, only SELECTs.

The two alternatives are:

* wrap the SQL statements in pl/sql packages to minimize the number of
SELECT statements made to Oracle. The packages would be called using JDBC.
The config tables would be set as CACHED, and perhaps index-organized.

* dump all the configuration tables in a OS file and, using Java classes
based on B-Trees, build a B-Tree file and make all the searches using Java
methods against this B-tree file. This file would be cached in memory by the
OS.

It is supposed that the Java methods querying in-memory data would beat the
pl/sql procedures querying the Oracle tables... Does it worth to dump all
the information from Oracle to a B-Tree file, having in mind that Oracle
uses the same technique to build indexes? Which solution would be more
efficient?

Thank you very much for your comments. Any hint would be welcomed.

Reply to this Message

Shuaib Kauchali - 29 Mar 2004 20:11 GMT

"Jos? Antonio Cuesta" <joseantonio.cuesta@ono.es> wrote in message

> It is supposed that the Java methods querying in-memory data would beat the
> pl/sql procedures querying the Oracle tables... Does it worth to dump all
> the information from Oracle to a B-Tree file, having in mind that Oracle
> uses the same technique to build indexes? Which solution would be more
> efficient?

If the SQL statements are identical then it may be a good idea to use
middle-tier caching frameworks like OSCACHE (www.opensymphony.org).

Regarding the B-Tree and indexing stuff you talked about, I don't know much,
but I'd avoid the temptation to re-invent another rdbms/SQL engine like
Oracle. :-)

Reply to this Message

Manuel J. Goyenechea - 30 Mar 2004 08:24 GMT

Jos?

You may want to take a look at Servertec Persistent Object Store,
www.servertec.com. It is very easy to use and can handle very large number
of objects very efficiently. It should be able to significantly outperform
any RDBMS.

Manny

Reply to this Message

Silvio Bierman - 30 Mar 2004 09:27 GMT

> Hi,
>
[quoted text clipped - 27 lines]
>
> Thank you very much for your comments. Any hint would be welcomed.

You would be reimplementing something Oracle has already done and probably
in a superiour way. Why do you think that OS file caching will help your
scheme more than it does Oracle? I think it is a bad idea, stick with Oracle
and optimize to its capabilities.

Silvio Bierman

Reply to this Message

Gregory Burd - 02 Apr 2004 21:56 GMT

José,

You might consider Berkeley DB Java Edition (JE). Everything that
you've come to appreciate about Berkeley DB (transactions,
scaleability, concurrency, speed, etc) is now available in a pure Java
implementation. We have many customers doing exactly what you
describe, using Berkeley DB as a front end cache to a backend RDBMS
such as Oracle.

http://www.sleepycat.com/products/je.shtml

-greg

_____________________________________________________________________

Gregory Burd
Product Manager gburd@sleepycat.com
Sleepycat Software, Inc. http://www.sleepycat.com/

Reply to this Message

Performance: "pl/sql" vs. "OS b-tree files"

Free Magazines