Mark Levis

1
PL/SQL Profiling

• Mark Levis
• Senior Developer
• MLevisw_at_bigfoot.com

2
Introduction

• Who am I?
• Why am I here?
• What will be discussed?

3
PL/SQL Profiling

• What is Profiling
• Profiling is the gathering of execution
• timing information.

4
PL/SQL Profiling

• Why Profile?
• To find what package / procedure / SQL
• statement(s) are executing outside of your
• threshold values.
• To find what SQL are actually being
• executed.

5
API

• API consists of a Package ,3 tables and a
• sequence
• DBMS_PROFILER
• PLSQL_PROFILER_RUNS
• PLSQL_PROFILER_UNITS
• PLSQL_PROFILER_DATA
• PLSQL_PROFILER_RUNNUMBER

6
Getting Started

• Install DBMS_PROFILER package.
• Must connect as SYS.
• Execute
• /rdbms/admin/profload.sql

7
Getting Started

• Install profiling tables
• 2 choices
• Tables in each user schema (default)
• Central tables

8
Default Tables

• Connect as user where tables should be
• installed
• Run /rdbms/admin/proftab.sql

9
Central Install

• Execute proftab.sql
• Create public synonyms for (same name)
• plsql_profiler_data
• plsql_profiler_units
• plsql_profiler_runs
• plsql_profiler_runnumber

10
Central Install

• Grant Insert, Update, Select, Delete to
• plsql_profiler_data
• plsql_profiler_units
• plsql_profiler_runs
• Grant Select to plsql_profiler_runnumber

11
DBMS_PROFILER

• The main functions in the profile APII are
• START_PROFILER
• STOP_PROFILER
• PAUSE_PROFILER
• FLUSH_DATA

12
Start_Profiler

• function start_profiler(
• run_comment IN varchar2 sysdate,
• run_comment1 IN varchar2 '',
• run_number OUT binary_integer)
• return binary_integer

13
Start_Profiler

• procedure start_profiler(
• run_comment IN varchar2 sysdate,
• run_comment1 IN varchar2 '',
• run_number OUT binary_integer)

14
Start_Profiler

• function start_profiler(
• run_comment IN varchar2 sysdate,
• run_comment1 IN varchar2 '')
• return binary_integer

15
Start_Profiler

• procedure start_profiler(
• run_comment IN varchar2 sysdate,
• run_comment1 IN varchar2 '')

16
Stop_Profiler

• function stop_profiler return binary_integer
• procedure stop_profiler

17
Pause_Profiler

• function pause_profiler return binary_integer
• procedure pause_profiler

18
Flush_Data

• function flush_data return binary_integer
• procedure flush_data

19
Rollup

• procedure rollup_unit(run_number IN number, unit
  IN number)
• procedure rollup_run(
• run_number IN number)

20
Tables

• PLSQL_PROFILER_RUNS
• PLSQL_PROFILER_UNITS
• PLSQL_PROFILER_DATA

21
Runs

• create table plsql_profiler_runs
• (
• runid number primary key,
• related_run number,
• run_owner varchar2(32),
• run_date date,
• run_comment varchar2(2047),
• run_total_time number,
• run_system_info varchar2(2047),
• run_comment1 varchar2(2047),
• spare1 varchar2(256))

22
Units

• create table plsql_profiler_units(
• runid number references
  plsql_profiler_runs,
• unit_number number,
• unit_type varchar2(32),
• unit_owner varchar2(32),
• unit_name varchar2(32),
• unit_timestamp date,
• total_time number DEFAULT 0 NOT
  NULL,
• spare1 number,
• spare2 number,
• primary key (runid, unit_number)
• )

23
Data

• create table plsql_profiler_data (
• runid number,
• unit_number number,
• line number not null,
• total_occur number,
• total_time number,
• min_time number,
• max_time number,
• spare1 through spare4 number,
• primary key (runid, unit_number, line),
• foreign key (runid, unit_number) references
  plsql_profiler_units )

24
PL/SQL Profiling

• Oracle provides for
• Line by line timing.
• Max time
• Min Time
• Avg. Time
• of times a line is called

