Database server and SQL tuning

From: Tuhin Kumar <tkumar_at_ipolicynet.com>
Date: 6 May 2004 08:05:29 -0700
Message-ID: <e4ad76f5.0405060705.338e14bd@posting.google.com>

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 Received on Thu May 06 2004 - 10:05:29 CDT