Re: Use of context
- From: "Gints Plivna" <gints.plivna@xxxxxxxxx>
- To: Louis.Brouillette@xxxxxxx
- Date: Thu, 7 Dec 2006 17:37:46 +0200
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@xxxxxxx>:
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@xxxxxxx
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
