RSS Feed

Method R Discussion Group

Since the DBMS_PROFILER provides timings per line of PL/SQL, will these timings be integrated into ILO?

ILO and DBMS_PROFILER basically have two different end games in mind.

DBMS_PROFILER, as was outlined in the webinar, shows execution counts and durations per line of a given PL/SQL block (whether anonymous block, procedure, function or package). Because of the measurement intrusion factor, this is useful for debugging and troubleshooting, but not something you would want to do on an ongoing basis in a production environment.

HOTSOS_ILO's aim is to make it easier to gather properly scoped trace data for a given SESSION, whether it includes PL/SQL or not. The SLA Manager extension to ILO captures begin and end timestamps for instrumented tasks in an ILO_RUN table with a goal of less than a 5% intrusion factor for the privilege of getting that data.

In my opinion, the way that HOTSOS_ILO and DBMS_PROFILER should be used together is this:

If you notice a disturbing trend in the Response time data (R data) of a particular task that was gathered by ILO (or indeed in the trace data generated by ILO), then it would absolutely be prudent to use that information to drive using DBMS_PROFILER and DBMS_TRACE to troubleshoot the particular PL/SQL packages that were identified when tracing the task.

I don't, however, believe that there should be a hard link between ILO and DBMS_PROFILER. There are numerous instances where I could envision where ILO is used where there might be no PL/SQL what so ever. For instance, a C# program that makes a connection to ORACLE. No PL/SQL might be used in the execution of a real business task, but instead the programmatic logic is in the C# code its self.

I hope this helps to answer the question.

Please don't hesitate to ask for follow-up.