Use dbms_profile to locate stored procedures or inefficient statements in package
Http://blog.chinaunix.net/uid-74941-id-85352.html
Https://www.bbsmax.com/A/MAzAgQNq59/
1. Check if the dbms_ profile package is installed
If it is not installed, execute the following statement to install
@? / rdbms/admin/profload.sql
two。 Assign the execution right of dbms_profiler to the test user scott
Grant execute on dbms_profiler to scott
3. Log in using the test user scott and execute proftab.sql (create tracking related system tables)
@? / rdbms/admin/proftab.sql
4. The process of creating a test
The use of DMMS_PROFILER:
(1). DBMS_PROFILER.start_profiler starts monitoring
(2)。 Run the stored procedures that need to be analyzed, which can be multiple
(3). DBMS_PROFILER.stop_profiler ends monitoring
-- create test stored procedures
Create or replace procedure sp_profiler_test1
As
Begin
For x in 1..10000
Loop
Insert into t_t1 values (x)
End loop
Commit
End sp_profiler_test1
/
-- execute DMMS_PROFILER
Set serverout on
DECLARE
V_run_number integer
BEGIN
-- start profiler
Sys.DBMS_PROFILER.start_profiler (run_number = > v_run_number)
-- displays the running sequence number of the current trace (to be used for later queries)
DBMS_OUTPUT.put_line ('run_number:' | | v_run_number)
-- run the PLSQL to trace
Sp_profiler_test1;-the test sample stored procedure created in the previous step
-- stop profiler
Sys.DBMS_PROFILER.stop_profiler
END
/
5. View the result
-- query using test users
Select d.linecodes,-- line number of the source code
S.text,-- source code
Round (d.totalroomtimestamp 1000000000000pr 5) total_time,-- Total elapsed time (in seconds)
D.total_occur-- Total number of runs
Round (d. Minus timestamp 1000000000000pr 5) min_time,-- minimum running time at a time
Round (d.maxmaxtimepact1000000000000pr 5) max_time-maximum run time at a time
From plsql_profiler_data d, sys.all_source s, plsql_profiler_units u
Where d.runid = 2-- run number
And u.unit_name = 'SP_PROFILER_TEST1'-the name of the unit, that is, the name of the stored procedure being tested
And u.runid = d.runid
And d.unit_number = u.unit_number
And d.total_occur 0
And s.type (+) = u.unit_type
And s.owner (+) = u.unit_owner
And s.name (+) = u.unit_name
And d.line# = nvl (s.line, d.line#)
Order by u.unit_number, d.line#
1 second = 1000 milliseconds bai (ms)
1 second = 1000000 microseconds (μ dus)
1 second = 1000000000 nanoseconds (ns)
1 second = 100,000,000,000 picoseconds (ps) (in 100,000,000,000 picoseconds)
1 second = 1000000000000000 femtosecond (fs)
