| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Login Triggers + Sys_Context
At 07:30 PM 3/22/2004, you wrote:
>Yep, it works fine. And the advantage of having context variables is that
>they can be accessible globally in all sessions in an instence. Tanel.
Thanks, I appreciate that. One of the developers gave us a sheet in a recent meeting claiming login triggers working with sys_context area perform disk reads, but I didnt really believe it, and I dont think Oracle would have produced anything like that, its beyond the whole point.
Thanks again,
maa
> > Remember this?
> >
> > Instead of using packaged variables, has anyone tried anything
> > close to this BUT with utilizing the Sys_Context area instead?
> >
> > Thanks,
> > maa
> >
> >
> >
> > At 02:16 PM 3/12/2004, you wrote:
> > >Maryann,
> > >Then what you want is possibly packaged variables. You must create a
> > >package holding these variables, plus a function to get their values
> > >from the DB which will be called only once, possibly from the
> > >initialisation section of the package. You can call a function from
> > the
> > >package in the initialization trigger, but this isn't mandatory. In
> > >fact, the very first reference to something in the package will cause
> > >the initialisation section to fire.
> > >
> > > That said, I doubt that caching such information will do much to
> > >improve a single query of death which run for 6 hours. I'd look
> > >elsewhere. Those techniques are useful for avoiding 600,000 select
> > >sys_context('USERENV', 'whatever') from dual per hour.
> > >
> > >SF
> > >
> > >
> > >Maryann Atkinson wrote:
> > > >
> > > > The queries we have which are slow, have some where clauses,
> > > > among others that is, to which we embed user information,
> > > > like privs, level, status etc, ie most likely personal info for
> > each user.
> > > >
> > > > What I was hoping is to fire a login trigger that would save this
> > info
> > > > in memory somehow, and then the queries could take it from there
> > > > instead of issuing other queries to find it...
> > > >
> > > > But thanks for the materialized views examples,
> > > > we are going to have to think and perhaps compare
> > > > before we figure it out...
> > > >
> > > > Thanks,
> > > > maa
> > > >
> > > > At 12:59 PM 3/12/2004, you wrote:
> > > >
> > > > >Materialized views are much, much easier to deal with in this
> > > > >situation. In addition to the fact that they're available to
> > everyone all
> > > > >the time, they get refreshed automatically, you don't have to
> > teach users
> > > > >where to go to get the data, and you don't execute huge queries
> > when you
> > > > >don't need the results.
> > > > >
> > > > >
> > > > >Justin Cave
> > > > >Distributed Database Consulting, Inc.
> > > > >http://www.ddbcinc.com/askDDBC
> > > > >
> > > > >-----Original Message-----
> > > > >From: oracle-l-bounce_at_freelists.org
> > [mailto:oracle-l-bounce_at_freelists.org]
> > > > >On Behalf Of Nick Gabrielatos
> > > > >Sent: Friday, March 12, 2004 10:39 AM
> > > > >To: oracle-l_at_freelists.org
> > > > >Subject: Re: Login Triggers
> > > > >
> > > > >Thanks, that helps quite a bit.
> > > > >Let me ask you this, though,
> > > > >you think we should use cashed tables and materialized views
> > instead of
> > > > >login triggers?
> > > > >
> > > > >Wouldnt a login trigger have an advantage?
> > > > >
> > > > >thanks,
> > > > >maa
> > > > >
> > > > >At 11:43 AM 3/12/2004, you wrote:
> > > > > >Maryan, materialized views are pre-built queries whose results
> > are
> > > > > >stored in a table and refreshed periodically. Here is slightly
> > modified
> > > > > >example from the manuals:
> > > > > >
> > > > > >CREATE TABLE sales_sum_table
> > > > > > (month VARCHAR2(8), state VARCHAR2(40), sales NUMBER(10,2))
> > > > > > STORAGE ( BUFFER_POOL KEEP);
> > > > > >
> > > > > >CREATE MATERIALIZED VIEW sales_sum_table
> > > > > > ON PREBUILT TABLE
> > > > > > REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 1/(24*60)
> > > > > > ENABLE QUERY REWRITE
> > > > > > AS SELECT t.calendar_month_desc AS month,
> > > > > > c.cust_state_province AS state,
> > > > > > SUM(s.amount_sold) AS sales
> > > > > > FROM times t, customers c, sales s
> > > > > > WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id
> > > > > > GROUP BY t.calendar_month_desc, c.cust_state_province
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Mon Mar 22 2004 - 20:33:44 CST
 |
 |