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@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
> > > >On Behalf Of Nick Gabrielatos
> > > >Sent: Friday, March 12, 2004 10:39 AM
> > > >To: oracle-l@xxxxxxxxxxxxx
> > > >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
---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------
