Re: Database server and SQL tuning

"Tuhin Kumar" <tkumar_at_ipolicynet.com> wrote in message news:e4ad76f5.0405060705.338e14bd_at_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

You must use bind variables. (say that 3 times) Also if you can create the cursor,parse it once, bind, execute, rebind, execute,.. You give us no version information, no hardware information, no explain plans, no useful information at all. Where is the bottleneck? Jim Received on Thu May 06 2004 - 19:07:23 CDT