Re: Use of context

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 -
http://www28.cplan.com/cc139/catalog.jsp?ilc=139-1&ilg=english&isort_sessions=&isort_demos=&isort_exhibitors=&is=yes&ip=%3C%2Fipresentations%3E&search_sessions=yes&icriteria3=+&icriteria1=+&icriteria9=+&icriteria6=&icriteria8=&icriteria4=+&icriteria5=S282983&icriteria7=+

(if the link is broken then go to
http://www28.cplan.com/cc139/catalog.jsp 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 http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14261/packages.htm#i2416 "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
http://www.gplivna.eu

2006/12/7, Louis BROUILLETTE <Louis.Brouillette_at_uqtr.ca>:
> 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: brouille_at_uqtr.ca
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l