RE: Insert Rate/Second

From: mkb <mkb125_at_yahoo.com>
Date: Wed, 24 Aug 2005 11:25:15 -0700 (PDT)
Message-ID: <20050824182515.64976.qmail@web32801.mail.mud.yahoo.com>

• Bruce McCartney <bruce.mccartney_at_dbinfosystems.com> wrote:

> 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;
>

>-----------8<-------------  

Hi Bruce,

Thanks, one typo needed in the above: add a '/' here

> SYS.DBA_TAB_MODIFICATIONS A
> /' text_stuff

to make is work correctly.

Also, I think the time values need to be converted to seconds, no?

The final modified version of the select statement that I have is:

select 'create or replace view 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||'''))*24*60*60),2)

   ins_rate,
   round(A.UPDATES/((sysdate -
to_date('''||sysdate||'''))*24*60*60),2)

   upd_rate ,
   round(A.DELETES/((sysdate -
to_date('''||sysdate||'''))*24*60*60),2)

   del_rate ,
   round((A.INSERTS + A.UPDATES + A.DELETES)/((sysdate -

   to_date('''||sysdate||'''))*24*60*60),2) op_rate FROM
   SYS.DBA_TAB_MODIFICATIONS A
/' text_stuff
from dual
/

I tried it on a small test table by inserting about 7k records. Running the script, gives a result of about 230 inserts a second which doesn't seem right. It should about 7k rows/second as shown below:

18:15:23 SQL> /   7874 rows created.  

18:15:24 SQL> commit;

Maybe I should remove the conversion-to-seconds factor in the above SQL? But if I do, I would get insert rates in the millions/second. Now that don't seem right either.

Oh well, I'll keep hunting and see what comes up.

Appreciate all the help.

--
mohammed



		
____________________________________________________
Start your day with Yahoo! - make it your home page 
http://www.yahoo.com/r/hs 
 
--
http://www.freelists.org/webpage/oracle-l

Received on Wed Aug 24 2005 - 13:27:26 CDT