Re: Issues with Triggers in an Oracle RAC
- From: "Rumpi Gravenstein" <rgravens@xxxxxxxxx>
- To: "Hemant K Chitale" <hkchital@xxxxxxxxxxxxxx>
- Date: Wed, 9 Apr 2008 11:19:30 -0400
I would like to add audit triggers to populate record create user and
timestamp columns ( :new.rcd_create_user_id := USER and :new.rcd_create_dt
:= SYS_TIMESTAMP). They have an unwritten policy that states triggers
should be avoided if at all possible. I'm trying to find factual support
that their contention that triggers cause "library cache lock waits" which
in turn impact the database resulting in unacceptable production problems is
misplaced, especially for the type of audit triggers I'm considering.
On 4/9/08, Hemant K Chitale <hkchital@xxxxxxxxxxxxxx> wrote:
>
> Seems to be F.U.D .
>
> Many purists prefer to avoid triggers altogether -- or minimise the use of
> triggers.
> RAC performance issues come from non-scalable contention which is
> aggravated
> when across instances.
>
> So your DBAs have put 2 and 2 together and come up with 4.
>
> Two different issues together make their argument sound stronger !
>
> Yes, FOR EACH ROW triggers firing concurrently across multiple instances
> may introduce contention --- but this could also be controlled by
> application
> and data partitioning (the same trigger for the same group of rows
> [blocks]
> doesn't get fired concurrently from two instances).
>
> If they already do have trigger-based "limited" auditing, what are the
> additional
> triggers that you are contemplating ?
>
>
> Hemant K Chitale
> http://hemantoracledba.blogspot.com
>
> At 08:37 PM Wednesday, you wrote:
>
> > All,
> >
> > I've been having a conversation where I work about using triggers in our
> > application environment. The DBAs here say that triggers are not
> > appropriate in an Oracle RAC. Specifically they tell me:
> >
> > <snip>
> > Triggers in a RAC environment have been known to cause library cache
> > lock waits (in RAC because of the global cache and global coordination that
> > occurs, this wait event gets more pronounced - lib cache concurrency). This
> > was one of the main reasons we didn't do fast refreshes of snapshots for the
> > heavily used tables. Currently we also have limited auditing turned on and
> > it is trigger based and wouldn't want to open the flood-gates for trigger
> > based solutions - primarily for performance reasons
> > </snip>
> >
> > Needless to say I'm confused as I've used simple audit triggers to
> > populate change user IDs and timestamps in rows for years without issue. I
> > understand that if there is complicated trigger logic there may be problems,
> > but for calculating audit columns I can't see it being an issue. Anyone
> > else have a policy that doesn't allow triggers in RAC environments?
> >
> > We are on Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 -
> > Prod.
> >
> > --
> > Rumpi Gravenstein
> >
>
>
>
>
>
--
Rumpi Gravenstein
Other related posts:
