Re: Transaction monitoring

From: Robert Lockard <rpl999_at_hotmail.com>
Date: Sun, 6 Feb 2000 07:59:54 -0500
Message-ID: <87jrnk$6g0$1@bob.news.rcn.net>

For developing I use a util package I created, that allows the user to monitor the transactions in any one session.

In sqlplus run
Use in conjunction with

select a.action
,      a.module
,      b.block_gets
,      b.consistent_gets
,      b.physical_reads
,      b.block_changes
,      b.consistent_changes
from v$session a,
     v$sess_io b

where a.sid = b.sid
order by a.username;

call the procedure speed in your pl/sql TotKount NUMBER :=0;
CurrKount NUMBER :=0;
StartTime DATE := SYDATE;
Module VARCHAR2(35) := 'MY MODUAL';

BEGIN
  SELECT COUNT(*)
  INTO TotKount
  FROM X
  WHERE ....;   FOR REC IN C1 LOOP
    MY TRANSACTION;
    CurrKount := CurrKount + 1;
    speed(TotKount, CurrKount, StartTime, Module)   END LOOP;
END; ---     PROCEDURE Speed (TotKount NUMBER, CurrKount NUMBER, StartTime DATE, Module VARCHAR2) IS

    RunRate  NUMBER(9,2);
    RunTime  NUMBER(9,2);
    TimeLeft NUMBER(9,2);

    BEGIN
      RunTime := (((TO_DATE(TO_CHAR(StartTime,'HH24:MI:SS'),'HH24:MI:SS')
                            -

TO_DATE(TO_CHAR(SYSDATE,'HH24:MI:SS'),'HH24:MI:SS'))*86400)*-1);

      IF RunTime = 0 THEN
        RunTime := -1;  -- avoid devide BY zero
      END IF;

      RunRate := CurrKount / RunTime;

      IF RunRate = 0 OR RunRate = 1 THEN
        RunRate := -1;
      END IF;

      TimeLeft := (TotKount - CurrKount) / RunRate / 60;          -- Time

left in minutes

      DBMS_APPLICATION_INFO.SET_MODULE(module_name => Module, action_name => ' ' || TO_CHAR(CurrKount) || ' ' || TO_CHAR(TotKount) || ' ' || TO_CHAR(RunRate) || ' ' || TO_CHAR(TimeLeft));

    END Speed;

--
Robert P. Lockard

rob_at_oraclewizard.com

www.oraclewizard.com will be online in 57 days. New tools, resources, qualified positions for Independent Oracle Consultants, E-mail and more.

"Randy Reiter" <rreiter_at_sqlpower.com> wrote in message news:86as26$bar$1_at_bgtnsc01.worldnet.att.net...
> Refer to the Zero Impact Sql Monitor and Zero Impact Service Level Monitor
> at www.sqlpower.com.
>
> --
> Regards,
>
> Randy Reiter
>
> Sql Power Tools
> 702.990.3209
> www.sqlpower.com
>
> boo <boo_at_yahoo.com> wrote in message news:38874497.30649DB2_at_yahoo.com...
> > Running Oracle 8.0.5 on Solaris 2.7
> > I know I have seen this posted before, but is there a way to determine
> > transactions per minute in an Oracle Database?
> > This information is often requested by hardware vendors, and I haven't
> > found a way to determine this.
> >
> > Thanks for any help.
> >
> >
>
>
Received on Sun Feb 06 2000 - 06:59:54 CST