Oracle ' s DBMS

oracle| Performance
Oracle ' s dbms_profiler:pl/sql performance tuning



Dbms_profiler Package Examples



Here's a simple example of how I can use configuration, run a configuration file to test the performance of the following routines. The custom script used by the routine is immediately followed.

1. Create the process.

Create or Replace procedure Am_perf_chk (pi_seq in number,

Pio_status in Out nocopy varchar2) is

L_dat Date: = Sysdate;

Begin

If trunc (l_dat) = ' 21-sep-02 ' and pi_seq = 1 Then

Pio_status: = ' OK ';

Else

Pio_status: = ' Invalid tape loaded ';

End If;

exception

When others then

Pio_status: = ' Error in Am_perf_chek ';

End



2. Using the configuration file to invoke the routine

Replace the previous routine, execute the Call_profiler.sql script (see below for scripting code), and pass in the pi_seq=2

Sql> @d:\am\call_profiler.sql

Profiler started

Invalid Tape Loaded

Pl/sql procedure successfully completed.

Profiler stopped

Profiler flushed

Runid:8



3. Assessment of Implementation time:
Execute Eavluate_profiler_results.sql script, get time statistics

Sql> @d:\am\evaluate_profiler_results.sql

Enter value for Runid:8

Enter value for Name:am_perf_chk

Enter value for Owner:scott

Line occur Msec Text

---------- ---------- ---------- -------------------------------------------------------------------

1 procedure Am_perf_chk (pi_seq in number,

2 pio_status in Out nocopy varchar2) is

3 2 43.05965 l_dat Date: = Sysdate;

4 begin

5 1 86.35732 if trunc (l_dat) = ' 21-sep-02 ' and pi_seq = 1 Then

6 0 0 Pio_status: = ' OK ';

7 Else

8 1 8.416151 pio_status: = ' Invalid tape loaded ';

9 End If;

Ten exception

One when others then

0 0 Pio_status: = ' Error in Am_perf_chek ';!

1 2.410361 end;

Rows selected.



Code% coverage

--------------

66.6666667



4. As you can see, the third line of execution time is raised to 86 milliseconds. But changing the IF statement and then performing the procedure above will result in new results:

Line occur Msec Text

---------- ---------- ---------- -------------------------------------------------------------------

1 procedure Am_perf_chk (pi_seq in number,

2 pio_status in Out nocopy varchar2) is

3 2 17.978816 l_dat Date: = Sysdate;

4 begin

5 1 8.419503 If Pi_seq = 1 and trunc (l_dat) = ' 21-sep-02 ' Then

6 0 0 Pio_status: = ' OK ';

7 Else

8 1 7.512684 pio_status: = ' Invalid tape loaded ';

9 End If;

Ten exception

One when others then

0 0 Pio_status: = ' Error in!am_perf_chek ';

1 731657 end;

Rows selected.



Code% coverage

--------------

66.6666667



5. As you can see, the third line of execution time in this scenario is reduced from 86 milliseconds to 8 milliseconds, the extra time is caused by the built-in trunc () function. If the first condition is false, the trunc () function is not executed. This is just a simple example, when you're testing a routine that's bigger , the challenges you face are even greater.

This configuration result also shows how many lines of code are overwritten during execution, allowing us to know the scope of code in performance monitoring. If any pl/sql block performance problems, it can also extract the various scenarios of the code being executed and check the configuration results, so as to identify the problem.

6. For a particular scenario, if a particular piece of code is executed, a reasonable analysis can be obtained, even if the code is not running at all.



Creation of the environment

The Dbms_profiler package is not installed automatically when the default installation or database is created, and the DBA creates it with a profload.sql script. Create a table that stores statistics with a larger or a single user. If

Created with a user such as SYS, the DML permissions are granted to other users, and a common shorthand name is created for those tables.



The table is created as follows:

Plsql_profiler_runs table: Run details for pl/sql configuration.

Plsql_profiler_units table: Information for each library unit in a run.

Plsql_profiler_data table: Cumulative data for all profiles at run time.

The Plsql_profiler_runnumber sequence provides a Runid



Running and interpreting configuration data

Oracle provides three tables for counting, populating Runid. There are a number of third-party tools that can provide customized reports based on this data, Oracle provides PROFREP.SQL script evaluation data (in the <oracle_home>\plsql\demo\ directory), and the following two simple scripts are used above. Used to check the execution time of the program unit. Execution time is stored in milliseconds

-----------------------------------------------------------

Script:call_profiler.sql

-----------------------------------------------------------

Set head off

Set pages 0

Select Decode (Dbms_profiler.start_profiler, ' 0 ', ' Profiler started ', ' Profiler error ')

from dual;



--< Place your routine in the below block >--

Declare

L_status VARCHAR2 (200);

Begin

Am_perf_chk (2, l_status);

Dbms_output.put_line (L_status);

End

/



Select Decode (Dbms_profiler.stop_profiler, ' 0 ', ' Profiler stopped ', ' Profiler error ')

from dual;

Select Decode (Dbms_profiler.flush_data, ' 0 ', ' Profiler flushed ', ' Profiler error ')

from dual;

Select ' Runid: ' | | Plsql_profiler_runnumber.currval

from dual;

Set head on

Set pages 200



-----------------------------------------------------------

Script:evaluate_profiler_results.sql

-----------------------------------------------------------

Undef Runid

UNDEF owner

undef name

Set Verify off

Select S.line "line", p.total_occur "occur", P.total_time "Msec", S.text "text"

From All_source S, (select U.unit_owner, U.unit_name, U.unit_type, d.line#,

D.total_occur, d.total_time/1000000 Total_time

From Plsql_profiler_data D, plsql_profiler_units u

where U.runid = &&runid

and U.runid = D.runid

and u.unit_number = D.unit_number) p

where S.owner = P.unit_owner (+)

and S.name = P.unit_name (+)

and S.type = P.unit_type (+)

and S.line = p.line# (+)

and s.name = Upper (' &&name ')

and S.owner = Upper (' &&owner ')

Order BY S.line;

Select Exec.cnt/total.cnt * "Code% coverage"

From (select COUNT (1) CNT

From Plsql_profiler_data D, plsql_profiler_units u

where D.runid = &&runid

and U.runid = D.runid

and U.unit_number = D.unit_number

and u.unit_name = Upper (' &&name ')

and U.unit_owner = Upper (' &&owner ')) Total,

(select COUNT (1) CNT

From Plsql_profiler_data D, plsql_profiler_units u

where D.runid = &&runid

and U.runid = D.runid

and U.unit_number = D.unit_number

and u.unit_name = Upper (' &&name ')

and U.unit_owner = Upper (' &&owner ')

and d.total_occur > 0) exec;

Undef Runid

UNDEF owner

undef name



Conclusion

Dbms_profiler is a very powerful tool, one of which is the ability to identify pl/sql performance problems. This tool is best used during the development period to adjust code based on a variety of application scenarios, and it also adjusts the routines already in use and takes the obvious time to execute. In short, this tool can give performance statistics to each line of code, which can help us evaluate and adjust to an excellent level, Pl/sql code should not be ignored when checking the performance problem of SQL statements, instead should be adjusted to the best results.