Oracle fetches 6000/sec?

From: Ming Luo <ming_at_sol1.lrsm.upenn.edu>
Date: 16 Feb 1999 22:25:23 GMT
Message-ID: <7acr8j$n4p$1@netnews.upenn.edu>

I tested the performance of an Oracle installed in a Windows NT server. I found Oracle let me fetch about 6000 records per second. Could somebody please enlighten me how to improve this?

The followings are the table and stored procedure I used in the testing.

Thanks.

---

• Create the test table

CREATE TABLE Port
(PortID INTEGER,
 EquipID INTEGER,
 TplNdx INTEGER,
 TplPtID INTEGER,
 RxFrom INTEGER,
 CONSTRAINT Pk_PortID PRIMARY KEY (PortID));

---

• Create the stored procedure

CREATE OR REPLACE PACKAGE FetchSpeed
IS
  PROCEDURE LoopTest;
END FetchSpeed;
/

CREATE OR REPLACE PACKAGE BODY FetchSpeed IS
  PROCEDURE LoopTest
  IS
    nPortID number := 1;
    nRxFrom number := 0;
  BEGIN
    FOR i IN 1 .. 6000 LOOP

      IF nPortID > 100 THEN  
        nPortID := 1;
      ELSE
        nPortID := nPortID + 1;
      END IF;

      SELECT RxFrom 
        INTO nRxFrom 
        FROM Port
       WHERE PortID = nPortID;

    END LOOP;
  END LoopTest;
END FetchSpeed;
/

---

SET TIMING ON
BEGIN
  FetchSpeed.LoopTest;
END;
/
Received on Tue Feb 16 1999 - 16:25:23 CST