Sample Profiling Session

Go Up to Using PL/SQL Profiler

The DBArtisan installations include two scripts for the sample profiling session:

• PROFILER_BUILD_DEMO.SQL
• PROFILER_DEMO.SQL

The PROFILER_BUILD_DEMO.SQL creates the objects that you profile in the walk through, and the PROFILER_DEMO.SQL is what you profile during the walk through.

Note: To create the objects in the PROFILER_BUILD_DEMO.SQL script, you need CREATE privileges.

The sample script demonstrates the following features of the PL/SQL Profiler:

• Unit Detail
• Run Detail
• Show Only Hit Lines
• Advanced View

The scripts are located in the \UsrScrpt subfolder of the main installation folder.

Overview

Sample Profiling Session is divided into six parts:

• Getting Started
• Starting the Session
• Executing the Sample Script
• Stopping the Session
• Re-running & Re-executing the Session
• Stopping & Analyzing

Sample Profiling Session - Getting Started

In this step of Sample Profiling Session, you create the objects that you profile in the walk through.

Overview

The Getting Started section guides you through:

• Opening PROFILER_BUILD_DEMO.SQL.
• Changing the Datasource Navigator/Explorer Display.
• Confirming the Creation of the Package.

Getting Started

1. On the File menu, select Open.

   The Open Files dialog opens.

2. In the Open Files dialog, navigate to the UsrScrpt subfolder of the main installation folder, press ENTER, and then double-click PROFILER_BUILD_DEMO.SQL to open the script in a SQL Editor window.

   The PROFILER_BUILD_DEMO.SQL script opens in an SQL Editor window.

3. On the SQL Editor window, click Execute.

   Tthe script executes and create the package.

4. On the Navigator/Explorer window list, click Organize by Owner.
5. On the Navigator/Explorer window, click the node of your owner name.

   Your schema objects are displayed.

6. Double-click the Packages node to display PF_COUNT_TIME_INTERVAL and confirm its creation.

   Note: If you were not able to create the package, check the error messages to determine the problem.

Sample Profiling Session - Starting the Session

In this step of Sample Profiling Session, you start the profiling session.

Sample Profiling Session - Starting the Session

To start the session, do the following:

1. On the File menu, select Open.

   The Open Files dialog opens.

2. In the Open Files dialog, type the path to the UsrScrpt directory, press ENTER, and then double-click PROFILER_DEMO.SQL.

   The script opens in a SQL Editor window.

3. If using DBArtisan, on the Utilities menu, select PL/SQL Profiler > Start.

The PL/SQL Profiler - Start dialog opens.

4. In the Profile Label list, enter DemoProfile.

5. Click OK.

The profiling session begins.

Note: If this is the first time you start the PL/SQL Profiler, a dialog box opens.

Note: Click Yes to have SQL*Plus create the tables. You need to start the profiling session again (see step 3 above.)

Sample Profiling Session - Executing the Sample Script

In this step of Sample Profiling Session, you execute the DEMO script.

Sample Profiling Session - Executing the Sample Script

To execute the sample script, do the following:

1. On the SQL Editor window toolbar, click Execute.

The script executes and opens a Results tab.

Sample Profiling Session - Stopping the Session

In this step of Sample Profiling Session, you stop the profiling run.

Sample Profiling Session - Stopping the Session

To stop the session, do the following:

1. If using DBArtisan, on the Utilities menu, select PL/SQL Profiler > Stop.

The PL/SQL Profiler - Stop dialog opens.

2. Click Stop.

Sample Profiling Session - Re-running & Re-executing the Session

In this step of Sample Profiling Session, you run the same profile session and execute the DEMO script again.

Sample Profiling Session - Re-running & Re-executing the Session

To re-run and re-execute the session, do the following:

1. In the SQL Editor, click the Query tab.

2. If using DBArtisan, on the Utilities menu, select PL/SQL Profiler > Start.

The PL/SQL Profiler - Start dialog opens again.

3. Click the down arrow on the Profile Label list, and then click DemoProfile.

4. Click OK.

The profiling session begins.

5. On the SQL Editor toolbar, click Execute.

The script executes again and the Results tab opens.

Sample Profiling Session - Stopping & Analyzing

In this step of Sample Profiling Session, you stop profiling and analyze the runs.

Sample Profiling Session - Stopping & Analyzing

To stop and analyze the sample profiling session, do the following:

1. If using DBArtisan, on the Utilities menu, select PL/SQL Profiler > Stop.

The PL/SQL Profiler - Stop dialog opens again.

2. Click Stop & Analyze.

The PL/SQL Profiler - Run Detail window opens.

3. Click the Run list, and then click Run#x.

Note: A number is assigned to each profiling session. These numbers increase incrementally each time you run a profiling session. x= the number that was assigned to your first run.

PL/SQL Profiler populates the grid with information on the procedure, package body and package specification.

Note: For the purposes of this walk though we have created this package under the account SCOTT.

4. Click the Run list again, and then click the Run#x for your second run.

Notice this time there is no information on the package specification. It was created in the first run.

5. Right-click, and then click Detail.

The PL/SQL Profiler - Unit Detail window opens, the grid is populated with the average time to execute each unit and the source code. Notice the time to execute SELECT object_name, in the example is 126 ms.

6. In the PL/SQL Profiler - Unit Detail window, click the Run list, and then click Run#x for your first run.

7. Click the Unit list, and then click user name.PF_COUNT_SYSTEM_OBJECTS.

Notice the time to execute SELECT object_name is considerably greater: in the example it is 24476 ms.

8. Right-click, and then click Show Only Hit Lines.

The PL/SQL Profiler shows only the lines of code that executed.

9. Right-click, and then click Advanced View.

The Advanced View window opens.

10. Continue clicking the Run and Unit lists to compare the performance of each run and each session.

This concludes the Sample Profiling Session. You can delete the objects created during the Sample Profiling Session. They are:

• Check Constraints, PLSQL_PROFILER_UNITS, PLSQL_PROFILER_DATA
• Foreign Keys, PLSQL_PROFILER_UNITS, PLSQL_PROFILER_DATA
• Package, PF_COUNT_TIME_INTERVAL
• Package functions, WEEKEND_DAYS_( ), WORKING_DAYS_( ), YEARS_ELAPSED_BETWEEN_ ()
• PL/SQL code Profiles, DemoProfile
• Primary Keys, PLSQL_PROFILER_RUNS, PLSQL_PROFILER_UNITS, PLSQL_PROFILER_DATA
• Procedure, PF_COUNT_SYSTEM_OBJECTS
• Sequence, PLSQL_PROFILER_RUNNUMBER
• Tables, PLSQL_PROFILER_RUNS, PLSQL_PROFILER_UNITS, PLSQL_PROFILER_DATA