Home » SQL & PL/SQL » SQL & PL/SQL » MAX INSERT PER SECOND (ORACLE 10G)
MAX INSERT PER SECOND [message #320839] Fri, 16 May 2008 07:28 Go to next message
ddbbaa
Messages: 4
Registered: May 2008
Junior Member
Hi all,

somebody could say me how i estimated the max rows that a user can insert in a Oracle 10G XE database por second (only a user is connected at the same time)?

Thanks.
Re: MAX INSERT PER SECOND [message #320844 is a reply to message #320839] Fri, 16 May 2008 07:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The answer will depend on how large the row is, how many indexes it has, how many constraints there are on the table, how many triggers there are on the table, what the configuration of the underlying disks is, what the processor speed is, and several other factors I haven't thought of.

Why do you need to know?
Re: MAX INSERT PER SECOND [message #320845 is a reply to message #320839] Fri, 16 May 2008 07:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Create a table without 1,000,000 rows.
Execute:
set timing on
update mytable set mycol=mycol*2
and see the time.

Regards
Michel
Re: MAX INSERT PER SECOND [message #320846 is a reply to message #320845] Fri, 16 May 2008 07:49 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It also depends on how you do the inserts.
Run this on your system and it'll give you two very different times for inserting the rows:
create table insert_test (run_id number, row_id number,tstamp  number(10,0));

begin
  delete insert_test;

  for i in 1..100000 loop
    insert into insert_test values (1,i,dbms_utility.get_time);
  end loop;
    
  insert into insert_test (select 2,level,dbms_utility.get_time from dual connect by level <= 100000);
 
end;
/

select run_id, max(tstamp)-min(tstamp)
from   insert_test
group by run_id;
Re: MAX INSERT PER SECOND [message #320849 is a reply to message #320846] Fri, 16 May 2008 07:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry, I read "update" instead of "insert", who know why? Embarassed

Regards
Michel
Re: MAX INSERT PER SECOND [message #321130 is a reply to message #320846] Mon, 19 May 2008 03:25 Go to previous message
ddbbaa
Messages: 4
Registered: May 2008
Junior Member
Thank you very much for your help. It was just what i needed.
Previous Topic: Returning Value when i calling insert Query from Java
Next Topic: Fcopy operation cannot be performed
Goto Forum:
  


Current Time: Sat Nov 02 12:28:48 CDT 2024