As you may have guessed from the tip previous to this one, I've just applied the 18.104.22.168 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