Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Use of context

Re: Use of context

From: Gints Plivna <>
Date: Thu, 7 Dec 2006 17:37:46 +0200
Message-ID: <>

Even using tables you can minimize consistent gets to at least 3 using simple index on table, you can minimize it down to 1 using IOT table, you can minimize it using single table hash clusters. About all these you can find info in Oracle docs as well as a recent OOW 2006 presetnation can be helpful -

(if the link is broken then go to and serach for session id S282983)

I haven't used contexts for such a feature, but I've used global package variables that are initialized in package body. Whenever the user calls package for the first time variable is initialized, but all subsequent calls use already intialized variables without running queries to tables.
See "The initialization part of a package plays a minor role because, unlike subprograms, a package cannot be called or passed parameters. As a result, the initialization part of a package is run only once, the first time you reference the package.

Remember, if a package spec declares only types, constants, variables, exceptions, and call specs, the package body is unnecessary. However, the body can still be used to initialize items declared in the package spec."

The problem of course remains if someone changes the already read table values, therefore I always use package variables for values that almost never changes, and if they are changed then simply webserver is restarted or all sessions killed :)))

And yes, it is much faster than querying table each time.

Gints Plivna

2006/12/7, Louis BROUILLETTE <>:
> We have a 1 record table with application parameters. Some of these
> parameters are widely used by applications (sometimes 2 millions of hits
> per hour). Would it be a good idea to load these parameters in a context
> variable (the context being defined as "accessed globally") at database
> startup (a trigger would change the value whenever necessary but that's
> typically 3 times a year) and read the parameter value from that context
> instead of reading the table.
> I ran some tests in sqlplus with "set autotrace on statistics". When I
> read the table I get "7 consistent gets" and when I read the context I get
> none. It looks pretty good. Any gotchas ???
> Louis Brouillette
> Analyste en informatique (DBA)
> Universite du Quebec a Trois-Rivieres
> Tel: (819) 376-5011 ext. 2435
> Email:
> --

Received on Thu Dec 07 2006 - 09:37:46 CST

Original text of this message