| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Limit UPDATE statements to affect only one row
bernard (bernard_at_bosvark.com) wrote:
> I will benchmark both solutions and see which is fastest with least
> amount of latches. I suspect the trigger will be since it will stop
> execution after it touched 2 rows, but latching might be slightly
> higher due to contention on the package counter.
I'm sure no one cares anymore but as promised, I did some benchmarking using Tom Kyte's RUNSTATS package to see the difference using the Trigger and VPD/RLS solution and got the following results:
RUN1: Maxim Demenko Solution using Triggers and Package Counter RUN2: Bernard's Solution using VPD/RLS security function to analyse the SQL before execution
Test Case: using a 000 iterations of the following SQL procedure:
create or replace procedure do_updates_on_emp as
begin
begin
update emp set sal = sal+1;
exception
when others then
null;
end;
begin
update emp set sal = sal+1 where emp.ename = 'SMITH';
exception
when others then
null;
end;
begin
update emp set sal = sal+1 where emp.job = 'MANAGER';
exception
when others then
null;
end;
commit;
end;
I did rerun the test and also switched the RUN1 and RUN2 scenarios to ensure constant results.
-------------------------RESULTS (1) ---------------------------------
Using 'raise exception' instead of 'return 1=0' in VPD/RLS function so
that the security
function act the same as the trigger and pacakge solution resulted in:
Run1 ran in 2222 hsecs
Run2 ran in 2261 hsecs
run 1 ran in 98.28% of the time
Name Run1 Run2 Diff
.................
STAT...consistent gets - exami 160 45 -115
STAT...buffer is not pinned co 199 62 -137
STAT...undo change vector size 276,656 276,904 248
LATCH.library cache lock 885 192 -693
STAT...consistent gets 1,304 2,110 806
STAT...consistent gets from ca 1,304 2,110 806
STAT...session logical reads 7,380 8,197 817
LATCH.cache buffers chains 25,791 26,668 877
STAT...calls to get snapshot s 2,074 3,028 954
STAT...shared hash latch upgra 1,051 2,015 964
STAT...index scans kdiixs1 1,051 2,015 964
LATCH.PL/SQL warning settings 16 1,002 986
STAT...redo size 869,588 871,052 1,464
STAT...execute count 1,072 3,028 1,956
STAT...parse count (total) 67 2,023 1,956
STAT...session cursor cache hi 34 2,003 1,969
STAT...opened cursors cumulati 45 2,025 1,980
STAT...recursive calls 7,734 13,404 5,670
LATCH.row cache objects 774 9,377 8,603
LATCH.session allocation 1,024 10,396 9,372
LATCH.library cache pin 7,234 22,376 15,142
LATCH.library cache 10,091 29,590 19,499
Run1 latches total versus runs -- differnece and pct
Run1 Run2 Diff Pct
-------------------------RESULTS (2) ---------------------------------
Using 'return 1=0' instead of 'raise exception' in VPD/RLS function so
that the security
function does not act the same as the trigger and pacakge solution
resulted in:
Run1 ran in 2200 hsecs
Run2 ran in 2258 hsecs
run 1 ran in 97.43% of the time
Name Run1 Run2 Diff
.................
STAT...consistent gets - exami 153 46 -107
LATCH.dml lock allocation 2,207 2,086 -121
STAT...buffer is not pinned co 185 62 -123
LATCH.enqueue hash chains 4,649 4,507 -142
LATCH.simulator lru latch 599 385 -214
LATCH.simulator hash latch 607 388 -219
STAT...redo size 869,220 869,492 272
STAT...undo change vector size 276,592 276,980 388
STAT...consistent gets from ca 1,287 2,113 826
STAT...consistent gets 1,287 2,113 826
STAT...session logical reads 7,356 8,191 835
STAT...calls to get snapshot s 2,069 3,029 960
STAT...shared hash latch upgra 1,049 2,015 966
STAT...index scans kdiixs1 1,049 2,015 966
LATCH.PL/SQL warning settings 16 1,001 985
LATCH.shared pool 1,318 199 -1,119
LATCH.library cache lock 1,484 182 -1,302
STAT...parse count (total) 64 2,023 1,959
STAT...execute count 1,069 3,028 1,959
STAT...session cursor cache hi 31 2,003 1,972
STAT...opened cursors cumulati 42 2,025 1,983
LATCH.cache buffers chains 29,843 26,540 -3,303
LATCH.session allocation 5,798 10,390 4,592
STAT...recursive calls 7,698 13,404 5,706
LATCH.row cache objects 2,090 9,323 7,233
LATCH.library cache pin 8,530 22,334 13,804
LATCH.library cache 12,077 29,532 17,455
Run1 latches total versus runs -- differnece and pct
Run1 Run2 Diff Pct
-------------------------RESULTS (3) ---------------------------------
Using "policy_type = dbms_rls.STATIC" when adding the policy to the table to reduce the parsing.
Run1 ran in 2197 hsecs
Run2 ran in 2180 hsecs
run 1 ran in 100.78% of the time
Name Run1 Run2 Diff
STAT...parse count (total) 65 25 -40
STAT...consistent gets - exami 153 45 -108
STAT...buffer is not pinned co 185 62 -123
STAT...session logical reads 7,359 7,191 -168
STAT...consistent gets from ca 1,287 1,111 -176
STAT...consistent gets 1,287 1,111 -176
STAT...recursive calls 7,698 7,403 -295
STAT...undo change vector size 276,588 276,892 304
LATCH.session allocation 837 511 -326
LATCH.row cache objects 382 720 338
LATCH.cache buffers chains 25,019 25,461 442
LATCH.library cache lock 242 796 554
STAT...redo size 869,508 870,228 720
LATCH.library cache pin 6,520 3,051 -3,469
LATCH.library cache 8,804 3,788 -5,016
STAT...session uga memory 7,488 0 -7,488
Run1 latches total versus runs -- differnece and pct
Run1 Run2 Diff Pct
Seems that the raising of exceptions have no (or little) impact on the latch or wall clock difference of two solutions.
The parses are killing the VPD solution, policy_type = dbms_rls.STATIC makes it using fewer latches.
Once the parses are reduced then VPD/RLS solution is more scalable.
------------------Test Script -----------------------
clear
set serveroutput on
declare
i int;
begin
runstats_pkg.rs_start;
execute immediate 'alter table emp enable all triggers';
dbms_rls.drop_policy (object_schema => 'SCOTT',
object_name => 'EMP',
policy_name => 'VPDPOLICY3');
for i in 1..1000 loop
do_updates_on_emp;
end loop;
runstats_pkg.rs_middle;
execute immediate 'alter table emp disable all triggers';
dbms_rls.add_policy (object_schema => 'SCOTT',
object_name => 'EMP',
policy_name => 'VPDPOLICY3',
function_schema => 'SCOTT',
policy_function => 'VPDPOLICY3_FNC',
statement_types => 'UPDATE',
policy_type => dbms_rls.STATIC);
for i in 1..1000 loop
do_updates_on_emp;
end loop;
runstats_pkg.rs_stop;
end;
/
Received on Tue Aug 15 2006 - 05:36:18 CDT
 |
 |