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

[Ignacio]

Hi,

we're developing an application in which performance is a very important
issue (what a new!:)
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.

[Jim Kennedy]

"Ignacio" <barbudo@ono.com> wrote in message
news:a4U9c.1040$9c.130331@news.ono.com... Hi, we're developing an application in which performance is a very important issue (what a new!:) 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. why build t\your own. Taqke advantage of then other what you are paying
for. You can put those objects in a keep buffer pool and make sure the pool
is large enough.
Query the database.

why build tyour own. Take advantage of then other what you are paying for.
You can put those objects in a keep buffer pool and make sure the pool is
large enough. Let the database do what it does well, handle queries of
data. What is more important is to use bind variables (prepared statements)
and make 1 query and just reexecute on subsequent querirs. Much faster.
Jim

[Billy Verreynne]

"Ignacio" <barbudo@ono.com> wrote

<snipped>Any hint would be welcomed.

Get someone that *knows* Oracle. Seriously. That posting is showing a
severe lack of understanding about RDBMS in general and Oracle in
particular.

Thinking that Java knowledge can make one understand and use Oracle
correctly, is like bringing a knife to a gunfight.


--
Billy

[Noons]

'sOK. I got told today that performance in mine
was not important...

Now, THAT is new!
--
Cheers
Nuno Souto
wizofoz2k@yahoo.com.au.nospam
"Ignacio" <barbudo@ono.com> wrote in message
news:a4U9c.1040$9c.130331@news.ono.com... we're developing an application in which performance is a very important issue (what a new!:)

[Niall Litchfield]

Performance is *always* important - even if it is explicitly stated that it
isn't.

create or replace trigger perf_unimportant
after logon on database
begin
dbms_lock.sleep(3600);
end;
/

with a grant execute on the appropriate package to public should illustrate
nicely.


--
Niall Litchfield
Oracle DBA
Audit Commission UK
"Noons" <wizofoz2k@yahoo.com.au> wrote in message
news:40693774$0$16966$afc38c87@news.optusnet.com.au... 'sOK. I got told today that performance in mine was not important... Now, THAT is new! -- Cheers Nuno Souto wizofoz2k@yahoo.com.au.nospam "Ignacio" <barbudo@ono.com> wrote in message news:a4U9c.1040$9c.130331@news.ono.com... we're developing an application in which performance is a very important issue (what a new!:)