RE: Insert Rate/Second

From: Bruce McCartney <bruce.mccartney_at_dbinfosystems.com>
Date: Tue, 23 Aug 2005 19:28:49 -0600
Message-ID: <!~!UENERkVCMDkAAQACAAAAAAAAAAAAAAAAABgAAAAAAAAAkEdZ3vpKf0Cnkb171va42cKAAAAQAAAACzvKL+aF9kWC3qfjMosOCAEAAAAA@dbinfosystems.com>

Hi Mohammed,

If you want an accurate reading by table you can use dba_tab_modifications; something like this, I used SYSTEM so had to grant access:

grant all on SYS.DBA_TAB_MODIFICATIONS to system with grant option;

#!/bin/ksh
DBAPASS=<your password>
unset SQLPATH
sqlplus -S /nolog <<EOF
connect system/$DBAPASS
set pagesize 0 echo off feedback off termout off spool doit.sql
select 'alter table '||owner||'.'||table_name||' monitoring;' from dba_tables
where owner not in ('SYS','SYSTEM') -- others you want to exclude too
/

-- have to make sure use have detailed nls_date_format

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

• this view includes the current time that monitoring was turned on. This allows you to compute a rate at any time in the future. select 'create or replace view system.tab_modifications as SELECT A.TABLE_OWNER , A.TABLE_NAME , A.INSERTS , A.UPDATES , A.DELETES , A.TIMESTAMP , A.TRUNCATED, A.INSERTS + A.UPDATES + A.DELETES operations, round(sysdate - to_date('''||sysdate||'''),8) mod_time, round(A.INSERTS/(sysdate - to_date('''||sysdate||''')),2) ins_rate, round(A.UPDATES/(sysdate - to_date('''||sysdate||''')),2) upd_rate , round(A.DELETES/(sysdate - to_date('''||sysdate||''')),2) del_rate , round((A.INSERTS + A.UPDATES + A.DELETES)/(sysdate - to_date('''||sysdate||''')),2) op_rate FROM SYS.DBA_TAB_MODIFICATIONS A ' text_stuff from dual
  /
  exit EOF

sqlplus /nolog <<EOF >${owner}monon.out 2>&1 connect system/$DBAPASS
set timing on echo on feedback off
set serveroutput on size 1000000 format wrapped exec dbms_lock.sleep(30)
@doit.sql
/

exit
EOF rm doit.sql

Bruce McCartney  

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Aug 23 2005 - 20:30:59 CDT