Event 10520


As you may have guessed from the tip previous to this one, I've just applied the 9.0.1.2 patch. In the patch set instructions, it includes setting event 10520 which raised my curiousity. SQLERRM on the event reveals

"ORA-10520: recreate package/procedure/view only if definition has changed"

This sped up the application of the patchset considerably (during the running of catalog.sql and catproc.sql), but this is something that developers could also take advantage of. When deploying a large number of PL/SQL objects, a single script can be used to re-process all of the PL/SQL with this event being set to avoid unnecessary invalidation of objects.

Of course, the intelligent developer will have already broken the dependency chain with the exclusive use of packages, but we'll assume that this is not always the case :-)

For example, save the following code to a file (say demo.sql) and then run it through. It creates 400 small PL/SQL procedures (A1 to A199, B1 to B199) with various dependencies between the objects. It shows performance gain when using event 10520. (The script will also cleanup all of the objects it creates)
 
REM
REM demo.sql
REM 
REM
PROMPT
PROMPT Generating scripts
PROMPT
set termout off
set pages 0
set arraysize 50
spool creproc.sql
select 'create or replace procedure A'||rownum||' as '||chr(10)||
       'begin null; '||
       decode(mod(rownum,5),0,'A'||(rownum-4)||';',null)||chr(10)||
       'end;'||chr(10)||
       '/'
from sys.source$
where rownum < 200
union all
select 'create or replace procedure B'||rownum||' as '||chr(10)||
       'begin A'||rownum||';'||chr(10)||
       'end;'||chr(10)||
       '/'
from sys.source$
where rownum < 200
/
spool off
spool dropproc.sql
select 'drop procedure A'||rownum||';'||chr(10)||
       'drop procedure B'||rownum||';'
from sys.source$
where rownum < 200
/
spool off
set termout on

set pages 99
set feedback off
set serverout on
PROMPT
PROMPT Creating 400 PL/SQL objects
PROMPT
@creproc.sql

PROMPT Timing test 1 (Recreation of same objects)
variable x number;
exec :x := dbms_utility.get_time;
@C:\creproc.sql
exec dbms_output.put_line('Elapsed: '||(dbms_utility.get_time-:x)/100);

PROMPT Timing test 2 (same but with event 10520 set)
ALTER SESSION SET EVENTS '10520 TRACE NAME CONTEXT FOREVER, LEVEL 10'; 
variable x number;
exec :x := dbms_utility.get_time;
@creproc.sql
exec dbms_output.put_line('Elapsed: '||(dbms_utility.get_time-:x)/100);

PROMPT Cleanup
@dropproc.sql
ALTER SESSION SET EVENTS '10520 TRACE NAME CONTEXT OFF';

Here is some output from a sample run

SQL> @demo.sql

Generating scripts

Initial creation

Timing test 1 (normal recreation)
Elapsed: 41.15
Timing test 2 (same but with event 10520 set)
Elapsed: 3.51
Cleanup