Re: Database server and SQL tuning

On 6 May 2004 08:05:29 -0700, tkumar_at_ipolicynet.com (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

Kinda hard to say without knowing some critical information. Like:

Operating system?
Oracle Version?
Where is the bottleneck?

- what did an EXPLAIN PLAN on the statements show?
- what did tkprof say about your trace?
- what does statspack report?

Without knowing any of that, it's pretty much like answering the question "how long is a piece of string?"

I can say one thing . . . not using bind variables is an absolute killer for scalability. If you quoted your statements exactly, then you need to fix that problem first. Received on Thu May 06 2004 - 11:00:50 CDT