http://www.sqle.ru/index.php?action=history&feed=atom&title=Oracle_PL%2FSQL_Tutorial%2FSystem_Packages%2FDBMS_PROFILER Oracle PL/SQL Tutorial/System Packages/DBMS PROFILER - История изменений 2026-05-24T04:30:22Z История изменений этой страницы в вики MediaWiki 1.30.0 http://www.sqle.ru/index.php?title=Oracle_PL/SQL_Tutorial/System_Packages/DBMS_PROFILER&diff=3320&oldid=prev в 13:45, 26 мая 2010 2010-05-26T13:45:46Z <p></p> <table class="diff diff-contentalign-left" data-mw="interface"> <tr style="vertical-align: top;" lang="ru"> <td colspan="1" style="background-color: white; color:black; text-align: center;">← Предыдущая</td> <td colspan="1" style="background-color: white; color:black; text-align: center;">Версия 13:45, 26 мая 2010</td> </tr><tr><td colspan="2" style="text-align: center;" lang="ru"><div class="mw-diff-empty">(нет различий)</div> </td></tr></table> http://www.sqle.ru/index.php?title=Oracle_PL/SQL_Tutorial/System_Packages/DBMS_PROFILER&diff=3321&oldid=prev Admin: 1 версия 2010-05-26T10:05:58Z <p>1 версия</p> <p><b>Новая страница</b></p><div>== Call dbms_profiler.start_profiler to start profiler==<br /> <br /> <br /> <br /> <br /> &lt;!-- start source code --&gt;<br /> <br /> &lt;source lang=&quot;sql&quot;&gt;<br /> SQL&gt;<br /> SQL&gt;<br /> SQL&gt; exec dbms_profiler.start_profiler( &quot;factorial iterative&quot; )<br /> BEGIN dbms_profiler.start_profiler( &quot;factorial iterative&quot; ); END;<br /> SQL&gt;<br /> SQL&gt;<br /> SQL&gt; create or replace<br /> 2 function fact_iterative( n int ) return number<br /> 3 as<br /> 4 l_result number default 1;<br /> 5 begin<br /> 6 for i in 2 .. n<br /> 7 loop<br /> 8 l_result := l_result * i;<br /> 9 end loop;<br /> 10 return l_result;<br /> 11 end;<br /> 12 /<br /> Function created.<br /> SQL&gt;<br /> SQL&gt; begin<br /> 2 for i in 1 .. 50 loop<br /> 3 dbms_output.put_line( fact_iterative(50) );<br /> 4 end loop;<br /> 5 end;<br /> 6 /<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> PL/SQL procedure successfully completed.<br /> SQL&gt;<br /> SQL&gt;<br /> SQL&gt; exec dbms_profiler.stop_profiler<br /> PL/SQL procedure successfully completed.<br /> SQL&gt;&lt;/source&gt;<br /> <br /> <br /> <br /> == Capture DBMS_PROFILER information for the specified script==<br /> <br /> <br /> <br /> <br /> &lt;!-- start source code --&gt;<br /> <br /> &lt;source lang=&quot;sql&quot;&gt;<br /> SQL&gt; define script=&quot;&amp;1&quot;;<br /> Enter value for 1:<br /> SQL&gt; set verify off;<br /> SQL&gt;<br /> SQL&gt; declare<br /> 2<br /> 3 n_run_number number;<br /> 4<br /> 5 begin<br /> 6 DBMS_PROFILER.start_profiler(&quot;&amp;script&quot;||&quot; on &quot;||to_char(SYSDATE, &quot;YYYYMMDD HH24MISS&quot;),<br /> 7 &quot; &quot;,<br /> 8 n_run_number);<br /> 9<br /> 10 DBMS_OUTPUT.PUT_LINE(&quot;DBMS_PROFILER run_number = &quot;||to_char(n_run_number));<br /> 11 end;<br /> 12 /<br /> <br /> SQL&gt;<br /> SQL&gt;<br /> SQL&gt; execute DBMS_PROFILER.stop_profiler;<br /> PL/SQL procedure successfully completed.<br /> SQL&gt; execute DBMS_PROFILER.flush_data;<br /> PL/SQL procedure successfully completed.<br /> SQL&gt;<br /> SQL&gt; set verify on;<br /> SQL&gt;<br /> SQL&gt;&lt;/source&gt;<br /> <br /> <br /> <br /> == DBMS_PROFILER.get_version==<br /> <br /> <br /> <br /> <br /> &lt;!-- start source code --&gt;<br /> <br /> &lt;source lang=&quot;sql&quot;&gt;<br /> SQL&gt; declare<br /> 2 n_major number;<br /> 3 n_minor number;<br /> 4<br /> 5 begin<br /> 6 DBMS_PROFILER.get_version(n_major, n_minor);<br /> 7<br /> 8 DBMS_OUTPUT.PUT_LINE(&quot;DBMS_PROFILER Version &quot;||to_char(n_major)||&quot;.&quot;||to_char(n_minor));<br /> 9<br /> 10 DBMS_OUTPUT.PUT_LINE(DBMS_PROFILER.internal_version_check);<br /> 11 end;<br /> 12 /<br /> PL/SQL procedure successfully completed.<br /> SQL&gt;<br /> SQL&gt;&lt;/source&gt;<br /> <br /> <br /> <br /> == dbms_profiler.start_profiler==<br /> <br /> <br /> <br /> <br /> &lt;!-- start source code --&gt;<br /> <br /> &lt;source lang=&quot;sql&quot;&gt;<br /> SQL&gt;<br /> SQL&gt; create or replace procedure do_mod<br /> 2 as<br /> 3 cnt number := 0;<br /> 4 begin<br /> 5 dbms_profiler.start_profiler( &quot;mod&quot; );<br /> 6<br /> 7 for i in 1 .. 500000<br /> 8 loop<br /> 9 cnt := cnt + 1;<br /> 10 if ( mod(cnt,1000) = 0 )<br /> 11 then<br /> 12 commit;<br /> 13 end if;<br /> 14 end loop;<br /> 15<br /> 16 dbms_profiler.stop_profiler;<br /> 17 end;<br /> 18 /<br /> Procedure created.<br /> SQL&gt;<br /> SQL&gt; create or replace procedure no_mod<br /> 2 as<br /> 3 cnt number := 0;<br /> 4 begin<br /> 5 dbms_profiler.start_profiler( &quot;no mod&quot; );<br /> 6 for i in 1 .. 500000<br /> 7 loop<br /> 8 cnt := cnt + 1;<br /> 9 if ( cnt = 1000 )<br /> 10 then<br /> 11 commit;<br /> 12 cnt := 0;<br /> 13 end if;<br /> 14 end loop;<br /> 15 dbms_profiler.stop_profiler;<br /> 16 end;<br /> 17 /<br /> Procedure created.<br /> <br /> SQL&gt;<br /> SQL&gt;<br /> SQL&gt;&lt;/source&gt;<br /> <br /> <br /> <br /> == Demonstrate DBMS_PROFILER==<br /> <br /> <br /> <br /> <br /> &lt;!-- start source code --&gt;<br /> <br /> &lt;source lang=&quot;sql&quot;&gt;<br /> SQL&gt;<br /> SQL&gt; create or replace procedure modtest<br /> 2 as<br /> 3 begin<br /> 4 dbms_profiler.start_profiler(&quot;mod&quot;);<br /> 5 for x in 1..1000 loop<br /> 6 if mod(x,100) = 0 then null; end if;<br /> 7 end loop;<br /> 8 dbms_profiler.stop_profiler;<br /> 9 end;<br /> 10 /<br /> SQL&gt;<br /> SQL&gt;&lt;/source&gt;<br /> <br /> <br /> <br /> == Profiler for recursive function==<br /> <br /> <br /> <br /> <br /> &lt;!-- start source code --&gt;<br /> <br /> &lt;source lang=&quot;sql&quot;&gt;<br /> SQL&gt;<br /> SQL&gt; exec dbms_profiler.start_profiler( &quot;factorial recursive&quot; )<br /> BEGIN dbms_profiler.start_profiler( &quot;factorial recursive&quot; ); END;<br /> SQL&gt;<br /> SQL&gt; create or replace<br /> 2 function fact_recursive( n int ) return number<br /> 3 as<br /> 4 begin<br /> 5 if ( n = 1 )<br /> 6 then<br /> 7 return 1;<br /> 8 else<br /> 9 return n * fact_recursive(n-1);<br /> 10 end if;<br /> 11 end;<br /> 12 /<br /> Function created.<br /> SQL&gt; begin<br /> 2 for i in 1 .. 50 loop<br /> 3 dbms_output.put_line( fact_recursive(50) );<br /> 4 end loop;<br /> 5 end;<br /> 6 /<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> 30414093201713378043612608166064768844300000000000000000000000000<br /> PL/SQL procedure successfully completed.<br /> SQL&gt;<br /> SQL&gt;<br /> SQL&gt; exec dbms_profiler.stop_profiler<br /> PL/SQL procedure successfully completed.<br /> SQL&gt;&lt;/source&gt;<br /> <br /> <br /> <br /> == Use DBMS_PROFILER to compare the performance differences between row-at-a-time processing and bulk processing==<br /> <br /> <br /> <br /> <br /> &lt;!-- start source code --&gt;<br /> <br /> &lt;source lang=&quot;sql&quot;&gt;<br /> SQL&gt;<br /> SQL&gt; create table t1 as select * from dba_objects where 0=1;<br /> Table created.<br /> SQL&gt; create table t2 as select * from dba_objects where 0=1;<br /> Table created.<br /> SQL&gt;<br /> SQL&gt;<br /> SQL&gt; create or replace procedure row_at_a_time_test<br /> 2 as<br /> 3 begin<br /> 4 dbms_profiler.start_profiler(&quot;row test&quot;);<br /> 5 for x in ( select * from all_objects )<br /> 6 loop<br /> 7 insert into t1 values X;<br /> 8 end loop;<br /> 9 dbms_profiler.stop_profiler;<br /> 10 end;<br /> 11 /<br /> Procedure created.<br /> SQL&gt; show errors<br /> No errors.<br /> SQL&gt;<br /> SQL&gt; create or replace procedure bulk_test<br /> 2 as<br /> 3 type array is table of all_objects%rowtype;<br /> 4 l_data array;<br /> 5 cursor c is select * from all_objects;<br /> 6 begin<br /> 7 dbms_profiler.start_profiler(&quot;bulk test&quot;);<br /> 8 open c;<br /> 9 loop<br /> 10 fetch c bulk collect into l_data LIMIT 100;<br /> 11<br /> 12 forall i in 1 .. l_data.count<br /> 13 insert into t2 values l_data(i);<br /> 14<br /> 15 exit when c%notfound;<br /> 16 end loop;<br /> 17 dbms_profiler.stop_profiler;<br /> 18 end;<br /> 19 /<br /> Procedure created.<br /> SQL&gt; show errors<br /> No errors.<br /> SQL&gt;<br /> SQL&gt;<br /> SQL&gt;<br /> SQL&gt; drop table t1;<br /> Table dropped.<br /> SQL&gt; drop table t2;<br /> Table dropped.<br /> SQL&gt;<br /> SQL&gt;&lt;/source&gt;</div> Admin