Oracle's dbms_profiler: PL / SQL Performance Adjustment
DBMS_PROFILER package example
Below is the simple example I provide to use the configuration, run the configuration file to test the performance of the following routines. The custom script used by the routine is followed.
1. Create a 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 Load';
END IF;
EXCEPTION
When Others Then
PIO_STATUS: = 'Error in am_perf_chek';
END;
2. Use the configuration file call routine
Replace the above routine, execute the call_profiler.sql script (see below), incoming 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. Evaluate the execution time: eavluate_profiler_results.sql script execution to give 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; 10 Exception 11 When Others Then 12 0 0 PIO_STATUS: = 'Error in am_perf_chek' ;! 13 1 2.410361 end; 13 rows selected.code% coverage -------------- 66.6666667
4. As you can see, the third line execution time is increased to 86 milliseconds. But change the IF statement, re-execute the above process, will get 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 Load'; 9 End IF; 10 Exception 11 When Others The 12 0 0 PIO_STATUS: = 'Error In! AM_PERF_CHEK'; 13 1 .731657 End; 13 ROW S Selected.code% coverage -------------- 66.6666667
5. As you can see, the third line of execution time is reduced from 86 milliseconds from 86 milliseconds in this situation, and the excess time is caused by the built-in trunc () function. In this situation, if the first condition is False, The trunc () function will not be executed. This is just a simple example. The bigger the routine you test, the challenge you face is bigger. This configuration results also prove how much the code is covered during the execution, so let us know Code range in performance monitoring. If any PL / SQL block performance occurs, it can also extract the code that is executing in various scenarios and checks the configuration results, thus identifying the problem. 6. For a specific scenario, if you perform a special code segment, you can get reasonable analysis, even if the code is at all, you cannot run.
Environmental creation
DBMS_Profiler packets are not automatically installed by default installation or database, and DBA creates it with a profload.sql script. Use a larger or a separate user to create a table that stores statistics. If you created with SYS users, give other users to DML permissions and create a common book name for these tables.
The following: PLSQL_PROFILER_RUNS Table: PL / SQL Configuration Run Detail: PLSQL_Profiler_Units Table: The information of each library unit is running. PLSQL_Profiler_Data table: All configuration files run the data accumulated. Plsql_profiler_runnumber sequence provides RunID operation and interpretation configuration data
Oracle provides three tables to count, populate RUNID. There are many third-party tools to provide customized reports, Oracle provides Profrep.sql script evaluation data (in
Script: Call_Profiler.sql
-------------------------------------------------- --------- set head offset pages 0select decode (dbms_profiler.start_profiler, '0', 'profiler started ",' profiler error ') from dual;
-
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 Onset Pages 200
-------------------------------------------------- ---------
Script: Evaluate_Profiler_Results.sql
-------------------------------------------------- --------- Undef Runidundef Ownerundef Nameset Verify Offselect 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) Pwhere 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 * 100 "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 Runidundef Owner undef name Conclusion
