Re: bind variable
- From: Nigel Thomas <nigel_cl_thomas@xxxxxxxxx>
- To: davidb158@xxxxxxxxxxx, niall.litchfield@xxxxxxxxx
- Date: Mon, 6 Nov 2006 09:49:55 -0800 (PST)
>> We have lots of row level audit triggers that audit update and delete on
>> every column for each row.
>> But bind variable can not be used in the trigger.
David
First point: PL/SQL does the business with bind variables (as long as you use
PL/SQL variables); you don't have to do any nasty dynamic SQL (native or
DBMS_SQL) yourself.
>>The alternative is to create a procedure that uses bind variable and is
>>called by the trigger.
>>Does any one have experience on this? How is the performance with calling the
>>procedure from trigger back and forth? The database is shared by many
>>home grow and vendor applications.
I'm assuming your code is auditing column by column (rather than does something
like:
IF :new.my_first_column != :old.my_first_column THEN
insert into audit_trail(column, update_date, pk_column, old_value,
new_value)
values ('MY_FIRST_COLUMN', sysdate, :new.pk_column,
:old.my_first_column, :new.my_first_column);
END IF;
IF :new.my_first_column != :old.my_first_column THEN
insert into audit_trail(column, update_date, pk_column, old_value,
new_value)
values ('MY_SECOND_COLUMN', sysdate, :new.pk_column,
:old.my_second_column, :new.my_second_column);
END IF;
and so on for all columns (and the calls are more complex in real life, of
course).
You can either:
1) copy the column name into a variable in each block:
IF :new.my_first_column != :old.my_first_column THEN
l_column := 'MY_FIRST_COLUMN'
insert into audit_trail(column, update_date, pk_column, old_value,
new_value)
values (l_column, sysdate, :new.pk_column, :old.my_first_column,
:new.my_first_column);
END IF;
IF :new.my_first_column != :old.my_first_column THEN
insert into audit_trail(column, update_date, pk_column, old_value,
new_value)
values (l_column, sysdate, :new.pk_column, :old.my_second_column,
:new.my_second_column);
END IF;
The insert statements should (from V$SQL) look identical.
2) create a packaged procedure to handle column audit
IF :new.my_first_column != :old.my_first_column THEN
pk_audit.insert('MY_FIRST_COLUMN', sysdate, :new.pk_column,
:old.my_first_column, :new.my_first_column);
END IF;
IF :new.my_first_column != :old.my_first_column THEN
pk_audit.insert('MY_SECOND_COLUMN', sysdate, :new.pk_column,
:old.my_second_column, :new.my_second_column);
END IF;
The insert will now use the very same cursor in each case, minimising the load
on the shared pool.
I recommend (2), and this approach is very widely used.
In the old days (Oracle 7) the slowness of trigger compilation (at run time)
meant that factoring out as much code from triggers into packages was strongly
encouraged for performance reasons. That's less of an issue now (I believe) but
I still think it is good practice to reduce the size of triggers. Remember
that the cost of a PL/SQL package call is orders of magnitude less than the
cost of an extra SQL parse - even a soft one - or the cost of managing a larger
cursor cache.
>> If we change the cursor_sharing to force, what are the pros and cons?
I definitely don't recommend setting up CURSOR_SHARING=FORCE or SIMILAR except
as a temporary workaround to 3rd party vendor's non-binding code. Given that
you say you have have many applications, messing with this could have
unexpected side effects (eg similar SQL that currently intentionally uses
different execution plans may be forced to use the same one). If it isn't
broke, don't break it youself! If you ever DO need to use CURSOR_SHARING, you
should limit the scope to a single application if possible (eg by setting the
parameter for the session in a LOGON trigger, rather than setting it instance
wide).
HTH
Regards Nigel
Other related posts:
