Description of Oracle PL/SQL Performance Analysis Tool profiler

 

I. Description

Oracle provides a profiler toolkit that allows you to view the performance of each module during PL/SQL Execution. You can download this script from MOS:

Implementing andusing the PL/SQL profiler [ID 243755.1]

 

You can also download it from my csdn:

Http://download.csdn.net/detail/tianlesoftware/4051100

When there is asignificant gap between user elapsed time and SQL processing elapsed time, andthere is PL/SQL code involved, the PL/SQL profiler becomes a very useful tool. it helps to identify the lines of PL/SQL code which are taking longer toprocess.

-- When user elapsed time is significantly different from SQL processing elapsedtime and involves PL/SQL code, PL/SQL profiler can be used, it can specify the time of the row-level PL/SQL.

 

For example, IFA transaction which uses PL/SQL libraries (packages, procedures, functions ortriggers) executes in one hour of user elapsed time, and reviewing the resultsof the Trace Analyzer or tkprof only 10 minutes of the elapsed time can beexplained with SQL commands being executed, then, by using the PL/SQL profiler, a line-by-line of the executed PL/SQL application code is reported, includingthe total execution time for each line of code, and how many times each ofthese lines was executed.

-- For example, if a PL/SQL database (package, process, function, or trigger) is executed, userelapsed time takes 1 hour, but it takes only 10 minutes to display it through trace, in this case, PL/SQL profiler can be used to report the execution time of each line of code.

 

The actualpl/SQL profiler is provided with the Core RDBMS code, and it is well known entedon the supplied PL/SQL packages and types reference manual, under the packagename dbms_profiler. this note is about implementing and using the PL/sqlprofiler on any 9i or higher database, In order to debug the performance of anypl/SQL application library. the main script provided in this note (profiler. SQL) generates a comprehensive HTML report on the performance data extracted by thedbms_profiler package.

-- In fact, PL/SQL profiler only provides the core RDBMS code, which is also described in the dbms_profiler package.

 

PL/SQL profiler contains the following three scripts:

Profiler. SQL-reporting PL/SQL profilerdata generated by dbms_profiler (main script)

Profgsrc. SQL-Get source code for PL/sqllibrary (package, procedure, function or trigger)

Proftab. SQL-create tables for the PL/sqlprofiler

 

 

2. Preparations before use

 

2.1 if used for the first time, determineif dbms_profiler is installed by doing a describe on that package

-- If it is used for the first time, check whether the dbms_profiler package is installed.

# Sqlplus apps/<PWD> -- pay attention to users
SQL> DESC dbms_profiler;

 

2.2 If dbms_profiler is not installed, connect as sys into SQL * Plus on database server, and execute command below tocreate the missing package:

-- If dbms_profiler is not installed, use sys to execute the following command to install the package.

# Sqlplus sys/<PWD> -- pay attention to the user
SQL> start? /Rdbms/admin/profload. SQL;

 

2.3 if used for the first time, andonce dbms_profiler is installed, connect as application user into SQL * Plus, andcreate the repository tables, plsql_profiler_units andplsql_profiler_data (proftab. SQL is provided within prof.zip, and is alsoavailable under $ ORACLE_HOME/rdbms/admin)

-- If it is used for the first time and dbms_profiler has been installed, run the proftab. SQL script to create tables for storing statistics: plsql_profiler_runs, plsql_profiler_units and plsql_profiler_data. This script is included in the prof package, and is also included in the $ ORACLE_HOME/rdbms/Admin directory.

# Sqlplus apps/<PWD>
SQL> Start proftab. SQL;

 

Or:

SQL> start? /Rdbms/admin/proftab. SQL

Drop table plsql_profiler_data cascadeconstraints

*

Error at line 1:

ORA-00942: Table or view does not exist

 

Drop table plsql_profiler_units cascadeconstraints

*

Error at line 1:

ORA-00942: Table or view does not exist

 

Drop table plsql_profiler_runs cascadeconstraints

*

Error at line 1:

ORA-00942: Table or view does not exist

 

Drop sequence plsql_profiler_runnumber

*

Error at line 1:

ORA-02289: sequence does not exist.

 

Table created.

Comment created.

 

Table created.

Comment created.

 

Table created.

Comment created.

 

Sequence created.

 

SQL>

 

 

2.4 Since main script on this note (profiler. SQL) reports on data generated by package dbms_profiler, be sure toprofile your PL/SQL Library prior to try reporting the results. to profile APL/SQL Library (package, procedure, function or trigger), include in its bodythe two cballs to actually start and complete the profiling.

-- Because the data displayed in the main script (profiler. SQL) is generated using the dbms_profiler package, profile PL/SQL code is required before the result is displayed.

 

Use the example below on any PL/SQL libraryto profile.

 

Begin
Dbms_profiler.start_profiler ('any comment to identify this execution ');
...
Dbms_profiler.stop_profiler;
Exception -- this line may exist in your code
...
End;
/

 

2.5 In order to modify your PL/SQL library, find first the script that creates it, make a backup, and insert manually thestart and stop CILS for the profiler. if unable to find the script thatcreates your package, procedure, function or trigger, use the provided scriptprofgsrc. SQL executing with PL/SQL library name as inline parameter:

-- To modify the PL/SQL database, you must first find the script, back it up, and manually add start and stop. If the original code cannot be found, you can use the profgsrc. SQL script to obtain the original code:

 

# Sqlplus apps/<PWD>
SQL> Start profgsrc. SQL <PL/SQL library name>;

 

SQL> start? /Rdbms/admin/profgsrc. SQL logon_audit;

Usage:

Sqlplus apps/<PWD>

SQL> Start profgsrc. SQL <PL/sqllibrary Name>

 

Name type

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

Logon_audit trigger

 

Generating spool file for triggerlogon_audit

 

Here I copy the script to $ ORACLE_HOME/rdbms/admin. After execution, it will generate an SQL script with the same name under the current directory of sqlplus. The script is the SQL code of our object.

 

 

2.6 script profgsrc. SQL extracts fromuser_source the actual source code for the requested PL/SQL library. itgenerates a text spool file as a SQL script to regenerate the PL/SQL library. make a backup of the spool file before modifying it. compile your modifiedpl/SQL library by executing it from SQL * Plus and connecting as your applicationuser.

-- Profgsrc. SQL extracts the object code from user_source.

 

2.7 once your compiled PL/SQL librarycontains the start and stop profiler procedure CILS, execute your library fromyur application. every execution generates a new run_id which can then bereported on, by using the profiler. SQL script.

-- After the start and stop profiler calls are added to the PL/SQL object, the application is started. A run_id is generated each time the application is executed, and then the profiler is used. the SQL tool can use run_id to obtain the corresponding report.

 

Report Generation Command

SQL> Start profiler. SQL <run_id>

 

Where run_id isthe execution ID returned by the dbms_profiler (which must be installed first ).

If run_id isunknown, execute without any parameter and the script will display a list tochoose from.

-- If no parameters are provided, all run_id is automatically listed.

 

 

Iii. Example

The stored procedure is as follows:

Create or replace procedure proc_test

As

Begin

Insert into test_pro select * From all_objects;

Commit;

For X in (select * fromall_users where user_id> 90)

Loop

Dbms_output.put_line(X. username );

End loop;

End proc_test;

 

 

3.1 Example 1

 

SQL> set serveroutput on

SQL> begin

2 dbms_profiler.start_profiler ('Dave test PL/SQL profiler ');

3 proc_test;

4 dbms_profiler.stop_profiler;

5 end;

6/

Xs $ null

Dave3

Dave2

Dave1

Xezf

Mgmt_view

Sysman

Anqing

DVD

 

PL/SQL procedure successfully completed.

 

