How to increase oracle insert query speed?

Present oracle insert query speed through delphi tool i can get 600- 800 record inserts per second. i wnat more inserts per second ( expecting 1500-2000 per second).
Hardware conifgurations : Hp Proliant ML 350 G6 E 5560 Processor, 16 GB RAM, 146x 8 SAS HDD 10k RPM. OS :windows 2008 R2, Database Oracle 11g.
kbapireddyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
can you post your code?

are you using append hint?  is space preallocated?  are you using bulk inserts? is your array size large enough?
0
kbapireddyAuthor Commented:
0
Wasim Akram ShaikCommented:
bapireddy, is this an oracle procedure?

If this is an oracle procedure, then i didn't understand the code.
definitely @sdstuber will help you...

if this is not an oracle procedure, then i think you can handle your code on button click you can just invoke  a call to procedure which will then do the bulk inserts by passing those values to a  plsql table in runtime and use forall statement
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

kbapireddyAuthor Commented:
This is delphi code. From Delphi Application(client)  to oracle database server.
0
jogosCommented:
Oracle is ages ago,  but give it a try.

Truncate table means that space must be allocated during inserts -> allocate apropriate space before you start

If you put the insert in a procedure in your orace db and pass it the parameters to prevent some overhead of parsing the insert statement.  

I see Doquery is depreciated
http://docs.oracle.com/cd/E13155_01/wlp/docs103/javadoc/com/bea/query/QueryManager.html
0
sdstuberCommented:
you are looping doing one sql statement per row inserted.

use bulk insert (array/dataset processing)

also, I don't know how doquery works,  is it issuing a commit for each insert?  if so, that will also slow down the operations.

0
agodfrinCommented:
You should take a full server-side SQL trace, making sure to include wait events. This will tell you exactly where the time is spent.

Glancing at your code I can tell you that a major slowdown is the fact that you do not use any bind variables in your insert (all values are constants). This means that you will need a full hard parse for each insert. The cost of that hard parse is going to be much larger than the actual insert.

I am not sure what you hope to achieve by tweaking the COMMIT_WRITE parameter. This is one that is better left alone.

Check also what doQuery() does in terms of frequency of commits. Hopefully it will not commit each insert: this is also guaranteed to make your inserts slow.
0
jogosCommented:
Indeed avoid looping and executing same statement if you can and do a bulk/rowset action that will dramaticly increase speed .  
0
jayeshshahCommented:
some suggestions

1. If there are any triggers on this table, try disabling the trigger and then re-enable them. Same with the foreign keys.
2. Also try to use sqlloader if you have a specific file format. This is much quicker.
0
DavidSenior Oracle Database AdministratorCommented:
Likewise disable or defer your index and other constraints if feasible.  As a DBA I would probably glance at the redo log sizing and turnover frequency, if you have to have transaction logging in the first place.
0
Geert GOracle dbaCommented:
you have a few problems which cause performance loss:

1: (smaller issue) you are calling a procedure which probably creates a query component at runtime
Does DoQuery contain a TQueryType.Create and Destroy ?
If Yes, then place the query object inside the procedure (if this is part of your framework, then leave it, but extend it with parameters)
I need to see your DoQuery procedure to give better advice ...

2: What are you using as Query type ? Ado is slow
> I use DevArt's Odac, which is very fast.
see www.devart.com for delphi query tools

3: You are creating unique queries for oracle everytime, because you are not using parameters
> this forces the oracle database to have to parse every statement as a new one
Check this sample:
 
procedure TForm1.btnInsertClick(Sender: TObject);
var
  t, str1,str2,str3 :string;
  i:Integer;
  Start_time,end_time,Diff_time : tdatetime;
  Query: TOraQuery;
begin
  PktCntFrmFep:=0;
  case  rgCommitWriteMode.ItemIndex of
    0: str2:='WAIT';
    1: str2:='NOWAIT';
    2: str2:='BATCH';
    3: str2:='IMMEDIATE';
    4: str2:='BATCH,WAIT';
    5: str2:='BATCH,NOWAIT';
    6: str2:='IMMEDIATE,WAIT';
    7: str2:='IMMEDIATE,NOWAIT';
    ELSE str2:='WAIT';
  end;
  str1 := 'ALTER SESSION SET COMMIT_WRITE='''+ str2+'''';
  doquery(str1);
  str3 := 'TRUNCATE TABLE commit_test';
  doquery(str3);
  Start_time := now;
  memo1.lines.add( FormatDateTime('"Start: " hh:nn:ss.zzz', Start_time));
  Query := TQuery.Create(Self);
  try
    Query.Session := OraSession;
    Query.AutoCommit := False;
    Query.SQL.Text := 
      'insert into commit_test(col1,col2,col3,col4,col5,dob,creation_date) values(:COL1, :COL2, :COL3, :COL4, :COL5, :DOB, :DT);
    Query.Prepare;  // this lets oracle parse the query 
    for i:= 1 to strtoint(cmbPostingRecords.Text) do
    begin
      Query.ParamByName('COL1').AsInteger := I;
      Query.ParamByName('COL2').AsString := Format('aaa%d', [I]);
      Query.ParamByName('COL3').AsString := Format('aaa%d', [65535-I]);
      Query.ParamByName('COL4').AsString := Format('%d', [I*2]);
      Query.ParamByName('COL5').AsString := Format('%d.%d', [I, I]);
      Query.ParamByName('DOB').AsString := Format('TO_DATE(''%s'', ''dd-mon-yyyy hh24:mi:ss''', [FormatDateTime('dd-mm-yyyy hh:nn:ss', Now)]);
      Query.ParamByName('DT').AsString := Format('TO_DATE(''%s'', ''dd-mon-yyyy hh24:mi:ss''', [FormatDateTime('dd-mm-yyyy hh:nn:ss', Now)]);
      Query.ExecSQL;
    end;
    Query.Session.Commit;
  finally
    Query.Free;
  end;
  end_time := now;
  memo1.Lines.add( FormatDateTime('"Stop: " hh:nn:ss.zzz', end_time));
  memo1.Lines.add( FormatDateTime('"Diff: " hh:nn:ss.zzz', Start_time - end_time));
  memo1.Lines.add( cmbPostingRecords.Text + 'Records Posted of COMMIT_WRITE = '+str2 +   DateTimeToStr(Now-start_time));
end;

Open in new window


4: By default, delphi commits each statement
In the sample i switched this off, and executed a commit after the loop

5: how is the oracle tnsnames setup ? are you using dedicated ora shared connections ?
> if you don't have enough connections and have a shared server setup, you may be waiting on a connection every time you run doquery ...
> are you keeping your connection open, or reconnecting for every insert ?


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kbapireddyAuthor Commented:
Geert_Gruwez:

5: how is the oracle tnsnames setup ? are you using dedicated ora shared connections ?
> if you don't have enough connections and have a shared server setup, you may be waiting on a connection every time you run doquery ...
> are you keeping your connection open, or reconnecting for every insert ?

Dedicated Server, Open connection
0
Geert GOracle dbaCommented:
have you got the basics of the DoQuery ?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.