25
PL/SQL Profiling

• Some items missing
• Hierarchical data Call Stack
• Automatic rollups
• Must derive code coverage data

26

• OK enough of the boring background info
• HOW do we use it?

27
Simple Example

• Declare
• run_id NUMBER
• BEGIN
• run_id sys.dbms_profiler.start_profiler()
• xsql_orders_pack.add_order(10, 100.5)
• sys.dbms_profiler.stop_profiler()
• End

28
Getting the results

• select runid, run_owner, run_date, run_total_time
  from plsql_profiler_runs
• ID OWNER DATE TOTAL_TIME
• 1 XSQL 11-AUG-02 9865611690

29
Unit Results

• select unit_number, unit_type, unit_owner,
  unit_name from plsql_profiler_units where runid
  1
• Num type
  owner name
• 1 ANONYMOUS BLOCK
  
• 2 PACKAGE BODY XSQL
  XSQL_ORDERS_PACK
• 3 TRIGGER XSQL
  XSQL_ORDERS_LIST_BI

30
Unit times

• First we need to get the times into the unit
• and run tables.
• exec dbms_profiler.rollup_run(1)

31
Unit times

• Now we select the data and convert it to
• seconds.
• select unit_name, total_time / 1000000000 from
  plsql_profiler_units
• UNIT_NAME
  TOTAL_TIME/1000000000
• --------------------------------
  ---------------------
• .020763251
• XSQL_ORDERS_PACK .722392643
• XSQL_ORDERS_LIST_BI .021092901

32
Data

• Select pu.unit_name, pd.line, pd.total_occur,
• pd.total_time
• FROM PLSQL_PROFILER_DATA pd,
• PLSQL_PROFILER_UNITS pu
• WHERE pd.runid 1 and
• pd.unit_number pu.unit_number
• ORDER BY TOTAL_TIME DESC

33
Data

• UNIT_NAME LINE OCCUR TIME
• XSQL_ORDERS_PACK 20 1
  529093781
• XSQL_ORDERS_PACK 44 1
  68406180
• XSQL_ORDERS_PACK 29 1
  51942914
• XSQL_ORDERS_PACK 63 1
  49449301
• 5
  1 18986211
• XSQL_ORDERS_LIST_BI 21 4
  15548065

34
Data

• Select pu.unit_name, pd.line, pd.total_occur,
• pd.total_time
• FROM PLSQL_PROFILER_DATA pd,
• PLSQL_PROFILER_UNITS pu
• WHERE pd.runid 1 and
• pd.unit_number pu.unit_number
• ORDER BY TOTAL_OCCUR DESC

35
Data

• UNIT_NAME LINE OCCUR
  TIME
• XSQL_ORDERS_LIST_BI 21 4
  15548065
• XSQL_ORDERS_PACK 13 2
  2557866
• XSQL_ORDERS_PACK 22 2
  1727035
• XSQL_ORDERS_PACK 24 2
  4053587
• XSQL_ORDERS_PACK 33 2
  4214502
• XSQL_ORDERS_PACK 23 2
  6904788
• 4
  1 484139
• 5
  1 18986211
• 6
  1 1292901
• XSQL_ORDERS_PACK 15 1
  306463

36
Data

• Well thats great I know the line. Now
• what about the code that goes along with
• that line?

37
Data

• What about determining the amount of
• code actually executed?

38
Data

• In order to answer those questions we have
• a few different options.
• Write SQLs to join to ALL_SOURCE and
  ALL_TRIGGERS.
• Use the demo scripts as a start to some reports.
• Use Thomas Kytes modified source from Expert one
  on one.

39
More Complex

• Modify application source to start / stop
  profiling.
• Hijack a connection to turn profiling on.
• Logon / logoff triggers.

40
Code Coverage

• Code coverage data can be computed from
• the data present in the plsql_profiler_data
• table.
• Total of lines with a time 0 / total
• lines for each unit.
• Only executable lines are present.

41
Other uses

• QA can show where the performance problem lies.
  The developers can see the actual data.
• Can view differences in performance from
  different DBs.
• Can see what code is actually tested.

42
Summary