Shulou(Shulou.com)06/01 Report--

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)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.