RSS Feed

Method R Discussion Group

Hi Hotsos,

I sincerely appreciate all the efforts Hotsos and its staff make and their achievements. This is merely to point out that ILO does not do what the Hotsos web site
says it does and to hopefully initiate development of a better version (details after the enhancements).

I have the following enhancement requests for ILO:
1. allow to set max_dump_file_size (certain size or unlimited)
2. allow to set trace_file_identifier
3. fix issue below (set v$session.client_identifier with dbms_session)
4. allow tracing without binds. Currently no control over bind tracing. Loss of
  functionality vs. dbms_support/dbms_monitor
5. clarify documentation concerning v$session.client_info and
  v$session.client_identifier

client_identifier issue:
On page https://portal.hotsos.com/products/hotsos_ilo the following is stated:
Using BEGIN_TASK and END_TASK does all the Oracle housekeeping your application needs. All that stuff you’ve ever heard about DBMS_APPLICATION_INFO and DBMS_SESSION… Now you can forget about it. All you have to remember is to mark your tasks.

In file readme.txt in ILO package HOTSOS_ILO_1.3 the following is stated:
Developers no longer need to worry about understanding when or why to use
    DBMS_APPLICATION_INFO or DBMS_SESSION to register parts of their
    applications with the Oracle database. [...]
HOTSOS_ILO sets a standard for using DBMS_APPLICATION_INFO and DBMS_SESSION
  to register database calls, and for using Oracle's extended SQL trace
  feature to profile the performance of specified business tasks.[...]
Astonishingly, ILO does not use DBMS_SESSION at all:
grep -i dbms_session hotsos*
hotsos_ilo_task.pks:--  DBMS_APPLICATION_INFO and DBMS_SESSION. Now you can forget about it. All you
The only reference to DBMS_SESSION is a comment.

The documentation in file Hotsos_Ilo_Task.html states this:
•  The CLIENT_IDENTIFIER should be more technical information or commentary that will be displayed in the V$SESSION view when this BEGIN_TASK is executed.
 
&#38;#8226;  If the CLIENT_IDENTIFIER has not been set then a the default client_id will be set to: <Client's OS User>~<Client's IP Address>~<Client's program>~<Service being accessed>. For example, if JSmith is on a PC at IP address 192.168.1.40, using SQL*Plus to access the instance through the service name ORCL10G, then the default client identifier will be: JSmith~192.168.1.40~SQLPLUS.exe~ORCL10G 

This suggests that V$SESSION.CLIENT_IDENTIFIER gets set by calling HOTSOS_ILO_TASK.BEGIN_TASK, however this is not the case. HOTSOS_ILO_TASK.BEGIN_TASK sets V$SESSION.CLIENT_INFO which is useless as far as
trcsess and v$client_stats are concerned.

sqlplus ndebes

SQL> col module format a25
SQL> col action format a15
SQL> col client_info format a20
SQL> col client_identifier format a20
SQL> set linesize 120
SQL> set null <NULL>
SQL> select module,action,CLIENT_INFO,CLIENT_IDENTIFIER from v$session where sid=(select sid from v$mystat where rownum=1);

MODULE                    ACTION          CLIENT_INFO          CLIENT_IDENTIFIER
------------------------- --------------- -------------------- --------------------
SQL*Plus                  <NULL>          <NULL>              <NULL>

SQL> exec hotsos_ilo_task.begin_task(module=> 'mymod', action=>'myact', client_id=> 'myclient', comment=>'no comment');

PL/SQL procedure successfully completed.

SQL> select module,action,CLIENT_INFO,CLIENT_IDENTIFIER from v$session where sid=(select sid from v$mystat where rownum=1);

MODULE                    ACTION          CLIENT_INFO          CLIENT_IDENTIFIER
------------------------- --------------- -------------------- --------------------
mymod                    myact          myclient            <NULL>

SQL> exec dbms_session.set_identifier('dbms_session.set_identifier')

PL/SQL procedure successfully completed.

SQL> select module,action,CLIENT_INFO,CLIENT_IDENTIFIER from v$session where sid=(select sid from v$mystat where rownum=1);

MODULE                    ACTION          CLIENT_INFO          CLIENT_IDENTIFIER
------------------------- --------------- -------------------- --------------------
mymod                    myact          myclient            dbms_session.set_ide
                                                              ntifier

Conclusion: HOTSOS_ILO_TASK fails to set V$SESSION.CLIENT_IDENTIFIER. Thus, it does not live up to its promise (see quotes from documentation above). Developers and DBAs who would like to benefit from 10g DBMS_MONITOR.CLIENT_ID_STAT_ENABLE or who would like to use 10g trcsess clientid=<v$session.client_identifier> to combine trace files need to call DBMS_SESSION on top of HOTSOS_ILO_TASK. The code in file hotsos_ilo_task.pkb calls DBMS_APPLICATION_INFO.SET_CLIENT_INFO but not DBMS_SESSION.SET_IDENTIFIER.
Note: the call to DBMS_SESSION.SET_IDENTIFIER causes insertion of a line like this into the trace file:
*** CLIENT ID:(dbms_session.set_identifier) 2007-01-25 16:52:05.017
Such lines can be used by trcsess to combine several files with the same CLIENT_IDENTIFIER into a single file for further processing by TKPROF.

Please comment.

Kind regards,
Norbert Debes

Norbert,

First of all, please let me apologize for the time it has taken me to answer your post. We were right in the middle of making some significant changes to ILO for version 1.4, and I wanted to take into account the things you had brought to our attention.

First and foremost, regarding the CLIENT_IDENTIFIER issue that you raised. You were absolutely correct to point out that the data entered into the CLIENT_ID parameter of the BEGIN_TASK procedure call was going into the wrong area. This has been rectified as of ILO V1.4 and now uses DBMS_SESSION.SET_IDENTIFIER to register the data correctly.

The new version of the packages also takes into account that the DBMS_SESSION.SET_IDENTIFIER call was not present in version 8i, and does the call dynamically depending on the database version.

I will also check the documentation to make sure that the new functionality is complete and accurate.

I will also submit your requests (No's 1 and 2) for a method to set the MAX_DUMP_FILE_SIZE and TRACE_FILE_IDENTIFIER and they will be in the queue of features for the next release.

Request number 4 is something that I will need to discuss with Cary Millsap to make sure it doesn't go against the design principles he had in mind for ILO. I'll register the request so that we all can discuss this via the correct channels.

Thanks and I hope that I've addressed all of your concerns.

The decision not to allow any tracing level other than BINDS=>TRUE and WAITS=>TRUE is a specification decision.

My staff and I teach our students to trace only with both BINDS=>TRUE and WAITS=>TRUE. I've seen your mail message referring to the application that never uses SQL value placeholders and hence never benefits from BINDS=>TRUE. However, even such an application as you have described will use bound values in the recursive SQL called beneath the application to manipulate the Oracle data dictionary. Without those values in a trace file, you would have an incomplete diagnostic data collection, which completely defeats the principal purpose of having ILO to begin with.

One of the advantages of open source software is that of course you can change its behavior if you like. But my intent with the specification of ILO as it is today is that nobody will use BINDS=>FALSE without conscious effort.