Re: Oracle performance under NT

From: Andrew Protasov <oracle_at_protasov.kiev.ua>
Date: Mon, 7 Dec 98 15:57:59 +0200
Message-ID: <AKdxzQsK42@protasov.kiev.ua>

Hi,

I think it depends on how many indexes has your table, what are the constraints, are there any triggers on this table, do you make additional checks on client and in stored packages, how client is implemented, what is the sorce of the data and so on. You should provide additional details. But in any case you must develop test and run it against reasonable complex test data.

There is no problem to insert hundreds of records per second in PL/SQL code into non-indexed table on NT. But I do not believe that it is your case. This is on PII/233 with Quantum Fireball TM IDE HDD and Oracle 8.0.3:

SVRMGR> CREATE TABLE TESTIND
     2> ( OPERATOR VARCHAR2(30)
     3> , LINE     INTEGER
     4> , TEXT     VARCHAR2(2000)
     5> )
     6> STORAGE(INITIAL 1M NEXT 1M PCTINCREASE 0);
Statement processed.
Parse             0.21 (Elapsed)     0.00 (CPU)
Execute/Fetch     0.00 (Elapsed)     0.00 (CPU)
Total             0.21               0.00
SVRMGR>
SVRMGR> BEGIN
     2>  FOR I IN 1..50000 LOOP
     3>   INSERT INTO TESTIND(OPERATOR,LINE,TEXT)
     4>    VALUES(USER,I,LPAD(TO_CHAR(I),200));
     5>  END LOOP;
     6> END;
     7> /
Statement processed.
Parse             0.24 (Elapsed)     0.00 (CPU)
Execute/Fetch    54.09 (Elapsed)     0.00 (CPU)
Total            54.33               0.00

SVRMGR> COMMIT;

Statement processed.
Parse             0.00 (Elapsed)     0.00 (CPU)
Execute/Fetch     0.05 (Elapsed)     0.00 (CPU)
Total             0.05               0.00
SVRMGR>
SVRMGR> SELECT COUNT(*) FROM TESTIND
     2> WHERE OPERATOR=USER
     3> ORDER BY LINE;

COUNT(*)

---

     50000
1 row selected.

Parse             0.00 (Elapsed)     0.00 (CPU)
Execute/Fetch    12.03 (Elapsed)     0.00 (CPU)
Total            12.03               0.00

SVRMGR>
SVRMGR> UPDATE TESTIND SET TEXT=TEXT WHERE OPERATOR=USER; 50000 rows processed.

Parse             0.00 (Elapsed)     0.00 (CPU)
Execute/Fetch    66.61 (Elapsed)     0.00 (CPU)
Total            66.61               0.00

SVRMGR> COMMIT;

Statement processed.
Parse             0.00 (Elapsed)     0.00 (CPU)
Execute/Fetch     0.03 (Elapsed)     0.00 (CPU)
Total             0.03               0.00
SVRMGR>
SVRMGR> DELETE FROM TESTIND
     2> WHERE OPERATOR=USER;
50000 rows processed.
Parse             0.00 (Elapsed)     0.00 (CPU)
Execute/Fetch    59.28 (Elapsed)     0.00 (CPU)
Total            59.28               0.00

SVRMGR> COMMIT;

Statement processed.
Parse             0.00 (Elapsed)     0.00 (CPU)
Execute/Fetch     0.02 (Elapsed)     0.00 (CPU)
Total             0.02               0.00
SVRMGR>
SVRMGR> SELECT LINE,TEXT FROM TESTIND
     2> WHERE OPERATOR='abcde'
     3> ORDER BY LINE;
LINE       TEXT
---------- --------------------------------------------------------------------------------
0 rows selected.
Parse             0.00 (Elapsed)     0.00 (CPU)
Execute/Fetch     9.41 (Elapsed)     0.00 (CPU)
Total             9.41               0.00
SVRMGR>
SVRMGR> SELECT LINE,TEXT FROM TESTIND
     2> WHERE OPERATOR=USER
     3> ORDER BY LINE;
LINE       TEXT
---------- --------------------------------------------------------------------------------
0 rows selected.
Parse             0.01 (Elapsed)     0.00 (CPU)
Execute/Fetch     2.73 (Elapsed)     0.00 (CPU)
Total             2.74               0.00

SVRMGR>
SVRMGR> DROP TABLE TESTIND;

Statement processed.
Parse             0.88 (Elapsed)     0.00 (CPU)
Execute/Fetch     0.01 (Elapsed)     0.00 (CPU)
Total             0.89               0.00

Andrew Protasov

> Does anybody have any performance statistics for Oracle running on NT ? We
> are considering a system that will have to perform about 40 table inserts
> per second for a period every day. The inserts are about 6 attributes each.
> We basically want to know whether this sort of performance can be achieved
> on an NT platform, or if we need to look to Unix.
>
> Any case histories are appreciated
>
> Stephen Vear
> vears_at_logica.com
>
>
>
>
>
Received on Mon Dec 07 1998 - 07:57:59 CST