Re: Login Triggers + Sys

From: Maryann Atkinson <maryann_30_at_yahoo.com>
Date: Mon, 22 Mar 2004 16:54:34 -0500
Message-Id: <5.2.1.1.2.20040322165212.040f4e20@pop.netaddress.com>

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

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
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 - 17:21:13 CST

Re: Login Triggers + Sys_Context