Re: Caching within packages..

andreyNSPAM_at_bookexchange.net (NetComrade) wrote in message news:<3fd64fd1.3892236_at_nyc.news.speakeasy.net>...

> Either one of o'reilly books, or some other book, talked about how if
> you declare a function/procedure as part of a function, you can cache
> objects in an array for the life of a session, so u don't have to make
> a trip to a table again.. anyone remembers which one?
> 
> Thanks
> .......
> We use Oracle 8.1.7.4 on Solaris 2.7 boxes
> remove NSPAM to email

I saw a presentation on this at IOUG-A 2001. I believe the presenter was Kevin Toepke, who presents regularly at OOUG.
It was titled something like "an application tuner's bag-of-tricks". I think that I have the CDROM here, still, yes. It seems only fair to ask him if I can send you a copy, unless we can find it on the web somewhere.

It seems like a good idea, the use of lookup tables stored in memory.

I'm considering it where it could make sense, but for lookup data that is stored in read only tablespaces that are only changed when the application version is updated, whereby the instance is terminated and the database opened restricted.

The real question is, how many logical IOs will this remove, as opposed to how much memory is occupies per session. After that is answered, I'll consider using it in production.

Its up against storing such data in hash clusters, may the best method prevail.

wow - I made the same reply over a year ago, but still haven't tried it out yet.
guess its about time.

Pd

Paul Drake
Mar 26 2002 19:59 Dave wrote:

> I have a base table (BASE) that is partitioned on a date column and
has 250,000,000
> rows. BASE has 12 code columns, each matching to a Lookup table
(LKUP1-12).
> LKUP1-12 tables have the code column and a matching description. I
have a view
> (VIEW_ALL) that joins BASE to LKUP1-12, to pull in the code column
descriptions,
> instead of storing 12x50 byte fields across 250mm rows. The code
columns may or may
> not be NULL, so each join in VIEW_ALL is an outer-join.
>
> My problem is VIEW_ALL is insanely slow. An explain plan shows a
"hash outer join"
> for each lookup back to the driving table, BASE. This means 12 HJs!
If I create a
> unique index on all the LKUP1-12 tables, I get a plan using "nested
loops outer"
> for all 12 tables, again scanning BASE 12 times, once per join.
>
> Is there any way to join all 12 at once, scanning BASE only once? I
thought the
> AND_EQUAL hint would work, listing the indexes from LKUP1-12 but it
was ignored.
>

At a presentation at IOUG-A 2001, someone mentioned loading your lookup tables into
package variables (indexed) so that you could use a function to return the values,
without using gets. Sounds interesting.

I don't have my conference CD here, but his name is Kevin Toepke, and he belongs to
the Ohio Oracle User's Group in Columbus. Check out http://www.ooug.org for contact
info. It should also be available through the ioug website.

hth,

Paul Received on Fri Dec 12 2003 - 01:11:49 CST