Using DBMS_PROFILER from Asktom

Using DBMS_PROFILER from Asktom

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:458240723799

create or replace procedure do_mod as
cnt number := 0;
begin
dbms_profiler.start_profiler( 'mod' );
for i in 1 .. 500000
loop
cnt := cnt + 1;
if ( mod(cnt,1000) = 0 )
then
commit;
end if;
end loop;
dbms_profiler.stop_profiler;
end;
/
create or replace procedure no_mod as
cnt number := 0;
begin
dbms_profiler.start_profiler( 'no mod' );
for i in 1 .. 500000
loop
cnt := cnt + 1;
if ( cnt = 1000 )
then
commit;
cnt := 0;
end if;
end loop;
dbms_profiler.stop_profiler;
end;
/
exec do_mod
exec no_mod

Now, after running the profiler reports I find:
=
=
====================
Total time

GRAND_TOTAL
------------------
11.41
=
=
====================
Total time spent on each run

RUNID RUN_COMMENT SECS
----- ----------- -------
1 mod 8.18
2 no mod 3.23
=
=
====================
Percentage of time in each module, for each run separately

RUNID RUN_COMMENT UNIT_OWNER UNIT_NAME SECS PERCEN
----- ----------- ---------- ------------ ----- ---------
1 mod OPS$TKYTE DO_MOD 8.18 100.0
2 no mod OPS$TKYTE NO_MOD 3.23 100.0
=
=
====================
Percentage of time in each module, summarized across runs

UNIT_OWNER UNIT_NAME SECS PERCENTAG
----------- -------------- ------ ---------------
OPS$TKYTE DO_MOD 8.18 71.67
OPS$TKYTE NO_MOD 3.23 28.32
SYS DBMS_PROFILER .00 .00

So already, this shows that the MOD function takes longer. Just to prove that it is MOD
=
=
====================
Lines taking more than 1% of the total time, each run separate

RUNID HSECS PCT OWNER UNIT_NAME LINE# TEXT
----- ------ ---- ------ ---------- ------ --------------------
1 550.06 48.2 OPS$TKYTE DO_MOD 9 if ( mod(cnt,1000) = 0 )
1 135.22 11.9 OPS$TKYTE DO_MOD 8 cnt := cnt + 1;
2 107.71 9.4 OPS$TKYTE NO_MOD 8 cnt := cnt + 1;
2 104.34 9.1 OPS$TKYTE NO_MOD 9 if ( cnt = 1000 )
1 67.95 6.0 OPS$TKYTE DO_MOD 6 for i in 1..500000
2 64.66 5.7 OPS$TKYTE NO_MOD 12 cnt := 0;
1 64.64 5.7 OPS$TKYTE DO_MOD 11 commit;
2 44.99 3.9 OPS$TKYTE NO_MOD 6 for i in 1..500000

8 rows selected.

that clinches it -- using MOD took about 5.5 seconds, doing if ( cnt=1000 ) took 1 second
PLUS the time to do cnt := 0 gives a grant total of about 1.5 seconds.

5.5 for mod
1.5 for cnt=1000; cnt:=0;

1) sounds like somebody modified the remote procedure and added a COMMIT or ROLLBACK or
something that does a commit or rollback implicitly (DDL for example). Find out what has
changed in the remote procedure -- you cannot commit in a remote procedure -- only the
invoking procedure can do that.

2)

Your DBA may have to install the profiler in your database. The procedure for
installing this package is simple:

cd $ORACLE_HOME/rdbms/admin
Using SVRMGRL you would connect as SYS or INTERNAL
Run profload.sql

In order to actually use the profiler after that, you will need to have the profiling
tables installed. You can install these once per database but I recommend each developer
would have their own copy. Fortunately, the DBMS_PROFILER package is built with invokers
rights and unqualified tablenames so that we can install the tables in each schema and
the profiler package will use them. The reason you each want your own tables is so that
you only see the results of your profiling runs, not those of your coworkers. In order
to get a copy of the profiling tables in your schema, you would run
$ORACLE_HOME\rdbms\admin\proftab in SQLPlus. After you run proftab.sql, you¡¦ll need to
run profrep.sql as well. This script creates views and packages to operate on the
profiler tables in order to generate reports. This script is found in
$ORACLE_HOME\plsql\demo\profrep.sql. You should run this in your schema as well after
creating the tables.

I like to keep a small script around to reset these tables (clear them out). After I¡¦ve
done a run or two and have analyzed the results ¡V I run this script to reset the tables.
I have the following in a script I call profreset.sql:

delete from plsql_profiler_data;
delete from plsql_profiler_units;
delete from plsql_profiler_runs;

finally, and to answer the question, you will find $ORACLE_HOME/plsql/demo/profsum.sql.
that is what generates the rep