-- Call PL/sqlprofiler. SQL

SQL> @? /Rdbms/admin/profiler. SQL

 

Runid run_date run_comment

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

1 08-feb-12 14:55:38 Dave testpl/SQL profiler

2 08-feb-12 15:03:11 Dave testpl/SQL profiler

 

 

Usage:

Sqlplus apps/<PWD>

SQL> Start profiler. SQL <runid>

 

Enter value for 1: 2

SQL>

-- It automatically lists all runids and starts them. Here we select the corresponding runid, enter 2, and press Enter.

 

After the execution is complete, a profiler_2.html Report will be generated in the current directory of sqlplus. here 2 is runid.

 

 

3.2 Example 2

 

Create or replace procedure proc_test2

As

Begin

Dbms_profiler.start_profiler('Dave test PL/SQL profiler ');

Insert into test_pro select * From all_objects;

Commit;

For X in (select * fromall_users where user_id> 90)

Loop

Dbms_output.put_line(X. username );

End loop;

Dbms_profiler.stop_profiler;

End proc_test2;

 

 

SQL> exec proc_test2

PL/SQL procedure successfully completed.

 

SQL> @? /Rdbms/admin/profiler. SQL

Runid run_date run_comment

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

1 08-feb-12 14:55:38 Dave testpl/SQL profiler

2 08-feb-12 15:03:11 Dave testpl/SQL profiler

3 08-feb-12 15:07:26 Dave testpl/SQL profiler

4 08-feb-12 15:09:40 Dave testpl/SQL profiler

-- Here, the run_comment content is specified in dbms_profiler.start_profiler to identify our runid.

 

Usage:

Sqlplus apps/<PWD>

SQL> Start profiler. SQL <runid>

 

Enter value for 1: 4

SQL>

 

 

3.3 Considerations:

1. The proftab. SQL script must be executed under the user where the PL/SQL object is located. Otherwise, the following error is reported:

 

SQL> exec proc_test2

Begin proc_test2; end;

 

*

Error at line 1:

ORA-06528: Error executing PL/SQL profiler

ORA-06512: At "SYS. dbms_profiler", line 123

ORA-06512: At "SYS. dbms_profiler", line 132

ORA-06512: At "Dave. proc_test2", line 4

ORA-06512: At line 1

 

2. The test was initially performed in Windows 7 + Oracle 11gr2 and no error was reported, but the final report generated had no data. And then transfer to Linux + Oracle 10gr2 for testing. It is normal.

After testing, the problem was found. The previous environment was 64-bit Windows 7 + 32-bit Oracle 11gr2, and oracle was changed to 64-bit 11gr2. This cause may be related to the version, because it cannot be ruled out by some unknown factors solved by the re-installation.

 

The test results are as follows:

 

3. The difference between the two examples is that example 1 does not modify the PL/SQL object, but starts and stops dbms_profiler In the PL/SQL code block. In Example 2, the PL/SQL object is modified and dbms_profiler is added to the PL/SQL object.

The official website information is to first back up the PL/SQL object, then modify it, and add dbms_profiler to the object. This advantage is that the profiler information will be counted every time you call it. Of course, the trouble is that you need to modify the object. In example 1, enable and disable the PL/SQL code block without modifying the PL/SQL object.

Therefore, the specific method should be based on your own needs to achieve the goal.

 

 

 

 

Bytes -------------------------------------------------------------------------------------------------------

All rights reserved. reprinted articles are allowed, but source addresses must be indicated by links. Otherwise, the documents will be held legally responsible!

Email: tianlesoftware@gmail.com

Skype: tianlesoftware

Blog: http://www.tianlesoftware.com

WEAVER: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

 

------- Add a group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, reject the application ----

Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)

Super DBA group: 63306533 (full); dba4 group: 83829929 dba5 group: 142216823

Dba6 group: 158654907 dba7 group: 172855474 DBA group: 104207940