Toad World® Forums

plsql profiler


#1

some questions (i’m somewhat new to using profiler)

  1. in the tree, when i’ve selected a certain run, the packages/procedures/triggers/anonymous blocks are displayed. In what order are the objects displayed? it seems completely random (well, maybe not completely, most timetaking seem mostly to be on top and less timetaking mostly later, but not in exact order definitely).
  2. in the tree, some triggers are displayed 2, 3 or even more times (up to 11 times, in the particular case) not in the row, but for example, “biu trigger, then bu trigger then aud trigger” three times, then some packages, a couple of other table triggers and then again “biu trigger, bi trigger, ai trigger, biu trigger, bu trigger, aud trigger, biu trigger, bu trigger, aud trigger”. all trigger on the same table. (bu = before insert, aud = after update or delete etc)
  3. this is sloooow. It takes more than a minute to see the details (#of executeble lines = 25585, # of covered lines = 2996, # of packages = 42, # of triggers = 76 (actual much less, as triggers seem to be counted many times)) and every click on the package takes (depending of the size of it) couple of seconds to tens of seconds.
    when clicking on the run, sql monitor shows a lot of activity, and most of the time seems to go reading packages? what’s in there? why the need to go in there?
    Example from sql monitor:

Timestamp: 12:14:24.142
SELECT LINE, TEXT FROM ALL_SOURCE WHERE name=‘WWV_FLOW’ AND owner = ‘FLOWS_030100’ AND type = ‘PACKAGE BODY’ ORDER BY LINE

Timestamp: 12:14:36.861
SELECT HASH FROM SQLN_PROF_UNIT_HASH WHERE RUNID = 21 AND UNIT_NUMBER = 21

12 seconds reading an apex package body (the profiler was started and stopped in an apex session)?

  1. when i click on a run detail (a package body or trigger), it shows the details of the object, the coverage tab goes away and source viewer appears. What is the source viewer supposed to do? In every run in every code unit it shows “Source code is not available (has been modified)”. So it took two minutes reading all those package bodys for nothing? Or is it counting the number of total executable lines? Sorry, i don’t need that for such high time penalty (option not to calculate?).

  2. if from profiler options i check “display line data directly”, nothing changes. whats this option meant to do? (besides that when it’s checked, when run is selected (not the code units under that), time statistics display empty list and empty chart)

  3. if i uncheck “parse packages for subroutines” also nothing changes ( i didn’t see any subroutines before, when the option was checked, either)

  4. as all of this applies to 5.5 as well, it might be a wrong forum for this? (profiler not changed in 6.x at all?)


#2

Hi Andres,
The Profiler hasn’t been touched for a long time so there wasn’t any changes in terms of features.

  1. if the summary tab is being shown, clicking on a package or trigger won’t make it change to Source viewer. If after the run is generated and the code of the object is changed, it will give the message ‘source code is not available’. I don’t see any significant delay but maybe it’s just the test I ran was too simple.

  2. The difference can be seen in the Time Statistics > Chart tab when the package is selected. If the option is unchecked, the vertical axis will contain the routines (member function/procedure of the package). Otherwise, it will contain line numbers.

Gwen

Message was edited by: Gwen


#3

Then there must be a problem reading source code. Because the message “Source code is not available(has been modified)” is there for EACH and EVERY package, procedure, trigger etc, although nothing has changed. Also in Time Statistics => chart tab i see only line numbers whether the option “Parse packages for subroutines” is checked or not. Also maybe triggers showing up multiple times and unknown sort order could be fixed?


#4

Are you sure that nothing has changed? sometimes, Oracle marks the package/proc/func as invalid when any object that it depends on get changed. So maybe you make change to objects that are related to those.

Time statistics -> chart tab is for ‘Display Line Data Directly’ option.

I haven’t been able to reproduce the issue where triggers showing up multiple times. I will try again tomorrow.

Gwen


#5

Yes, i am very sure nothing has changed. (let me repeat: ALL, EVERY code unit is displayed as “Source code is not available(has been modified)”)
and sorry, no luck playing with Display line data directly. But this may be related to the thing “source code is not available”? - when no source code can be read, no procedures from package can be shown?

running as dba with dba views enabled, the packages are also under the same schema

Message was edited by: Andres


#6

one more feature request - the tree offering runs should also show run owner, filtering would be even better.


#7

The objects in the tree are sorted according to the order that they get executed. In my test, even though the same object got called twice, it’s only listed once in the list. I’m not sure why the triggers are listed multiple times.
Could you provide some insights into the structure of the package and the order that the objects were called.

Gwen


#8

ok, the executing order in the tree makes some sense.
all objects seem to be listed once, only triggers multiple times.
as there are many plsql packages involved i don’t know what insights you mean.
Package1proc1 calling package2proc2 in a loop calling package3proc3 via execute immediate making row insert where in a bu trigger package4 is called which does a bunch of checks using package 5 and 6 and inserts on its own (to different tables of course)… and then some updates on the freshly inserted rows that cascade about the same amount of code.


#9

Hi

I found out that the “profile options” window always opens in a size too small to show it all.
Even after resizing, next time it will open again in that initial (small) size

Regards

Filipe


#10

Hi all,

All issues related to the Profiler will be looked at in the next version.

Gwen