Re: Database server and SQL tuning

Tuhin Kumar wrote:
> Hi,
> I have two tables as shown below
>
> CREATE TABLE DummyTbl1
> (
> Id NUMBER(10),
> Type NUMBER(10),
> ComponentId NUMBER(10),
> CompType NUMBER(10),
> CompNum NUMBER(10),
> CompParentNum NUMBER(10),
> CompDBId NUMBER(10),
> TimeStamp DATE,
> Severity NUMBER(10),
> Category NUMBER(10),
> State NUMBER(10),
> CauseType NUMBER(10),
> CauseCode NUMBER(10),
> UnitType NUMBER(10),
> UnitId NUMBER(10),
> UnitName VARCHAR2(256),
> UnitDBId NUMBER(10),
> ServerName VARCHAR2(256),
> AckTimeStamp DATE,
> DelTimeStamp DATE,
> StrParam VARCHAR2(256),
> CustomerId NUMBER(10),
> ParentCustId NUMBER(10),
> ExtraParam1 NUMBER(10),
> ExtraParam2 NUMBER(10),
> ExtraParam3 NUMBER(10),
> ExtraStrParam VARCHAR2(256),
> futureInt1 NUMBER (10),
> futureInt2 NUMBER (10),
> futureInt3 NUMBER (10) ,
> futureInt4 NUMBER (10) ,
> futureStr1 VARCHAR2(256) ,
> futureStr2 VARCHAR2(256)
> );
>
> CREATE TABLE DummyTbl2
> (
> Id NUMBER(10),
> ComponentId NUMBER(10),
> CustomerId NUMBER(10),
> ProfileId NUMBER(10),
> ParentProfileId NUMBER(10),
> String LONG RAW,
> ScriptName VARCHAR2(256),
> SrcIPAddress NUMBER(10),
> DestIPAddress NUMBER(10),
> SrcPort NUMBER(10),
> DestPort NUMBER(10),
> SessionId NUMBER(10),
> IFId NUMBER (10),
> SessionDir NUMBER (10),
> RuleId NUMBER (10),
> CustomerName RAW(512),
> futureInt1 NUMBER (10),
> futureInt2 NUMBER (10),
> futureInt3 NUMBER (10),
> futureInt4 NUMBER (10),
> futureStr1 VARCHAR2(256),
> futureStr2 VARCHAR2(256)
> );
>
> I also have a sequence
> CREATE SEQUENCE dummyid_seq START WITH 1 INCREMENT BY 1;
>
> The queries I am doing per transaction are as below
>
> 1)SELECT dummyid_seq.NextVal FROM Dual;
>
> 2)INSERT INTO DummyTbl1 (Id, ComponentId, CompNum, CompType,
> CompParentNum, CompDbId, TimeStamp, Severity, Category,
> State, CauseType, CauseCode, UnitType, UnitId, UnitName, UnitDBId,
> ServerName, StrParam, CustomerId, ParentCustId,
> ExtraParam1, ExtraParam2, ExtraParam3, ExtraStrParam,Type) VALUES(
> 186,805322753, 1, 3, 1, 1,
> TO_DATE('2004-05-06 10:26:07', 'YYYY-MM-DD HH24:MI:SS'), 3, 2, 2, 0,
> 10, 0, 0, '',0, 'ipe3k_vpn',
> 'FTP_CTRL', 18, 0,1,1,0,'H',3);
>
> 3)INSERT INTO DummyTbl2 (Id, ComponentId, CustomerId, ScriptName,
> ProfileId, String, ParentProfileId,
> SrcIPAddress, DestIPAddress, SrcPort, DestPort, SessionId, IFid,
> SessionDir, RuleId, CustomerName) VALUES ( 186,
> 805322753, 18, '', 0, 'ABCDABCDABCDABCD', 0, 3232248321, 2886739969,
> 1691, 80, 2986, 1, 1, 1023, 'ABCDABCDABCDABCD');
>
> 4)COMMIT;
>
> The no. of transactions that I am able to achieve are 71 per seconds,
> whereas I need of the magnitude of 5000 per sec.
> Please let me know of all those things I need to tune for a better
> performance.
>
> Thanks,
> Tuhin

In addition to what the others have said, I've got the nasty feeling that you're doing this whole stuff in 4 separate steps from the client. DON'T DO IT. You're network is going to be a large contributing factor to the overall performance.

Wrap the 4 steps into a stored procedure or better a package, use bind variables instead of literals and put the dummyid_seq.nextval into the insert. At the second insert, you'll probably have to use dummyid_seq.currval.

And for a magnitude of 5000 per second, would you care to describe what it is exactly what you want to do? This is not a bulk load, is it?

My 2c
Holger Received on Fri May 07 2004 - 03:05:06 CDT