Oracle PL / SQL PROFILER Application Guide
Profiler is an application of a debugging optimization tracking scheme for Oracle PL / SQL
Relative to the SQLTrace TKPROF tool debugging optimization tracking scheme, PROFILER has the most intuitive and more convenient advantages, because it is not necessary to generate and read the server-side tracking file, it is in the database table that will track all the stored data, so I got some Support for third-party tools, such as PL / SQL Developer.
Note: The tracking information generated by Profiler is far without the detailed TRACE generation. It does not have a plan, there is no CPU and IO information, which only generates the main execution time information, so it is more expensive, it is more intuitive, let us make us fastest Time positioning to optimize SQL.
Detailed application of Oracle PL / SQL PROFILER will be described below
1, installation
Install a total of two Oralce's own service scriptures
(1) Creating the infrastructure of Profiler (login with future users used)
@ $ Oracle_home / rdbms / admin / proFTab.sql;
Proteab.sql will create as the following structure and sequence under the current user:
PLSQL_PROFILER_RUNS - PROFILER Run Information
PLSQL_PROFILER_UNITS - PROFILER Each Unit Information
PLSQL_PROFILER_DATA - Details of each unit of PROFILER
PLSQL_PROFILER_RUNNUNUNUNUMBER is used to generate a sequence of a unique run number of Profiler
(2) Create a database service running package (login with SYS users, you need to use SQLPLUS, you can't use PL / SQL Developer Command Window)
@ $ Oracle_home / rdbms / admin / profload.sql;
Profload.sql is mainly to create a sys.dbms_profiler package
The main function process of the package is
START_PROFILER,
STOP_PROFILER,
Pause_profiler,
Resume_profiler,
Flush_data,
INTERNAL_VERSION_CHECK,
Get_version,
Rollup_Unit,
Rollup_run
The main function is
START_PROFILER - Start Profiler
STOP_PROFILER - Stop PROFILER
Other auxiliary functions
2, application example
Declare
v_run_number integer;
v_temp1 integer;
Begin
--1. Start Profiler
Sys.dbms_profiler.start_profiler (run_number => v_run_number);
- Show the current tracking running number (after the query is used)
DBMS_OUTPUT.PUT_LINE ('Run_Number:' || v_run_number);
--2. PL / SQL to be tracked
--Select Count (*) INTO V_TEMP1 from Scott.emp T;
--Select Count (*) INTO V_TEMP1 from Scott.dept T;
--3. Stop Profiler
Sys.dbms_profiler.stop_profiler;
END;
3, query results
SELECT U.Unit_name, - Unit Name
D.Line, - code line number
D. Total_Time, - A total of running time (unit: 10 million in seconds)
D. Total_OCCUR, - Total number of runs
D.min_time, - Minimum Runtime
D.max_time, - maximum running time
S.Text - Sourcecodes
From plsql_profiler_data d, sys.all_source s, plsql_profiler_units uwhere d.runid = 33 - Run number v_run_number
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 #
The above introduction is the application of Profiler by manual method, using relatively more troubles, and finally inquiry is not intuitive, the following will be detailed in PL / SQL Developer Application Profiler
1. Open Test Window
Method 1, create a new Test window
Second, select the process to track the run, shortcut menu test, as shown below
Enter the PL / SQL script you want to execute in Test Window
2. Open Profiler, as shown below
If an error prompt appears in the Open Profiler, you don't have PROFILER, and the detailed installation steps see the installation process above.
3, F8 execute scripts, switch to the Profiler tab, as shown below
The detailed significance of each column is as follows:
Unit - unit name
Line - code line number
Total Time - A total of running time (percentage of execution time of the Bank code and the maximum code execution time)
Occurrences - total running
TEXT - source code
Average Time-Average Operation Time
Maximum Time - Maximum Run Time
Minimum Time - Minimum Runtime
The source code displayed in the list is only no line. If you want to position, you can open the right button in the corresponding row.
[Go to Unit Line], this will jump directly to the corresponding source code location.
toolbar
1. Display the configuration dialog
2, refresh
3. Delete the data of the current runtime
4, Run: Display all list of all Profiler, default is current tracking
5, Unit: Display the unit list information of this tracking (execution time), default is executed for all units
Configure the PL / SQL Developer PROFILER option, as shown below
Available Column: Available columns
SELECTED COLMNS: Select the column you want to view
Time Units: Time Unit (Second, Mixi, Microsecond)
Show 0 Occurrences: Whether to display the processing statement to perform 0 times
Graphical Time Display: Color depth percentage of graphic display processing time
Yasheng
2005-04-17
