RSS Feed

Method R Discussion Group

I just got back from the OPINT class in dallas last week and have been trying to install the sql test harness on our 9.2.0.7 and discovered , it required a lot of tweaking as it does not work on 9i.

Can hotsos fix these and provide a release tested on 9i. Am listing some of the things I had to fix to get it to work (atleast partially).

* SQL*PLUS version 9 and below does not support spool {filename} replace , so I had to go in and remove the replace from do.sql, docmd.sql, start_harness.sql

* If installing under a different user than "hotsos", had to remove hotsos.hotsos_ilo_task references in do.sql

* hotsos_sysutil package has a bug in begin_task and end_task [v_db_ver != '8' or v_db_ver != '9' should be v_db_ver != '8' AND v_db_ver != '9', since dbms_monitor does not exist in ver 9 and below]

* [Problem with 8i only] with clause does not work in sql*plus versions 8.

Atleast these are the versions

I still dont have it working completely (issues with hbldstatdetail.sql giving numeric error. Is there a different version of the harness that needs to be downloaded for 10g??

Last line should read , is there a different version of the harness for 9i

Got everything to work with some more tweaking. here is one more thing that needs to be changed for anyone else that might be trying to get this to work on 9i in hbldstatdtl.sql and hscstatdtl.sql the stat_pos section of the query needs to be as below. (since there is no pr= and pw= in 9i) the p_start/p_end and w_start/w_end are not being used in the bigger query anyway
stat_pos as
(  select text,
          instr(text, 'id=') + 3 id_start,
          instr(text, 'cnt=') -1 id_end,
          instr(text, 'cnt=') + 4 cnt_start,
          instr(text, 'pid=') - 1 cnt_end,
          instr(text, 'pid=') + 4 pid_start,
          instr(text, 'pos=') - 1 pid_end,
          instr(text, 'op=') + 4 op_start,
          instr(text, '(cr=') - 1 op_end,
          substr(text, instr(text,'(cr=')) rowsource_stats,
          instr(text, '(cr=') + 4 cr_start,
          instr(text, ' r=')  cr_end,
          instr(text, ' r=') + 3 pr_start,
          instr(text, 'w=') - 1 pr_end,
          instr(text, 'w=') + 2 pw_start,
          instr(text, 'time=') - 1 pw_end,
          instr(text, 'time=') + 5 time_start,
          instr(text, 'us)') - 1 time_end,
          instr(text, 'p=') + 2 p_start,
          instr(text, 'w=') - 1 p_end,
            instr(text, 'w=') + 2 w_start,
            instr(text, 'time=') - 1 w_end
      from stat_lines
),

Well, it appears you found your way through getting harness version 3.0.1 running on version 9.  Good work!  The main functionality of this version of the harness should work on v9 and above, but many of the subordinate scripts have version 10 specific calls - that's why there are multiple scripts that do pretty much the same thing.  It is truly the intention to run v3+ of the harness on a version 10 database.  If you wish to run the harness on a database version prior to  10, we would suggest you download and use version 2.0.1 of the harness.  It is also available for download at the Free Downloads area on our portal (https://portal.hotsos.com/education/ed-free-downloads) and has a PDF installation guide to help you walk through getting it installed.  It's a bit different from v3 but doesn't implement the Hotsos ILO and has version specific setup actions that allow you to use the tool on versions from v8 forward.

We'll make sure to be more clear about the version specific differences in the future so that it doesn't present issues for others.  Thanks for your diligence and patience in working around the issues you found.

(https://portal.hotsos.com/education/ed-free-downloads)  is broken?