Re: Login Triggers + Sys_Context

Which version are you on?

I think it was even 9.2 where sys_context did a select from dual when you assigned it's output to a variable in plsql (for example v := sys_context('USERENV', 'SESSIONID')). Reading from dual isn't normally causing a disk read (after first scan, and if buffer cache is properly configured), but if reading is done extremely often, then the LIOs may become a performance problem.

Did this sheet, the developer gave you, include some test results which showed that in your environment sys_context causes disk reads?

Tanel

---
Saatja: Maryann Atkinson <maryann_30_at_yahoo.com>
Kuupäev: 23.03.2004 4:35:11
---


> 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

> 

> ----------------------------------------------------------------

> 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

> -----------------------------------------------------------------

> 

> 


----------------------------------------------------------------
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
-----------------------------------------------------------------